c# 创建DBhelper类,实现对Oracle,SQL server,MySQL,sqlite 等主流数据库的连接,且实现对数据库的增删改查功能,返回报错,返回执行结果,还能执行存储过程,事务
时间: 2023-10-22 14:07:45 浏览: 55
好的,我可以为您提供一个基本的DBhelper类,它可以连接到Oracle、SQL Server、MySQL和SQLite数据库,并实现常见的增删改查功能、事务和存储过程的执行。以下是示例代码:
```csharp
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;
public class DBHelper
{
private string connectionString; // 数据库连接字符串
private DbProviderFactory factory; // 数据库工厂对象
public DBHelper(string dbType, string connStr)
{
connectionString = connStr;
// 根据数据库类型创建对应的数据库工厂对象
switch (dbType.ToLower())
{
case "oracle":
factory = DbProviderFactories.GetFactory("System.Data.OracleClient");
break;
case "sqlserver":
factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
break;
case "mysql":
factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
break;
case "sqlite":
factory = DbProviderFactories.GetFactory("System.Data.SQLite");
break;
default:
throw new Exception("不支持的数据库类型!");
}
}
// 执行查询,并返回查询结果的 DataTable 对象
public DataTable ExecuteQuery(string sql, CommandType cmdType, params DbParameter[] parameters)
{
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
// 执行非查询操作,返回受影响的行数
public int ExecuteNonQuery(string sql, CommandType cmdType, params DbParameter[] parameters)
{
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
int result = cmd.ExecuteNonQuery();
return result;
}
}
// 执行查询,并返回查询结果的第一行第一列的值(Object类型)
public object ExecuteScalar(string sql, CommandType cmdType, params DbParameter[] parameters)
{
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
object result = cmd.ExecuteScalar();
return result;
}
}
// 开始一个事务
public DbTransaction BeginTransaction()
{
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();
DbTransaction trans = conn.BeginTransaction();
return trans;
}
// 提交一个事务
public void CommitTransaction(DbTransaction trans)
{
trans.Commit();
trans.Connection.Close();
}
// 回滚一个事务
public void RollbackTransaction(DbTransaction trans)
{
trans.Rollback();
trans.Connection.Close();
}
// 执行存储过程
public void ExecuteProcedure(string procName, CommandType cmdType, params DbParameter[] parameters)
{
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = cmdType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
cmd.ExecuteNonQuery();
}
}
}
```
使用示例:
```csharp
// 创建一个连接 Oracle 数据库的 DBHelper 对象
DBHelper oracleHelper = new DBHelper("oracle", "Data Source=127.0.0.1:1521/orcl;User ID=scott;Password=tiger;");
// 执行查询,并返回查询结果的 DataTable 对象
DataTable dataTable = oracleHelper.ExecuteQuery("SELECT * FROM emp", CommandType.Text);
// 执行非查询操作,返回受影响的行数
int result = oracleHelper.ExecuteNonQuery("UPDATE emp SET sal = sal * 1.1", CommandType.Text);
// 执行查询,并返回查询结果的第一行第一列的值(Object类型)
object value = oracleHelper.ExecuteScalar("SELECT COUNT(*) FROM emp", CommandType.Text);
// 开始一个事务
DbTransaction trans = oracleHelper.BeginTransaction();
try
{
// 执行一些操作
// 提交事务
oracleHelper.CommitTransaction(trans);
}
catch (Exception ex)
{
// 回滚事务
oracleHelper.RollbackTransaction(trans);
}
// 执行存储过程
DbParameter[] parameters = new DbParameter[]
{
new OracleParameter("inParam", "value"),
new OracleParameter("outParam", OracleType.VarChar, 50, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, null)
};
oracleHelper.ExecuteProcedure("procName", CommandType.StoredProcedure, parameters);
```
以上是一个简单的DBhelper类,您可以根据自己的需求进行扩展和优化。