|
數(shù)據(jù)庫連接類庫大全 可以勝任sqlserver數(shù)據(jù)庫操作 簡化編程難度 在頁面中可以直接調(diào)用 也可以供其他類庫調(diào)用 以下是類庫 直接復(fù)制到cs文件中既可 需要添加System.Configuration引用
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
namespace ZfServer
{
public class DBAction
{
private SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DB"].ToString()); //獲取config AppSettings節(jié)點設(shè)置的數(shù)據(jù)庫連接用戶名和密碼
/// <summary>
/// 根據(jù)SQL查詢返回DataSet對象，如果沒有查詢到則返回NULL
/// </summary>
/// <param name="sql">查詢語句</param>
/// <returns>DataSet</returns>
public DataSet returnDS(SqlCommand cmd, string TempTableName)
{
DataSet ds = new DataSet();
try
{
cmd.Connection = con;
cmd.CommandTimeout = 30;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, TempTableName);
}
catch (Exception e)
{
throw (e);
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 根據(jù)SQL查詢返回DataSet對象，如果沒有查詢到則返回NULL
/// </summary>
/// <param name="sql">查詢語句</param>
/// <param name="sRecord">開始記錄數(shù)</param>
/// <param name="mRecord">最大記錄數(shù)</param>
/// <returns>DataSet</returns>
public DataSet returnDS(SqlCommand cmd, string TempTableName, int sRecord, int mRecord)
{
DataSet ds = new DataSet();
try
{
cmd.Connection = con;
cmd.CommandTimeout = 30;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, sRecord, mRecord, TempTableName);
}
catch (Exception e)
{
ds = null;
throw (e);
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 返回DataTable對象
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public DataTable returnDb(SqlCommand cmd)
{
DataTable ds = new DataTable();
try
{
cmd.Connection = con;
cmd.CommandTimeout = 30;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
}
catch (Exception e)
{
throw (e);
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 根據(jù)SqlDataComand對象返回查找的記錄集,如果沒有則返回NULL
/// </summary>
/// <param name="cmd">SqlCommand對象</param>
/// <returns>SqlDataReader對象</returns>
public SqlDataReader returnReader(SqlCommand cmd)
{
SqlDataReader reader;
try
{
cmd.Connection = con;
cmd.CommandTimeout = 30;
this.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
reader = null;
throw (e);
}
return reader;
}
/// <summary>
/// 根據(jù)SqlDataComand對象返回查找的值,如果沒有則返回NULL
/// </summary>
/// <param name="cmd">SqlCommand對象</param>
/// <returns>Object對象</returns>
public object returnScalar(SqlCommand cmd)
{
object obj;
try
{
cmd.Connection = con;
cmd.CommandTimeout = 30;
this.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception e)
{
obj = null;
throw (e);
}
return obj;
}
/// <summary>
/// 對數(shù)據(jù)庫的增，刪，改的操作
/// </summary>
/// <param name="sql">SQL語句</param>
/// <returns>是否成功</returns>
public bool OperateDB(SqlCommand cmd)
{
bool succeed = false;
int cnt = 0;
try
{
cmd.Connection = con;
cmd.CommandTimeout =30;
this.Open();
cnt = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw (e);
}
finally
{
if (cnt > 0)
{
succeed = true;
}
this.Close();
}
return succeed;
}
/// <summary>
/// 獲得該SQL查詢返回DataTable，如果沒有查詢到則返回NULL
/// </summary>
/// <param name="sql">查詢語句</param>
/// <returns></returns>
public DataTable getTable(SqlCommand cmd, string TempTableName)
{
DataTable tb = null;
DataSet ds = this.returnDS(cmd, TempTableName);
if (ds != null)
{
tb = ds.Tables[TempTableName];
}
return tb;
}
/// <summary>
/// 打開數(shù)據(jù)庫連接.
/// </summary>
private void Open()
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
else if (con.State == System.Data.ConnectionState.Broken)
{
con.Close();
con.Open();
}
}
/// <summary>
/// 關(guān)閉數(shù)據(jù)庫連接
/// </summary>
public void Close()
{
if (con != null)
{
con.Close();
}
}
/// <summary>
/// 釋放資源
/// </summary>
public void Dispose()
{
// 確認(rèn)連接是否已經(jīng)關(guān)閉
if (con != null)
{
con.Dispose();
con = null;
}
}
}
}
|
