首先在工程中引用各种数据库组件.
包括:SqlServer,OleDb,Oracle,ODBC.
调用方法很简单,在创建该类的实例对象时初始化connectionstring数据库连接字符串即可.
该类提供了几种初始化重载方法,可以直接将连接字符串传入,或者通过web.config配置文件ConnectionStrings["connectionstring"].可以直接指明数据库类型,也可以不指明(该类有自动分析功能).
该类代码如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.IO;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
namespace BinaryIntellect.DataAccess
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
public class DatabaseHelper:IDisposable
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private string strConnectionString;
private DbConnection objConnection;
private DbCommand objCommand;
private DbProviderFactory objFactory = null;
private bool boolHandleErrors;
private string strLastError;
private bool boolLogError;
private string strLogFile;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DatabaseHelper(string connectionstring,Providers provider)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strConnectionString = connectionstring;
switch (provider)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
case Providers.SqlServer:
objFactory = SqlClientFactory.Instance;
break;
case Providers.OleDb:
objFactory = OleDbFactory.Instance;
break;
case Providers.Oracle:
objFactory = OracleClientFactory.Instance;
break;
case Providers.ODBC:
objFactory = OdbcFactory.Instance;
break;
case Providers.ConfigDefined:
string providername=ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
switch (providername)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
case "System.Data.SqlClient":
objFactory = SqlClientFactory.Instance;
break;
case "System.Data.OleDb":
objFactory = OleDbFactory.Instance;
break;
case "System.Data.OracleClient":
objFactory = OracleClientFactory.Instance;
break;
case "System.Data.Odbc":
objFactory = OdbcFactory.Instance;
break;
}
break;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
objConnection = objFactory.CreateConnection();
objCommand = objFactory.CreateCommand();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
objConnection.ConnectionString = strConnectionString;
objCommand.Connection = objConnection;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DatabaseHelper(Providers provider):this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,provider)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DatabaseHelper(string connectionstring): this(connectionstring, Providers.SqlServer)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DatabaseHelper():this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,Providers.ConfigDefined)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public bool HandleErrors
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return boolHandleErrors;
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
boolHandleErrors = value;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public string LastError
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return strLastError;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public bool LogErrors
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return boolLogError;
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
boolLogError=value;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public string LogFile
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return strLogFile;
}
set
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strLogFile = value;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int AddParameter(string name,object value)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DbParameter p = objFactory.CreateParameter();
p.ParameterName = name;
p.Value=value;
return objCommand.Parameters.Add(p);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int AddParameter(DbParameter parameter)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return objCommand.Parameters.Add(parameter);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DbCommand Command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return objCommand;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void BeginTransaction()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (objConnection.State == System.Data.ConnectionState.Closed)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Open();
}
objCommand.Transaction = objConnection.BeginTransaction();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void CommitTransaction()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Transaction.Commit();
objConnection.Close();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void RollbackTransaction()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Transaction.Rollback();
objConnection.Close();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int ExecuteNonQuery(string query)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int ExecuteNonQuery(string query,CommandType commandtype)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int ExecuteNonQuery(string query,ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteNonQuery(query,CommandType.Text,connectionstate);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
int i=-1;
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (objConnection.State == System.Data.ConnectionState.Closed)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Open();
}
i = objCommand.ExecuteNonQuery();
}
catch (Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
HandleExceptions(ex);
}
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Close();
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return i;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public object ExecuteScalar(string query)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public object ExecuteScalar(string query,CommandType commandtype)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public object ExecuteScalar(string query, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteScalar(query, CommandType.Text, connectionstate);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
object o = null;
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (objConnection.State == System.Data.ConnectionState.Closed)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Open();
}
o = objCommand.ExecuteScalar();
}
catch (Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
HandleExceptions(ex);
}
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Close();
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return o;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DbDataReader ExecuteReader(string query)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DbDataReader ExecuteReader(string query,CommandType commandtype)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteReader(query, CommandType.Text, connectionstate);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
DbDataReader reader=null;
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (objConnection.State == System.Data.ConnectionState.Closed)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Open();
}
if (connectionstate == ConnectionState.CloseOnExit)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
reader = objCommand.ExecuteReader();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
catch (Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
HandleExceptions(ex);
}
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Parameters.Clear();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return reader;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet ExecuteDataSet(string query)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet ExecuteDataSet(string query,CommandType commandtype)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return ExecuteDataSet(query, CommandType.Text, connectionstate);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DbDataAdapter adapter = objFactory.CreateDataAdapter();
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
adapter.SelectCommand = objCommand;
DataSet ds = new DataSet();
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
adapter.Fill(ds);
}
catch (Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
HandleExceptions(ex);
}
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (objConnection.State == System.Data.ConnectionState.Open)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Close();
}
}
}
return ds;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private void HandleExceptions(Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (LogErrors)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
WriteToLog(ex.Message);
}
if (HandleErrors)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strLastError = ex.Message;
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private void WriteToLog(string msg)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StreamWriter writer= File.AppendText(LogFile);
writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
writer.Close();
}
public void Dispose()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objConnection.Close();
objConnection.Dispose();
objCommand.Dispose();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public enum Providers
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlServer,OleDb,Oracle,ODBC,ConfigDefined
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public enum ConnectionState
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
KeepOpen,CloseOnExit
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
在使用该类的同时需要在web.config中配置connectionStrings节,以下为sql的连接字符串
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="connectionstring" connectionString="data source=.\sqlexpress;initial catalog=northwind;integrated security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
举几个该类的使用例子:
1.指明了数据库类型。(OleDb,在web.config配置好为OleDb的数据库)
DatabaseHelper db = new DatabaseHelper(Providers.OleDb);
db.AddParameter("@country", "USA");
object obj=db.ExecuteScalar("select count(*) from customers where country=@country");
Console.WriteLine("No. of Customers from USA :" + obj.ToString());
Console.ReadLine();
2.未指明数据库类型。(在web.config配置好据库连接)
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@country", "USA");
SqlDataReader reader = (SqlDataReader)db.ExecuteReader("select companyname from customers where country=@country");
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
reader.Close();
Console.ReadLine();
3.文件操作
DatabaseHelper db = new DatabaseHelper();
db.LogErrors = true;
db.LogFile=@"D:\Bipin\Errorlog.txt";
db.AddParameter("@country", "USA");
object obj = db.ExecuteScalar("select count(*) from customerstable where country=@country");
Console.WriteLine("Total customers in USA :" + obj.ToString());
Console.ReadLine();