SqlHelper类的方法.docx
《SqlHelper类的方法.docx》由会员分享,可在线阅读,更多相关《SqlHelper类的方法.docx(13页珍藏版)》请在冰豆网上搜索。
SqlHelper类的方法
usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Web.UI.HtmlControls;
usingSystem.Data.SqlClient;
usingSystem.Collections;
///
///SqlHelper的摘要说明
publicclassSQLHelper
{
//Fields
publicstaticreadonlystringCONN_STRING;
privatestaticHashtableparmCache;
staticSQLHelper()
//definitiontheDatabaseConnection;
SQLHelper.CONN_STRING="DataSource=.;InitialCatalog=two_company;PersistSecurityInfo=True;UserID=sa;Password=sa";
SQLHelper.parmCache=Hashtable.Synchronized(newHashtable());
}
publicstaticvoidCacheParameters(stringcacheKey,paramsSqlParameter[]cmdParms)
SQLHelper.parmCache[cacheKey]=cmdParms;
//cleartheSqlCommandParamsSetitValuesisNull
publicstaticvoidClearParameterValues(paramsSqlParameter[]cmdParms)
SqlParameter[]parameterArray1=cmdParms;
for(intnum1=0;num1{SqlParameterparameter1=parameterArray1[num1];parameter1.Value=DBNull.Value;}}publicstaticSqlParameterCreateReturnParameter(stringparameterName){returnnewSqlParameter(parameterName,SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null);}publicstaticDataRowExecuteDataRow(stringcmdText){DataSetset1=SQLHelper.ExecuteDataset(cmdText);if((set1.Tables.Count>0)&&(set1.Tables[0].Rows.Count>0)){//whyReturnFirstRowreturnset1.Tables[0].Rows[0];}returnnull;}publicstaticDataSetExecuteDataset(stringcmdText){DataSetset2;SqlCommandcommand1=newSqlCommand();SqlConnectionconnection1=newSqlConnection(SQLHelper.CONN_STRING);DataSetset1=newDataSet();try{//Oftenneedtodetermineif(connection1.State!=ConnectionState.Open){connection1.Open();}command1.Connection=connection1;command1.CommandText=cmdText;command1.CommandType=CommandType.Text;SqlDataAdapteradapter1=newSqlDataAdapter();adapter1.SelectCommand=command1;adapter1.Fill(set1);set2=set1;}catch{throw;}finally{connection1.Close();}returnset2;}publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText){returnSQLHelper.ExecuteDataset(connection,commandType,commandText,(SqlParameter[])null);}publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText){returnSQLHelper.ExecuteDataset(transaction,commandType,commandText,(SqlParameter[])null);}publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText){returnSQLHelper.ExecuteDataset(connectionString,commandType,commandText,(SqlParameter[])null);}publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters){SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,connection,null,commandType,commandText,commandParameters);SqlDataAdapteradapter1=newSqlDataAdapter(command1);DataSetset1=newDataSet();adapter1.Fill(set1);command1.Parameters.Clear();returnset1;}publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters){SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,transaction.Connection,transaction,commandType,commandText,commandParameters);SqlDataAdapteradapter1=newSqlDataAdapter(command1);DataSetset1=newDataSet();adapter1.Fill(set1);command1.Parameters.Clear();returnset1;}publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters){DataSetset1;using(SqlConnectionconnection1=newSqlConnection(connectionString)){connection1.Open();set1=SQLHelper.ExecuteDataset(connection1,commandType,commandText,commandParameters);}returnset1;}publicstaticDataSetExecuteDataset(stringconnString,CommandTypecommandType,stringcommandText,SqlParameter[]commandParameters,intstartRecord,intmaxRecords,stringsrcTable){SqlConnectionconnection1=newSqlConnection(connString);SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,connection1,null,commandType,commandText,commandParameters);SqlDataAdapteradapter1=newSqlDataAdapter(command1);DataSetset1=newDataSet();adapter1.Fill(set1,startRecord,maxRecords,srcTable);//RememberThesecommand1.Parameters.Clear();returnset1;}publicstaticDataTableExecuteDataTable(stringcmdText){DataSetset1=SQLHelper.ExecuteDataset(cmdText);if(set1.Tables.Count>0){returnset1.Tables[0];}returnnull;}publicstaticintExecuteNonQuery(stringcmdText){intnum2;SqlCommandcommand1=newSqlCommand();using(SqlConnectionconnection1=newSqlConnection(SQLHelper.CONN_STRING)){SQLHelper.PrepareCommand(command1,connection1,null,CommandType.Text,cmdText,null);intnum1=command1.ExecuteNonQuery();command1.Parameters.Clear();num2=num1;}returnnum2;}//IfSqlCommand.ExecuteNonQueryimplementationofsuccessful,ItReturn1or>1elseitReturn-1publicstaticintExecuteNonQuery(SqlConnectionconn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,conn,null,cmdType,cmdText,cmdParms);intnum1=command1.ExecuteNonQuery();command1.Parameters.Clear();returnnum1;}publicstaticintExecuteNonQuery(SqlTransactiontrans,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,trans.Connection,trans,cmdType,cmdText,cmdParms);intnum1=command1.ExecuteNonQuery();command1.Parameters.Clear();returnnum1;}publicstaticintExecuteNonQuery(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){intnum2;SqlCommandcommand1=newSqlCommand();using(SqlConnectionconnection1=newSqlConnection(connString)){SQLHelper.PrepareCommand(command1,connection1,null,cmdType,cmdText,cmdParms);intnum1=command1.ExecuteNonQuery();command1.Parameters.Clear();num2=num1;}returnnum2;}publicstaticSqlDataReaderExecuteReader(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){SqlDataReaderreader2;SqlCommandcommand1=newSqlCommand();SqlConnectionconnection1=newSqlConnection(connString);try{SQLHelper.PrepareCommand(command1,connection1,null,cmdType,cmdText,cmdParms);SqlDataReaderreader1=command1.ExecuteReader(CommandBehavior.CloseConnection);command1.Parameters.Clear();reader2=reader1;}catch{connection1.Close();throw;}returnreader2;}publicstaticSqlDataReaderExecuteReader(SqlConnectioncn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){SqlDataReaderreader2;SqlCommandcommand1=newSqlCommand();try{SQLHelper.PrepareCommand(command1,cn,null,cmdType,cmdText,cmdParms);SqlDataReaderreader1=command1.ExecuteReader(CommandBehavior.CloseConnection);command1.Parameters.Clear();reader2=reader1;}catch{cn.Close();throw;}returnreader2;}publicstaticobjectExecuteScalar(stringcmdText){objectobj2;SqlCommandcommand1=newSqlCommand();using(SqlConnectionconnection1=newSqlConnection(SQLHelper.CONN_STRING)){SQLHelper.PrepareCommand(command1,connection1,null,CommandType.Text,cmdText,null);objectobj1=command1.ExecuteScalar();command1.Parameters.Clear();obj2=obj1;}returnobj2;}publicstaticobjectExecuteScalar(SqlConnectionconn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){SqlCommandcommand1=newSqlCommand();SQLHelper.PrepareCommand(command1,conn,null,cmdType,cmdText,cmdParms);objectobj1=command1.ExecuteScalar();command1.Parameters.Clear();returnobj1;}publicstaticobjectExecuteScalar(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms){objectobj2;SqlCommandcommand1=newSqlCommand();using(SqlConnectionconnection1=newSqlConnection(connString)){SQLHelper.PrepareCommand(command1,connection1,null,cmdType,cmdText,cmdParms);objectobj1=command1.ExecuteScalar();command1.Parameters.Clear();obj2=obj1;}returnobj2;}publicstaticSqlParameter[]GetCachedParameters(stringcacheKey){SqlParameter[]parameterArray1=(SqlParameter[])SQLHelper.parmCache[cacheKey];if(parameterArray1==null){returnnull;}SqlParameter[]parameterArray2=newSqlParameter[parameterArray1.Length];intnum1=0;intnum2=parameterArray1.Length;while(num1{parameterArray2[num1]=(SqlParameter)((ICloneable)parameterArray1[num1]).Clone();num1++;}returnparameterArray2;}//performSqlCommandprivatestaticvoidPrepareCommand(SqlCommandcmd,SqlConnectionconn,SqlTransactiontrans,CommandTypecmdType,stringcmdText,SqlParameter[]cmdParms){if(conn.State!=ConnectionState.Open){conn.Open();}cmd.Connection=conn;cmd.CommandText=cmdText;if(trans!=null){cmd.Transaction=trans;}cmd.CommandType=cmdType;if(cmdParms!=null){SqlParameter[]parameterArray1=cmdParm
SqlParameterparameter1=parameterArray1[num1];
parameter1.Value=DBNull.Value;
publicstaticSqlParameterCreateReturnParameter(stringparameterName)
returnnewSqlParameter(parameterName,SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null);
publicstaticDataRowExecuteDataRow(stringcmdText)
DataSetset1=SQLHelper.ExecuteDataset(cmdText);
if((set1.Tables.Count>0)&&(set1.Tables[0].Rows.Count>0))
//whyReturnFirstRow
returnset1.Tables[0].Rows[0];
returnnull;
publicstaticDataSetExecuteDataset(stringcmdText)
DataSetset2;
SqlCommandcommand1=newSqlCommand();
SqlConnectionconnection1=newSqlConnection(SQLHelper.CONN_STRING);
DataSetset1=newDataSet();
try
//Oftenneedtodetermine
if(connection1.State!
=ConnectionState.Open)
connection1.Open();
command1.Connection=connection1;
command1.CommandText=cmdText;
command1.CommandType=CommandType.Text;
SqlDataAdapteradapter1=newSqlDataAdapter();
adapter1.SelectCommand=command1;
adapter1.Fill(set1);
set2=set1;
catch
throw;
finally
connection1.Close();
returnset2;
publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText)
returnSQLHelper.ExecuteDataset(connection,commandType,commandText,(SqlParameter[])null);
publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText)
returnSQLHelper.ExecuteDataset(transaction,commandType,commandText,(SqlParameter[])null);
publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText)
returnSQLHelper.ExecuteDataset(connectionString,commandType,commandText,(SqlParameter[])null);
publicstaticDataSetExecuteDataset(SqlConnectionconnection,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
SQLHelper.PrepareCommand(command1,connection,null,commandType,commandText,commandParameters);
SqlDataAdapteradapter1=newSqlDataAdapter(command1);
command1.Parameters.Clear();
returnset1;
publicstaticDataSetExecuteDataset(SqlTransactiontransaction,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
SQLHelper.PrepareCommand(command1,transaction.Connection,transaction,commandType,commandText,commandParameters);
publicstaticDataSetExecuteDataset(stringconnectionString,CommandTypecommandType,stringcommandText,paramsSqlParameter[]commandParameters)
DataSetset1;
using(SqlConnectionconnection1=newSqlConnection(connectionString))
set1=SQLHelper.ExecuteDataset(connection1,commandType,commandText,commandParameters);
publicstaticDataSetExecuteDataset(stringconnString,CommandTypecommandType,stringcommandText,SqlParameter[]commandParameters,intstartRecord,intmaxRecords,stringsrcTable)
SqlConnectionconnection1=newSqlConnection(connString);
SQLHelper.PrepareCommand(command1,connection1,null,commandType,commandText,commandParameters);
adapter1.Fill(set1,startRecord,maxRecords,srcTable);
//RememberThese
publicstaticDataTableExecuteDataTable(stringcmdText)
if(set1.Tables.Count>0)
returnset1.Tables[0];
publicstaticintExecuteNonQuery(stringcmdText)
intnum2;
using(SqlConnectionconnection1=newSqlConnection(SQLHelper.CONN_STRING))
SQLHelper.PrepareCommand(command1,connection1,null,CommandType.Text,cmdText,null);
intnum1=command1.ExecuteNonQuery();
num2=num1;
returnnum2;
//IfSqlCommand.ExecuteNonQueryimplementationofsuccessful,ItReturn1or>1elseitReturn-1
publicstaticintExecuteNonQuery(SqlConnectionconn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
SQLHelper.PrepareCommand(command1,conn,null,cmdType,cmdText,cmdParms);
returnnum1;
publicstaticintExecuteNonQuery(SqlTransactiontrans,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
SQLHelper.PrepareCommand(command1,trans.Connection,trans,cmdType,cmdText,cmdParms);
publicstaticintExecuteNonQuery(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
using(SqlConnectionconnection1=newSqlConnection(connString))
SQLHelper.PrepareCommand(command1,connection1,null,cmdType,cmdText,cmdParms);
publicstaticSqlDataReaderExecuteReader(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
SqlDataReaderreader2;
SqlDataReaderreader1=command1.ExecuteReader(CommandBehavior.CloseConnection);
reader2=reader1;
returnreader2;
publicstaticSqlDataReaderExecuteReader(SqlConnectioncn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
SQLHelper.PrepareCommand(command1,cn,null,cmdType,cmdText,cmdParms);
cn.Close();
publicstaticobjectExecuteScalar(stringcmdText)
objectobj2;
objectobj1=command1.ExecuteScalar();
obj2=obj1;
returnobj2;
publicstaticobjectExecuteScalar(SqlConnectionconn,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
returnobj1;
publicstaticobjectExecuteScalar(stringconnString,CommandTypecmdType,stringcmdText,paramsSqlParameter[]cmdParms)
publicstaticSqlParameter[]GetCachedParameters(stringcacheKey)
SqlParameter[]parameterArray1=(SqlParameter[])SQLHelper.parmCache[cacheKey];
if(parameterArray1==null)
SqlParameter[]parameterArray2=newSqlParameter[parameterArray1.Length];
intnum1=0;
intnum2=parameterArray1.Length;
while(num1{parameterArray2[num1]=(SqlParameter)((ICloneable)parameterArray1[num1]).Clone();num1++;}returnparameterArray2;}//performSqlCommandprivatestaticvoidPrepareCommand(SqlCommandcmd,SqlConnectionconn,SqlTransactiontrans,CommandTypecmdType,stringcmdText,SqlParameter[]cmdParms){if(conn.State!=ConnectionState.Open){conn.Open();}cmd.Connection=conn;cmd.CommandText=cmdText;if(trans!=null){cmd.Transaction=trans;}cmd.CommandType=cmdType;if(cmdParms!=null){SqlParameter[]parameterArray1=cmdParm
parameterArray2[num1]=(SqlParameter)((ICloneable)parameterArray1[num1]).Clone();
num1++;
returnparameterArray2;
//performSqlCommand
privatestaticvoidPrepareCommand(SqlCommandcmd,SqlConnectionconn,SqlTransactiontrans,CommandTypecmdType,stringcmdText,SqlParameter[]cmdParms)
if(conn.State!
conn.Open();
cmd.Connection=conn;
cmd.CommandText=cmdText;
if(trans!
=null)
cmd.Transaction=trans;
cmd.CommandType=cmdType;
if(cmdParms!
SqlParameter[]parameterArray1=cmdParm
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1