1. 首页>
  2. 腾讯云代理

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

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

腾讯云代理 腾讯云直播申请 游戏上云

摘要:

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

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

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

需要引用MySql.Data.dll

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


  1. *using System;


  2. /// <summary>

  3. /// 类名:QQRobot.DataAccess.MySqlManager

  4. /// 

  5. /// 继承:N/A

  6. /// 

  7. /// 描述:MySql数据库管理对象

  8. /// </summary>

  9. public sealed class MySqlManager

  10. {

  11. #region 构造方法


  12. /// <summary>

  13. /// 无参构造方法

  14. /// </summary>

  15. public MySqlManager()

  16. {

  17. this.initClass();

  18. }


  19. /// <summary>

  20. /// 初始化类

  21. /// </summary>

  22. private void initClass()

  23. {

  24. string server = ConfigManagement.IniReadValue("mysql", "host").ToString();

  25. string user = ConfigManagement.IniReadValue("mysql", "user").ToString();

  26. string password = ConfigManagement.IniReadValue("mysql", "password").ToString();

  27. string database = ConfigManagement.IniReadValue("mysql", "database").ToString();

  28. string charset = ConfigManagement.IniReadValue("mysql", "charset").ToString();

  29. int port = Convert.ToInt32(ConfigManagement.IniReadValue("mysql", "port").ToString());

  30. this.connectionString = "server = " + server + "; user id = " + user + "; password = " + password + "; database = " + database + ";CharSet=" + charset + "; port = " + port;


  31. }


  32. #endregion 构造方法s


  33. #region 属性


  34. /// <summary>

  35. /// 数据库连接

  36. /// </summary>

  37. private string connectionString = null;


  38. /// <summary>

  39. /// 获取或设置数据库连接

  40. /// </summary>

  41. public string ConnectionString

  42. {

  43. get { return connectionString; }

  44. set { connectionString = value; }

  45. }


  46. /// <summary>

  47. /// 数据库连接

  48. /// </summary>

  49. MySql.Data.MySqlClient.MySqlConnection conn = null;


  50. /// <summary>

  51. /// 执行对象

  52. /// </summary>

  53. System.Data.IDbCommand cmd = null;


  54. /// <summary>

  55. /// 事务对象

  56. /// </summary>

  57. System.Data.IDbTransaction trans = null;


  58. /// <summary>

  59. /// 是否事务

  60. /// </summary>

  61. private bool isTrans = false;


  62. #endregion 属性


  63. #region 通用方法


  64. /// <summary>

  65. /// 测试数据库连接

  66. /// </summary>

  67. /// <returns>返回测试结果</returns>

  68. public bool Test()

  69. {

  70. bool result = true;

  71. try

  72. {

  73. this.OpenDB();

  74. this.CloseDB();

  75. }

  76. catch

  77. {

  78. result = false;

  79. }

  80. return result;

  81. }


  82. /// <summary>

  83. /// 打开数据库

  84. /// </summary>

  85. public void OpenDB()

  86. {

  87. this.conn = new MySql.Data.MySqlClient.MySqlConnection(this.connectionString);

  88. this.conn.Open();

  89. }


  90. /// <summary>

  91. /// 关闭数据库

  92. /// </summary>

  93. public void CloseDB()

  94. {

  95. if (conn != null && conn.State == System.Data.ConnectionState.Open)

  96. {

  97. conn.Close();

  98. }

  99. if (cmd != null)

  100. {

  101. cmd.Dispose();

  102. }

  103. }


  104. /// <summary>

  105. /// 开始一个事物

  106. /// </summary>

  107. public void BeginTrans()

  108. {

  109. this.cmd = conn.CreateCommand();

  110. this.trans = conn.BeginTransaction();

  111. this.cmd.Transaction = this.trans;

  112. this.isTrans = true;

  113. }


  114. /// <summary>

  115. /// 提交事物

  116. /// </summary>

  117. public void CommitTrans()

  118. {

  119. this.trans.Commit();

  120. this.isTrans = false;

  121. }


  122. /// <summary>

  123. /// 回滚事物

  124. /// </summary>

  125. public void RollbackTrans()

  126. {

  127. this.trans.Rollback();

  128. this.isTrans = false;

  129. }


  130. /// <summary>

  131. /// 根据SQL语句

  132. /// </summary>

  133. /// <param name="pQuerySql">SQL语句</param>

  134. /// <returns>返回结果</returns>

  135. public int ExecuteNullQuery(string pQuerySql)

  136. {

  137. return ExecuteNullQuery(pQuerySql, null);

  138. }


  139. /// <summary>

  140. /// 根据SQL语句

  141. /// </summary>

  142. /// <param name="pQuerySql">SQL语句</param>

  143. /// <param name="pParameters">参数列表</param>

  144. /// <returns>返回结果</returns>

  145. public int ExecuteNullQuery(string pQuerySql, object[] pParameters)

  146. {

  147. int result = 0;

  148. if (!string.IsNullOrEmpty(connectionString))

  149. {

  150. try

  151. {

  152. if (!this.isTrans)

  153. {

  154. this.OpenDB();

  155. cmd = conn.CreateCommand();

  156. }

  157. cmd.CommandText = pQuerySql;

  158. if (pParameters != null)

  159. {

  160. foreach (object item in pParameters)

  161. {

  162. cmd.Parameters.Add(item);

  163. }

  164. }

  165. result = cmd.ExecuteNonQuery();

  166. }

  167. catch (System.Exception ex)

  168. {

  169. throw new System.ApplicationException(ex.Message, ex);

  170. }

  171. finally

  172. {

  173. if (!this.isTrans)

  174. {

  175. this.CloseDB();

  176. }

  177. }

  178. }

  179. return result;

  180. }


  181. /// <summary>

  182. /// 根据指定的存储过程名称,指的的参数列表,执行存储过程

  183. /// </summary>

  184. /// <param name="pProcedureName">指定的存储过程名称</param>

  185. /// <param name="pParameters">指的的参数列表</param>

  186. /// <returns>返回结果</returns>

  187. public int ExecuteProcedure(string pProcedureName, object[] pParameters)

  188. {

  189. int result = 0;

  190. if (!string.IsNullOrEmpty(connectionString))

  191. {

  192. try

  193. {

  194. if (!this.isTrans)

  195. {

  196. this.OpenDB();

  197. cmd = conn.CreateCommand();

  198. }

  199. cmd.CommandText = pProcedureName;

  200. cmd.CommandType = System.Data.CommandType.StoredProcedure;

  201. if (pParameters != null)

  202. {

  203. foreach (object item in pParameters)

  204. {

  205. cmd.Parameters.Add(item);

  206. }

  207. }

  208. result = cmd.ExecuteNonQuery();

  209. }

  210. catch (System.Exception ex)

  211. {

  212. throw new System.ApplicationException(ex.Message, ex);

  213. }

  214. finally

  215. {

  216. if (!this.isTrans)

  217. {

  218. this.CloseDB();

  219. }

  220. }

  221. }

  222. return result;

  223. }


  224. /// <summary>

  225. /// 根据sql语句执行查询

  226. /// </summary>

  227. /// <param name="pQuerySql">SQL语句</param>

  228. /// <returns>放回结果</returns>

  229. public System.Data.DataTable ExecuteQuery(string pQuerySql)

  230. {

  231. System.Data.DataTable dtResult = null;

  232. if (!string.IsNullOrEmpty(this.connectionString))

  233. {

  234. try

  235. {

  236. if (!this.isTrans)

  237. {

  238. this.OpenDB();

  239. }

  240. MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(pQuerySql, this.conn);


  241. dtResult = new System.Data.DataTable();

  242. adap.Fill(dtResult);

  243. }

  244. catch (System.Exception ex)

  245. {

  246. throw new System.ApplicationException(ex.Message, ex);

  247. }

  248. finally

  249. {

  250. if (!this.isTrans)

  251. {

  252. this.CloseDB();

  253. }

  254. }

  255. }

  256. return dtResult;

  257. }


  258. /// <summary>

  259. /// 根据sql语句执行查询

  260. /// </summary>

  261. /// <param name="pQuerySql">SQL语句</param>

  262. ///<param name="pSchemaType">模式</param>

  263. /// <returns>放回结果</returns>

  264. public System.Data.DataTable ExecuteQuery(string pQuerySql, System.Data.SchemaType pSchemaType)

  265. {

  266. System.Data.DataTable dtResult = null;

  267. if (!string.IsNullOrEmpty(this.connectionString))

  268. {

  269. try

  270. {

  271. if (!this.isTrans)

  272. {

  273. this.OpenDB();

  274. }

  275. MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(pQuerySql, this.conn);


  276. dtResult = new System.Data.DataTable();

  277. adap.FillSchema(dtResult, pSchemaType);

  278. adap.Fill(dtResult);

  279. }

  280. catch (System.Exception ex)

  281. {

  282. throw new System.ApplicationException(ex.Message, ex);

  283. }

  284. finally

  285. {

  286. if (!this.isTrans)

  287. {

  288. this.CloseDB();

  289. }

  290. }

  291. }

  292. return dtResult;

  293. }


  294. /// <summary>

  295. /// 根据sql语句查询查询结果,并范围第一行第一列的值

  296. /// </summary>

  297. /// <param name="pQuerySql">SQL语句</param>

  298. /// <returns>放回结果集</returns>

  299. public object ExecuteScalar(string pQuerySql)

  300. {

  301. object obj = null;

  302. if (!string.IsNullOrEmpty(this.connectionString))

  303. {

  304. try

  305. {

  306. if (!this.isTrans)

  307. {

  308. this.OpenDB();

  309. cmd = conn.CreateCommand();

  310. }

  311. cmd.CommandText = pQuerySql;

  312. obj = cmd.ExecuteScalar();

  313. }

  314. catch (System.Exception ex)

  315. {

  316. throw new System.ApplicationException(ex.Message, ex);

  317. }

  318. finally

  319. {

  320. if (!this.isTrans)

  321. {

  322. this.CloseDB();

  323. }

  324. }

  325. }

  326. return obj;

  327. }


  328. /// <summary>

  329. /// 执行多个SQL

  330. /// </summary>

  331. /// <param name="sqlList">SQL语句集合</param>

  332. public void ExecArraySql(object[] sqlList)

  333. {

  334. if (!string.IsNullOrEmpty(connectionString))

  335. {

  336. // 自己的事务

  337. bool isTransSalf = true;

  338. int index = 0;

  339. try

  340. {

  341. if (!this.isTrans)

  342. {

  343. this.OpenDB();

  344. this.BeginTrans();

  345. isTransSalf = true;

  346. }

  347. foreach (object sql in sqlList)

  348. {

  349. cmd.CommandText = sql.ToString();

  350. cmd.ExecuteNonQuery();

  351. index++;

  352. }

  353. if (!this.isTrans || isTransSalf)

  354. {

  355. this.CommitTrans();

  356. }

  357. }

  358. catch (System.Exception ex)

  359. {

  360. if (!this.isTrans || isTransSalf)

  361. {

  362. this.RollbackTrans();

  363. }

  364. var obj = new System.ApplicationException(ex.Message, ex);

  365. obj.Source = string.Concat(sqlList[index]);

  366. throw obj;

  367. }

  368. finally

  369. {

  370. if (!this.isTrans || isTransSalf)

  371. {

  372. this.CloseDB();

  373. }

  374. }

  375. }

  376. }


  377. /// <summary>

  378. /// 执行修改

  379. /// </summary>

  380. /// <param name="pData">数据</param>

  381. /// <param name="pPrimaryKey">主键列</param>

  382. /// <returns>反正影响的行数</returns>

  383. public int Update(System.Data.DataTable pData, System.Data.DataColumn[] pPrimaryKey)

  384. {

  385. int result = 0;

  386. if (pData != null && pData.Rows.Count > 0)

  387. {

  388. if (!string.IsNullOrEmpty(pData.TableName))

  389. {

  390. if (pPrimaryKey != null && pPrimaryKey.Length > 0)

  391. {

  392. System.Collections.Generic.List<string> sqlList = new System.Collections.Generic.List<string>();

  393. string sql = null, columns = null, values = null, set = null, where = null;

  394. System.Data.DataTable dtTemp = pData.GetChanges(System.Data.DataRowState.Added);

  395. if (dtTemp != null)

  396. {

  397. sql = "insert into " + pData.TableName + " ({0}) values ({1});";

  398. foreach (System.Data.DataRow dr in dtTemp.Rows)

  399. {

  400. columns = values = "";

  401. foreach (System.Data.DataColumn col in dtTemp.Columns)

  402. {

  403. if (!string.IsNullOrEmpty(columns))

  404. {

  405. columns += ",";

  406. values += ",";

  407. }

  408. columns += col.ColumnName;

  409. values += Utility.GetSQLValue(dr[col.ColumnName]);

  410. }

  411. sqlList.Add(string.Format(sql, columns, values));

  412. }

  413. }

  414. dtTemp = pData.GetChanges(System.Data.DataRowState.Modified);

  415. if (dtTemp != null)

  416. {

  417. sql = "update " + pData.TableName + " set {0} where {1}";

  418. foreach (System.Data.DataRow dr in dtTemp.Rows)

  419. {

  420. set = where = null;

  421. foreach (System.Data.DataColumn col in dtTemp.Columns)

  422. {

  423. if (! Utility.CompareObjectValue(dr[col.ColumnName], dr[col.ColumnName, System.Data.DataRowVersion.Original]))

  424. {

  425. if (!string.IsNullOrEmpty(set))

  426. {

  427. set += ",";

  428. }

  429. set += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName]);

  430. }

  431. }

  432. foreach (System.Data.DataColumn col in pPrimaryKey)

  433. {

  434. if (!string.IsNullOrEmpty(where))

  435. {

  436. where += " and ";

  437. }

  438. where += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName, System.Data.DataRowVersion.Original]);

  439. }

  440. if (!string.IsNullOrEmpty(set))

  441. {

  442. sqlList.Add(string.Format(sql, set, where));

  443. }

  444. }

  445. }

  446. dtTemp = pData.GetChanges(System.Data.DataRowState.Deleted);

  447. if (dtTemp != null)

  448. {

  449. sql = "delete " + pData.TableName + " where {0}";

  450. foreach (System.Data.DataRow dr in dtTemp.Rows)

  451. {

  452. where = null;

  453. foreach (System.Data.DataColumn col in pPrimaryKey)

  454. {

  455. if (!string.IsNullOrEmpty(where))

  456. {

  457. where += " and ";

  458. }

  459. where += col.ColumnName + " = " + Utility.GetSQLValue(dr[col.ColumnName, System.Data.DataRowVersion.Original]);

  460. }

  461. sqlList.Add(string.Format(sql, where));

  462. }

  463. }

  464. if (sqlList.Count > 0)

  465. {

  466. ExecArraySql(sqlList.ToArray());

  467. result = sqlList.Count;

  468. }

  469. }

  470. else

  471. {

  472. throw new System.ApplicationException("未设置主键列!");

  473. }

  474. }

  475. else

  476. {

  477. throw new System.ApplicationException("未设置表名!");

  478. }

  479. }

  480. return result;

  481. }


  482. #endregion 通用方法

  483. }


复制代码



腾讯云服务商邀请关注:

http://partners.qcloud.com/invitation/99377079157835da208bd0

关注服务商的链接,提供下您关注的QQ,我们这边审核下即可快速回复您的工单问题与技术支持! 感谢您的信任与支持!

auto_1452.png

腾讯云技术交流群


ps:本站信息来源于 bbs.qcloud.com

相关文章

在线客服
淘宝购买
腾讯云直播申请 title=
+成为腾讯云VIP客户 腾讯云直播申请 客服电话

15818558013

0755-33940501-803

0755-33940501-808