自己琢磨出来的数据库访问底层类
为了让数据访问更加简单，我给数据库访问抽象了一下，
下面是我的数据访问底层的架构图：
代码
/// <summary>
/// 数据库操作基类
/// </summary>
public class BaseHelper<T> : IDisposable
where T : AbsConnString, new()
{
//Database connection strings
private static T _connString = new T();
private IDbConnection _dbconnection;
private IDbCommand _dbcommand;
/// <summary>
/// 实现构造函数
/// </summary>
/// <param name="conn">必须是集成自AbsConnString的类</param>
public BaseHelper()
{
_dbconnection = CreateConnection();
_dbcommand = CreateCommand(_dbconnection);
}
private static IDbCommand CreateCommand(IDbConnection conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = _connString.TimeOut;
return cmd;
}
private static IDbConnection CreateConnection()
{
IDbConnection conn = new SqlConnection();
conn.ConnectionString = _connString.ConnectionString;
return conn;
}
public int ExecuteNonQuery(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
}
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
int val = _dbcommand.ExecuteNonQuery();
_dbcommand.Parameters.Clear();
return val;
}
public IDataReader ExecuteReader(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteReader(CommandType.Text, cmdText, commandParameters);
}
public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
IDataReader rdr = _dbcommand.ExecuteReader(CommandBehavior.CloseConnection);
_dbcommand.Parameters.Clear();
return rdr;
}
public object ExecuteScalar(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteScalar(CommandType.Text, cmdText, commandParameters);
}
public object ExecuteScalar(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
object obj = _dbcommand.ExecuteScalar();
_dbcommand.Parameters.Clear();
return obj;
}
public DataTable ExecuteTable(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
using (DataSet st = new DataSet())
{
IDbDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = _dbcommand;
ap.Fill(st);
_dbcommand.Parameters.Clear();
return st.Tables[0];
}
}
public DataTable ExecuteTable(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteTable(CommandType.Text, cmdText, commandParameters);
}
public object ExecuteScalarByTrans(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbTransaction trans = _dbconnection.BeginTransaction())
{
PrepareCommand(_dbcommand, trans, cmdType, cmdText, commandParameters);
try
{
object val = _dbcommand.ExecuteScalar();
if (val != null)
{
trans.Commit();
_dbcommand.Parameters.Clear();
return val;
}
else
{
trans.Rollback();
}
}
catch
{
trans.Rollback();
}
return null;
}
}
public int ExecuteNonQueryByTrans(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
using (IDbTransaction trans = _dbconnection.BeginTransaction())
{
try
{
int val = _dbcommand.ExecuteNonQuery();
if (val > 0)
{
trans.Commit();
_dbcommand.Parameters.Clear();
return val;
}
else
{
trans.Rollback();
}
}
catch
{
trans.Rollback();
}
return -1;
}
}
public IDbDataParameter CreateParameter(string parameterName)
{
return StaticCreateParameter(parameterName);
}
public IDbDataParameter CreateParameter(string parameterName, object value)
{
return StaticCreateParameter(parameterName, value);
}
public IDbDataParameter CreateParameter(string parameterName, SqlDbType dbType, int size, object value)
{
return StaticCreateParameter(parameterName, dbType, size, value);
}
public IDbDataParameter CreateParameter(string parameterName, SqlDbType dbType, object value)
{
return StaticCreateParameter(parameterName, dbType, value);
}
public void SetParameter(IDataParameter parameter, object value)
{
StaticSetParameter(parameter, value);
}
#region Static Method
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int StaticExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection conn = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(conn))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataTable StaticExecuteTable(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection connection = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(connection))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
using (DataSet st = new DataSet())
{
IDbDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
ap.Fill(st);
cmd.Parameters.Clear();
return st.Tables[0];
}
}
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object StaticExecuteScalar(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection connection = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(connection))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
}
public static IDbDataParameter StaticCreateParameter(string parameterName)
{
SqlParameter p = new SqlParameter();
p.ParameterName = parameterName;
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, value);
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, SqlDbType dbType, int size, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, dbType, size);
p.Value = value;
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, SqlDbType dbType, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, dbType);
p.Value = value;
return p;
}
public static void StaticSetParameter(IDataParameter parameter, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
parameter.Value = value;
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">IDbCommand Object</param>
/// <param name="trans">IDbTransaction Object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">IDbDataParameters to use in the command</param>
private static void PrepareCommand(IDbCommand cmd, IDbTransaction trans, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParms)
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null && cmdParms.Length > 0)
{
foreach (IDbDataParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
#endregion
#region IDisposable Members
protected void Dispose()
{
//托管代码
if (_dbcommand != null)
{
_dbcommand.Dispose();
_dbcommand = null;
}
//非托管代码
if (_dbconnection != null)
{
_dbconnection.Dispose();
_dbconnection = null;
}
}
#endregion
}
/// 数据库操作基类
/// </summary>
public class BaseHelper<T> : IDisposable
where T : AbsConnString, new()
{
//Database connection strings
private static T _connString = new T();
private IDbConnection _dbconnection;
private IDbCommand _dbcommand;
/// <summary>
/// 实现构造函数
/// </summary>
/// <param name="conn">必须是集成自AbsConnString的类</param>
public BaseHelper()
{
_dbconnection = CreateConnection();
_dbcommand = CreateCommand(_dbconnection);
}
private static IDbCommand CreateCommand(IDbConnection conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = _connString.TimeOut;
return cmd;
}
private static IDbConnection CreateConnection()
{
IDbConnection conn = new SqlConnection();
conn.ConnectionString = _connString.ConnectionString;
return conn;
}
public int ExecuteNonQuery(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
}
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
int val = _dbcommand.ExecuteNonQuery();
_dbcommand.Parameters.Clear();
return val;
}
public IDataReader ExecuteReader(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteReader(CommandType.Text, cmdText, commandParameters);
}
public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
IDataReader rdr = _dbcommand.ExecuteReader(CommandBehavior.CloseConnection);
_dbcommand.Parameters.Clear();
return rdr;
}
public object ExecuteScalar(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteScalar(CommandType.Text, cmdText, commandParameters);
}
public object ExecuteScalar(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
object obj = _dbcommand.ExecuteScalar();
_dbcommand.Parameters.Clear();
return obj;
}
public DataTable ExecuteTable(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
using (DataSet st = new DataSet())
{
IDbDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = _dbcommand;
ap.Fill(st);
_dbcommand.Parameters.Clear();
return st.Tables[0];
}
}
public DataTable ExecuteTable(string cmdText, params IDbDataParameter[] commandParameters)
{
return ExecuteTable(CommandType.Text, cmdText, commandParameters);
}
public object ExecuteScalarByTrans(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbTransaction trans = _dbconnection.BeginTransaction())
{
PrepareCommand(_dbcommand, trans, cmdType, cmdText, commandParameters);
try
{
object val = _dbcommand.ExecuteScalar();
if (val != null)
{
trans.Commit();
_dbcommand.Parameters.Clear();
return val;
}
else
{
trans.Rollback();
}
}
catch
{
trans.Rollback();
}
return null;
}
}
public int ExecuteNonQueryByTrans(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
PrepareCommand(_dbcommand, null, cmdType, cmdText, commandParameters);
using (IDbTransaction trans = _dbconnection.BeginTransaction())
{
try
{
int val = _dbcommand.ExecuteNonQuery();
if (val > 0)
{
trans.Commit();
_dbcommand.Parameters.Clear();
return val;
}
else
{
trans.Rollback();
}
}
catch
{
trans.Rollback();
}
return -1;
}
}
public IDbDataParameter CreateParameter(string parameterName)
{
return StaticCreateParameter(parameterName);
}
public IDbDataParameter CreateParameter(string parameterName, object value)
{
return StaticCreateParameter(parameterName, value);
}
public IDbDataParameter CreateParameter(string parameterName, SqlDbType dbType, int size, object value)
{
return StaticCreateParameter(parameterName, dbType, size, value);
}
public IDbDataParameter CreateParameter(string parameterName, SqlDbType dbType, object value)
{
return StaticCreateParameter(parameterName, dbType, value);
}
public void SetParameter(IDataParameter parameter, object value)
{
StaticSetParameter(parameter, value);
}
#region Static Method
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int StaticExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection conn = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(conn))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataTable StaticExecuteTable(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection connection = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(connection))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
using (DataSet st = new DataSet())
{
IDbDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
ap.Fill(st);
cmd.Parameters.Clear();
return st.Tables[0];
}
}
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object StaticExecuteScalar(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection connection = CreateConnection())
{
using (IDbCommand cmd = CreateCommand(connection))
{
PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
}
public static IDbDataParameter StaticCreateParameter(string parameterName)
{
SqlParameter p = new SqlParameter();
p.ParameterName = parameterName;
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, value);
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, SqlDbType dbType, int size, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, dbType, size);
p.Value = value;
return p;
}
public static IDbDataParameter StaticCreateParameter(string parameterName, SqlDbType dbType, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
SqlParameter p = new SqlParameter(parameterName, dbType);
p.Value = value;
return p;
}
public static void StaticSetParameter(IDataParameter parameter, object value)
{
if (value == null)
{
value = (object)DBNull.Value;
}
parameter.Value = value;
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">IDbCommand Object</param>
/// <param name="trans">IDbTransaction Object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">IDbDataParameters to use in the command</param>
private static void PrepareCommand(IDbCommand cmd, IDbTransaction trans, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParms)
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null && cmdParms.Length > 0)
{
foreach (IDbDataParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
#endregion
#region IDisposable Members
protected void Dispose()
{
//托管代码
if (_dbcommand != null)
{
_dbcommand.Dispose();
_dbcommand = null;
}
//非托管代码
if (_dbconnection != null)
{
_dbconnection.Dispose();
_dbconnection = null;
}
}
#endregion
}
代码
/// <summary>
/// 数据库连接抽象类类
/// </summary>
public abstract class AbsConnString
{
/// <summary>
/// 超时默认时间为30
/// </summary>
private int _timeOut = 30;
/// <summary>
/// 超时时间
/// </summary>
public virtual int TimeOut
{
get
{
return _timeOut;
}
}
/// <summary>
/// 数据库的连接字符串
/// </summary>
public abstract string ConnectionString { get; }
}
/// 数据库连接抽象类类
/// </summary>
public abstract class AbsConnString
{
/// <summary>
/// 超时默认时间为30
/// </summary>
private int _timeOut = 30;
/// <summary>
/// 超时时间
/// </summary>
public virtual int TimeOut
{
get
{
return _timeOut;
}
}
/// <summary>
/// 数据库的连接字符串
/// </summary>
public abstract string ConnectionString { get; }
}
代码
public class OOSConnString : Base.AbsConnString
{
public OOSConnString()
{
}
private string _connectionstring;
public override string ConnectionString
{
get
{
if (string.IsNullOrEmpty(_connectionstring))
{
_connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["NewMaximOOSConnString"].ConnectionString;
}
return _connectionstring;
}
}
}
{
public OOSConnString()
{
}
private string _connectionstring;
public override string ConnectionString
{
get
{
if (string.IsNullOrEmpty(_connectionstring))
{
_connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["NewMaximOOSConnString"].ConnectionString;
}
return _connectionstring;
}
}
}
/// <summary>
/// 对象型数据库操作类
/// </summary>
public class OOSHelper : Base.BaseHelper<OOSConnString>
{
}
/// 对象型数据库操作类
/// </summary>
public class OOSHelper : Base.BaseHelper<OOSConnString>
{
}
有了以上这个架构，我们数据库访问将更加方便，它支持静态调用和类的调用。
下面是采用类的调用方式（主要是应用于负责的访问方式，new时必须要用using 这样可以避免连接不能及时关闭的问题）
代码
using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Maxim.Entity;
using Maxim.DataAccess.Access;
namespace Maxim.DataAccess.Generated._Base
{
/// <summary>
/// Class:A_CustomerBase
/// Author:Steven
/// Create:2010-4-12 14:52:13
/// </summary>
public abstract class A_CustomerBase
{
/// <summary>
///
/// </summary>
public A_CustomerBase()
{
}
public List<A_CustomerInfo> GetA_CustomerInfoALL()
{
List<A_CustomerInfo> list = new List<A_CustomerInfo>();
string sql="SELECT * FROM [A_Customer]";
using(OOSHelper dbhelper=new OOSHelper())
{
using(IDataReader reader=dbhelper.ExecuteReader(sql))
{
while(reader.Read())
{
list.Add(A_CustomerInfo.SetValue(reader));
}
}
}
return list;
}
public A_CustomerInfo GetA_CustomerInfo(string _eRPNO)
{
A_CustomerInfo aCustomerinfo = null;
StringBuilder sql= new StringBuilder();
sql.Append("SELECT * FROM");
sql.Append(" [A_Customer]");
sql.Append(" WHERE");
sql.Append(" [ERPNO]=@ERPNO");
using(OOSHelper dbhelper=new OOSHelper())
{
IDbDataParameter[] p_Parms = new IDbDataParameter[]{
dbhelper.CreateParameter("@ERPNO",_eRPNO)
};
using(IDataReader reader=dbhelper.ExecuteReader(sql.ToString(),p_Parms))
{
if(reader.Read())
{
aCustomerinfo = A_CustomerInfo.SetValue(reader);
}
}
}
return aCustomerinfo;
}
public bool Insert(A_CustomerInfo aCustomerinfo)
{
StringBuilder sql=new StringBuilder();
sql.Append("INSERT INTO");
sql.Append(" [A_Customer](");
sql.Append("[ERPNO],");
sql.Append("[CorpName],");
sql.Append("[CorpAddress],");
sql.Append("[CorpPhone],");
sql.Append("[CorpContract],");
sql.Append("[CorpTax],");
sql.Append("[CorpBank],");
sql.Append("[CorpBankNO],");
sql.Append("[PayType],");
sql.Append("[CustomerType],");
sql.Append("[ERPContractType],");
sql.Append("[IsDirectShip],");
sql.Append("[IsEnabled],");
sql.Append("[UpdateTime],");
sql.Append("[CurrencyID],");
sql.Append("[NeedProformaInvoice]");
sql.Append(") VALUES(");
sql.Append("@ERPNO,");
sql.Append("@CorpName,");
sql.Append("@CorpAddress,");
sql.Append("@CorpPhone,");
sql.Append("@CorpContract,");
sql.Append("@CorpTax,");
sql.Append("@CorpBank,");
sql.Append("@CorpBankNO,");
sql.Append("@PayType,");
sql.Append("@CustomerType,");
sql.Append("@ERPContractType,");
sql.Append("@IsDirectShip,");
sql.Append("@IsEnabled,");
sql.Append("@UpdateTime,");
sql.Append("@CurrencyID,");
sql.Append("@NeedProformaInvoice");
sql.Append(")");
using(OOSHelper dbhelper=new OOSHelper())
{
IDbDataParameter[] p_Parms=new IDbDataParameter[]{
dbhelper.CreateParameter("@ERPNO",aCustomerinfo.ERPNO),
dbhelper.CreateParameter("@CorpName",aCustomerinfo.CorpName),
dbhelper.CreateParameter("@CorpAddress",aCustomerinfo.CorpAddress),
dbhelper.CreateParameter("@CorpPhone",aCustomerinfo.CorpPhone),
dbhelper.CreateParameter("@CorpContract",aCustomerinfo.CorpContract),
dbhelper.CreateParameter("@CorpTax",aCustomerinfo.CorpTax),
dbhelper.CreateParameter("@CorpBank",aCustomerinfo.CorpBank),
dbhelper.CreateParameter("@CorpBankNO",aCustomerinfo.CorpBankNO),
dbhelper.CreateParameter("@PayType",aCustomerinfo.PayType),
dbhelper.CreateParameter("@CustomerType",aCustomerinfo.CustomerType),
dbhelper.CreateParameter("@ERPContractType",aCustomerinfo.ERPContractType),
dbhelper.CreateParameter("@IsDirectShip",aCustomerinfo.IsDirectShip),
dbhelper.CreateParameter("@IsEnabled",aCustomerinfo.IsEnabled),
dbhelper.CreateParameter("@UpdateTime",aCustomerinfo.UpdateTime),
dbhelper.CreateParameter("@CurrencyID",aCustomerinfo.CurrencyID),
dbhelper.CreateParameter("@NeedProformaInvoice",aCustomerinfo.NeedProformaInvoice)
};
return 0 < dbhelper.ExecuteNonQuery(sql.ToString(),p_Parms);
}
}
public bool Update(A_CustomerInfo aCustomerinfo)
{
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE");
sql.Append(" [A_Customer]");
sql.Append(" SET");
sql.Append(" [CorpName]=@CorpName,");
sql.Append(" [CorpAddress]=@CorpAddress,");
sql.Append(" [CorpPhone]=@CorpPhone,");
sql.Append(" [CorpContract]=@CorpContract,");
sql.Append(" [CorpTax]=@CorpTax,");
sql.Append(" [CorpBank]=@CorpBank,");
sql.Append(" [CorpBankNO]=@CorpBankNO,");
sql.Append(" [PayType]=@PayType,");
sql.Append(" [CustomerType]=@CustomerType,");
sql.Append(" [ERPContractType]=@ERPContractType,");
sql.Append(" [IsDirectShip]=@IsDirectShip,");
sql.Append(" [IsEnabled]=@IsEnabled,");
sql.Append(" [UpdateTime]=@UpdateTime,");
sql.Append(" [CurrencyID]=@CurrencyID,");
sql.Append(" [NeedProformaInvoice]=@NeedProformaInvoice");
sql.Append(" WHERE");
sql.Append(" [ERPNO]=@ERPNO");
using(OOSHelper dbhelper=new OOSHelper())
{
IDbDataParameter[] p_Parms = new IDbDataParameter[]{
dbhelper.CreateParameter("@CorpName",aCustomerinfo.CorpName),
dbhelper.CreateParameter("@CorpAddress",aCustomerinfo.CorpAddress),
dbhelper.CreateParameter("@CorpPhone",aCustomerinfo.CorpPhone),
dbhelper.CreateParameter("@CorpContract",aCustomerinfo.CorpContract),
dbhelper.CreateParameter("@CorpTax",aCustomerinfo.CorpTax),
dbhelper.CreateParameter("@CorpBank",aCustomerinfo.CorpBank),
dbhelper.CreateParameter("@CorpBankNO",aCustomerinfo.CorpBankNO),
dbhelper.CreateParameter("@PayType",aCustomerinfo.PayType),
dbhelper.CreateParameter("@CustomerType",aCustomerinfo.CustomerType),
dbhelper.CreateParameter("@ERPContractType",aCustomerinfo.ERPContractType),
dbhelper.CreateParameter("@IsDirectShip",aCustomerinfo.IsDirectShip),
dbhelper.CreateParameter("@IsEnabled",aCustomerinfo.IsEnabled),
dbhelper.CreateParameter("@UpdateTime",aCustomerinfo.UpdateTime),
dbhelper.CreateParameter("@CurrencyID",aCustomerinfo.CurrencyID),
dbhelper.CreateParameter("@NeedProformaInvoice",aCustomerinfo.NeedProformaInvoice),
dbhelper.CreateParameter("@ERPNO",aCustomerinfo.ERPNO)
};
return 0 < dbhelper.ExecuteNonQuery(sql.ToString(),p_Parms);
}
}
public bool Delete(string _eRPNO)
{
StringBuilder sql = new StringBuilder();
sql.Append("DELETE FROM");
sql.Append(" [A_Customer]");
sql.Append(" WHERE");
sql.Append(" [ERPNO]=@ERPNO");
using(OOSHelper dbhelper=new OOSHelper())
{
IDbDataParameter[] p_Parms = new IDbDataParameter[]{
dbhelper.CreateParameter("@ERPNO",_eRPNO)
};
return 0 < dbhelper.ExecuteNonQuery(sql.ToString(),p_Parms);
}
}
}
}
还有一种是采用静态调用的方式（尤其在对一些简单数据访问时为了方便就用静态调用方式）：
public class WebServiceDAL
{
public DataTable Test()
{
string sql = "select * from test";
return OOSHelper.StaticExecuteTable(System.Data.CommandType.Text, sql);
}
}
以上文章是小弟经过多年的经验无聊的时候想到的，该代码已经经过我的压力测试。
不好意思，小弟语文从高中以后从没有及格过，文章没有几个字，但是想分享给大家看看，提提意见，不懂可直接mail给我，
