-
C#访问数据库类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections;
namespace DBUtility
{
public class DataAccess
{
private string _confirString = "ConnectionString";
public DataAccess()
{
}
/// <param name="configString">app.config 关键字</param>
public DataAccess(string configString)
{
ConfigString = configString;
}
/// <summary>
/// 属性,设置数据库连接字符串
/// </summary>
public string ConfigString
{
get
{
return _confirString;
}
set
{
_confirString = value;
}
}
//===========================================GetProviderName=============================
#region 获得数据库的类型public string GetProviderName(string ConfigString)
/// <summary>
/// 返回数据提供者
/// </summary>
/// <returns>返回数据提供者</returns>
public string GetProviderName(string ConfigString)
{
ConnectionStringSettingsCollection ConfigStringCollention = ConfigurationManager.ConnectionStrings;
if (ConfigStringCollention == null || ConfigStringCollention.Count <= 0)
{
throw new Exception("app.config 中无连接字符串!");
}
ConnectionStringSettings StringSettings = null;
if (ConfigString == string.Empty)
{
StringSettings = ConfigurationManager.ConnectionStrings["ConnectionString"];
}
else
{
StringSettings = ConfigurationManager.ConnectionStrings[ConfigString];
}
return StringSettings.ProviderName;
}
/// <summary>
/// 返回数据提供者
/// </summary>
/// <returns></returns>
public string GetProviderName()
{
return GetProviderName(ConfigString);
}
#endregion
//===========================================获得连接字符串==============================
#region 获得连接字符串
/// <summary>
/// 获得连接字符串
/// </summary>
/// <returns></returns>
private string GetConnectionString(string ConfigString)
{
ConnectionStringSettingsCollection ConfigStringCollention = ConfigurationManager.ConnectionStrings;
if (ConfigStringCollention == null || ConfigStringCollention.Count <= 0)
{
throw new Exception("app.config 中无连接字符串!");
}
ConnectionStringSettings StringSettings = null;
if (ConfigString == string.Empty)
{
StringSettings = ConfigurationManager.ConnectionStrings["ConnectionString"];
}
else
{
StringSettings = ConfigurationManager.ConnectionStrings[ConfigString];
}
return StringSettings.ConnectionString;
}
private string GetConnectionString()
{
return GetConnectionString(ConfigString);
}
#endregion
//===========================================GetDbproviderFactory========================
#region 返回数据工厂 public DbProviderFactory GetDbProviderFactory()
/// <summary>
/// 返回数据工厂
/// </summary>
/// <returns></returns>
private DbProviderFactory GetDbProviderFactory()
{
DbProviderFactory f = null;
string ProviderName = GetProviderName();
switch (ProviderName)
{
case "System.Data.SqlClient":
f = GetDbProviderFactory("System.Data.SqlClient");
break;
case "System.Data.OracleClient":
f = GetDbProviderFactory("System.Data.OracleClient");
break;
case "System.Data.OleDb":
f = GetDbProviderFactory("System.Data.OleDb");
break;
default:
f = GetDbProviderFactory("System.Data.SqlClient");
break;
}
return f;
}
/// <summary>
/// 返回数据工厂
/// </summary>
/// <param name="providername"></param>
/// <returns></returns>
private DbProviderFactory GetDbProviderFactory(string providername)
{
return DbProviderFactories.GetFactory(providername);
}
#endregion
//===========================================CreateConnection============================
#region 创建数据库连接 public DbConnection CreateConnection()
/// <summary>
/// 创建数据库连接
/// </summary>
/// <returns></returns>
private DbConnection CreateConnection()
{
DbConnection con = GetDbProviderFactory().CreateConnection();
con.ConnectionString = GetConnectionString();
return con;
}
/// <summary>
/// 创建数据库连接
/// </summary>
/// <param name="provdername"></param>
/// <returns></returns>
private DbConnection CreateConnection(string provdername)
{
DbConnection con = GetDbProviderFactory(provdername).CreateConnection();
con.ConnectionString = GetConnectionString();
return con;
}
#endregion
//===========================================CreateCommand===============================
#region 创建执行命令对象 public override DbCommand CreateCommand(string sql, CommandType cmdType, DbParameter[] parameters)
/// <summary>
/// 创建执行命令对象
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private DbCommand CreateCommand(string sql, CommandType cmdType, DbParameter[] parameters)
{
DbCommand _command = GetDbProviderFactory().CreateCommand();
_command.Connection = CreateConnection();
_command.CommandText = sql;
_command.CommandType = cmdType;
if (parameters != null && parameters.Length > 0)
{
foreach (DbParameter param in parameters)
{
_command.Parameters.Add(param);
}
}
return _command;
}
/// <summary>
/// 创建执行命令对象
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>执行命令对象实例</returns>
private DbCommand CreateCommand(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return CreateCommand(sql, CommandType.Text, parameters);
}
/// <summary>
/// 创建执行命令对象
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>执行命令对象实例</returns>
private DbCommand CreateCommand(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return CreateCommand(sql, cmdtype, parameters);
}
/// <summary>
/// 创建执行命令对象
/// </summary>
/// <param name="sql">ＳＱＬ语句</param>
/// <param name="parameters">参数</param>
/// <returns>执行命令对象实例</returns>
private DbCommand CreateCommand(string sql, DbParameter[] parameters)
{
return CreateCommand(sql, CommandType.Text, parameters);
}
#endregion
//===========================================CreateAdapter()=============================
#region 创建数据适配器 CreateAdapter(string sql)
/// <summary>
/// 创建数据适配器
/// </summary>
/// <param name="sql">SQL,语句</param>
/// <returns>数据适配器实例</returns>
private DbDataAdapter CreateAdapter(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return CreateAdapter(sql, CommandType.Text, parameters);
}
/// <summary>
/// 创建数据适配器
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <returns>数据适配器实例</returns>
private DbDataAdapter CreateAdapter(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return CreateAdapter(sql, cmdtype, parameters);
}
/// <summary>
/// 创建数据适配器
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>数据适配器实例</returns>
private DbDataAdapter CreateAdapter(string sql, CommandType cmdtype, DbParameter[] parameters)
{
DbConnection _connection = CreateConnection();
DbCommand _command = GetDbProviderFactory().CreateCommand();
_command.Connection = _connection;
_command.CommandText = sql;
_command.CommandType = cmdtype;
if (parameters != null && parameters.Length > 0)
{
foreach (DbParameter _param in parameters)
{
_command.Parameters.Add(_param);
}
}
DbDataAdapter da = GetDbProviderFactory().CreateDataAdapter();
da.SelectCommand = _command;
return da;
}
#endregion
//===========================================CreateParameter=============================
#region 生成参数 public override SqlParameter CreateParameter(string field, string dbtype, string value)
/// <summary>
/// 创建参数
/// </summary>
/// <param name="field">参数字段</param>
/// <param name="dbtype">参数类型</param>
/// <param name="value">参数值</param>
/// <returns></returns>
private DbParameter CreateParameter(string field, string dbtype, string value)
{
DbParameter p = GetDbProviderFactory().CreateParameter();
p.ParameterName = field;
p.Value = value;
return p;
}
#endregion
//===========================================ExecuteCommand()============================
#region 执行非查询语句,并返回受影响的记录行数 ExecuteCommand(string sql)
/// <summary>
/// 执行非查询语句,并返回受影响的记录行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>受影响记录行数</returns>
public int ExecuteCommand(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteCommand(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行非查询语句,并返回受影响的记录行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <returns>受影响记录行数</returns>
public int ExecuteCommand(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteCommand(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行非查询语句,并返回受影响的记录行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>受影响记录行数</returns>
public int ExecuteCommand(string sql, DbParameter[] parameters)
{
return ExecuteCommand(sql, CommandType.Text, parameters);
}
/// <summary>
///批量执行SQL语句
/// </summary>
/// <param name="SqlList">SQL列表</param>
/// <returns></returns>
public bool ExecuteCommand(ArrayList SqlList)
{
DbConnection con = CreateConnection();
con.Open();
bool iserror = false;
string strerror = "";
DbTransaction SqlTran = con.BeginTransaction();
try
{
for (int i = 0; i < SqlList.Count; i++)
{
DbCommand _command = GetDbProviderFactory().CreateCommand();
_command.Connection = con;
_command.CommandText = SqlList[i].ToString();
_command.Transaction = SqlTran;
_command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
iserror = true;
strerror = ex.Message;
}
finally
{
if (iserror)
{
SqlTran.Rollback();
throw new Exception(strerror);
}
else
{
SqlTran.Commit();
}
con.Close();
}
if (iserror)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 执行非查询语句,并返回受影响的记录行数
/// </summary>
