微软官方SqlHelper类 数据库辅助操作类 原创
数据库操作类真的没有必要自己去写，因为成熟的类库真的非常完善了，拿来直接用就好，省时省力。
本文就为大家介绍微软官方的程序PetShop4.0中的SqlHelper类，先来做一下简单的介绍，PetShop是一个范例，微软用它来展示.Net企业系统开发的能力。
那SqlHelper中封装了哪些方法呢？
里面的函数一堆，常用的就那几个，无非就是增删改查嘛，来看下几种常用的函数：
1.ExecuteNonQuery执行增删改
2.ExecuteReader 执行查询
3.ExecuteScalar返回首行首列
使用方法介绍
Web.config配置
<connectionStrings> <addname="ConnectionString"connectionString="server=127.0.0.1;uid=sa;pwd=ok;database=PetShop;MaxPoolSize=512;MinPoolSize=0;ConnectionLifetime=300;packetsize=1000;"providerName="System.Data.SqlClient"/> </connectionStrings>
调用函数的写法
sql="UPDATEStudentsetName=@NameWHEREId=@Id";
SqlHelper.ExecuteNonQuery(CommandType.Text,sql,newSqlParameter[]{
newSqlParameter("@Name",name),
newSqlParameter("@Id",id)
});
这样调用就比较简化，而且比较灵活
源码呈上
///<summary>
///TheSqlHelperclassisintendedtoencapsulatehighperformance,
///scalablebestpracticesforcommonusesofSqlClient.
///</summary>
publicabstractclassSqlHelper
{
//数据库连接字符串
publicstaticreadonlystringConnectionString=ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
#region私有函数和方法
///<summary>
///ThismethodisusedtoattacharrayofSqlParameterstoaSqlCommand.
///
///ThismethodwillassignavalueofDbNulltoanyparameterwithadirectionof
///InputOutputandavalueofnull.
///
///Thisbehaviorwillpreventdefaultvaluesfrombeingused,but
///thiswillbethelesscommoncasethananintendedpureoutputparameter(derivedasInputOutput)
///wheretheuserprovidednoinputvalue.
///</summary>
///<paramname="command">Thecommandtowhichtheparameterswillbeadded</param>
///<paramname="commandParameters">AnarrayofSqlParameterstobeaddedtocommand</param>
privatestaticvoidAttachParameters(SqlCommandcommand,SqlParameter[]commandParameters)
{
if(command==null)thrownewArgumentNullException("command");
if(commandParameters!=null)
{
foreach(SqlParameterpincommandParameters)
{
if(p!=null)
{
//Checkforderivedoutputvaluewithnovalueassigned
if((p.Direction==ParameterDirection.InputOutput||
p.Direction==ParameterDirection.Input)&&
(p.Value==null))
{
p.Value=DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
///<summary>
///ThismethodassignsdataRowcolumnvaluestoanarrayofSqlParameters
///</summary>
///<paramname="commandParameters">ArrayofSqlParameterstobeassignedvalues</param>
///<paramname="dataRow">ThedataRowusedtoholdthestoredprocedure'sparametervalues</param>
privatestaticvoidAssignParameterValues(SqlParameter[]commandParameters,DataRowdataRow)
{
if((commandParameters==null)||(dataRow==null))
{
//Donothingifwegetnodata
return;
}
inti=0;
//Settheparametersvalues
foreach(SqlParametercommandParameterincommandParameters)
{
//Checktheparametername
if(commandParameter.ParameterName==null||
commandParameter.ParameterName.Length<=1)
thrownewException(
string.Format(
"Pleaseprovideavalidparameternameontheparameter#{0},theParameterNamepropertyhasthefollowingvalue:'{1}'.",
i,commandParameter.ParameterName));
if(dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1))!=-1)
commandParameter.Value=dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///<summary>
///ThismethodassignsanarrayofvaluestoanarrayofSqlParameters
///</summary>
///<paramname="commandParameters">ArrayofSqlParameterstobeassignedvalues</param>
///<paramname="parameterValues">Arrayofobjectsholdingthevaluestobeassigned</param>
privatestaticvoidAssignParameterValues(SqlParameter[]commandParameters,object[]parameterValues)
{
if((commandParameters==null)||(parameterValues==null))
{
//Donothingifwegetnodata
return;
}
//Wemusthavethesamenumberofvaluesaswepaveparameterstoputthemin
if(commandParameters.Length!=parameterValues.Length)
{
thrownewArgumentException("ParametercountdoesnotmatchParameterValuecount.");
}
//IteratethroughtheSqlParameters,assigningthevaluesfromthecorrespondingpositioninthe
//valuearray
for(inti=0,j=commandParameters.Length;i<j;i++)
{
//IfthecurrentarrayvaluederivesfromIDbDataParameter,thenassignitsValueproperty
if(parameterValues[i]isIDbDataParameter)
{
IDbDataParameterparamInstance=(IDbDataParameter)parameterValues[i];
if(paramInstance.Value==null)
{
commandParameters[i].Value=DBNull.Value;
}
else
{
commandParameters[i].Value=paramInstance.Value;
}
}
elseif(parameterValues[i]==null)
{
commandParameters[i].Value=DBNull.Value;
}
else
{
commandParameters[i].Value=parameterValues[i];
}
}
}
///<summary>
///Thismethodopens(ifnecessary)andassignsaconnection,transaction,commandtypeandparameters
///totheprovidedcommand
///</summary>
///<paramname="command">TheSqlCommandtobeprepared</param>
///<paramname="connection">AvalidSqlConnection,onwhichtoexecutethiscommand</param>
///<paramname="transaction">AvalidSqlTransaction,or'null'</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParameterstobeassociatedwiththecommandor'null'ifnoparametersarerequired</param>
///<paramname="mustCloseConnection"><c>true</c>iftheconnectionwasopenedbythemethod,otherwoseisfalse.</param>
privatestaticvoidPrepareCommand(SqlCommandcommand,SqlConnectionconnection,SqlTransactiontransaction,CommandTypecommandType,stringcommandText,SqlParameter[]commandParameters,outboolmustCloseConnection)
{
if(command==null)thrownewArgumentNullException("command");
if(commandText==null||commandText.Length==0)thrownewArgumentNullException("commandText");
//Iftheprovidedconnectionisnotopen,wewillopenit
if(connection.State!=ConnectionState.Open)
{
mustCloseConnection=true;
connection.Open();
}
else
{
mustCloseConnection=false;
}
//Associatetheconnectionwiththecommand
command.Connection=connection;
//Setthecommandtext(storedprocedurenameorSQLstatement)
command.CommandText=commandText;
//Ifwewereprovidedatransaction,assignit
if(transaction!=null)
{
if(transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
command.Transaction=transaction;
}
//Setthecommandtype
command.CommandType=commandType;
//Attachthecommandparametersiftheyareprovided
if(commandParameters!=null)
{
AttachParameters(command,commandParameters);
}
return;
}
#endregionprivateutilitymethods&constructors
#regionExecuteNonQuery
publicstaticintExecuteNonQuery(CommandTypecmdType,stringcmdText)
{
returnExecuteNonQuery(ConnectionString,cmdType,cmdText);
}
publicstaticintExecuteNonQuery(CommandTypecmdType,stringcmdText,paramsSqlParameter[]commandParameters)
{
returnExecuteNonQuery(ConnectionString,cmdType,cmdText,commandParameters);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultsetandtakesnoparameters)againstthedatabasespecifiedin
///theconnectionstring
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(connString,CommandType.StoredProcedure,"PublishOrders");
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(stringconnectionString,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteNonQuery(connectionString,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultset)againstthedatabasespecifiedintheconnectionstring
///usingtheprovidedparameters
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(connString,CommandType.StoredProcedure,"PublishOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
//Create&openaSqlConnection,anddisposeofitafterwearedone
using(SqlConnectionconnection=newSqlConnection(connectionString))
{
connection.Open();
//Calltheoverloadthattakesaconnectioninplaceoftheconnectionstring
returnExecuteNonQuery(connection,commandType,commandText,commandParameters);
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsnoresultset)againstthedatabasespecifiedin
///theconnectionstringusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///intresult=ExecuteNonQuery(connString,"PublishOrders",24,36);
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="spName">Thenameofthestoredprcedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(stringconnectionString,stringspName,paramsobject[]parameterValues)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connectionString,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteNonQuery(connectionString,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteNonQuery(connectionString,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultsetandtakesnoparameters)againsttheprovidedSqlConnection.
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(conn,CommandType.StoredProcedure,"PublishOrders");
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlConnectionconnection,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteNonQuery(connection,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultset)againstthespecifiedSqlConnection
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(conn,CommandType.StoredProcedure,"PublishOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlConnectionconnection,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connection==null)thrownewArgumentNullException("connection");
//Createacommandandprepareitforexecution
SqlCommandcmd=newSqlCommand();
boolmustCloseConnection=false;
PrepareCommand(cmd,connection,(SqlTransaction)null,commandType,commandText,commandParameters,outmustCloseConnection);
//Finally,executethecommand
intretval=cmd.ExecuteNonQuery();
//DetachtheSqlParametersfromthecommandobject,sotheycanbeusedagain
cmd.Parameters.Clear();
if(mustCloseConnection)
connection.Close();
returnretval;
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsnoresultset)againstthespecifiedSqlConnection
///usingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///intresult=ExecuteNonQuery(conn,"PublishOrders",24,36);
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlConnectionconnection,stringspName,paramsobject[]parameterValues)
{
if(connection==null)thrownewArgumentNullException("connection");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connection,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteNonQuery(connection,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteNonQuery(connection,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultsetandtakesnoparameters)againsttheprovidedSqlTransaction.
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(trans,CommandType.StoredProcedure,"PublishOrders");
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlTransactiontransaction,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteNonQuery(transaction,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsnoresultset)againstthespecifiedSqlTransaction
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///intresult=ExecuteNonQuery(trans,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlTransactiontransaction,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
//Createacommandandprepareitforexecution
SqlCommandcmd=newSqlCommand();
boolmustCloseConnection=false;
PrepareCommand(cmd,transaction.Connection,transaction,commandType,commandText,commandParameters,outmustCloseConnection);
//Finally,executethecommand
intretval=cmd.ExecuteNonQuery();
//DetachtheSqlParametersfromthecommandobject,sotheycanbeusedagain
cmd.Parameters.Clear();
returnretval;
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsnoresultset)againstthespecified
///SqlTransactionusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///intresult=ExecuteNonQuery(conn,trans,"PublishOrders",24,36);
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Anintrepresentingthenumberofrowsaffectedbythecommand</returns>
publicstaticintExecuteNonQuery(SqlTransactiontransaction,stringspName,paramsobject[]parameterValues)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(transaction.Connection,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteNonQuery(transaction,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteNonQuery(transaction,CommandType.StoredProcedure,spName);
}
}
#endregionExecuteNonQuery
#regionExecuteDataset
publicstaticDataSetExecuteDataset(CommandTypecommandType,stringcommandText)
{
returnExecuteDataset(ConnectionString,commandType,commandText);
}
publicstaticDataSetExecuteDataset(CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
returnExecuteDataset(ConnectionString,commandType,commandText,commandParameters);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againstthedatabasespecifiedin
///theconnectionstring.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(connString,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteDataset(connectionString,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthedatabasespecifiedintheconnectionstring
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(connString,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
//Create&openaSqlConnection,anddisposeofitafterwearedone
using(SqlConnectionconnection=newSqlConnection(connectionString))
{
connection.Open();
//Calltheoverloadthattakesaconnectioninplaceoftheconnectionstring
returnExecuteDataset(connection,commandType,commandText,commandParameters);
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthedatabasespecifiedin
///theconnectionstringusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///DataSetds=ExecuteDataset(connString,"GetOrders",24,36);
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(stringconnectionString,stringspName,paramsobject[]parameterValues)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connectionString,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteDataset(connectionString,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteDataset(connectionString,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againsttheprovidedSqlConnection.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(conn,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteDataset(connection,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlConnection
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(conn,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connection==null)thrownewArgumentNullException("connection");
//Createacommandandprepareitforexecution
SqlCommandcmd=newSqlCommand();
boolmustCloseConnection=false;
PrepareCommand(cmd,connection,(SqlTransaction)null,commandType,commandText,commandParameters,outmustCloseConnection);
//CreatetheDataAdapter&DataSet
using(SqlDataAdapterda=newSqlDataAdapter(cmd))
{
DataSetds=newDataSet();
//FilltheDataSetusingdefaultvaluesforDataTablenames,etc
da.Fill(ds);
//DetachtheSqlParametersfromthecommandobject,sotheycanbeusedagain
cmd.Parameters.Clear();
if(mustCloseConnection)
connection.Close();
//Returnthedataset
returnds;
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlConnection
///usingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///DataSetds=ExecuteDataset(conn,"GetOrders",24,36);
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlConnectionconnection,stringspName,paramsobject[]parameterValues)
{
if(connection==null)thrownewArgumentNullException("connection");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connection,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteDataset(connection,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteDataset(connection,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againsttheprovidedSqlTransaction.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(trans,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteDataset(transaction,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlTransaction
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///DataSetds=ExecuteDataset(trans,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
//Createacommandandprepareitforexecution
SqlCommandcmd=newSqlCommand();
boolmustCloseConnection=false;
PrepareCommand(cmd,transaction.Connection,transaction,commandType,commandText,commandParameters,outmustCloseConnection);
//CreatetheDataAdapter&DataSet
using(SqlDataAdapterda=newSqlDataAdapter(cmd))
{
DataSetds=newDataSet();
//FilltheDataSetusingdefaultvaluesforDataTablenames,etc
da.Fill(ds);
//DetachtheSqlParametersfromthecommandobject,sotheycanbeusedagain
cmd.Parameters.Clear();
//Returnthedataset
returnds;
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthespecified
///SqlTransactionusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///DataSetds=ExecuteDataset(trans,"GetOrders",24,36);
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Adatasetcontainingtheresultsetgeneratedbythecommand</returns>
publicstaticDataSetExecuteDataset(SqlTransactiontransaction,stringspName,paramsobject[]parameterValues)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(transaction.Connection,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteDataset(transaction,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteDataset(transaction,CommandType.StoredProcedure,spName);
}
}
#endregionExecuteDataset
#regionExecuteReader
///<summary>
///Thisenumisusedtoindicatewhethertheconnectionwasprovidedbythecaller,orcreatedbySqlHelper,sothat
///wecansettheappropriateCommandBehaviorwhencallingExecuteReader()
///</summary>
privateenumSqlConnectionOwnership
{
///<summary>ConnectionisownedandmanagedbySqlHelper</summary>
Internal,
///<summary>Connectionisownedandmanagedbythecaller</summary>
External
}
publicstaticSqlDataReaderExecuteReader(CommandTypecmdType,stringcmdText)
{
returnExecuteReader(ConnectionString,cmdType,cmdText);
}
publicstaticSqlDataReaderExecuteReader(CommandTypecmdType,stringcmdText,paramsSqlParameter[]commandParameters)
{
returnExecuteReader(ConnectionString,cmdType,cmdText,commandParameters);
}
///<summary>
///CreateandprepareaSqlCommand,andcallExecuteReaderwiththeappropriateCommandBehavior.
///</summary>
///<remarks>
///Ifwecreatedandopenedtheconnection,wewanttheconnectiontobeclosedwhentheDataReaderisclosed.
///
///Ifthecallerprovidedtheconnection,wewanttoleaveittothemtomanage.
///</remarks>
///<paramname="connection">AvalidSqlConnection,onwhichtoexecutethiscommand</param>
///<paramname="transaction">AvalidSqlTransaction,or'null'</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParameterstobeassociatedwiththecommandor'null'ifnoparametersarerequired</param>
///<paramname="connectionOwnership">Indicateswhethertheconnectionparameterwasprovidedbythecaller,orcreatedbySqlHelper</param>
///<returns>SqlDataReadercontainingtheresultsofthecommand</returns>
privatestaticSqlDataReaderExecuteReader(SqlConnectionconnection,SqlTransactiontransaction,CommandTypecommandType,stringcommandText,SqlParameter[]commandParameters,SqlConnectionOwnershipconnectionOwnership)
{
if(connection==null)thrownewArgumentNullException("connection");
boolmustCloseConnection=false;
//Createacommandandprepareitforexecution
SqlCommandcmd=newSqlCommand();
try
{
PrepareCommand(cmd,connection,transaction,commandType,commandText,commandParameters,outmustCloseConnection);
//Createareader
SqlDataReaderdataReader;
//CallExecuteReaderwiththeappropriateCommandBehavior
if(connectionOwnership==SqlConnectionOwnership.External)
{
dataReader=cmd.ExecuteReader();
}
else
{
dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//DetachtheSqlParametersfromthecommandobject,sotheycanbeusedagain.
//HACK:Thereisaproblemhere,theoutputparametervaluesarefletched
//whenthereaderisclosed,soiftheparametersaredetachedfromthecommand
//thentheSqlReadercan磘setitsvalues.
//Whenthishappen,theparameterscan磘beusedagaininothercommand.
boolcanClear=true;
foreach(SqlParametercommandParameterincmd.Parameters)
{
if(commandParameter.Direction!=ParameterDirection.Input)
canClear=false;
}
if(canClear)
{
cmd.Parameters.Clear();
}
returndataReader;
}
catch
{
if(mustCloseConnection)
connection.Close();
throw;
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againstthedatabasespecifiedin
///theconnectionstring.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(connString,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(stringconnectionString,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteReader(connectionString,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthedatabasespecifiedintheconnectionstring
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(connString,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
SqlConnectionconnection=null;
try
{
connection=newSqlConnection(connectionString);
connection.Open();
//Calltheprivateoverloadthattakesaninternallyownedconnectioninplaceoftheconnectionstring
returnExecuteReader(connection,null,commandType,commandText,commandParameters,SqlConnectionOwnership.Internal);
}
catch
{
//IfwefailtoreturntheSqlDatReader,weneedtoclosetheconnectionourselves
if(connection!=null)connection.Close();
throw;
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthedatabasespecifiedin
///theconnectionstringusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///SqlDataReaderdr=ExecuteReader(connString,"GetOrders",24,36);
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(stringconnectionString,stringspName,paramsobject[]parameterValues)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connectionString,spName);
AssignParameterValues(commandParameters,parameterValues);
returnExecuteReader(connectionString,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteReader(connectionString,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againsttheprovidedSqlConnection.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(conn,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlConnectionconnection,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteReader(connection,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlConnection
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(conn,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlConnectionconnection,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
//Passthroughthecalltotheprivateoverloadusinganulltransactionvalueandanexternallyownedconnection
returnExecuteReader(connection,(SqlTransaction)null,commandType,commandText,commandParameters,SqlConnectionOwnership.External);
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlConnection
///usingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///SqlDataReaderdr=ExecuteReader(conn,"GetOrders",24,36);
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlConnectionconnection,stringspName,paramsobject[]parameterValues)
{
if(connection==null)thrownewArgumentNullException("connection");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connection,spName);
AssignParameterValues(commandParameters,parameterValues);
returnExecuteReader(connection,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteReader(connection,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultsetandtakesnoparameters)againsttheprovidedSqlTransaction.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(trans,CommandType.StoredProcedure,"GetOrders");
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlTransactiontransaction,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteReader(transaction,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsaresultset)againstthespecifiedSqlTransaction
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///SqlDataReaderdr=ExecuteReader(trans,CommandType.StoredProcedure,"GetOrders",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlTransactiontransaction,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
//Passthroughtoprivateoverload,indicatingthattheconnectionisownedbythecaller
returnExecuteReader(transaction.Connection,transaction,commandType,commandText,commandParameters,SqlConnectionOwnership.External);
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsaresultset)againstthespecified
///SqlTransactionusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///SqlDataReaderdr=ExecuteReader(trans,"GetOrders",24,36);
///</remarks>
///<paramname="transaction">AvalidSqlTransaction</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>ASqlDataReadercontainingtheresultsetgeneratedbythecommand</returns>
publicstaticSqlDataReaderExecuteReader(SqlTransactiontransaction,stringspName,paramsobject[]parameterValues)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrollbackedorcommited,pleaseprovideanopentransaction.","transaction");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(transaction.Connection,spName);
AssignParameterValues(commandParameters,parameterValues);
returnExecuteReader(transaction,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteReader(transaction,CommandType.StoredProcedure,spName);
}
}
#endregionExecuteReader
#regionExecuteScalar
publicstaticobjectExecuteScalar(CommandTypecmdType,stringcmdText)
{
returnExecuteScalar(ConnectionString,cmdType,cmdText);
}
publicstaticobjectExecuteScalar(CommandTypecmdType,stringcmdText,paramsSqlParameter[]commandParameters)
{
returnExecuteScalar(ConnectionString,cmdType,cmdText,commandParameters);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsa1x1resultsetandtakesnoparameters)againstthedatabasespecifiedin
///theconnectionstring.
///</summary>
///<remarks>
///e.g.:
///intorderCount=(int)ExecuteScalar(connString,CommandType.StoredProcedure,"GetOrderCount");
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Anobjectcontainingthevalueinthe1x1resultsetgeneratedbythecommand</returns>
publicstaticobjectExecuteScalar(stringconnectionString,CommandTypecommandType,stringcommandText)
{
//PassthroughthecallprovidingnullforthesetofSqlParameters
returnExecuteScalar(connectionString,commandType,commandText,(SqlParameter[])null);
}
///<summary>
///ExecuteaSqlCommand(thatreturnsa1x1resultset)againstthedatabasespecifiedintheconnectionstring
///usingtheprovidedparameters.
///</summary>
///<remarks>
///e.g.:
///intorderCount=(int)ExecuteScalar(connString,CommandType.StoredProcedure,"GetOrderCount",newSqlParameter("@prodid",24));
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<paramname="commandParameters">AnarrayofSqlParamtersusedtoexecutethecommand</param>
///<returns>Anobjectcontainingthevalueinthe1x1resultsetgeneratedbythecommand</returns>
publicstaticobjectExecuteScalar(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
//Create&openaSqlConnection,anddisposeofitafterwearedone
using(SqlConnectionconnection=newSqlConnection(connectionString))
{
connection.Open();
//Calltheoverloadthattakesaconnectioninplaceoftheconnectionstring
returnExecuteScalar(connection,commandType,commandText,commandParameters);
}
}
///<summary>
///ExecuteastoredprocedureviaaSqlCommand(thatreturnsa1x1resultset)againstthedatabasespecifiedin
///theconnectionstringusingtheprovidedparametervalues.Thismethodwillquerythedatabasetodiscovertheparametersforthe
///storedprocedure(thefirsttimeeachstoredprocedureiscalled),andassignthevaluesbasedonparameterorder.
///</summary>
///<remarks>
///Thismethodprovidesnoaccesstooutputparametersorthestoredprocedure'sreturnvalueparameter.
///
///e.g.:
///intorderCount=(int)ExecuteScalar(connString,"GetOrderCount",24,36);
///</remarks>
///<paramname="connectionString">AvalidconnectionstringforaSqlConnection</param>
///<paramname="spName">Thenameofthestoredprocedure</param>
///<paramname="parameterValues">Anarrayofobjectstobeassignedastheinputvaluesofthestoredprocedure</param>
///<returns>Anobjectcontainingthevalueinthe1x1resultsetgeneratedbythecommand</returns>
publicstaticobjectExecuteScalar(stringconnectionString,stringspName,paramsobject[]parameterValues)
{
if(connectionString==null||connectionString.Length==0)thrownewArgumentNullException("connectionString");
if(spName==null||spName.Length==0)thrownewArgumentNullException("spName");
//Ifwereceiveparametervalues,weneedtofigureoutwheretheygo
if((parameterValues!=null)&&(parameterValues.Length>0))
{
//Pulltheparametersforthisstoredprocedurefromtheparametercache(ordiscoverthem&populatethecache)
SqlParameter[]commandParameters=SqlHelperParameterCache.GetSpParameterSet(connectionString,spName);
//Assigntheprovidedvaluestotheseparametersbasedonparameterorder
AssignParameterValues(commandParameters,parameterValues);
//CalltheoverloadthattakesanarrayofSqlParameters
returnExecuteScalar(connectionString,CommandType.StoredProcedure,spName,commandParameters);
}
else
{
//OtherwisewecanjustcalltheSPwithoutparams
returnExecuteScalar(connectionString,CommandType.StoredProcedure,spName);
}
}
///<summary>
///ExecuteaSqlCommand(thatreturnsa1x1resultsetandtakesnoparameters)againsttheprovidedSqlConnection.
///</summary>
///<remarks>
///e.g.:
///intorderCount=(int)ExecuteScalar(conn,CommandType.StoredProcedure,"GetOrderCount");
///</remarks>
///<paramname="connection">AvalidSqlConnection</param>
///<paramname="commandType">TheCommandType(storedprocedure,text,etc.)</param>
///<paramname="commandText">ThestoredprocedurenameorT-SQLcommand</param>
///<returns>Anobjectcontainingthevalueinthe1x1resultsetgeneratedbythecommand</returns>
publicstaticobjectExecuteScalar(SqlConnectionconnection,CommandTypecommandType,stringcommandText)
{
//Passthroughthecallprovidingnullfortheset