c# 数据库,连接类库 ---DbHelp.cs

using System; using System.Data.SqlClient; using System.Data; using System.Collections.Generic; using System.Web.Configuration; using System.Data.Common; using System.Web; using System.Text; namespace pub.mo { public class dbhelp { /// <summary> /// sql prv /// </summary> private static IDbProvider m_provider; /// <summary> /// oledb prv /// </summary> private static IDbProvider m_provider_oledb; private static object lockHelper = new object(); private static object lockHelper_oledb = new object(); #region 插入,更新或删除 /// <summary> /// 插入,更新或删除 /// </summary> /// <param name="sql">sql语句</param> /// <returns>int</returns> public static int update(string sql) { return update(sql, CommandType.Text, null, defaultPrv); } /// <summary> /// 插入,更新或删除 /// </summary> /// <param name="sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>int</returns> public static int update(string sql,IDataParameter[] sp) { return update(sql, CommandType.Text, sp, defaultPrv); } /// <summary> /// 插入,更新或删除 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <returns>int</returns> public static int update(string sql, CommandType ct, IDataParameter[] sp) { return update(sql, ct, sp, defaultPrv); } /// <summary> /// 插入,更新或删除 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="connDb">数据驱动</param> /// <returns>int</returns> public static int update(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb) { using (IDbConnection conn = connDb.create_connection(connDb.get_conn)) { IDbCommand cmd = conn.CreateCommand(); prepare_command(cmd, conn, null, ct, sql, sp); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } #endregion #region DataTable /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">sql语句</param> /// <returns>DataTable</returns> public static DataTable datatable(string sql) { return datatable(sql, CommandType.Text, null, defaultPrv); } /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">存储过程名称</param> /// <param name="ct"></param> /// <returns></returns> public static DataTable datatable(string sql, CommandType ct) { return datatable(sql, ct, null, defaultPrv); } /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>DataTable</returns> public static DataTable datatable(string sql, IDataParameter[] sp) { return datatable(sql, CommandType.Text, sp, defaultPrv); } /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <returns>DataTable</returns> public static DataTable datatable(string sql, CommandType ct, IDataParameter[] sp) { return datatable(sql, ct, sp, defaultPrv); } /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="connDb">数据驱动</param> /// <returns>DataTable</returns> public static DataTable datatable(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb) { using (IDbConnection conn = connDb.create_connection(connDb.get_conn)) { IDbCommand cmd = conn.CreateCommand(); DataTable dt = new DataTable(); DbDataAdapter sda; prepare_command(cmd, conn, null, ct, sql, sp); sda = connDb.new_DataAdapter(); sda.SelectCommand = (DbCommand)cmd; sda.Fill(dt); cmd.Parameters.Clear(); cmd.Dispose(); return dt; } } #endregion #region DataSet /// <summary> /// 返回DataSet /// </summary> /// <param name="sql">sql语句</param> /// <returns>DataSet</returns> public static DataSet dataset(string sql) { return dataset(sql, CommandType.Text, null, defaultPrv); } /// <summary> /// 返回DataSet /// </summary> /// <param name="sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>DataSet</returns> public static DataSet dataset(string sql, IDataParameter[] sp) { return dataset(sql, CommandType.Text, sp, defaultPrv); } /// <summary> /// 返回DataSet /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <returns>DataSet</returns> public static DataSet dataset(string sql, CommandType ct, IDataParameter[] sp) { return dataset(sql, ct, sp, defaultPrv); } public static DataSet dataset_r(ref string sql, CommandType ct, IDataParameter[] sp) { return dataset(sql, ct, sp, defaultPrv); } public static DataSet dataset_r(StringBuilder sql, CommandType ct, IDataParameter[] sp) { return dataset(sql.ToString(), ct, sp, defaultPrv); } /// <summary> /// 返回DataSet /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="connDb">数据驱动</param> /// <returns>DataSet</returns> public static DataSet dataset(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb) { using (IDbConnection conn = connDb.create_connection(connDb.get_conn)) { IDbCommand cmd = conn.CreateCommand(); DataSet ds = new DataSet(); DbDataAdapter sda; prepare_command(cmd, conn, null, ct, sql, sp); sda = connDb.new_DataAdapter(); sda.SelectCommand = (DbCommand)cmd; sda.Fill(ds); cmd.Parameters.Clear(); cmd.Dispose(); return ds; } } #endregion public static DataTable datatable1(string sql) { using (IDbConnection conn = defaultPrv.create_connection(defaultPrv.get_conn)) { IDbCommand cmd = conn.CreateCommand(); DataTable ds = new DataTable(); DbDataAdapter sda; prepare_command(cmd, conn, null, CommandType.Text, sql, null); sda = defaultPrv.new_DataAdapter(); sda.SelectCommand = (DbCommand)cmd; sda.Fill(ds); cmd.Parameters.Clear(); cmd.Dispose(); return ds; } } public static DataTable datatable2(string sql) { using (IDbConnection conn = defaultPrv.create_connection(defaultPrv.get_conn)) { DataTable dt = new DataTable(); dt.Load(read(sql, CommandType.Text, null, defaultPrv)); return dt; } } #region DataTable分页 /// <summary> /// DataTable分页 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页记录数</param> /// <returns>DataTable</returns> public static DataTable datatable_fy(string sql, int pageIndex, int pageSize) { return datatable_fy(sql, CommandType.Text, null, defaultPrv, pageIndex, pageSize); } /// <summary> /// DataTable分页 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页记录数</param> /// <returns>DataTable</returns> public static DataTable datatable_fy(string sql, CommandType ct, IDataParameter[] sp, int pageIndex, int pageSize, IDbProvider connDb) { return datatable_fy(sql, ct, sp, connDb, pageIndex, pageSize); } /// <summary> /// DataTable分页 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="connDb">数据驱动</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页记录数</param> /// <returns>DataTable</returns> public static DataTable datatable_fy(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb, int pageIndex, int pageSize) { using (IDbConnection conn = connDb.create_connection(connDb.get_conn)) { IDbCommand cmd = conn.CreateCommand(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DbDataAdapter sda; prepare_command(cmd, conn, null, ct, sql, sp); sda = connDb.new_DataAdapter(); sda.SelectCommand = (DbCommand)cmd; if (pageIndex < 1) pageIndex = 1; int startRecord = (pageIndex - 1) * pageSize; sda.Fill(ds, startRecord, pageSize, "0"); dt = ds.Tables[0]; cmd.Parameters.Clear(); cmd.Dispose(); return dt; } } #endregion #region DataReader /// <summary> /// 读取记录 /// </summary> /// <param name="sql">sql语句</param> /// <returns>IDataReader</returns> public static IDataReader read(string sql) { return read(sql, CommandType.Text, null, defaultPrv); } /// <summary> /// 读取记录 /// </summary> /// <param name="sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>IDataReader</returns> public static IDataReader read(string sql, IDataParameter[] sp) { return read(sql, CommandType.Text, sp, defaultPrv); } /// <summary> /// 读取记录 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <returns>IDataReader</returns> public static IDataReader read(string sql, CommandType ct, IDataParameter[] sp) { return read(sql, ct, sp, defaultPrv); } /// <summary> /// 读取记录 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="connDb">数据驱动</param> /// <returns>IDataReader</returns> public static IDataReader read(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb) { IDbConnection conn = connDb.create_connection(connDb.get_conn); try { IDbCommand cmd = conn.CreateCommand(); prepare_command(cmd, conn, null, ct, sql, sp); IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); return null; } } #endregion #region 返回第一行第一列 可能会null /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql">sql语句</param> /// <returns>object</returns> public static object scalar(string sql) { return scalar(sql, CommandType.Text, null, defaultPrv); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>object</returns> public static object scalar(string sql, IDataParameter[] sp) { return scalar(sql, CommandType.Text, sp, defaultPrv); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <returns>object</returns> public static object scalar(string sql, CommandType ct, IDataParameter[] sp) { return scalar(sql, ct, sp, defaultPrv); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="conn_prov">数据驱动</param> /// <returns>object</returns> public static object scalar(string sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb) { using (IDbConnection conn = connDb.create_connection(connDb.get_conn)) { IDbCommand cmd = conn.CreateCommand(); prepare_command(cmd, conn, null, ct, sql, sp); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } #endregion #region 返回第一行第一列 /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="str_sql">sql语句</param> /// <returns>string</returns> public static string get_str(string str_sql) { return get_str(str_sql, CommandType.Text, null, defaultPrv, config.empty); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="str_sql">sql语句</param> /// <param name="str_return">无时返回值</param> /// <returns>string</returns> public static string get_str(string str_sql, string str_return) { return get_str(str_sql, CommandType.Text, null, defaultPrv,str_return); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="str_sql">sql语句</param> /// <param name="sp">参数</param> /// <returns>string</returns> public static string get_str(string str_sql, IDataParameter[] sp) { return get_str(str_sql, CommandType.Text, sp, defaultPrv, config.empty); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="str_sql">sql语句</param> /// <param name="sp">参数</param> /// <param name="str_return">无时返回值</param> /// <returns>string</returns> public static string get_str(string str_sql, IDataParameter[] sp, string str_return) { return get_str(str_sql, CommandType.Text, sp, defaultPrv,str_return); } public static string get_str(string str_sql, CommandType ct, IDataParameter[] sp, string str_return) { return get_str(str_sql, ct, sp, defaultPrv, str_return); } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="str_sql">sql语句</param> /// <param name="ct">命令类型</param> /// <param name="sp">参数</param> /// <param name="str_return">无时返回值</param> /// <param name="connDb">数据驱动</param> /// <returns>string</returns> public static string get_str(string str_sql, CommandType ct, IDataParameter[] sp, IDbProvider connDb, string str_return) { string str; using (IDataReader dr = dbhelp.read(str_sql, ct, sp)) { if (dr.Read()) str = dr[0].ToString(); else str = str_return; /* * select max(ID)+1 from ClassList where len(ID)=4 and left(ID,2)=70 * 如出现以上sql语句时 * dr.Read()=True 且dr[0].ToString()为空 * * */ if (string.IsNullOrEmpty(str)) { str = str_return; } dr.Close(); dr.Dispose(); return str; } } #endregion /// <summary> /// 参数 /// </summary> /// <param name="cmd">命令</param> /// <param name="conn">数据驱动字符串</param> /// <param name="trans">事务</param> /// <param name="cmd_type">命令类型</param> /// <param name="cmd_text">sql或存储过程名称</param> /// <param name="sp">参数</param> private static void prepare_command(IDbCommand cmd, IDbConnection conn, SqlTransaction trans, CommandType cmd_type, string cmd_text, IDataParameter[] sp) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmd_text; //if (trans != null) //cmd.Transaction = trans; cmd.CommandType = cmd_type; if (sp != null)//添加参数 { for (int sp_i = 0; sp_i < sp.Length; sp_i++) cmd.Parameters.Add(sp[sp_i]); } } #region 数据库类型 /// <summary> /// 默认 数据库 /// </summary> public static IDbProvider defaultPrv { get { if (m_provider == null) { lock (lockHelper) { if (m_provider == null) { m_provider = new SqlServerProvider(config.connStr); } } } return m_provider; } } /// <summary> /// OleDb 数据库 /// </summary> public static IDbProvider oledb_prv { get { if (m_provider_oledb == null) { lock (lockHelper_oledb) { if (m_provider_oledb == null) { m_provider_oledb = new OleDbProvider(config.connStr); } } } return m_provider_oledb; } } #endregion } }