Home
Backend DevelopmentC#.Net TutorialC#.NET universal database access encapsulation class (ACCESS, SQLServer, Oracle)
Backend DevelopmentC#.Net TutorialC#.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文件中写上数据库中连接信息：
<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=你的数据库器（本机可用LocalHost,如果是EXPRESS开发版则必须是.\SQLEXPRESS);
Initial Catalog=数据库名字;
User ID=用户名;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;
}
}
//数据访问基础类--构造函数
public DataBaseLayer(string strConnect, string dataType)
{
this.ConntionString = strConnect;
this.DbType = dataType;
}
//数据访问基础类--构造函数
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>
/// 数据库类型
/// </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 转换参数
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 创建 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 执行简单SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</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>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(ArrayList SQLStringList)
{
//using作为语句，用于定义一个范围，在此范围的末尾将释放对象
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>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</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>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</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>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（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>
/// 执行查询语句，返回IDataAdapter
/// </summary>
/// <param name="strSQL">查询语句</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>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</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>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="sqlString">查询语句</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>
/// 执行SQL语句 返回存储过程
/// </summary>
/// <param name="sqlString">Sql语句</param>
/// <param name="dataSet">要填充的DataSet</param>
/// <param name="startIndex">开始记录</param>
/// <param name="pageSize">页面记录大小</param>
/// <param name="tableName">表名称</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>
/// 执行查询语句，向XML文件写入数据
/// </summary>
/// <param name="sqlString">查询语句</param>
/// <param name="xmlPath">XML文件路径</param>
public void WriteToXml(string sqlString, string xmlPath)
{
Query(sqlString).WriteXml(xmlPath);
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="SqlString">查询语句</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>
/// 执行查询语句
/// </summary>
/// <param name="SqlString">查询语句</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>
/// 执行查询，并以DataView返回结果集
/// </summary>
/// <param name="Sql">SQL语句</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 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</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>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
IDbCommand iCmd = GetCommand();
try
{
//循环
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>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（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>
/// 执行查询语句，返回IDataReader
/// </summary>
/// <param name="strSQL">查询语句</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>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</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 存储过程操作
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</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>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</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>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <param name="startIndex">开始记录索引</param>
/// <param name="pageSize">页面记录大小</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>
/// 执行存储过程 填充已经存在的DataSet数据集
/// </summary>
/// <param name="storeProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="dataSet">要填充的数据集</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>
/// 执行存储过程并返回受影响的行数
/// </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>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</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>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</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>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</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
}
}
小注：
这个类封装成dll后，相关数据库连接可在，引用该dll项目的app.config文件中写，如果是web程序，这需要修改代码，将构造函数中获取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.php.cn) for more related content!
Statement
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
如何使用C#编写时间序列预测算法Sep 19, 2023 pm 02:33 PM如何使用C#编写时间序列预测算法时间序列预测是一种通过分析过去的数据来预测未来数据趋势的方法。它在很多领域，如金融、销售和天气预报中有广泛的应用。在本文中，我们将介绍如何使用C#编写时间序列预测算法，并附上具体的代码示例。数据准备在进行时间序列预测之前，首先需要准备好数据。一般来说，时间序列数据应该具有足够的长度，并且是按照时间顺序排列的。你可以从数据库或者
如何使用Redis和C#开发分布式事务功能Sep 21, 2023 pm 02:55 PM如何使用Redis和C#开发分布式事务功能引言分布式系统的开发中，事务处理是一项非常重要的功能。事务处理能够保证在分布式系统中的一系列操作要么全部成功，要么全部回滚。Redis是一种高性能的键值存储数据库，而C#是一种广泛应用于开发分布式系统的编程语言。本文将介绍如何使用Redis和C#来实现分布式事务功能，并提供具体代码示例。I.Redis事务Redis
如何实现C#中的人脸识别算法Sep 19, 2023 am 08:57 AM如何实现C#中的人脸识别算法人脸识别算法是计算机视觉领域中的一个重要研究方向，它可以用于识别和验证人脸，广泛应用于安全监控、人脸支付、人脸解锁等领域。在本文中，我们将介绍如何使用C#来实现人脸识别算法，并提供具体的代码示例。实现人脸识别算法的第一步是获取图像数据。在C#中，我们可以使用EmguCV库（OpenCV的C#封装）来处理图像。首先，我们需要在项目
Redis在C#开发中的应用：如何实现高效的缓存更新Jul 30, 2023 am 09:46 AMRedis在C#开发中的应用：如何实现高效的缓存更新引言：在Web开发中，缓存是提高系统性能的常用手段之一。而Redis作为一款高性能的Key-Value存储系统，能够提供快速的缓存操作，为我们的应用带来了不少便利。本文将介绍如何在C#开发中使用Redis，实现高效的缓存更新。Redis的安装与配置在开始之前，我们需要先安装Redis并进行相应的配置。你可以
如何使用C#编写动态规划算法Sep 20, 2023 pm 04:03 PM如何使用C#编写动态规划算法摘要：动态规划是求解最优化问题的一种常用算法，适用于多种场景。本文将介绍如何使用C#编写动态规划算法，并提供具体的代码示例。一、什么是动态规划算法动态规划（DynamicProgramming，简称DP）是一种用来求解具有重叠子问题和最优子结构性质的问题的算法思想。动态规划将问题分解成若干个子问题来求解，通过记录每个子问题的解，
如何实现C#中的图像压缩算法Sep 19, 2023 pm 02:12 PM如何实现C#中的图像压缩算法摘要：图像压缩是图像处理领域中的一个重要研究方向，本文将介绍在C#中实现图像压缩的算法，并给出相应的代码示例。引言：随着数字图像的广泛应用，图像压缩成为了图像处理中的重要环节。压缩能够减小存储空间和传输带宽，并能提高图像处理的效率。在C#语言中，我们可以通过使用各种图像压缩算法来实现对图像的压缩。本文将介绍两种常见的图像压缩算法：
C#开发中如何处理跨域请求和安全性问题Oct 08, 2023 pm 09:21 PMC#开发中如何处理跨域请求和安全性问题在现代的网络应用开发中，跨域请求和安全性问题是开发人员经常面临的挑战。为了提供更好的用户体验和功能，应用程序经常需要与其他域或服务器进行交互。然而，浏览器的同源策略导致了这些跨域请求被阻止，因此需要采取一些措施来处理跨域请求。同时，为了保证数据的安全性，开发人员还需要考虑一些安全性问题。本文将探讨C#开发中如何处理跨域请
如何实现C#中的遗传算法Sep 19, 2023 pm 01:07 PM如何在C#中实现遗传算法引言：遗传算法是一种模拟自然选择和基因遗传机制的优化算法，其主要思想是通过模拟生物进化的过程来搜索最优解。在计算机科学领域，遗传算法被广泛应用于优化问题的解决，例如机器学习、参数优化、组合优化等。本文将介绍如何在C#中实现遗传算法，并提供具体的代码示例。一、遗传算法的基本原理遗传算法通过使用编码表示解空间中的候选解，并利用选择、交叉和
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
3 weeks agoByDDD
R.E.P.O. Save File Location: Where Is It & How to Protect It?
3 weeks agoByDDD
Hot Tools
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
SublimeText3 English version
Recommended: Win version, supports code prompts!
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
