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

云中漫步

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(outstring sError, string sKeyField, string sTableName){DataTable dt = GetDataTable(outsError, "selectisnull(max([" + sKeyField + "]),0)asMaxIDfrom[" + 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(outstring 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://blog.csdn.net/keenweiwei/article/details/6845709