SqlHelper:带sqlParameter，对sql server增、册、查、改的公用方法

SqlHelper:带sqlParameter，对sql server增、册、查、改的公用方法

2011-10-13 14:58:54      我来说两句      

收藏     我要投稿

using System.Data; 
using System.Data.SqlCient; 
 
        #region ###jonse 
        //共通函数 把sql语句执行的查询结果放入datatable中 
        // 传入sql语句，datatable名称，数据连接字符可选 
        public static DataTable GetDataTable(out string sEx, string sSql, string sDataTableName, string sConnStr = "", SqlParameter[] commandParameters = null) 
        { 
            sEx = string.Empty; 
            if (string.IsNullOrEmpty(sDataTableName)) 
            { 
                sEx = "datatable的名字不能为空"; 
                return null; 
            } 
            if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString; 
            DataTable dt = new DataTable(); 
 
            try 
            { 
                if (commandParameters == null) 
                { 
                    dt = ExecuteDataTable(sConnStr, CommandType.Text, sSql); 
                } 
                else 
                { 
                    dt = ExecuteDataTable(sConnStr, CommandType.StoredProcedure, sSql, commandParameters); 
                } 
            } 
            catch (Exception ex) 
            { 
                sEx = ex.Message.ToString(); 
            } 
            finally 
            { 
                dt.TableName = sDataTableName; 
            } 
 
            return dt; 
        } 
 
        // 取dataset   
        public static DataSet GetDataSet(out string sError, string sSQL, string sConnStr="")  
        {  
            DataSet ds = null;  
            sError = string.Empty;  
            try  
            { 
                if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString; 
 
                SqlConnection conn = new SqlConnection(sConnStr);  
                SqlCommand comm = new SqlCommand();  
                comm.Connection = conn;  
                comm.CommandText = sSQL;  
                SqlDataAdapter dapter = new SqlDataAdapter(comm);  
                ds = new DataSet();  
                dapter.Fill(ds);  
            }  
            catch (Exception ex)  
            {  
                sError = ex.Message;  
            }  
            return ds;  
        }  
 
 
        // 取某个单一的元素    
        public static object GetSingle(out string sError, string sSQL) 
        { 
            DataTable dt = GetDataTable(out sError, sSQL, "singleTbl"); 
            if (dt != null && dt.Rows.Count > 0) 
            { 
                return dt.Rows[0][0]; 
            } 
 
            return null; 
        } 
 
        // 取最大的ID   
        public static Int32 GetMaxID(out string sError, string sKeyField, string sTableName) 
        { 
            DataTable dt = GetDataTable(out sError, "select isnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]","maxID"); 
            if (dt != null && dt.Rows.Count > 0) 
            { 
                return Convert.ToInt32(dt.Rows[0][0].ToString()); 
            } 
 
            return 0; 
        } 
 
 
        // 执行 insert,update,delete 动作，也可以使用事务   
        public static bool UpdateData(out string sError, string sSQL, SqlParameter[] sqlParams=null,bool bUseTransaction = false, string sConnStr = "") 
        { 
            int iResult = 0; 
            sError = string.Empty; 
            if (string.IsNullOrEmpty(sConnStr)) sConnStr = DbHelperSQL.connectionString; 
 
            if (!bUseTransaction) 
            { 
                try 
                { 
                    SqlConnection conn = new SqlConnection(sConnStr); 
                    if (conn.State != ConnectionState.Open) 
                        conn.Open(); 
                    SqlCommand cmd = new SqlCommand(); 
                    cmd.Connection = conn; 
                    cmd.CommandText = sSQL; 
 
                    if (sqlParams != null) 
                    { 
                        foreach (SqlParameter parm in sqlParams) 
                            cmd.Parameters.Add(parm); 
                    } 
 
                    iResult = cmd.ExecuteNonQuery(); 
                } 
                catch (Exception ex) 
                { 
                    sError = ex.Message; 
                    iResult = -1; 
                } 
            } 
            else // 使用事务   
            { 
                SqlTransaction trans = null; 
                try 
                { 
                    SqlConnection conn = new SqlConnection(sConnStr); 
                    if (conn.State != ConnectionState.Open) 
                        conn.Open(); 
                    trans = conn.BeginTransaction(); 
                    SqlCommand cmd = new SqlCommand(); 
                    cmd.Connection = conn; 
                    cmd.CommandText = sSQL; 
 
                    if (sqlParams != null) 
                    { 
                        foreach (SqlParameter parm in sqlParams) 
                            cmd.Parameters.Add(parm); 
                    } 
 
                    cmd.Transaction = trans; 
                    iResult = cmd.ExecuteNonQuery(); 
                    trans.Commit(); 
                } 
                catch (Exception ex) 
                { 
                    sError = ex.Message; 
                    iResult = -1; 
                    trans.Rollback(); 
                } 
            } 
 
            return iResult > 0; 
        }  
 
 
        #endregion ###jonse 

提示：

1，需要定义 DbHelperSQL.connectionString 变量

     关于数据库连接字符串，一般有2种格式:
             (1),Windows验证    
                       Common.ConnString = @"Initial Catalog=数据库名;Data Source=电脑名或IP地址;Integrated Security=SSPI";

             (2), Sql混合验证

                         Common.ConnString = @"server=电脑名或IP地址;database=数据库名;uid=用户名;password=密码";

2, 具体调用例子，与下面的相似：http://www.2cto.com/database/201110/107655.html

摘自：keenweiwei的专栏