//********************************************************************* // Microsoft Data Access Application Block for .NET // http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp // // SQLHelper.cs // // This file contains the implementations of the SqlHelper and SqlHelperParameterCache // classes. // // For more information see the Data Access Application Block Implementation Overview. // using System; using System.Data; using System.Data.SqlClient; using System.Collections; using System.Data.OleDb;namespace WorkFlow.DAL { //********************************************************************* // // The SqlHelper class is intended to encapsulate high performance, scalable best practices for // common uses of SqlClient. // //********************************************************************* public class SqlHelper { //********************************************************************* // // Since this class provides only static methods, make the default constructor private to prevent // instances from being created with "new SqlHelper()". // //********************************************************************* private SqlHelper() {} //********************************************************************* // // This method is used to attach array of SqlParameters to a SqlCommand. // // This method will assign a value of DbNull to any parameter with a direction of // InputOutput and a value of null. // // This behavior will prevent default values from being used, but // this will be the less common case than an intended pure output parameter (derived as InputOutput) // where the user provided no input value. // // param name="command" The command to which the parameters will be added // param name="commandParameters" an array of SqlParameters tho be added to command // //********************************************************************* private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; }
command.Parameters.Add(p); } } //********************************************************************* // // This method assigns an array of values to an array of SqlParameters. // // param name="commandParameters" array of SqlParameters to be assigned values // param name="parameterValues" array of objects holding the values to be assigned // //********************************************************************* private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the SqlParameters, assigning the values from the corresponding position in the //value array for (int i = 0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } //********************************************************************* // // This method opens (if necessary) and assigns a connection, transaction, command type and parameters // to the provided command. // // param name="command" the SqlCommand to be prepared // param name="connection" a valid SqlConnection, on which to execute this command // param name="transaction" a valid SqlTransaction, or 'null' // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParameters to be associated with the command or 'null' if no parameters are required // //********************************************************************* private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } //********************************************************************* // // Execute a SqlCommand (that returns no resultset) against the database specified in the connection string // using the provided parameters. // // e.g.: // int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); // // param name="connectionString" a valid connection string for a SqlConnection // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParamters used to execute the command // returns an int representing the number of rows affected by the command // //********************************************************************* /// <summary> /// 执行sql语句，并返回受影响的行数 /// </summary> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } }
//********************************************************************* // // Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in // the connection string using the provided parameter values. This method will query the database to discover the parameters for the // stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. // // This method provides no access to output parameters or the stored procedure's return value parameter. // // e.g.: // int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); // // param name="connectionString" a valid connection string for a SqlConnection // param name="spName" the name of the stored prcedure // param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure // returns an int representing the number of rows affected by the command // //********************************************************************* public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } //********************************************************************* // // Execute a SqlCommand (that returns no resultset) against the specified SqlConnection // using the provided parameters. // // e.g.: // int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); // // param name="connection" a valid SqlConnection // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParamters used to execute the command // returns an int representing the number of rows affected by the command // //********************************************************************* public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//finally, execute the command. int retval = cmd.ExecuteNonQuery();
// detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } //********************************************************************* // // Execute a SqlCommand (that returns a resultset) against the database specified in the connection string // using the provided parameters. // // e.g.: // DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); // // param name="connectionString" a valid connection string for a SqlConnection // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParamters used to execute the command // returns a dataset containing the resultset generated by the command // //********************************************************************* public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } }
//********************************************************************* // // Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in // the connection string using the provided parameter values. This method will query the database to discover the parameters for the // stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. // // This method provides no access to output parameters or the stored procedure's return value parameter. // // e.g.: // DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); // // param name="connectionString" a valid connection string for a SqlConnection // param name="spName" the name of the stored procedure // param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure // returns a dataset containing the resultset generated by the command // //********************************************************************* public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } }
//********************************************************************* // // Execute a SqlCommand (that returns a resultset) against the specified SqlConnection // using the provided parameters. // // e.g.: // DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); // // param name="connection" a valid SqlConnection // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParamters used to execute the command // returns a dataset containing the resultset generated by the command // //********************************************************************* public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds);
// detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear();
//return the dataset return ds; } //********************************************************************* // // Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string // using the provided parameters. // // e.g.: // int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); // // param name="connectionString" a valid connection string for a SqlConnection // param name="commandType" the CommandType (stored procedure, text, etc.) // param name="commandText" the stored procedure name or T-SQL command // param name="commandParameters" an array of SqlParamters used to execute the command // returns an object containing the value in the 1x1 resultset generated by the command // //********************************************************************* public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } //********************************************************************* // // Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in // the connection string using the provided parameter values. This method will query the database to discover the parameters for the // stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. // // This method provides no access to output parameters or the stored procedure's return value parameter. // // e.g.: // int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); // // param name="connectionString" a valid connection string for a SqlConnection // param name="spName" the name of the stored procedure // param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure // returns an object containing the value in the 1x1 resultset generated by the command // //********************************************************************* public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } }
http://www.microsoft.com/China/Community/program/originalarticles/TechDoc/NETapplication.mspx
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and SqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.OleDb;namespace WorkFlow.DAL
{
//*********************************************************************
//
// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
// common uses of SqlClient.
//
//********************************************************************* public class SqlHelper
{
//*********************************************************************
//
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new SqlHelper()".
//
//********************************************************************* private SqlHelper() {} //*********************************************************************
//
// This method is used to attach array of SqlParameters to a SqlCommand.
//
// This method will assign a value of DbNull to any parameter with a direction of
// InputOutput and a value of null.
//
// This behavior will prevent default values from being used, but
// this will be the less common case than an intended pure output parameter (derived as InputOutput)
// where the user provided no input value.
//
// param name="command" The command to which the parameters will be added
// param name="commandParameters" an array of SqlParameters tho be added to command
//
//********************************************************************* private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
} //*********************************************************************
//
// This method assigns an array of values to an array of SqlParameters.
//
// param name="commandParameters" array of SqlParameters to be assigned values
// param name="parameterValues" array of objects holding the values to be assigned
//
//********************************************************************* private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
} // we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
} //iterate through the SqlParameters, assigning the values from the corresponding position in the
//value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
} //*********************************************************************
//
// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
// to the provided command.
//
// param name="command" the SqlCommand to be prepared
// param name="connection" a valid SqlConnection, on which to execute this command
// param name="transaction" a valid SqlTransaction, or 'null'
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParameters to be associated with the command or 'null' if no parameters are required
//
//********************************************************************* private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
} //associate the connection with the command
command.Connection = connection; //set the command text (stored procedure name or SQL statement)
command.CommandText = commandText; //if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
} //set the command type
command.CommandType = commandType; //attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
} return;
} //*********************************************************************
//
// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
// using the provided parameters.
//
// e.g.:
// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParamters used to execute the command
// returns an int representing the number of rows affected by the command
//
//*********************************************************************
/// <summary>
/// 执行sql语句，并返回受影响的行数
/// </summary>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open(); //call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
//
// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
//
// This method provides no access to output parameters or the stored procedure's return value parameter.
//
// e.g.:
// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="spName" the name of the stored prcedure
// param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure
// returns an int representing the number of rows affected by the command
//
//********************************************************************* public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
} //*********************************************************************
//
// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
// using the provided parameters.
//
// e.g.:
// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
//
// param name="connection" a valid SqlConnection
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParamters used to execute the command
// returns an int representing the number of rows affected by the command
//
//********************************************************************* public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
} //*********************************************************************
//
// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
// using the provided parameters.
//
// e.g.:
// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParamters used to execute the command
// returns a dataset containing the resultset generated by the command
//
//********************************************************************* public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open(); //call the overload that takes a connection in place of the connection string
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
//
// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
//
// This method provides no access to output parameters or the stored procedure's return value parameter.
//
// e.g.:
// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="spName" the name of the stored procedure
// param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure
// returns a dataset containing the resultset generated by the command
//
//********************************************************************* public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
//*********************************************************************
//
// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
// using the provided parameters.
//
// e.g.:
// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
//
// param name="connection" a valid SqlConnection
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParamters used to execute the command
// returns a dataset containing the resultset generated by the command
//
//********************************************************************* public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
//return the dataset
return ds;
}
//*********************************************************************
//
// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
// using the provided parameters.
//
// e.g.:
// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParamters used to execute the command
// returns an object containing the value in the 1x1 resultset generated by the command
//
//********************************************************************* public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open(); //call the overload that takes a connection in place of the connection string
return ExecuteScalar(cn, commandType, commandText, commandParameters);
}
} //*********************************************************************
//
// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
//
// This method provides no access to output parameters or the stored procedure's return value parameter.
//
// e.g.:
// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
//
// param name="connectionString" a valid connection string for a SqlConnection
// param name="spName" the name of the stored procedure
// param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure
// returns an object containing the value in the 1x1 resultset generated by the command
//
//********************************************************************* public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
说说设计思想总可以吧
自己调用Framework写个数据访问的类吧！
到时候引用就行
使用一个xml文件，列举可能用到的数据库，用一个字段来标示当前数据库的类型，比如sqlServer的Active为true表示当前数据库是sqlServer，然后生成对应的数据库访问的类，Command，Connection等，转换成IDbCommand，IDbConnection等接口来使用
http://www.15seconds.com/issue/040127.htm
using System.Data;namespace DataUtil.common2
{
/// <summary>
/// DBOperator 的摘要说明。
/// </summary>
public abstract class DBOperator
{
/// <summary>
/// 得到数据库连接
/// </summary>
public abstract IDbConnection Connection{get;}
/// <summary>
/// 打开数据库连接
/// </summary>
public abstract void Open();
/// <summary>
/// 关闭数据库连接
/// </summary>
public abstract void Close();
/// <summary>
/// 开始一个事务
/// </summary>
public abstract void BeginTrans();
/// <summary>
/// 提交一个事务
/// </summary>
public abstract void CommitTrans();
/// <summary>
/// 回滚一个事务
/// </summary>
public abstract void RollbackTrans();
/// <summary>
/// 执行无返回值的SQL语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="strParams"></param>
/// <param name="objValues"></param>
public abstract void exeSql(string strSql,string[] strParams,object[] objValues);
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="QueryString"></param>
/// <returns></returns>
public abstract DataSet exeSqlForDataSet(string QueryString);
}
}using System;namespace DataUtil.common2
{
/// <summary>
/// DBOperatorFactory 的摘要说明。
/// </summary>
public class DBOperatorFactory
{
public static DBOperator GetDBOperator(string strConnection)
{
if(strConnection.IndexOf("Provider=")<0) //SqlServer
{
return new SqlDBOperator(strConnection);
}
else //other database
{
return new OleDBOperator(strConnection);
}
}
}
}
using System.Data;
using System.Data.SqlClient;namespace DataUtil.common2
{
/// <summary>
/// SqlDBOperator 的摘要说明。
/// </summary>
internal class SqlDBOperator:DBOperator
{
private SqlConnection conn; //数据库连接
private SqlTransaction trans; //事务处理类
private bool inTransaction=false; //指示当前是否正处于事务中
/// <summary>
///
/// </summary>
public override IDbConnection Connection
{
get
{
return this.conn;
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="strConnection"></param>
public SqlDBOperator(string strConnection)
{
this.conn= new SqlConnection(strConnection);
}
public override void Open()
{
if(conn.State.ToString().ToUpper()!="OPEN")
this.conn.Open();
}
public override void Close()
{
if (conn.State.ToString().ToUpper()=="OPEN")
this.conn.Close();
}
public override void BeginTrans()
{
trans=conn.BeginTransaction() ;
inTransaction=true;
}
public override void CommitTrans()
{
trans.Commit();
inTransaction=false;
}
public override void RollbackTrans()
{
trans.Rollback();
inTransaction=false;
}
public override void exeSql(string strSql,string[] strParams,object[] strValues)
{
SqlCommand cmd=new SqlCommand();
cmd.Connection=this.conn ;
if(inTransaction)
cmd.Transaction=trans;
if((strParams!=null)&&(strParams.Length!=strValues.Length) )
return;
// throw new ParamValueNotMatchException("查询参数和值不对应!");
cmd.CommandText=strSql;
if(strParams!=null)
{
for(int i=0;i<strParams.Length;i++)
cmd.Parameters.Add(strParams[i],strValues[i]);
}
cmd.ExecuteNonQuery();
}
public override DataSet exeSqlForDataSet(string QueryString)
{
SqlCommand cmd=new SqlCommand();
cmd.Connection=this.conn ;
if(inTransaction)
cmd.Transaction=trans;
DataSet ds = new DataSet();
SqlDataAdapter ad = new SqlDataAdapter();
cmd.CommandText=QueryString;
ad.SelectCommand =cmd;
ad.Fill(ds);
return ds;
}
}
}using System;
using System.Data;
using System.Data.OleDb;namespace DataUtil.common2
{
/// <summary>
/// OleDBOperator 的摘要说明。
/// </summary>
public class OleDBOperator:DBOperator
{
private OleDbConnection conn;
private OleDbTransaction trans;
private bool inTransaction=false;
public OleDBOperator(string strConnection)
{
this.conn= new OleDbConnection(strConnection);
}
public override IDbConnection Connection
{
get
{
return this.conn;
}
}
public override void Open()
{
if(conn.State.ToString().ToUpper()!="OPEN")
this.conn.Open();
}
public override void Close()
{
if (conn.State.ToString().ToUpper()=="OPEN")
this.conn.Close();
}
public override void BeginTrans()
{
trans=conn.BeginTransaction() ;
inTransaction=true;
}
public override void CommitTrans()
{
trans.Commit();
inTransaction=false;
}
public override void RollbackTrans()
{
trans.Rollback();
inTransaction=false;
}
public override void exeSql(string strSql,string[] strParams,object[] strValues)
{
OleDbCommand cmd=new OleDbCommand();
cmd.Connection=this.conn ;
if(inTransaction)
cmd.Transaction=trans;
if((strParams!=null)&&(strParams.Length!=strValues.Length) )
return;
//throw new ParamValueNotMatchException("查询参数和值不对应!");
cmd.CommandText=this.ChangeQueryString(strSql);
if(strParams!=null)
{
for(int i=0;i<strParams.Length;i++)
cmd.Parameters.Add(strParams[i],strValues[i]);
}
cmd.ExecuteNonQuery();
}
public override DataSet exeSqlForDataSet(string QueryString)
{
OleDbCommand cmd=new OleDbCommand();
cmd.Connection=this.conn ;
if(inTransaction)
cmd.Transaction=trans;
DataSet ds = new DataSet();
OleDbDataAdapter ad = new OleDbDataAdapter();
cmd.CommandText=QueryString;
ad.SelectCommand =cmd;
ad.Fill(ds);
return ds;
}
/// <summary>
/// 转化SQL字符串，把mssql的转化成access的。
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>适合access的SQL语句</returns>
private string ChangeQueryString(string strSql)
{
return strSql;
}
}
}
Data Access Application Block for .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp