Home
Backend Development
C#.Net Tutorial
C#.NET universal database access encapsulation class (ACCESS, SQLServer, Oracle)
Backend Development
C#.Net Tutorial
C#.NET universal database access encapsulation class (ACCESS, SQLServer, Oracle)
C#.NET universal database access encapsulation class (ACCESS, SQLServer, Oracle)
Feb 25, 2017 am 11:18 AM
在app.config文件中寫(xiě)上數(shù)據(jù)庫(kù)中連接信息：
<appSettings>
<add key="connectionstring" value="Data Source=LocalHost;Initial Catalog=Test001;
User ID=sa;Password=aaaaaa" />
<add key="DataType" value="SqlServer" />
</appSettings>
app.config文件中代碼解析：
<appSettings>
<add key="connectionstring" value="Data Source=你的數(shù)據(jù)庫(kù)器（本機(jī)可用LocalHost,如果是EXPRESS開(kāi)發(fā)版則必須是.\SQLEXPRESS);
Initial Catalog=數(shù)據(jù)庫(kù)名字;
User ID=用戶(hù)名;Password=你自己的密碼" />
<add key="TemplatePATH" value="Template" />
</appSettings>Small note:
Similar to the web program, you can put the database connection information in web.config.
C#.NET universal database access encapsulation class code is as follows: (This code comes from Baidu Library, not written by myself)
<pre code_snippet_id="147172" snippet_file_name="blog_20140108_1_6139061" class="html" name="code">
<div><pre code_snippet_id="147172" snippet_file_name="blog_20140108_1_6139061" class="prettyprint" name="code">
<pre code_snippet_id="147172" snippet_file_name="blog_20140108_1_6139061" class="csharp" name="code">using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Configuration;
using System.Reflection;
namespace SystemFramework.DAL
{
class DataBaseLayer
{
#region 屬性變量
private string connectionString;
public string ConntionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}
//數(shù)據(jù)訪(fǎng)問(wèn)基礎(chǔ)類(lèi)--構(gòu)造函數(shù)
public DataBaseLayer(string strConnect, string dataType)
{
this.ConntionString = strConnect;
this.DbType = dataType;
}
//數(shù)據(jù)訪(fǎng)問(wèn)基礎(chǔ)類(lèi)--構(gòu)造函數(shù)
public DataBaseLayer()
{
this.connectionString =ConfigurationManager.AppSettings["ConnectionString"];
this.dbType =ConfigurationManager.AppSettings["DataType"];
//也可以在代碼中直接賦值
//this.connectionString = "data source=192.168.1.43;user id=sa;pwd=sa;database=temphrdb";
//this.dbType = "SqlServer";
}
/// <summary>
/// 數(shù)據(jù)庫(kù)類(lèi)型
/// </summary>
private string dbType;
public string DbType
{
get
{
if (string.IsNullOrEmpty(dbType))
{
return "Access";
}
else
{
return dbType;
}
}
set
{
if (value != string.Empty && value != null)
{
dbType = value;
}
if (string.IsNullOrEmpty(dbType))
{
dbType =ConfigurationManager.AppSettings["DataType"];
}
if (string.IsNullOrEmpty(dbType))
{
dbType = "Access";
}
}
}
#endregion
#region 轉(zhuǎn)換參數(shù)
private System.Data.IDbDataParameter iDbPara(string ParaName, string DataType)
{
switch (this.DbType)
{
case "SqlServer":
return GetSqlPara(ParaName, DataType);
case "Oracle":
return GetOleDbPara(ParaName, DataType);
case "Access":
return GetOleDbPara(ParaName, DataType);
default:
return GetSqlPara(ParaName, DataType);
}
}
private SqlParameter GetSqlPara(string ParaName, string DataType)
{
switch (DataType)
{
case "Decimal":
return new SqlParameter(ParaName, SqlDbType.Decimal);
case "Varchar":
return new SqlParameter(ParaName, SqlDbType.VarChar);
case "DateTime":
return new SqlParameter(ParaName, SqlDbType.DateTime);
case "Iamge":
return new SqlParameter(ParaName, SqlDbType.Image);
case "Int":
return new SqlParameter(ParaName, SqlDbType.Int);
case "Text":
return new SqlParameter(ParaName, SqlDbType.NText);
default:
return new SqlParameter(ParaName, SqlDbType.VarChar);
}
}
private OracleParameter GetOraclePara(string ParaName, string DataType)
{
switch (DataType)
{
case "Decimal":
return new OracleParameter(ParaName, OracleType.Double);
case "Varchar":
return new OracleParameter(ParaName, OracleType.VarChar);
case "DateTime":
return new OracleParameter(ParaName, OracleType.DateTime);
case "Iamge":
return new OracleParameter(ParaName, OracleType.BFile);
case "Int":
return new OracleParameter(ParaName, OracleType.Int32);
case "Text":
return new OracleParameter(ParaName, OracleType.LongVarChar);
default:
return new OracleParameter(ParaName, OracleType.VarChar);
}
}
private OleDbParameter GetOleDbPara(string ParaName, string DataType)
{
switch (DataType)
{
case "Decimal":
return new OleDbParameter(ParaName, System.Data.DbType.Decimal);
case "Varchar":
return new OleDbParameter(ParaName, System.Data.DbType.String);
case "DateTime":
return new OleDbParameter(ParaName, System.Data.DbType.DateTime);
case "Iamge":
return new OleDbParameter(ParaName, System.Data.DbType.Binary);
case "Int":
return new OleDbParameter(ParaName, System.Data.DbType.Int32);
case "Text":
return new OleDbParameter(ParaName, System.Data.DbType.String);
default:
return new OleDbParameter(ParaName, System.Data.DbType.String);
}
}
#endregion
#region 創(chuàng)建 Connection 和 Command
private IDbConnection GetConnection()
{
switch (this.DbType)
{
case "SqlServer":
return new SqlConnection(this.ConntionString);
case "Oracle":
return new OracleConnection(this.ConntionString);
case "Access":
return new OleDbConnection(this.ConntionString);
default:
return new SqlConnection(this.ConntionString);
}
}
private IDbCommand GetCommand(string Sql, IDbConnection iConn)
{
switch (this.DbType)
{
case "SqlServer":
return new SqlCommand(Sql, (SqlConnection)iConn);
case "Oracle":
return new OracleCommand(Sql, (OracleConnection)iConn);
case "Access":
return new OleDbCommand(Sql, (OleDbConnection)iConn);
default:
return new SqlCommand(Sql, (SqlConnection)iConn);
}
}
private IDbCommand GetCommand()
{
switch (this.DbType)
{
case "SqlServer":
return new SqlCommand();
case "Oracle":
return new OracleCommand();
case "Access":
return new OleDbCommand();
default:
return new SqlCommand();
}
}
private IDataAdapter GetAdapater(string Sql, IDbConnection iConn)
{
switch (this.DbType)
{
case "SqlServer":
return new SqlDataAdapter(Sql, (SqlConnection)iConn);
case "Oracle":
return new OracleDataAdapter(Sql, (OracleConnection)iConn);
case "Access":
return new OleDbDataAdapter(Sql, (OleDbConnection)iConn);
default:
return new SqlDataAdapter(Sql, (SqlConnection)iConn); ;
}
}
private IDataAdapter GetAdapater()
{
switch (this.DbType)
{
case "SqlServer":
return new SqlDataAdapter();
case "Oracle":
return new OracleDataAdapter();
case "Access":
return new OleDbDataAdapter();
default:
return new SqlDataAdapter();
}
}
private IDataAdapter GetAdapater(IDbCommand iCmd)
{
switch (this.DbType)
{
case "SqlServer":
return new SqlDataAdapter((SqlCommand)iCmd);
case "Oracle":
return new OracleDataAdapter((OracleCommand)iCmd);
case "Access":
return new OleDbDataAdapter((OleDbCommand)iCmd);
default:
return new SqlDataAdapter((SqlCommand)iCmd);
}
}
#endregion
#region 執(zhí)行簡(jiǎn)單SQL語(yǔ)句
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="SQLString">SQL語(yǔ)句</param>
/// <returns>影響的記錄數(shù)</returns>
public int ExecuteSql(string SqlString)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return rows;
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="SQLStringList">多條SQL語(yǔ)句</param>
public void ExecuteSqlTran(ArrayList SQLStringList)
{
//using作為語(yǔ)句，用于定義一個(gè)范圍，在此范圍的末尾將釋放對(duì)象
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
/// <summary>
/// 執(zhí)行帶一個(gè)存儲(chǔ)過(guò)程參數(shù)的的SQL語(yǔ)句。
/// </summary>
/// <param name="SQLString">SQL語(yǔ)句</param>
/// <param name="content">參數(shù)內(nèi)容,比如一個(gè)字段是格式復(fù)雜的文章，有特殊符號(hào)，可以通過(guò)這個(gè)方式添加</param>
/// <returns>影響的記錄數(shù)</returns>
public int ExecuteSql(string SqlString, string content)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(SqlString, iConn))
{
IDataParameter myParameter = this.iDbPara("@content", "Text");
myParameter.Value = content;
iCmd.Parameters.Add(myParameter);
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return rows;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 向數(shù)據(jù)庫(kù)里插入圖像格式的字段(和上面情況類(lèi)似的另一種實(shí)例)
/// </summary>
/// <param name="strSQL">SQL語(yǔ)句</param>
/// <param name="fs">圖像字節(jié),數(shù)據(jù)庫(kù)的字段類(lèi)型為image的情況</param>
/// <returns>影響的記錄數(shù)</returns>
public int ExecuteSqlInsertImg(string SqlString, byte[] fs)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(SqlString, iConn))
{
IDataParameter myParameter = this.iDbPara("@content", "Image");
myParameter.Value = fs;
iCmd.Parameters.Add(myParameter);
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return rows;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行一條計(jì)算查詢(xún)結(jié)果語(yǔ)句，返回查詢(xún)結(jié)果（object）。
/// </summary>
/// <param name="SQLString">計(jì)算查詢(xún)結(jié)果語(yǔ)句</param>
/// <returns>查詢(xún)結(jié)果（object）</returns>
public object GetSingle(string SqlString)
{
using (IDbConnection iConn = GetConnection())
{
using (IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iConn.Open();
try
{
object obj = iCmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回IDataAdapter
/// </summary>
/// <param name="strSQL">查詢(xún)語(yǔ)句</param>
/// <returns>IDataAdapter</returns>
public IDataAdapter ExecuteReader(string strSQL)
{
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
try
{
IDataAdapter iAdapter = this.GetAdapater(strSQL, iConn);
return iAdapter;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢(xún)語(yǔ)句</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(sqlString, iConn))
{
DataSet ds = new DataSet();
iConn.Open();
try
{
IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
iAdapter.Fill(ds);
return ds;
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet
/// </summary>
/// <param name="sqlString">查詢(xún)語(yǔ)句</param>
/// <param name="dataSet">要填充的DataSet</param>
/// <param name="tableName">要填充的表名</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString, DataSet dataSet, string tableName)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(sqlString, iConn))
{
iConn.Open();
try
{
IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
((OleDbDataAdapter)iAdapter).Fill(dataSet, tableName);
return dataSet;
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行SQL語(yǔ)句 返回存儲(chǔ)過(guò)程
/// </summary>
/// <param name="sqlString">Sql語(yǔ)句</param>
/// <param name="dataSet">要填充的DataSet</param>
/// <param name="startIndex">開(kāi)始記錄</param>
/// <param name="pageSize">頁(yè)面記錄大小</param>
/// <param name="tableName">表名稱(chēng)</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString, DataSet dataSet, int startIndex, int pageSize, string tableName)
{
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
try
{
IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
((OleDbDataAdapter)iAdapter).Fill(dataSet, startIndex, pageSize, tableName);
return dataSet;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，向XML文件寫(xiě)入數(shù)據(jù)
/// </summary>
/// <param name="sqlString">查詢(xún)語(yǔ)句</param>
/// <param name="xmlPath">XML文件路徑</param>
public void WriteToXml(string sqlString, string xmlPath)
{
Query(sqlString).WriteXml(xmlPath);
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句
/// </summary>
/// <param name="SqlString">查詢(xún)語(yǔ)句</param>
/// <returns>DataTable </returns>
public DataTable ExecuteQuery(string sqlString)
{
using (IDbConnection iConn = this.GetConnection())
{
//IDbCommand iCmd = GetCommand(sqlString,iConn);
DataSet ds = new DataSet();
try
{
IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
iAdapter.Fill(ds);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return ds.Tables[0];
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句
/// </summary>
/// <param name="SqlString">查詢(xún)語(yǔ)句</param>
/// <returns>DataTable </returns>
public DataTable ExecuteQuery(string SqlString, string Proc)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iCmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
try
{
IDataAdapter iDataAdapter = this.GetAdapater(SqlString, iConn);
iDataAdapter.Fill(ds);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return ds.Tables[0];
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)，并以DataView返回結(jié)果集
/// </summary>
/// <param name="Sql">SQL語(yǔ)句</param>
/// <returns>DataView</returns>
public DataView ExeceuteDataView(string Sql)
{
using (IDbConnection iConn = this.GetConnection())
{
using (IDbCommand iCmd = GetCommand(Sql, iConn))
{
DataSet ds = new DataSet();
try
{
IDataAdapter iDataAdapter = this.GetAdapater(Sql, iConn);
iDataAdapter.Fill(ds);
return ds.Tables[0].DefaultView;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
#endregion
#region 執(zhí)行帶參數(shù)的SQL語(yǔ)句
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="SQLString">SQL語(yǔ)句</param>
/// <returns>影響的記錄數(shù)</returns>
public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
{
using (IDbConnection iConn = this.GetConnection())
{
IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
int rows = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
return rows;
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="SQLStringList">SQL語(yǔ)句的哈希表（key為sql語(yǔ)句，value是該語(yǔ)句的SqlParameter[]）</param>
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
IDbCommand iCmd = GetCommand();
try
{
//循環(huán)
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行一條計(jì)算查詢(xún)結(jié)果語(yǔ)句，返回查詢(xún)結(jié)果（object）。
/// </summary>
/// <param name="SQLString">計(jì)算查詢(xún)結(jié)果語(yǔ)句</param>
/// <returns>查詢(xún)結(jié)果（object）</returns>
public object GetSingle(string SQLString, params IDataParameter[] iParms)
{
using (IDbConnection iConn = this.GetConnection())
{
IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
object obj = iCmd.ExecuteScalar();
iCmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回IDataReader
/// </summary>
/// <param name="strSQL">查詢(xún)語(yǔ)句</param>
/// <returns> IDataReader </returns>
public IDataReader ExecuteReader(string SQLString, params IDataParameter[] iParms)
{
IDbConnection iConn = this.GetConnection();
{
IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
return iReader;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢(xún)語(yǔ)句</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString, params IDataParameter[] iParms)
{
using (IDbConnection iConn = this.GetConnection())
{
IDbCommand iCmd = GetCommand();
{
PrepareCommand(out iCmd, iConn, null, sqlString, iParms);
try
{
IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
DataSet ds = new DataSet();
iAdapter.Fill(ds);
iCmd.Parameters.Clear();
return ds;
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/// <summary>
/// 初始化Command
/// </summary>
/// <param name="iCmd"></param>
/// <param name="iConn"></param>
/// <param name="iTrans"></param>
/// <param name="cmdText"></param>
/// <param name="iParms"></param>
private void PrepareCommand(out IDbCommand iCmd, IDbConnection iConn, System.Data.IDbTransaction iTrans, string cmdText, IDataParameter[] iParms)
{
if (iConn.State != ConnectionState.Open)
iConn.Open();
iCmd = this.GetCommand();
iCmd.Connection = iConn;
iCmd.CommandText = cmdText;
if (iTrans != null)
iCmd.Transaction = iTrans;
iCmd.CommandType = CommandType.Text;//cmdType;
if (iParms != null)
{
foreach (IDataParameter parm in iParms)
iCmd.Parameters.Add(parm);
}
}
#endregion
#region 存儲(chǔ)過(guò)程操作
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程
/// </summary>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
IDbConnection iConn = this.GetConnection();
{
iConn.Open();
using (SqlCommand sqlCmd = BuildQueryCommand(iConn, storedProcName, parameters))
{
return sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程
/// </summary>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <param name="tableName">DataSet結(jié)果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (IDbConnection iConn = this.GetConnection())
{
DataSet dataSet = new DataSet();
iConn.Open();
IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storedProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return dataSet;
}
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程
/// </summary>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <param name="tableName">DataSet結(jié)果中的表名</param>
/// <param name="startIndex">開(kāi)始記錄索引</param>
/// <param name="pageSize">頁(yè)面記錄大小</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, int startIndex, int pageSize, string tableName)
{
using (IDbConnection iConn = this.GetConnection())
{
DataSet dataSet = new DataSet();
iConn.Open();
IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storedProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, startIndex, pageSize, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return dataSet;
}
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程 填充已經(jīng)存在的DataSet數(shù)據(jù)集
/// </summary>
/// <param name="storeProcName">存儲(chǔ)過(guò)程名稱(chēng)</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <param name="dataSet">要填充的數(shù)據(jù)集</param>
/// <param name="tablename">要填充的表名</param>
/// <returns></returns>
public DataSet RunProcedure(string storeProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
{
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storeProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return dataSet;
}
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程并返回受影響的行數(shù)
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters)
{
int result = 0;
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (SqlCommand scmd = BuildQueryCommand(iConn, storedProcName, parameters))
{
result = scmd.ExecuteNonQuery();
}
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return result;
}
public string RunProcedureExecuteScalar(string storeProcName, IDataParameter[] parameters)
{
string result = string.Empty;
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (SqlCommand scmd = BuildQueryCommand(iConn, storeProcName, parameters))
{
object obj = scmd.ExecuteScalar();
if (obj == null)
result = null;
else
result = obj.ToString();
}
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return result;
}
/// <summary>
/// 構(gòu)建 SqlCommand 對(duì)象(用來(lái)返回一個(gè)結(jié)果集，而不是一個(gè)整數(shù)值)
/// </summary>
/// <param name="connection">數(shù)據(jù)庫(kù)連接</param>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(IDbConnection iConn, string storedProcName, IDataParameter[] parameters)
{
IDbCommand iCmd = GetCommand(storedProcName, iConn);
iCmd.CommandType = CommandType.StoredProcedure;
if (parameters == null)
{
return (SqlCommand)iCmd;
}
foreach (IDataParameter parameter in parameters)
{
iCmd.Parameters.Add(parameter);
}
return (SqlCommand)iCmd;
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過(guò)程，返回影響的行數(shù)
/// </summary>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <param name="rowsAffected">影響的行數(shù)</param>
/// <returns></returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (IDbConnection iConn = this.GetConnection())
{
int result;
iConn.Open();
using (SqlCommand sqlCmd = BuildIntCommand(iConn, storedProcName, parameters))
{
rowsAffected = sqlCmd.ExecuteNonQuery();
result = (int)sqlCmd.Parameters["ReturnValue"].Value;
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return result;
}
}
}
/// <summary>
/// 創(chuàng)建 SqlCommand 對(duì)象實(shí)例(用來(lái)返回一個(gè)整數(shù)值)
/// </summary>
/// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
/// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
/// <returns>SqlCommand 對(duì)象實(shí)例</returns>
private SqlCommand BuildIntCommand(IDbConnection iConn, string storedProcName, IDataParameter[] parameters)
{
SqlCommand sqlCmd = BuildQueryCommand(iConn, storedProcName, parameters);
sqlCmd.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return sqlCmd;
}
#endregion
}
}
小注：
這個(gè)類(lèi)封裝成dll后，相關(guān)數(shù)據(jù)庫(kù)連接可在，引用該dll項(xiàng)目的app.config文件中寫(xiě)，如果是web程序，這需要修改代碼，將構(gòu)造函數(shù)中獲取app.config的信息換成獲取web.config的信息。
The above is the C#.NET universal database access encapsulation class (ACCESS, SQLServer, Oracle) content, please pay attention to the PHP Chinese website (www.miracleart.cn) for more related content!
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Grass Wonder Build Guide | Uma Musume Pretty Derby
1 months ago
By Jack chen
Roblox: 99 Nights In The Forest - All Badges And How To Unlock Them
4 weeks ago
By DDD
Uma Musume Pretty Derby Banner Schedule (July 2025)
1 months ago
By Jack chen
RimWorld Odyssey Temperature Guide for Ships and Gravtech
3 weeks ago
By Jack chen
Windows Security is blank or not showing options
1 months ago
By 下次還敢
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
The difference between multithreading and asynchronous c#
Apr 03, 2025 pm 02:57 PM
The difference between multithreading and asynchronous is that multithreading executes multiple threads at the same time, while asynchronously performs operations without blocking the current thread. Multithreading is used for compute-intensive tasks, while asynchronously is used for user interaction. The advantage of multi-threading is to improve computing performance, while the advantage of asynchronous is to not block UI threads. Choosing multithreading or asynchronous depends on the nature of the task: Computation-intensive tasks use multithreading, tasks that interact with external resources and need to keep UI responsiveness use asynchronous.
C# vs. C : History, Evolution, and Future Prospects
Apr 19, 2025 am 12:07 AM
The history and evolution of C# and C are unique, and the future prospects are also different. 1.C was invented by BjarneStroustrup in 1983 to introduce object-oriented programming into the C language. Its evolution process includes multiple standardizations, such as C 11 introducing auto keywords and lambda expressions, C 20 introducing concepts and coroutines, and will focus on performance and system-level programming in the future. 2.C# was released by Microsoft in 2000. Combining the advantages of C and Java, its evolution focuses on simplicity and productivity. For example, C#2.0 introduced generics and C#5.0 introduced asynchronous programming, which will focus on developers' productivity and cloud computing in the future.
.NET Core Quick Start Tutorial 1. The beginning: Talking about .NET Core
May 07, 2025 pm 04:54 PM
1. The Origin of .NETCore When talking about .NETCore, we must not mention its predecessor .NET. Java was in the limelight at that time, and Microsoft also favored Java. The Java virtual machine on the Windows platform was developed by Microsoft based on JVM standards. It is said to be the best performance Java virtual machine at that time. However, Microsoft has its own little abacus, trying to bundle Java with the Windows platform and add some Windows-specific features. Sun's dissatisfaction with this led to a breakdown of the relationship between the two parties, and Microsoft then launched .NET. .NET has borrowed many features of Java since its inception and gradually surpassed Java in language features and form development. Java in version 1.6
What is c# multithreading programming? C# multithreading programming uses c# multithreading programming
Apr 03, 2025 pm 02:45 PM
C# multi-threaded programming is a technology that allows programs to perform multiple tasks simultaneously. It can improve program efficiency by improving performance, improving responsiveness and implementing parallel processing. While the Thread class provides a way to create threads directly, advanced tools such as Task and async/await can provide safer asynchronous operations and a cleaner code structure. Common challenges in multithreaded programming include deadlocks, race conditions, and resource leakage, which require careful design of threading models and the use of appropriate synchronization mechanisms to avoid these problems.
C# .NET: Building Applications with the .NET Ecosystem
Apr 27, 2025 am 12:12 AM
How to build applications using .NET? Building applications using .NET can be achieved through the following steps: 1) Understand the basics of .NET, including C# language and cross-platform development support; 2) Learn core concepts such as components and working principles of the .NET ecosystem; 3) Master basic and advanced usage, from simple console applications to complex WebAPIs and database operations; 4) Be familiar with common errors and debugging techniques, such as configuration and database connection issues; 5) Application performance optimization and best practices, such as asynchronous programming and caching.
From Web to Desktop: The Versatility of C# .NET
Apr 15, 2025 am 12:07 AM
C#.NETisversatileforbothwebanddesktopdevelopment.1)Forweb,useASP.NETfordynamicapplications.2)Fordesktop,employWindowsFormsorWPFforrichinterfaces.3)UseXamarinforcross-platformdevelopment,enablingcodesharingacrossWindows,macOS,Linux,andmobiledevices.
.NET Framework vs. C#: Decoding the Terminology
Apr 21, 2025 am 12:05 AM
.NETFramework is a software framework, and C# is a programming language. 1..NETFramework provides libraries and services, supporting desktop, web and mobile application development. 2.C# is designed for .NETFramework and supports modern programming functions. 3..NETFramework manages code execution through CLR, and the C# code is compiled into IL and runs by CLR. 4. Use .NETFramework to quickly develop applications, and C# provides advanced functions such as LINQ. 5. Common errors include type conversion and asynchronous programming deadlocks. VisualStudio tools are required for debugging.
What are the benefits of multithreading in c#?
Apr 03, 2025 pm 02:51 PM
The advantage of multithreading is that it can improve performance and resource utilization, especially for processing large amounts of data or performing time-consuming operations. It allows multiple tasks to be performed simultaneously, improving efficiency. However, too many threads can lead to performance degradation, so you need to carefully select the number of threads based on the number of CPU cores and task characteristics. In addition, multi-threaded programming involves challenges such as deadlock and race conditions, which need to be solved using synchronization mechanisms, and requires solid knowledge of concurrent programming, weighing the pros and cons and using them with caution.
