1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- using System.Xml;
- using System.Collections;
- namespace SCC.Common
- {
-
-
-
- public sealed class SqlHelper
- {
- #region 私有构造函数和方法
- private SqlHelper() { }
-
-
-
-
-
-
-
- private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
- {
- if (command == null) throw new ArgumentNullException("command");
- if (commandParameters != null)
- {
- foreach (SqlParameter p in commandParameters)
- {
- if (p != null)
- {
-
- if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
- (p.Value == null))
- {
- p.Value = DBNull.Value;
- }
- command.Parameters.Add(p);
- }
- }
- }
- }
-
-
-
-
-
- private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
- {
- if ((commandParameters == null) || (dataRow == null))
- {
- return;
- }
- int i = 0;
-
- foreach (SqlParameter commandParameter in commandParameters)
- {
-
- if (commandParameter.ParameterName == null ||
- commandParameter.ParameterName.Length <= 1)
- throw new Exception(
- string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
-
-
- if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
- commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
- i++;
- }
- }
-
-
-
-
-
- private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
- {
- if ((commandParameters == null) || (parameterValues == null))
- {
- return;
- }
-
- if (commandParameters.Length != parameterValues.Length)
- {
- throw new ArgumentException("参数值个数与参数不匹配.");
- }
-
- for (int i = 0, j = commandParameters.Length; i < j; i++)
- {
-
- if (parameterValues[i] is IDbDataParameter)
- {
- IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
- if (paramInstance.Value == null)
- {
- commandParameters[i].Value = DBNull.Value;
- }
- else
- {
- commandParameters[i].Value = paramInstance.Value;
- }
- }
- else if (parameterValues[i] == null)
- {
- commandParameters[i].Value = DBNull.Value;
- }
- else
- {
- commandParameters[i].Value = parameterValues[i];
- }
- }
- }
-
-
-
-
-
-
-
-
-
-
- private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
- {
- if (command == null) throw new ArgumentNullException("command");
- if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
-
- if (connection.State != ConnectionState.Open)
- {
- mustCloseConnection = true;
- connection.Open();
- }
- else
- {
- mustCloseConnection = false;
- }
-
- command.Connection = connection;
-
- command.CommandText = commandText;
-
- if (transaction != null)
- {
- if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- command.Transaction = transaction;
- }
-
- command.CommandType = commandType;
-
- if (commandParameters != null)
- {
- AttachParameters(command, commandParameters);
- }
- return;
- }
- #endregion 私有构造函数和方法结束
- #region 数据库连接
-
-
-
-
- public static string GetConnSting()
- {
- return ConfigurationManager.ConnectionStrings["SCCConnectionString"].ConnectionString;
- }
-
-
-
-
- public static SqlConnection GetConnection()
- {
- SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
- return Connection;
- }
- #endregion
- #region ExecuteNonQuery命令
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
- {
- return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
- return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
- {
- return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- return ExecuteNonQuery(GetConnection(), commandType, commandText, commandParameters);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connection == null) throw new ArgumentNullException("connection");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
-
- int retval = cmd.ExecuteNonQuery();
-
- cmd.Parameters.Clear();
- if (mustCloseConnection)
- connection.Close();
- return retval;
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
- {
- return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- int retval = cmd.ExecuteNonQuery();
-
- cmd.Parameters.Clear();
- return retval;
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion ExecuteNonQuery方法结束
- #region ExecuteDataset方法
-
-
-
-
-
-
- public static DataSet ExecuteDataset(CommandType commandType, string commandText)
- {
- return ExecuteDataset(GetConnSting(), commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(CommandType commandType, string commandText, SqlParameter[] param)
- {
- return ExecuteDataset(GetConnSting(), commandType, commandText, param);
- }
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
- {
- return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
-
- return ExecuteDataset(connection, commandType, commandText, commandParameters);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
- {
- return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connection == null) throw new ArgumentNullException("connection");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
-
- using (SqlDataAdapter da = new SqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
-
- da.Fill(ds);
- cmd.Parameters.Clear();
- if (mustCloseConnection)
- connection.Close();
- return ds;
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
- {
- return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- using (SqlDataAdapter da = new SqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
- da.Fill(ds);
- cmd.Parameters.Clear();
- return ds;
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion ExecuteDataset数据集命令结束
- #region ExecuteReader 数据阅读器
-
-
-
- private enum SqlConnectionOwnership
- {
-
- Internal,
-
- External
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- bool mustCloseConnection = false;
-
- SqlCommand cmd = new SqlCommand();
- try
- {
- PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- SqlDataReader dataReader;
- if (connectionOwnership == SqlConnectionOwnership.External)
- {
- dataReader = cmd.ExecuteReader();
- }
- else
- {
- dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- }
-
-
-
-
-
- bool canClear = true;
- foreach (SqlParameter commandParameter in cmd.Parameters)
- {
- if (commandParameter.Direction != ParameterDirection.Input)
- canClear = false;
- }
- if (canClear)
- {
- cmd.Parameters.Clear();
- }
- return dataReader;
- }
- catch
- {
- if (mustCloseConnection)
- connection.Close();
- throw;
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
- {
- return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- SqlConnection connection = null;
- try
- {
- connection = new SqlConnection(connectionString);
- connection.Open();
- return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
- }
- catch
- {
-
- if (connection != null) connection.Close();
- throw;
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
- {
- return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return ExecuteReader(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
- {
- return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
- AssignParameterValues(commandParameters, parameterValues);
- return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion ExecuteReader数据阅读器
- #region ExecuteScalar 返回结果集中的第一行第一列
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
- {
-
- return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
-
- return ExecuteScalar(connection, commandType, commandText, commandParameters);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
- {
-
- return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connection == null) throw new ArgumentNullException("connection");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
-
- object retval = cmd.ExecuteScalar();
-
- cmd.Parameters.Clear();
- if (mustCloseConnection)
- connection.Close();
- return retval;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
- {
-
- return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- object retval = cmd.ExecuteScalar();
-
- cmd.Parameters.Clear();
- return retval;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion ExecuteScalar
- #region ExecuteXmlReader XML阅读器
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
- {
-
- return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- bool mustCloseConnection = false;
-
- SqlCommand cmd = new SqlCommand();
- try
- {
- PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
-
- XmlReader retval = cmd.ExecuteXmlReader();
-
- cmd.Parameters.Clear();
- return retval;
- }
- catch
- {
- if (mustCloseConnection)
- connection.Close();
- throw;
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
- {
-
- return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
-
- SqlCommand cmd = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- XmlReader retval = cmd.ExecuteXmlReader();
-
- cmd.Parameters.Clear();
- return retval;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
-
- return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion ExecuteXmlReader 阅读器结束
- #region FillDataset 填充数据集
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
-
- FillDataset(connection, commandType, commandText, dataSet, tableNames);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(string connectionString, CommandType commandType,
- string commandText, DataSet dataSet, string[] tableNames,
- params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
-
- FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(string connectionString, string spName,
- DataSet dataSet, string[] tableNames,
- params object[] parameterValues)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
-
- FillDataset(connection, spName, dataSet, tableNames, parameterValues);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlConnection connection, CommandType commandType,
- string commandText, DataSet dataSet, string[] tableNames)
- {
- FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlConnection connection, CommandType commandType,
- string commandText, DataSet dataSet, string[] tableNames,
- params SqlParameter[] commandParameters)
- {
- FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlConnection connection, string spName,
- DataSet dataSet, string[] tableNames,
- params object[] parameterValues)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
- }
- else
- {
-
- FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlTransaction transaction, CommandType commandType,
- string commandText,
- DataSet dataSet, string[] tableNames)
- {
- FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlTransaction transaction, CommandType commandType,
- string commandText, DataSet dataSet, string[] tableNames,
- params SqlParameter[] commandParameters)
- {
- FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public static void FillDataset(SqlTransaction transaction, string spName,
- DataSet dataSet, string[] tableNames,
- params object[] parameterValues)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if ((parameterValues != null) && (parameterValues.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, parameterValues);
-
- FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
- }
- else
- {
-
- FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
- string commandText, DataSet dataSet, string[] tableNames,
- params SqlParameter[] commandParameters)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (dataSet == null) throw new ArgumentNullException("dataSet");
-
- SqlCommand command = new SqlCommand();
- bool mustCloseConnection = false;
- PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
-
- using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
- {
-
- if (tableNames != null && tableNames.Length > 0)
- {
- string tableName = "Table";
- for (int index = 0; index < tableNames.Length; index++)
- {
- if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
- dataAdapter.TableMappings.Add(tableName, tableNames[index]);
- tableName += (index + 1).ToString();
- }
- }
-
- dataAdapter.Fill(dataSet);
-
- command.Parameters.Clear();
- }
- if (mustCloseConnection)
- connection.Close();
- }
- #endregion
- #region UpdateDataset 更新数据集
-
-
-
-
-
-
-
-
-
-
-
-
- public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
- {
- if (insertCommand == null) throw new ArgumentNullException("insertCommand");
- if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
- if (updateCommand == null) throw new ArgumentNullException("updateCommand");
- if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
-
- using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
- {
-
- dataAdapter.UpdateCommand = updateCommand;
- dataAdapter.InsertCommand = insertCommand;
- dataAdapter.DeleteCommand = deleteCommand;
-
- dataAdapter.Update(dataSet, tableName);
-
- dataSet.AcceptChanges();
- }
- }
- #endregion
- #region CreateCommand 创建一条SqlCommand命令
-
-
-
-
-
-
-
-
-
-
-
- public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- SqlCommand cmd = new SqlCommand(spName, connection);
- cmd.CommandType = CommandType.StoredProcedure;
-
- if ((sourceColumns != null) && (sourceColumns.Length > 0))
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- for (int index = 0; index < sourceColumns.Length; index++)
- commandParameters[index].SourceColumn = sourceColumns[index];
-
- AttachParameters(cmd, commandParameters);
- }
- return cmd;
- }
- #endregion
- #region ExecuteNonQueryTypedParams 类型化参数(DataRow)
-
-
-
-
-
-
-
- public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion
- #region ExecuteDatasetTypedParams 类型化参数(DataRow)
-
-
-
-
-
-
-
- public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
-
- public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion
- #region ExecuteReaderTypedParams 类型化参数(DataRow)
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion
- #region ExecuteScalarTypedParams 类型化参数(DataRow)
-
-
-
-
-
-
-
- public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion
- #region ExecuteXmlReaderTypedParams 类型化参数(DataRow)
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
- }
- }
-
-
-
-
-
-
-
- public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
- {
- if (transaction == null) throw new ArgumentNullException("transaction");
- if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
-
- if (dataRow != null && dataRow.ItemArray.Length > 0)
- {
-
- SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
-
- AssignParameterValues(commandParameters, dataRow);
- return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
- }
- else
- {
- return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
- }
- }
- #endregion
- }
-
-
-
- public sealed class SqlHelperParameterCache
- {
- #region 私有方法,字段,构造函数
-
- private SqlHelperParameterCache() { }
-
- private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
-
-
-
-
-
-
-
-
- private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- SqlCommand cmd = new SqlCommand(spName, connection);
- cmd.CommandType = CommandType.StoredProcedure;
- connection.Open();
-
- SqlCommandBuilder.DeriveParameters(cmd);
- connection.Close();
-
- if (!includeReturnValueParameter)
- {
- cmd.Parameters.RemoveAt(0);
- }
-
- SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
-
- cmd.Parameters.CopyTo(discoveredParameters, 0);
-
- foreach (SqlParameter discoveredParameter in discoveredParameters)
- {
- discoveredParameter.Value = DBNull.Value;
- }
- return discoveredParameters;
- }
-
-
-
-
-
- private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
- {
- SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
- for (int i = 0, j = originalParameters.Length; i < j; i++)
- {
- clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
- }
- return clonedParameters;
- }
- #endregion 私有方法,字段,构造函数结束
- #region 缓存方法
-
-
-
-
-
-
- public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
- string hashKey = connectionString + ":" + commandText;
- paramCache[hashKey] = commandParameters;
- }
-
-
-
-
-
-
- public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
- string hashKey = connectionString + ":" + commandText;
- SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
- if (cachedParameters == null)
- {
- return null;
- }
- else
- {
- return CloneParameters(cachedParameters);
- }
- }
- #endregion 缓存方法结束
- #region 检索指定的存储过程的参数集
-
-
-
-
-
-
-
-
-
- public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
- {
- return GetSpParameterSet(connectionString, spName, false);
- }
-
-
-
-
-
-
-
-
-
-
- public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
- {
- if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
- }
- }
-
-
-
-
-
-
-
-
-
- internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
- {
- return GetSpParameterSet(connection, spName, false);
- }
-
-
-
-
-
-
-
-
-
-
-
-
- internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
- {
- return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
- }
- }
-
-
-
-
-
-
-
- private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
- {
- if (connection == null) throw new ArgumentNullException("connection");
- if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
- string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
- SqlParameter[] cachedParameters;
- cachedParameters = paramCache[hashKey] as SqlParameter[];
- if (cachedParameters == null)
- {
- SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
- paramCache[hashKey] = spParameters;
- cachedParameters = spParameters;
- }
- return CloneParameters(cachedParameters);
- }
- #endregion 参数集检索结束
- }
- }
|