连接数据库最常用的方式就是通过字符串连接,如下:
public static string connstr = "server = localhost; uid = root; pwd = 123456; database = db_sms;";
MySqlConnection conn = new MySqlConnection(connstr);
MySqlCommand comm = new MySqlCommand(sqlstr, conn);
但是这种方式不利于应用程序发布后的后期维护。
所有便通过另外一种方式进行连接——通过读取配置文件App.config
(1)首先进行编写App.config:
<configuration>
<connectionStrings>
<add name ="connstr" connectionString ="server = localhost; uid = root; pwd = 123456; database = db_sms;"/>
</connectionStrings>
</configuration>
一定要在 configuration 节点中进行编辑,写入连接数据库的字符串。
(2)添加引用 System.Configuration
(3)使用ConfigurationManger类:
public static string connstr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
接下来就是常规操作(增删改查)
注意事项:
(1)以下代码必须严格编写,不能有错,即使一个字符的错误(多一个字符,少一个字符,大小写)都会导致配置文件读取失败。
<configuration>
<connectionStrings>
<add name ="" connectionString =""/>
</connectionStrings>
</configuration>
(2)数据库连接字符串的名称(这里是“connstr”),在App.config中和代码中必须保持一致。
附上数据库操作类的完整代码:
-
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using MySql.Data; using MySql.Data.MySqlClient; using System.Configuration; namespace DAL { public class SQLHelper { //public static string connstr = "server = localhost; uid = root; pwd = 123456; database = db_sms;"; /* 1.在 SMS 下的 App.config 配置文件中添加: <connectionStrings> <add name ="connstr" connectionString ="server = localhost; uid = root; pwd = 123456; database = db_sms;"/> </connectionStrings> 2.在 DAL 下添加引用:System.Configuration 3.添加命名空间,使用 ConfigurationManager 类下的 ConnectionStrings */ // 通过读取配置文件进行数据库连接 public static string connstr = ConfigurationManager.ConnectionStrings["connstr"].ToString(); /// <summary> /// 执行增、删、改 /// </summary> /// <param name="sqlstr"></param> /// <returns></returns> private static int UpDate(string sqlstr) { MySqlConnection conn = new MySqlConnection(connstr); MySqlCommand comm = new MySqlCommand(sqlstr, conn); try { conn.Open(); int result = comm.ExecuteNonQuery(); return result; } catch (System.Exception ex) { // 写入系统日志 throw ex; } finally { conn.Close(); } } /// <summary> /// 查询:获取单一结果 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static object GetSingleResult(string sqlstr) { MySqlConnection conn = new MySqlConnection(connstr); MySqlCommand comm = new MySqlCommand(sqlstr, conn); try { conn.Open(); object result = comm.ExecuteScalar(); return result; } catch (System.Exception ex) { // 写入系统日志 throw ex; } conn.Close(); } /// <summary> /// 查询:获取结果集合 /// </summary> /// <param name="sqlstr"></param> /// <returns></returns> public static MySqlDataReader GetDataReader(string sqlstr) { MySqlConnection conn = new MySqlConnection(connstr); MySqlCommand comm = new MySqlCommand(sqlstr, conn); try { conn.Open(); return comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (System.Exception ex) { // 写入系统日志 conn.Clone(); throw ex; } finally { conn.Close(); } } } }