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

精选转载

hao123_66 2014-03-20 22:59:55

文章标签 server sql语句 null public 文章分类 .Net 后端开发

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的专栏