一、連接字符串(使用配置文件)
添加對 System.Configuration 的引用,並且在封裝類中引入該namespace
/// <summary> /// 功能:讀取配置文件中的連接字符串 /// 返回值類型:string /// </summary> /// <param name="conName">參數:配置文件中的連接字符串名稱</param> /// <returns>返回值:string 連接字符串</returns> public static string GetConnectString(string conName) { try { return ConfigurationManager.ConnectionStrings[conName].ConnectionString; } catch (Exception e) { throw e; } }
二、SqlConnection對象
/// <summary> /// 功能:根據給定的連接字符串創建一個SqlConnection對象 /// 返回類型:SqlConnection /// </summary> /// <param name="conStr">參數:連接字符串</param> /// <returns>返回值:SqlConnection對象</returns> public static SqlConnection GetConnect(string conStr) { return new SqlConnection(conStr); }
/// <summary> /// 功能:打開數據庫連接 /// </summary> /// <param name="Con">參數:SqlConnection對象</param> public static void OpenDBConnet(SqlConnection Con) { try { if (Con.State == ConnectionState.Open) { return; } else { Con.Open(); } } catch (SqlException e) { throw e; } }
/// <summary> /// 功能:關閉數據庫連接 /// </summary> /// <param name="Con">參數:SqlConnection對象</param> public static void CloseDBConnect(SqlConnection Con) { try { if (Con.State == ConnectionState.Closed) { return; } else { Con.Close(); } } catch (SqlException e) { throw e; } }
三、同步操作數據庫
SqlDataReader對象
/// <summary> /// 功能:執行存儲過程並返回一個SqlDataReader對象 /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter參數數組</param> /// <returns>返回值:SqlDataReader對象</returns> public static SqlDataReader GetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } } catch (SqlException s) { throw s; } return Cmd.ExecuteReader(CommandBehavior.CloseConnection); }
/// <summary> /// 功能:執行T-SQL語句語句並返回一個SqlDataReader對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter參數數組</param> /// <returns>返回值:SqlDataReader對象</returns> public static SqlDataReader GetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } } catch (SqlException s) { throw s; } return Cmd.ExecuteReader(CommandBehavior.CloseConnection); }
ExecuteNonQuery
/// <summary> /// 功能:執行存儲過程返回受影響的行數 /// 返回類型:int /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter參數數組</param> /// <returns>返回值:int 受影響的行數</returns> public static int GetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } } catch (SqlException s) { throw s; } return Cmd.ExecuteNonQuery(); }
/// <summary> /// 功能:執行T-SQL語句返回受影響的行數 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter參數數組</param> /// <returns>返回值:int 受影響的行數</returns> public static int GetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } } catch (SqlException s) { throw s; } return Cmd.ExecuteNonQuery(); }
ExecuteScalar
/// <summary> /// 功能:通過T-SQl語句執行SqlCommand的ExecuteScalar()方法返回object類型對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter參數數組</param> /// <returns>返回值:object對象</returns> public static object GetEScalarByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } } catch (SqlException s) { throw s; } return Cmd.ExecuteScalar(); }
/// <summary> /// 功能:通過執行T-SQL語句返回一個DataSet數據集對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="tableName">參數:DataSet表名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:可變SqlParameter參數數組</param> /// <returns>返回值:DataSet數據集對象</returns> public static DataSet GetDsByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; DataSet Ds = null; SqlDataAdapter Sda = null; try { Ds = new DataSet(tableName); Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Sda = new SqlDataAdapter(Cmd); Sda.Fill(Ds, tableName); } catch (SqlException s) { throw s; } return Ds; }
DataSet
/// <summary> /// 功能:通過執行存儲過程返回一個DataSet數據集對象 /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="tableName">參數:DataSet表名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:可變SqlParameter參數數組</param> /// <returns>返回值:DataSet數據集對象</returns> public static DataSet GetDsBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; DataSet Ds = null; SqlDataAdapter Sda = null; try { Ds = new DataSet(tableName); Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Sda = new SqlDataAdapter(Cmd); Sda.Fill(Ds, tableName); } catch (SqlException s) { throw s; } return Ds; }
/// <summary> /// 功能:執行存儲過程返回一個DataTable數據表對象 /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="tableName">參數:DataTable表名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:DataTable</returns> public static DataTable GetDtBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; DataTable Dt = null; SqlDataAdapter Sda = null; try { Cmd = new SqlCommand(sql, Con); Dt = new DataTable(tableName); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Sda = new SqlDataAdapter(Cmd); Sda.Fill(Dt); } catch (SqlException s) { throw s; } return Dt; }
DataTable
/// <summary> /// 功能:執行T-SQL語句返回一個DataTable數據表對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="tableName">參數:DataTable表名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:DataTable</returns> public static DataTable GetDtByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; DataTable Dt = null; SqlDataAdapter Sda = null; try { Cmd = new SqlCommand(sql, Con); Dt = new DataTable(tableName); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Sda = new SqlDataAdapter(Cmd); Sda.Fill(Dt); } catch (SqlException s) { throw s; } return Dt; }
其它同步ADO.NET操作
/// <summary> /// 功能:執行T-SQL語句判斷表中是否包含指定的內容 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:bool值</returns> public static bool IsContainFiledByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { try { using (SqlCommand Cmd = new SqlCommand(sql, Con)) { DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } using (SqlDataReader Sdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (Sdr.HasRows) { return true; } else { return false; } } } } catch (SqlException e) { throw e; } }
異步操作數據庫
/// <summary> /// 功能:通過存儲過程異步操作數據庫,返回SqlDataReader對象 /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:SqlDataReader對象</returns> public static SqlDataReader AsyncGetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteReader(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteReader(Iasy); } /// <summary> /// 功能:通過T-SQL語句異步操作數據庫,返回SqlDataReader對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:SqlDataReader對象</returns> public static SqlDataReader AsyncGetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteReader(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteReader(Iasy); } /// <summary> /// 功能:通過存儲過程異步操作數據庫返回受影響的行數 /// </summary> /// <param name="sql">參數:存儲過程</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:int 受影響行數</returns> public static int AsyncGetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteNonQuery(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteNonQuery(Iasy); } /// <summary> /// 功能:通過T-SQL語句異步操作數據庫返回受影響的行數 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:int 受影響行數</returns> public static int AsyncGetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteNonQuery(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteNonQuery(Iasy); } /// <summary> /// 功能:通過存儲過程異步操作數據庫返回XmlReader對象 /// </summary> /// <param name="sql">參數:存儲過程名</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:XmlReader</returns> public static XmlReader AsyncGetXrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteXmlReader(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteXmlReader(Iasy); } /// <summary> /// 功能:通過T-SQL語句異步操作數據庫返回XmlReader對象 /// </summary> /// <param name="sql">參數:T-SQL語句</param> /// <param name="Con">參數:SqlConnection對象</param> /// <param name="parameters">參數:SqlParameter可變參數數組</param> /// <returns>返回值:XmlReader</returns> public static XmlReader AsyncGetXrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters) { SqlCommand Cmd = null; IAsyncResult Iasy = null; try { Cmd = new SqlCommand(sql, Con); DBHelper.OpenDBConnet(Con); Cmd.CommandType = CommandType.Text; foreach (SqlParameter parameter in parameters) { Cmd.Parameters.Add(parameter); } Iasy = Cmd.BeginExecuteXmlReader(); } catch (SqlException s) { throw s; } return Cmd.EndExecuteXmlReader(Iasy); }
其它重要方法封裝
/// <summary> /// 功能:檢查字符串類型數據是不是空,為空則向數據庫插入Null /// </summary> /// <param name="notNullStr">字符串可變參數數組</param> /// <returns>最終要插入數據庫的數據</returns> public static List<object> CheckDBNullValue(params string[] notNullStr) { List<object> TheDBNllResult = new List<object>(notNullStr.Length); foreach (string s in notNullStr) { if ((s == string.Empty) || (s == "")) { TheDBNllResult.Add(DBNull.Value); } else { TheDBNllResult.Add(s.Trim()); } } return TheDBNllResult; } /// <summary> /// 功能:判斷數據閱讀器讀到的指定列是否是Null,如果是Null就返回空字符串,否則就讀取字段的值 /// </summary> /// <param name="Sdr">SqlDataReader</param> /// <param name="filedName">數據庫字段名</param> /// <returns>字符串表示的字段值</returns> public static string GetDBValue(SqlDataReader Sdr, string filedName) { string result = null; if (Sdr.IsDBNull(Sdr.GetOrdinal(filedName))) { result = string.Empty; } else { result = Sdr.GetString(Sdr.GetOrdinal(filedName)); } return result; }