使用过很多版本的dbhelper无论是自己写还是用被人的但是基本方法都是大同小异,各有千秋一个偶然的机会我发现的使用db工厂来创建操作数据库过程中所需要的各种对象,
这样有一个好处就是,进一步使得数据库与程序的分离,降低他们之间的耦合,当我们由access变更成sqlser或者oracle是仅仅只需要更改配置参数,甚至工厂类微软已经帮我们写好了,不需要明白具体的含义 只需要使用 ,然后更改配置便可以在不同数据库之间进行切换。
<connectionStrings>
<add name="ApplicationServices"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
<add name="DataConnection" connectionString="Data Source=.;Initial Catalog=实验4temp;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
以上是config文件中的配置字符串 这里以sqlserver参数为类
线面就是 dbhelper类了 ,此类的产生是由我参考别人的,再经过自己的理解建立起来的功能不是特别完善,但基本常用都有了,可以加入自己的理解自己的想法去进一步完善。
在程序中不同层之间以接口进行连接 ,降低耦合也是程序更易于维护,左右闲来无事分享下吧
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;
//============================================================
//http://www.cnblogs.com/mxxblog/
//联系Email:446883859@qq.com
//联系qq:446883859
//============================================================
namespace DbHelper
{
public class SqlHelperFactory
{
//db工厂参数
//Provider=Microsoft.Jet.OLEDB.4.0
//private static string connectionString = conStr;
//providerName="System.Data.SqlClient"
// providerName = "System.Data.OleDb";
/// <summary>
/// 链接字符串从Config文件中获取name为 DataConnection
/// </summary>
private static string connectionString = ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString;
/// <summary>
/// providerName
/// </summary>
private static string providerName = ConfigurationManager.ConnectionStrings["DataConnection"].ProviderName;
/// <summary>
/// DbProviderFactory db工厂
/// </summary>
private static DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(providerName);
/// <summary>
/// 创建数据库链接
/// </summary>
/// <returns></returns>
protected static DbConnection createDbConnection()
{
DbConnection dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = connectionString;
return dbConnection;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
/// <param name="dbtype">参数类型</param>
/// <param name="dataSize">大小</param>
/// <param name="parameterDirection">输入输出类型</param>
/// <returns>返回参数DbParameter</returns>
public static DbParameter createDbParameter(string parameterName, object value, DbType dbtype, int dataSize, ParameterDirection parameterDirection)
{
DbParameter dbParameter = dbProviderFactory.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.DbType = dbtype;
dbParameter.Size = dataSize;
dbParameter.Direction = parameterDirection;
return dbParameter;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
/// <param name="dbtype">参数类型</param>
/// <param name="parameterDirection">输入输出类型</param>
/// <returns>返回参数DbParameter</returns>
public static DbParameter createDbParameter(string parameterName, object value, DbType dbtype, ParameterDirection parameterDirection)
{
DbParameter dbParameter = dbProviderFactory.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.DbType = dbtype;
dbParameter.Direction = parameterDirection;
return dbParameter;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="parameterName">名称</param>
/// <param name="value">值</param>
/// <returns>返回参数DbParameter</returns>
public static DbParameter createDbParameter(string parameterName, object value)
{
DbParameter dbParameter = dbProviderFactory.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
return dbParameter;
}
/// <summary>
/// createAddDbParameter 添加参数到对应cmd
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <param name="dbtype"></param>
/// <param name="dataSize"></param>
/// <param name="parameterDirection"></param>
protected void createAddDbParameter(DbCommand cmd, string parameterName, object value, DbType dbtype, int dataSize, ParameterDirection parameterDirection)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.DbType = dbtype;
dbParameter.Size = dataSize;
dbParameter.Direction = parameterDirection;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// createAddDbParameter 添加参数到对应cmd
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <param name="dbtype"></param>
/// <param name="parameterDirection"></param>
protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value, DbType dbtype, ParameterDirection parameterDirection)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.DbType = dbtype;
dbParameter.Direction = parameterDirection;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// createAddDbParameter 添加参数到对应cmd
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <param name="parameterDirection"></param>
protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value, ParameterDirection parameterDirection)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = parameterDirection;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 向指定cmd添加参数
/// </summary>
/// <param name="cmd">cmd</param>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// RunExecuteNonQuery
/// </summary>
/// <param name="cmdTxt">要执行的sql语句</param>
/// <param name="paras">值集合</param>
/// <returns>返回受影响的行数</returns>
public static int RunExecuteNonQuery(string cmdTxt, params IDbDataParameter[] paras)
{
using (IDbConnection con = createDbConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = cmdTxt;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteNonQuery();
}
}
// return 0;
}
/// <summary>
/// RunExecuteNonQueryByPro 执行对应的存储过程
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <param name="paras">参数集合</param>
/// <returns>返回受影响的行数</returns>
public static int RunExecuteNonQueryByPro(string proName, params IDbDataParameter[] paras)
{
using (IDbConnection con = createDbConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteNonQuery();
}
}
// return 0;
}
/// <summary>
/// RunExecuteScalar
/// </summary>
/// <param name="cmdTxt">sql语句</param>
/// <param name="paras">参数集合</param>
/// <returns>返回首行首列</returns>
public static object RunExecuteScalar(string cmdTxt, params IDbDataParameter[] paras)
{
using (IDbConnection con = createDbConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = cmdTxt;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteScalar();
}
}
// return 0;
}
/// <summary>
/// RunExecuteScalarByPro
/// </summary>
/// <param name="proName">存储过程名称</param>
/// <param name="paras">参数集合</param>
/// <returns>返回首行首列</returns>
public static object RunExecuteScalarByPro(string proName, params IDbDataParameter[] paras)
{
using (IDbConnection con = createDbConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proName;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteScalar();
}
}
// return 0;
}
/// <summary>
/// RunExecuteDataReader
/// </summary>
/// <param name="cmdTxt">要执行的sql语句</param>
/// <param name="paras">参数集合</param>
/// <returns>返回数据读取器</returns>
public static IDataReader RunExecuteDataReader(string cmdTxt, params IDbDataParameter[] paras)
{
IDbConnection con = createDbConnection();
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = cmdTxt;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
/// <summary>
/// RunExecuteDataTable
/// </summary>
/// <param name="cmdTxt">执行的sql语句</param>
/// <param name="paras">参数集合</param>
/// <returns>DataSet通过适配器返回DataSet</returns>
public static DataSet RunExecuteDataTable(string cmdTxt, params IDbDataParameter[] paras)
{
DataSet ds = new DataSet();
using (IDbConnection con = createDbConnection())
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = cmdTxt;
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
IDbDataAdapter sda = dbProviderFactory.CreateDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(ds);
return ds;
}
}
}
}
}