///<summary> ///版本:SQLHelper 1.3 ///模块编号:1001 ///作用:数据库访问通用类 ///作者:邓政军 ///修改日期:2011-12-4 ///1.有新增 ///2.将ExecuteNonQuery修改成int返回值 ///3.完善注释 ///</summary> using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; namespace SqlHelper { public class SQLHelper { // //第一二版 /// <summary> /// 获取数据连接字符串 /// </summary> /// <returns>连接字符串</returns> public static string GetConnectionString() { return ConfigurationManager.ConnectionStrings["ASPNET3_5_DataBase"].ConnectionString; } /// <summary> /// 执行查询并将结果返回至DataTable中 /// </summary> /// <param name="strSql">查询语句</param> /// <param name="parameters">可能带的参数</param> /// <returns>返回一张查询结果表</returns> public static DataTable ExecuteDataTable(string strSql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; foreach (SqlParameter p in parameters) { cmd.Parameters.Add(p); } DataSet ds = new DataSet(); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.Fill(ds); return ds.Tables[0]; } } } } /// <summary> /// (重载)执行查询并将结果返回至DataTable中 /// </summary> /// <param name="strSql">查询语句</param> /// <returns>返回一张查询结果表</returns> public static DataTable ExecuteDataTable(string strSql) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; DataSet ds = new DataSet(); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.Fill(ds); return ds.Tables[0]; } } } } /// <summary> /// 执行对数据的增删改操作 /// </summary> /// <param name="strSql"></param> /// <param name="parameters"></param> /// <returns>返回影响行数</returns> public static int ExecuteNonQuery(string strSql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; foreach (SqlParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteNonQuery(); } } } /// <summary> /// (重载)执行对数据的增删改操作 /// </summary> /// <param name="strSql">查询语句</param> /// <returns>返回影响行数</returns> public static int ExecuteNonQuery(string strSql) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; return cmd.ExecuteNonQuery(); } } } /// <summary> /// 执行查询并返回结果集中第一行第一列的值 /// </summary> /// <param name="strSql"></param> /// <param name="parameters"></param> /// <returns></returns> public static object ExecuteScalar(string strSql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; foreach (SqlParameter p in parameters) { cmd.Parameters.Add(p); } return cmd.ExecuteScalar(); } } } /// <summary> /// (重载)执行查询并返回结果集中第一行第一列的值 /// </summary> /// <param name="strSql">查询语句</param> /// <returns></returns> public static object ExecuteScalar(string strSql) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = strSql; return cmd.ExecuteScalar(); } } } // // //第三版新增 /// <summary> /// (重载)向指定表中插入一行数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="cols">哈希表字段和值</param> /// <returns></returns> public static int Insert(string tableName, Hashtable cols) { //形式:insert into tablename(fileds)values(values); int num = 0; string strFileds = "("; string strValues = "("; //添加字段名和值 foreach (DictionaryEntry item in cols) { if (num == 0) { strFileds += item.Key.ToString(); strValues += item.Value.ToString(); } else { strFileds += "," + item.Key.ToString(); strValues += "," + item.Value.ToString(); } num++; } strFileds += ")"; strValues += ");"; string strSql = "insert into " + tableName + strFileds + "values" + strValues; return SQLHelper.ExecuteNonQuery(strSql); } } }
SQLHelper数据库通用访问类(第三版)
最新推荐文章于 2020-03-28 22:30:19 发布