腾讯云数据库相关问答&续

腾讯云 2017年05月03日浏览664

摘要：

Q1 腾讯云的数据库什么时候可以实现异地容灾备份？比如上海机房的数据可以实时同步至广州机房

目前CDB For Mysql已经推出灾备实例功能，简单几步配置就能实现异地容灾热备。可通过以下链接了解：https://www.qcloud.com/doc/product/236/6397
希望以上回答能帮到您。

Q2 一直写C#，用Access做单机数据库，已正常使用多年。现增加功能，联网多机共享部分数据。
没有使用网络数据库经验，现疑问：
１：何为内网访问，何为外网访问？云数据库不是外网访问模式吗？
２：C#如何连接腾讯云MySQL？文档里的例子不太清晰，有高人能露几行代码参考吗？

需要引用MySql.Data.dll

连接MYSQL的信息我是存在ini里面的
ConfigManagement.IniReadValue("mysql", "host").ToString(); 自行替换.

*using System;
/// <summary>
/// 类名：QQRobot.DataAccess.MySqlManager
///
/// 继承：N/A
///
/// 描述：MySql数据库管理对象
/// </summary>
public sealed class MySqlManager
{
#region 构造方法
/// <summary>
/// 无参构造方法
/// </summary>
public MySqlManager()
{
this.initClass();
}
/// <summary>
/// 初始化类
/// </summary>
private void initClass()
{
string server = ConfigManagement.IniReadValue("mysql", "host").ToString();
string user = ConfigManagement.IniReadValue("mysql", "user").ToString();
string password = ConfigManagement.IniReadValue("mysql", "password").ToString();
string database = ConfigManagement.IniReadValue("mysql", "database").ToString();
string charset = ConfigManagement.IniReadValue("mysql", "charset").ToString();
int port = Convert.ToInt32(ConfigManagement.IniReadValue("mysql", "port").ToString());
this.connectionString = "server = " + server + "; user id = " + user + "; password = " + password + "; database = " + database + ";CharSet=" + charset + "; port = " + port;
}
#endregion 构造方法s
#region 属性
/// <summary>
/// 数据库连接
/// </summary>
private string connectionString = null;
/// <summary>
/// 获取或设置数据库连接
/// </summary>
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
/// <summary>
/// 数据库连接
/// </summary>
MySql.Data.MySqlClient.MySqlConnection conn = null;
/// <summary>
/// 执行对象
/// </summary>
System.Data.IDbCommand cmd = null;
/// <summary>
/// 事务对象
/// </summary>
System.Data.IDbTransaction trans = null;
/// <summary>
/// 是否事务
/// </summary>
private bool isTrans = false;
#endregion 属性
#region 通用方法
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns>返回测试结果</returns>
public bool Test()
{
bool result = true;
try
{
this.OpenDB();
this.CloseDB();
}
catch
{
result = false;
}
return result;
}
/// <summary>
/// 打开数据库
/// </summary>
public void OpenDB()
{
this.conn = new MySql.Data.MySqlClient.MySqlConnection(this.connectionString);
this.conn.Open();
}
/// <summary>
/// 关闭数据库
/// </summary>
public void CloseDB()
{
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
}
/// <summary>
/// 开始一个事物
/// </summary>
public void BeginTrans()
{
this.cmd = conn.CreateCommand();
this.trans = conn.BeginTransaction();
this.cmd.Transaction = this.trans;
this.isTrans = true;
}
/// <summary>
/// 提交事物
/// </summary>
public void CommitTrans()
{
this.trans.Commit();
this.isTrans = false;
}
/// <summary>
/// 回滚事物
/// </summary>
public void RollbackTrans()
{
this.trans.Rollback();
this.isTrans = false;
}
/// <summary>
/// 根据SQL语句
/// </summary>
/// <param name="pQuerySql">SQL语句</param>
/// <returns>返回结果</returns>
public int ExecuteNullQuery(string pQuerySql)
{
return ExecuteNullQuery(pQuerySql, null);
}
/// <summary>
/// 根据SQL语句
/// </summary>
/// <param name="pQuerySql">SQL语句</param>
/// <param name="pParameters">参数列表</param>
/// <returns>返回结果</returns>
public int ExecuteNullQuery(string pQuerySql, object[] pParameters)
{
int result = 0;
if (!string.IsNullOrEmpty(connectionString))
{
try
{
if (!this.isTrans)
{
this.OpenDB();
cmd = conn.CreateCommand();
}
cmd.CommandText = pQuerySql;
if (pParameters != null)
{
foreach (object item in pParameters)
{
cmd.Parameters.Add(item);
}
}
result = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw new System.ApplicationException(ex.Message, ex);
}
finally
{
if (!this.isTrans)
{
this.CloseDB();
}
}
}
return result;
}
/// <summary>
/// 根据指定的存储过程名称，指的的参数列表，执行存储过程
/// </summary>
/// <param name="pProcedureName">指定的存储过程名称</param>
/// <param name="pParameters">指的的参数列表</param>
/// <returns>返回结果</returns>
public int ExecuteProcedure(string pProcedureName, object[] pParameters)
{
int result = 0;
if (!string.IsNullOrEmpty(connectionString))
{
try
{
if (!this.isTrans)
{
this.OpenDB();
cmd = conn.CreateCommand();
}
cmd.CommandText = pProcedureName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (pParameters != null)
{
foreach (object item in pParameters)
{
cmd.Parameters.Add(item);
}
}
result = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw new System.ApplicationException(ex.Message, ex);
}
finally
{
if (!this.isTrans)
{
this.CloseDB();
}
}
}
return result;
}
/// <summary>
/// 根据sql语句执行查询
/// </summary>
/// <param name="pQuerySql">SQL语句</param>
/// <returns>放回结果</returns>
public System.Data.DataTable ExecuteQuery(string pQuerySql)
{
System.Data.DataTable dtResult = null;
if (!string.IsNullOrEmpty(this.connectionString))
{
try
{
if (!this.isTrans)
{
this.OpenDB();
}
MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(pQuerySql, this.conn);
dtResult = new System.Data.DataTable();
adap.Fill(dtResult);
}
catch (System.Exception ex)
{
throw new System.ApplicationException(ex.Message, ex);
}
finally
{
if (!this.isTrans)
{
this.CloseDB();
}
}
}
return dtResult;
}
/// <summary>
/// 根据sql语句执行查询
/// </summary>
/// <param name="pQuerySql">SQL语句</param>
///<param name="pSchemaType">模式</param>
/// <returns>放回结果</returns>
public System.Data.DataTable ExecuteQuery(string pQuerySql, System.Data.SchemaType pSchemaType)
{
System.Data.DataTable dtResult = null;
if (!string.IsNullOrEmpty(this.connectionString))
{
try
{
if (!this.isTrans)
{
this.OpenDB();
}
MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(pQuerySql, this.conn);
dtResult = new System.Data.DataTable();
adap.FillSchema(dtResult, pSchemaType);
adap.Fill(dtResult);
}
catch (System.Exception ex)
{
throw new System.ApplicationException(ex.Message, ex);
}
finally
{
if (!this.isTrans)
{
this.CloseDB();
}
}
}
return dtResult;
}
/// <summary>
/// 根据sql语句查询查询结果，并范围第一行第一列的值
/// </summary>
/// <param name="pQuerySql">SQL语句</param>
/// <returns>放回结果集</returns>
public object ExecuteScalar(string pQuerySql)
{
object obj = null;
if (!string.IsNullOrEmpty(this.connectionString))
{
try
{
if (!this.isTrans)
{
this.OpenDB();
cmd = conn.CreateCommand();
}
cmd.CommandText = pQuerySql;
obj = cmd.ExecuteScalar();
}
catch (System.Exception ex)
{
throw new System.ApplicationException(ex.Message, ex);
}
finally
{
if (!this.isTrans)
{
this.CloseDB();
}
}
}
return obj;
}
/// <summary>
/// 执行多个SQL
/// </summary>
/// <param name="sqlList">SQL语句集合</param>
public void ExecArraySql(object[] sqlList)
{
if (!string.IsNullOrEmpty(connectionString))
{
// 自己的事务
bool isTransSalf = true;
int index = 0;
try
{
if (!this.isTrans)
{
this.OpenDB();
this.BeginTrans();
isTransSalf = true;
}
foreach (object sql in sqlList)
{
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
index++;
}
if (!this.isTrans || isTransSalf)
{
this.CommitTrans();
}
}
catch (System.Exception ex)
{
if (!this.isTrans || isTransSalf)
{
this.RollbackTrans();
}
var obj = new System.ApplicationException(ex.Message, ex);
obj.Source = string.Concat(sqlList[index]);
throw obj;
}
finally
{
if (!this.isTrans || isTransSalf)
{
this.CloseDB();
}
}
}
}
/// <summary>
/// 执行修改
/// </summary>
/// <param name="pData">数据</param>
/// <param name="pPrimaryKey">主键列</param>
/// <returns>反正影响的行数</returns>
public int Update(System.Data.DataTable pData, System.Data.DataColumn[] pPrimaryKey)
{
int result = 0;
if (pData != null && pData.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(pData.TableName))
{
if (pPrimaryKey != null && pPrimaryKey.Length > 0)
{
System.Collections.Generic.List<string> sqlList = new System.Collections.Generic.List<string>();
string sql = null, columns = null, values = null, set = null, where = null;
System.Data.DataTable dtTemp = pData.GetChanges(System.Data.DataRowState.Added);
if (dtTemp != null)
{
sql = "insert into " + pData.TableName + " ({0}) values ({1});";
foreach (System.Data.DataRow dr in dtTemp.Rows)
{
columns = values = "";
foreach (System.Data.DataColumn col in dtTemp.Columns)
{
if (!string.IsNullOrEmpty(columns))
{
columns += ",";
values += ",";
}
columns += col.ColumnName;
values += Utility.GetSQLValue(dr[col.ColumnName]);
}
sqlList.Add(string.Format(sql, columns, values));
}
}
dtTemp = pData.GetChanges(System.Data.DataRowState.Modified);
if (dtTemp != null)
{
sql = "update " + pData.TableName + " set {0} where {1}";
foreach (System.Data.DataRow dr in dtTemp.Rows)
{
set = where = null;
foreach (System.Data.DataColumn col in dtTemp.Columns)
{
if (! Utility.CompareObjectValue(dr[col.ColumnName], dr[col.ColumnName, System.Data.DataRowVersion.Original]))
{
if (!string.IsNullOrEmpty(set))
{
set += ",";
}
set += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName]);
}
}
foreach (System.Data.DataColumn col in pPrimaryKey)
{
if (!string.IsNullOrEmpty(where))
{
where += " and ";
}
where += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName, System.Data.DataRowVersion.Original]);
}
if (!string.IsNullOrEmpty(set))
{
sqlList.Add(string.Format(sql, set, where));
}
}
}
dtTemp = pData.GetChanges(System.Data.DataRowState.Deleted);
if (dtTemp != null)
{
sql = "delete " + pData.TableName + " where {0}";
foreach (System.Data.DataRow dr in dtTemp.Rows)
{
where = null;
foreach (System.Data.DataColumn col in pPrimaryKey)
{
if (!string.IsNullOrEmpty(where))
{
where += " and ";
}
where += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName, System.Data.DataRowVersion.Original]);
}
sqlList.Add(string.Format(sql, where));
}
}
if (sqlList.Count > 0)
{
ExecArraySql(sqlList.ToArray());
result = sqlList.Count;
}
}
else
{
throw new System.ApplicationException("未设置主键列！");
}
}
else
{
throw new System.ApplicationException("未设置表名！");
}
}
return result;
}
#endregion 通用方法
}