/**/ /******************************************************************************\ * 类名 : DBAccess * 功能 : * 对数据库进行操作的一些常用方法 * 原作者: Peter ZD Zhang * Date : 2006/09/25 * 修改者: Vegas Lee * Last UpDate : 2008/03/31 \*****************************************************************************/ using System; using System.Data; using System.Data.SqlClient; using System.Text; /**/ /// <summary> /**/ /********************** 2006 9 12 Peter **************************\ * 对数据库进行操作的一些常用方法 \*****************************************************************/ /**/ /// </summary> public class DBAccess { private string connStr; //数据库的连接字符串 /**//************************************************\ *DBAccess的构造函数 有两个重载 * DBAccess() * DBAccess(string connStr) *DBAccess()默认获取的连接数据库字符串为WebConfig->AppSettings节设置的key=ConnectionString的value *参数: * connStr:连接数据库的字符串 * 2006 09 19 Peter * * 更改DBAccess()默认获取的连接数据库字符串为WebConfig->connectionStrings节设置的name的connectionString * 2008/03/31 * \************************************************/ public DBAccess() { // connStr= System.Configuration.ConfigurationSettings.GetConfig("connectionStrings").ToString(); connStr = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString; // connStr="Server=sc00-test-001;uid=sa;pwd=123456;database=BIS"; } public DBAccess(string connStr) { connStr = connStr; } //属性 public string ConnectionString //设置或者获取数据库的连接字符串的属性 { get { return connStr; } set { connStr = value; } } //公有函数 检视DataSet做的变更,自动更新到数据库#region 检视DataSet做的变更,自动更新到数据库 public DataSet UpdateDataSet(string mySelectQuery, string myTableName) { SqlConnection myConn = new SqlConnection(connStr); SqlDataAdapter myDataAdapter = new SqlDataAdapter(); myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn); SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter); myConn.Open(); DataSet ds = new DataSet(); myDataAdapter.Fill(ds, myTableName); //code to modify data in DataSet here //Without the SqlCommandBuilder this line would fail myDataAdapter.Update(ds, myTableName); myConn.Close(); return ds; } #endregion public int GetMaxID(string sqlno) { //产生文件编号~~~~ Vegas Added DataSet dsNo = new DataSet(); dsNo = GetDataSet(sqlno); int tempno = 1; if (dsNo.Tables[0].Rows.Count == 0) { tempno = 1; } else { tempno = int.Parse(dsNo.Tables[0].Rows[0][0].ToString()) + 1; } return tempno; } public string GetParentID(string sqlno) { //产生文件编号~~~~ DataSet dsNo = new DataSet(); dsNo = GetDataSet(sqlno); string itemno = ""; if (dsNo.Tables[0].Rows.Count == 0) { itemno = "PN0000000001"; } else { itemno = dsNo.Tables[0].Rows[0][0].ToString(); int tempno = int.Parse(itemno.Substring(2, 10)); tempno = tempno + 1; itemno = tempno.ToString(); while (itemno.Length != 10) { itemno = "0" + itemno; } itemno = "PN" + itemno; } return itemno; } GetDataReader VegasAdd 08-03-06#region GetDataReader VegasAdd 08-03-06 public SqlDataReader GetDataReader(string sql) { SqlConnection conn = new SqlConnection(connStr); SqlCommand scm = new SqlCommand(sql, conn); conn.Open(); SqlDataReader dr = scm.ExecuteReader(); return dr; } #endregion GetDataSet#region GetDataSet public DataSet GetDataSet(string sql, string connStr) { 注释#region 注释 /**//*********************************************\ *功能: * 通过传进的其他connectionString的sql语句填充记录集 *参数: * sql :select语句 *返回: * failed: return null * success:return DataSet Object * Vegas 2008-3-21 \********************************************/ #endregion SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); DataSet ds = new DataSet(); sda.Fill(ds); return ds; } public DataSet GetDataSet(string sql) { 注释#region 注释 /**//*********************************************\ *功能: * 通过传进的sql语句填充记录集 *参数: * sql :select语句 *返回: * failed: return null * success:return DataSet Object * Peter 2006 09 19 \********************************************/ #endregion SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); DataSet ds = new DataSet(); sda.Fill(ds); return ds; } public DataSet GetDataSet(string procName, System.Data.IDataParameter[] paramers) { 注释#region 注释 /**//*********************************************\ *功能: * 执行存储过程,返回DataSet *参数: * sql:Proc Name * paramers:参数数组 *返回: DataSet Peter 2006 10 10 \********************************************/ #endregion SqlDataAdapter sda = new SqlDataAdapter(procName, connStr); sda.SelectCommand.CommandType = CommandType.StoredProcedure; foreach (System.Data.IDataParameter paramer in paramers) { sda.SelectCommand.Parameters.Add(paramer); } DataSet ds = new DataSet(); sda.Fill(ds); return ds; } #endregion GetDataTable#region GetDataTable public DataTable GetDataTable(string sql) { 注释#region 注释 /**//*********************************************\ *功能: * 通过传进的sql语句填充一个DataTable *参数: * sql :select语句 *返回: * return DataTable Object * Peter 2006 09 19 \********************************************/ #endregion SqlDataAdapter sda = new SqlDataAdapter(sql, connStr); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } #endregion ExecCommand#region ExecCommand public int ExecCommand(SqlCommand sqlcom) { 注释#region 注释 /**//*********************************************\ *功能: * 通过传进的sqlcommand对像执行该sqlcommand *参数: * sqlcom:sqlcommand对像 *返回: * return int(sqlcommand对像执行影响的行数) * Peter 2006 09 20 \********************************************/ #endregion SqlConnection conn = new SqlConnection(connStr); sqlcom.Connection = conn; conn.Open(); try { int rtn = sqlcom.ExecuteNonQuery(); return rtn; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } public int ExecCommand(string sql) { 注释#region 注释 /**//*********************************************\ *功能: * 执行该sql语句,插入,删除等语句 *参数: * sql:sql语句 *返回: * return int(sql语句执行时影响的行数) * Peter 2006 09 20 \********************************************/ #endregion if (sql.EndsWith(",")) sql = sql.Substring(0, sql.Length - 1); SqlCommand sqlcom = new SqlCommand(sql); return ExecCommand(sqlcom); } #endregion ExecuteScalar#region ExecuteScalar public object ExecuteScalar(string sql) { 注释#region 注释 /**//*********************************************\ *功能: * 执行sql语句,并获取该sql语句的查询到的第一行数据 *参数: * sql:sql select语句 *返回: * return object(在调用该函数时需要把返回值进行强制类型转换) * Peter 2006 09 20 \********************************************/ #endregion SqlConnection conn = new SqlConnection(connStr); SqlCommand sqlcom = new SqlCommand(sql, conn); conn.Open(); try { object rtn = sqlcom.ExecuteScalar(); return rtn; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } #endregion ExecSPCommand#region ExecSPCommand public void ExecSPCommand(string procName, System.Data.IDataParameter[] paramers) { 注释#region 注释 /**//*********************************************\ *功能: * 执行带参数的sql语句,也可以是存储过程 主要是insert update delete 语句 *参数: * sql:带参数的sql语句 * paramers:参数数组 *返回: 无 * Peter 2006 09 20 \********************************************/ #endregion SqlConnection conn = new SqlConnection(connStr); SqlCommand sqlcom = new SqlCommand(procName, conn); sqlcom.CommandType = CommandType.StoredProcedure; foreach (System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); try { sqlcom.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } #endregion ExecSPDataSet#region ExecSPDataSet public DataSet ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers) { 注释#region 注释 /**//*********************************************\ *功能: * 执行带参数的sql语句,主要是select 语句 *参数: * sql:带参数的sql select语句 * paramers:参数数组 *返回: * return DataSet Object * Peter 2006 09 20 \********************************************/ #endregion SqlConnection conn = new SqlConnection(connStr); SqlCommand sqlcom = new SqlCommand(sql, conn); sqlcom.CommandType = CommandType.StoredProcedure; foreach (System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } conn.Open(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlcom; DataSet ds = new DataSet(); try { da.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } #endregion UpdateTable#region UpdateTable public void UpdateTable(DataTable dt, string TableName, string KeyName) { 注释#region 注释 /**//*********************************************\ *功能: * 更新一个表 *参数: * dt:要更新的表在内存中存放的DataTable对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion foreach (DataRow dr in dt.Rows) { updateRow(dr, TableName, KeyName); } } #endregion InsertTable#region InsertTable 注释#region 注释 /**//*********************************************\ *功能: * 插入DataTable对象的所有记录到数据库中,有三个重载 *参数: * dt:要更新的表在内存中存放的DataTable对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion //用於主键是数据库表名+ID类型的 public void InsertTable(DataTable dt) { string TableName = "[" + dt.TableName + "]"; string KeyName = dt.TableName + "ID"; foreach (DataRow dr in dt.Rows) { insertRow(dr, TableName, KeyName); } } //用於主键是任意类型的 public void InsertTable(DataTable dt, string KeyName) { string TableName = "[" + dt.TableName + "]"; foreach (DataRow dr in dt.Rows) { insertRow(dr, TableName, KeyName); } } //指定表名且用於主键是任意类型的 public void InsertTable(DataTable dt, string TableName, string KeyName) { foreach (DataRow dr in dt.Rows) { insertRow(dr, TableName, KeyName); } } #endregion DeleteTable#region DeleteTable 注释#region 注释 /**//*********************************************\ *功能: * 在数据库删除DataTable对象的所有记录,有二个重载 *参数: * dt:要更新的表在内存中存放的DataTable对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion //用於默认表名的 public void DeleteTable(DataTable dt, string KeyName) { string TableName = "[" + dt.TableName + "]"; foreach (DataRow dr in dt.Rows) { deleteRow(dr, TableName, KeyName); } } //用於指定表名的 public void DeleteTable(DataTable dt, string TableName, string KeyName) { foreach (DataRow dr in dt.Rows) { deleteRow(dr, TableName, KeyName); } } #endregion GetSqlCount#region GetSqlCount // Function Name: GetSqlCount() // Function Description: Return the Count of the recored in a certern condition// // Return Value Type: int // Parameters List: string sql(e.g "select count(*) from TableName where Condition1 ..etc" // Author: Hunk Hu // Create Date: 2006/10/01 public static int GetSqlCount(string sql) { int Cnt = 0; string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; SqlConnection Con = new SqlConnection(ConnectionString); try { Con.Open(); SqlCommand Cmd = new SqlCommand(sql, Con); Cnt = (Int32)Cmd.ExecuteScalar(); Con.Close(); } catch (Exception e) { throw new Exception(e.Message.ToString()); } finally { if (Con != null) Con.Close(); } return Cnt; } #endregion ExecuteTransaction#region ExecuteTransaction // Function Name: ExecuteTransaction() // Function Description: Execute a set of sql transaction which must be successfully run,if any failed roll back the transaction // Return Value Type: bool (if all sql execute without any exception returns True,else return False) // Parameters List: string Array strSqlList[] which contains your SQL Transaction(such as insert into ;update etc); // Author: Hunk Hu // Create Date: 2006/10/01 public bool ExecuteTransaction(string[] strSqlList) { SqlConnection Con = new SqlConnection(connStr); SqlCommand Cmd = new SqlCommand(); SqlTransaction sqlTrans; Cmd.Connection = Con; Con.Open(); sqlTrans = Con.BeginTransaction(IsolationLevel.ReadCommitted); Cmd.Transaction = sqlTrans; bool rtnVal = false; try { for (int i = 0; i < strSqlList.Length; i++) { string sqlstr = strSqlList[i]; Cmd.CommandText = sqlstr; Cmd.ExecuteNonQuery(); } sqlTrans.Commit(); Con.Close(); rtnVal = true; } catch (Exception e) { sqlTrans.Rollback(); throw new Exception(e.Message.ToString()); } finally { if (Con != null) Con.Close(); } return rtnVal; } #endregion ExecuteNonQuery#region ExecuteNonQuery // Function Name: ExecuteNonQuery(string sql) // Function Description: Execute a Command (that returns no resultset and takes no parameters) against the database // Return Value Type: The number of rows affected // Parameters List: string sql (such as update ,insert , delete etc) // Author: Hunk Hu // Create Date: 2006/10/11 public int ExecuteNonQuery(string sql) { 注释#region 注释 #endregion SqlConnection conn = new SqlConnection(connStr); SqlCommand sqlcom = new SqlCommand(sql, conn); conn.Open(); try { int rtn = sqlcom.ExecuteNonQuery(); return rtn; } catch (Exception ex) { throw ex; } finally { if (conn != null) conn.Close(); } } #endregion GetSqlFirstItem#region GetSqlFirstItem public string GetSqlFirstItem(string sql) { string eItem = ""; SqlConnection Conn = new SqlConnection(connStr); SqlCommand Cmd = new SqlCommand(sql, Conn); Conn.Open(); try { eItem = Cmd.ExecuteScalar().ToString(); return eItem; } catch (Exception e) { throw new Exception(e.Message.ToString()); } finally { if (Conn != null) Conn.Close(); } } #endregion BindDataToDrpList#region BindDataToDrpList public static void BindDataToDrpList(System.Web.UI.WebControls.DropDownList drp, System.Data.DataTable dt, string fieldStr, string valuefieldStr) { /**//***********************************************************\ //功能参数: // 绑定数据到DropDownList控件 // //参数: // drp:System.Web.UI.WebControls.DropDownList // dt: System.Data.DataTable // fieldStr:DataTable的字段名,作为DropDownList的Text valuefieldStr:DataTable的字段名,作为DropDownList的value //返回:无 // by peter 2006 09 25 \***********************************************************/ if (dt.Rows.Count != 0) { for (int i = 0; i < dt.Rows.Count; i++) { drp.Items.Add(new System.Web.UI.WebControls.ListItem(dt.Rows[i][fieldStr].ToString(), dt.Rows[i][valuefieldStr].ToString())); } } } #endregion //私有函数 IsNumeric#region IsNumeric // Function Name: IsNumeric(string number) // Function Description: 判断number是否为数字类型 // Return Value Type: bool // Parameters List: string // Author: angela // Create Date: 2006/10/11 public bool IsNumeric(string number) { try { int k = 0; for (int i = 0; i < number.Length; i++) { if (!char.IsNumber(number, i)) { if (number.Substring(i, 1) != "." || k >= 2) { return false; } else { k = k + 1; } } } return true; } catch { return false; } } #endregion DbType#region DbType private System.Data.DbType GetDbType(Type type) { 注释#region 注释 /**//*********************************************\ *功能: * 返回Type类型的名称 *参数: * type:Type对像 *返回:System.Data.DbType * Peter 2006 09 20 \********************************************/ #endregion DbType result = DbType.String; if (type.Equals(typeof(int)) || type.IsEnum) result = DbType.Int32; else if (type.Equals(typeof(long))) result = DbType.Int32; else if (type.Equals(typeof(double)) || type.Equals(typeof(Double))) result = DbType.Decimal; else if (type.Equals(typeof(DateTime))) result = DbType.DateTime; else if (type.Equals(typeof(bool))) result = DbType.Boolean; else if (type.Equals(typeof(string))) result = DbType.String; else if (type.Equals(typeof(decimal))) result = DbType.Decimal; else if (type.Equals(typeof(byte[]))) result = DbType.Binary; else if (type.Equals(typeof(Guid))) result = DbType.Guid; return result; } #endregion updateRow#region updateRow private void updateRow(DataRow dr, string TableName, string KeyName) { 注释#region 注释 /**//*********************************************\ *功能: * 更新一个表中的一行,私有函数 *参数: * dr:要向数据库中更新的DataRow对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion if (dr[KeyName] == DBNull.Value) { throw new Exception(KeyName + "的值不能为空"); } if (dr.RowState == DataRowState.Deleted) { deleteRow(dr, TableName, KeyName); } else if (dr.RowState == DataRowState.Modified) { midifyRow(dr, TableName, KeyName); } else if (dr.RowState == DataRowState.Added) { insertRow(dr, TableName, KeyName); } else if (dr.RowState == DataRowState.Unchanged) { midifyRow(dr, TableName, KeyName); } } #endregion deleteRow#region deleteRow private void deleteRow(DataRow dr, string TableName, string KeyName) { 注释#region 注释 /**//*********************************************\ *功能: * 在数据库中删除该DataRow表示的记录,私有函数 *参数: * dr:标识要在数据库中删除哪一行的DataRow对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion string sql = "Delete {0} where {1} =@{1}"; DataTable dtb = dr.Table; sql = string.Format(sql, TableName, KeyName); SqlCommand sqlcom = new SqlCommand(sql); System.Data.IDataParameter iparam = new SqlParameter(); iparam.ParameterName = "@" + KeyName; iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType); iparam.Value = dr[KeyName]; sqlcom.Parameters.Add(iparam); ExecCommand(sqlcom); } #endregion midifyRow#region midifyRow private void midifyRow(DataRow dr, string TableName, string KeyName) { 注释#region 注释 /**//*********************************************\ *功能: * 在数据库中修改该DataRow标识的记录,私有函数 *参数: * dr:标识要在数据库中修改哪一行的DataRow对像 * TableName:要更新的表的名字 * KeyName:要更新的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion string UpdateSql = "Update {0} set {1} {2}"; string setSql = "{0}= @{0}"; string wherSql = " Where {0}=@{0}"; StringBuilder setSb = new StringBuilder(); SqlCommand sqlcom = new SqlCommand(); DataTable dtb = dr.Table; for (int k = 0; k < dr.Table.Columns.Count; ++k) { System.Data.IDataParameter iparam = new SqlParameter(); iparam.ParameterName = "@" + dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters.Add(iparam); if (dtb.Columns[k].ColumnName == KeyName) { wherSql = string.Format(wherSql, KeyName); } else { setSb.Append(string.Format(setSql, dtb.Columns[k].ColumnName)); setSb.Append(","); } } string setStr = setSb.ToString(); setStr = setStr.Substring(0, setStr.Length - 1); //trim , string sql = string.Format(UpdateSql, TableName, setStr, wherSql); sqlcom.CommandText = sql; ExecCommand(sqlcom); } #endregion insertRow#region insertRow private void insertRow(DataRow dr, string TableName, string KeyName) { 注释#region 注释 /**//*********************************************\ *功能: * 在数据库中插入该DataRow,私有函数 *参数: * dr:要在数据库中插入的DataRow对像 * TableName:要插入记录的表的名字 * KeyName:要插入记录的表的主键名称 *返回:无 * Peter 2006 09 20 \********************************************/ #endregion string InsertSql = "Insert into {0}({1}) values({2})"; SqlCommand sqlcom = new SqlCommand(); DataTable dtb = dr.Table; StringBuilder insertValues = new StringBuilder(); StringBuilder cloumn_list = new StringBuilder(); for (int k = 0; k < dr.Table.Columns.Count; ++k) { //just for genentae, if (dtb.Columns[k].ColumnName == KeyName) continue; System.Data.IDataParameter iparam = new SqlParameter(); iparam.ParameterName = "@" + dtb.Columns[k].ColumnName; iparam.DbType = GetDbType(dtb.Columns[k].DataType); iparam.Value = dr[k]; sqlcom.Parameters.Add(iparam); cloumn_list.Append(dtb.Columns[k].ColumnName); insertValues.Append("@" + dtb.Columns[k].ColumnName); cloumn_list.Append(","); insertValues.Append(","); } string cols = cloumn_list.ToString(); cols = cols.Substring(0, cols.Length - 1); string values = insertValues.ToString(); values = values.Substring(0, values.Length - 1); string sql = string.Format(InsertSql, TableName, cols, values); sqlcom.CommandText = sql; ExecCommand(sqlcom); } #endregion}