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

}

}