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;    }}