整理了一个多数据库通用操作类

来源：互联网发布：数据库系统实现公开课编辑：程序博客网时间：2024/05/19 19:14

DataBase.cs

[csharp] view plaincopyprint?

using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
public enum MyDbType
{
MSSQL,
ORACLE,
MYSQL,
ACCESS
}
public class DataBase
{
private DbConnection cnn;//抽象类型
private DbCommand cmd;//抽象类型
private DbProviderFactory provider;
private string providerName;
private string connectionString;
public DataBase() : this(MyDbType.MSSQL)
{
}
public DataBase(MyDbType dbType)
{
providerName = ConfigurationManager.ConnectionStrings[dbType.ToString()].ProviderName;
provider = DbProviderFactories.GetFactory(providerName);
cnn = provider.CreateConnection();
cnn.ConnectionString = ConfigurationManager.ConnectionStrings[dbType.ToString()].ConnectionString;
cmd = provider.CreateCommand();
cmd.Connection = cnn;
}
#region 执行不带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
public int ExecuteSQL(string sql)
{
return ExecuteSQL(sql, null);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
public int ExecuteSqlTran(List<string> sqlList)
{
int count = -1;
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
try
{
cmd.Transaction = tx;
for (int n = 0; n < sqlList.Count; n++)
{
string strsql = sqlList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count = cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (SqlException e)
{
tx.Rollback();
cnn.Close();
throw new Exception(e.Message);
}
return count;
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
public int ExecuteScalar(string sql)
{
return ExecuteScalar(sql, null);
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
public DataSet GetDataSet(string sql)
{
return GetDataSet(sql, null);
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
public DataTable GetDataTable(string sql)
{
return GetDataSet(sql).Tables[0];
}
/// <summary>
/// 执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)
/// </summary>
public DbDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, null);
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
public int ExecuteSQL(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
public int ExecuteSqlTran(Hashtable sqlList)
{
int count = -1;
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
try
{
cmd.Transaction = tx;
foreach (DictionaryEntry myDE in sqlList)
{
string cmdText = myDE.Key.ToString();
DbParameter[] cmdParms = (DbParameter[])myDE.Value;
CreateCommand(cmdText, cmdParms);
count = cmd.ExecuteNonQuery();
}
tx.Commit();
}
catch (SqlException e)
{
tx.Rollback();
cnn.Close();
throw new Exception(e.Message);
}
return count;
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
public int ExecuteScalar(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
object o = cmd.ExecuteScalar();
return int.Parse(o.ToString());
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
public DataSet GetDataSet(string sql, params DbParameter[] cmdParms)
{
DataSet ds = new DataSet();
try
{
CreateCommand(sql, cmdParms);
DbDataAdapter adapter = provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
return ds;
}
/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
{
return GetDataSet(sql, cmdParms).Tables[0];
}
/// <summary>
/// 执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)
/// </summary>
public DbDataReader ExecuteReader(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
DbDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, object Value)
{
DbParameter Param = cmd.CreateParameter();
Param.ParameterName = ParamName;
Param.DbType = DbType;
if(Size > 0)
Param.Size = Size;
if (Value != null)
Param.Value = Value;
return Param;
}
private DbCommand CreateCommand(string cmdText, DbParameter[] Prams)
{
return CreateCommand(CommandType.Text, cmdText, Prams);
}
private DbCommand CreateCommand(CommandType cmdType, string cmdText, DbParameter[] Prams)
{
if (cnn.State != ConnectionState.Open)
cnn.Open();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (Prams != null)
{
cmd.Parameters.Clear();
foreach (DbParameter Parameter in Prams)
cmd.Parameters.Add(Parameter);
}
return cmd;
}
public DataSet GetDataSetByProc(string ProcName, DbParameter[] Params)
{
cnn.Open();
DbCommand cmd = CreateCommand(CommandType.StoredProcedure, ProcName, Params);
DbDataAdapter adapter = provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
cnn.Close();
return ds;
}
#endregion
}

Web.config

[html] view plaincopyprint?

<appSettings/>
<connectionStrings>
<add name="ORACLE" connectionString="Data Source=TestSer;User ID=user1; Password=pwd1; Omit Oracle Connection Name=True; enlist=false" providerName="System.Data.OracleClient"/>
<add name="MSSQL" connectionString="Data Source=TestSer;Initial Catalog=forum;Integrated Security=True" providerName="System.Data.SqlClient"/>
<add name="ACCESS" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|db1.mdb" providerName="System.Data.OleDb"/>
<add name="MYSQL" connectionString="Data Source=192.168.0.2;Initial Catalog=test;Persist Security Info=True;User ID=user1;Password=pass1" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
<system.data>
<DbProviderFactories>
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>

0 0