C# 封装的DBHelper

内容不全，以后会添加。

C#与 SQL 链接。

1.链接字符串配置信息保存于App.config中

  <!--连接数据库字符串-->  <connectionStrings>    <add name="DBStrConn" connectionString="Data Source=.; initial Catalog=数据库; user id=用户名; passWord=密码"/>  </connectionStrings>

2.添加 System.configuration 引用

3.在DBHelper中获取链接字符串

/* * 获取连接数据库字符串 * 此字符串存在App.config应用配置文件中*/PRivate static string strConnection = ConfigurationManager.ConnectionStrings["DBStrConn"].ConnectionString;　　//DBStrConn为在App.config中的"name"名

4.1 ExecuteNonQuery方法执行 插入、修改、删除

public static int ToInUpDel_ExecuteNonQuery(string sql){    //连接命令    using (SqlConnection conn = new SqlConnection(strConnection))    {       conn.Open();   //打开连接       using (SqlCommand cmd = conn.CreateCommand())       {          //执行sql语句          cmd.CommandText = sql;          //返回受影响的行数          return cmd.ExecuteNonQuery();       }    }} 

4.2 ExecuteScalar方法 执行 查询

public static int ToSelect_ExecuteScalar(string sql){    using (SqlConnection conn = new SqlConnection(strConnection))    {        conn.Open();        using (SqlCommand cmd = conn.CreateCommand())        {            cmd.CommandText = sql;            //返回结果            return (int)cmd.ExecuteScalar();        }    }}

4.3 DataTable 用来查询结果比较少的sql

public static DataTable ExecuteDataTable(string sql){       using (SqlConnection conn = new SqlConnection(strConnection))       {             conn.Open();             using (SqlCommand cmd = conn.CreateCommand())             {                    cmd.CommandText = sql;                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);                    DataSet dataset = new DataSet();                    adapter.Fill(dataset);                    return dataset.Tables[0];             }       } }       

4.4 SqlDataReader 需要手动关闭

public static SqlDataReader ExecuteReader(string sql){    SqlConnection conn = new SqlConnection(strConnection);    SqlCommand cmd = new SqlCommand(sql,conn);    try    {　　conn.Open();　　SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);　　//CommandBehavior.CloseConnection 在关闭DataReader的时候自动关闭Connection　　return reader;    }    catch(SqlException ex)    {　　throw ex;    } }

-------------------------------------------分割线-----------------------------------------------

---------------------------------------以下是修改的--------------------------------------------

-----------------------------------修改时间：2015.3.2----------------------------------------

5.1 执行insert/update/delete 带参数

public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms){    using (SqlConnection conn = new SqlConnection(connStr))    {using (SqlCommand cmd = new SqlCommand(sql, conn)){    cmd.CommandType = cmdType;  //设置当前执行的是存储过程还是带参数的sql语句    if (pms != null)    {cmd.Parameters.AddRange(pms);    }    conn.Open();    return cmd.ExecuteNonQuery();}    }}

5.2 执行select(执行返回单个值的方法) 带参数

public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms){    using (SqlConnection conn = new SqlConnection(connStr))    {using (SqlCommand cmd = new SqlCommand(sql, conn)){    cmd.CommandType = cmdType;    if (pms != null)    {cmd.Parameters.AddRange(pms);    }    conn.Open();    return cmd.ExecuteScalar();}    }}

5.3 返回SqlDataReader的方法 带参数

public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms){    SqlConnection conn = new SqlConnection(connStr);    using (SqlCommand cmd = new SqlCommand(sql, conn))    {cmd.CommandType = cmdType;if (pms != null){    cmd.Parameters.AddRange(pms);}try{    conn.Open();    return cmd.ExecuteReader(CommandBehavior.CloseConnection);}catch{    conn.Close();    conn.Dispose();    throw;}    }    }

5.4 返回DataTable的方法 带参数

public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms){    DataTable dt = new DataTable();    using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr))    {adapter.SelectCommand.CommandType = cmdType;if (pms != null){    adapter.SelectCommand.Parameters.AddRange(pms);}adapter.Fill(dt);return dt;    }}