using System;
|
using System.Data;
|
using System.Xml;
|
using System.Data.SqlClient;
|
using System.Collections;
|
|
namespace BatchService.Framework.Utility
|
{
|
/// <summary>
|
/// SqlServerÊý¾Ý·ÃÎÊ°ïÖúÀà
|
/// </summary>
|
public sealed class SqlHelper
|
{
|
private static string g_connstring = "";
|
|
#region ˽Óй¹Ô캯ÊýºÍ·½·¨
|
|
private SqlHelper() { }
|
|
/// <summary>
|
/// ½«SqlParameter²ÎÊýÊý×é(²ÎÊýÖµ)·ÖÅä¸øSqlCommandÃüÁî.
|
/// Õâ¸ö·½·¨½«¸øÈκÎÒ»¸ö²ÎÊý·ÖÅäDBNull.Value;
|
/// ¸Ã²Ù×÷½«×èֹĬÈÏÖµµÄʹÓÃ.
|
/// </summary>
|
/// <param name="command">ÃüÁîÃû</param>
|
/// <param name="commandParameters">SqlParametersÊý×é</param>
|
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)
|
{
|
// ¼ì²éδ·ÖÅäÖµµÄÊä³ö²ÎÊý,½«Æä·ÖÅäÒÔDBNull.Value.
|
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
|
(p.Value == null))
|
{
|
p.Value = DBNull.Value;
|
}
|
command.Parameters.Add(p);
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// ½«DataRowÀàÐ͵ÄÁÐÖµ·ÖÅäµ½SqlParameter²ÎÊýÊý×é.
|
/// </summary>
|
/// <param name="commandParameters">Òª·ÖÅäÖµµÄSqlParameter²ÎÊýÊý×é</param>
|
/// <param name="dataRow">½«Òª·ÖÅä¸ø´æ´¢¹ý³Ì²ÎÊýµÄDataRow</param>
|
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));
|
// ´ÓdataRowµÄ±íÖлñȡΪ²ÎÊýÊý×éÖÐÊý×éÃû³ÆµÄÁеÄË÷Òý.
|
// Èç¹û´æÔںͲÎÊýÃû³ÆÏàͬµÄÁÐ,Ôò½«ÁÐÖµ¸³¸øµ±Ç°Ãû³ÆµÄ²ÎÊý.
|
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
|
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
|
i++;
|
}
|
}
|
|
/// <summary>
|
/// ½«Ò»¸ö¶ÔÏóÊý×é·ÖÅä¸øSqlParameter²ÎÊýÊý×é.
|
/// </summary>
|
/// <param name="commandParameters">Òª·ÖÅäÖµµÄSqlParameter²ÎÊýÊý×é</param>
|
/// <param name="parameterValues">½«Òª·ÖÅä¸ø´æ´¢¹ý³Ì²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
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 the current array value derives from IDbDataParameter, then assign its Value property
|
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];
|
}
|
}
|
}
|
/// <summary>
|
/// Ô¤´¦ÀíÓû§ÌṩµÄÃüÁî,Êý¾Ý¿âÁ¬½Ó/ÊÂÎñ/ÃüÁîÀàÐÍ/²ÎÊý
|
/// </summary>
|
/// <param name="command">Òª´¦ÀíµÄSqlCommand</param>
|
/// <param name="connection">Êý¾Ý¿âÁ¬½Ó</param>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÊÂÎñ»òÕßÊÇnullÖµ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»ò¶¼T-SQLÃüÁîÎı¾</param>
|
/// <param name="commandParameters">ºÍÃüÁîÏà¹ØÁªµÄSqlParameter²ÎÊýÊý×é,Èç¹ûûÓвÎÊýΪ'null'</param>
|
/// <param name="mustCloseConnection"><c>true</c> Èç¹ûÁ¬½ÓÊÇ´ò¿ªµÄ,ÔòΪtrue,ÆäËüÇé¿öÏÂΪfalse.</param>
|
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 the provided connection is not open, we will open it
|
if (connection.State != ConnectionState.Open)
|
{
|
mustCloseConnection = true;
|
connection.Open();
|
}
|
else
|
{
|
mustCloseConnection = false;
|
}
|
|
// ¸øÃüÁî·ÖÅäÒ»¸öÊý¾Ý¿âÁ¬½Ó.
|
command.Connection = connection;
|
|
// ÉèÖÃÃüÁîÎı¾(´æ´¢¹ý³ÌÃû»òSQLÓï¾ä)
|
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 Êý¾Ý¿âÁ¬½Ó
|
/// <summary>
|
/// Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®
|
/// </summary>
|
/// <returns></returns>
|
public static string GetConnSting()
|
{
|
return g_connstring;
|
}
|
/// <summary>
|
/// Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó
|
/// </summary>
|
/// <returns></returns>
|
public static SqlConnection GetConnection()
|
{
|
SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
|
return Connection;
|
}
|
|
private static SqlConnection g_conn = null;
|
public static SqlConnection GetConnection(string i_connstring)
|
{
|
if (g_conn != null && g_conn.State == ConnectionState.Open)
|
{
|
g_conn.Close();
|
}
|
g_conn = new SqlConnection(i_connstring);
|
g_conn.Open();
|
return g_conn;
|
}
|
|
public static void SetConnection(string i_connstring)
|
{
|
g_connstring = i_connstring;
|
}
|
|
public static bool TestConn(string i_connstring)
|
{
|
bool t_is_ok = false;
|
try
|
{
|
int t_test = (int)SqlHelper.ExecuteScalar(
|
i_connstring,
|
CommandType.Text,
|
"select count(*) from bc_item");
|
t_is_ok = true;
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error("SQL", ex.ToString());
|
t_is_ok = false;
|
}
|
return t_is_ok;
|
}
|
#endregion
|
|
#region ExecuteNonQueryÃüÁî
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®,ÀàÐ͵ÄSqlCommand.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òSQLÓï¾ä</param>
|
/// <returns>·µ»ØÃüÁîÓ°ÏìµÄÐÐÊý</returns>
|
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
|
{
|
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®,ÀàÐ͵ÄSqlCommand.Èç¹ûûÓÐÌṩ²ÎÊý,²»·µ»Ø½á¹û.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òSQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParameter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÃüÁîÓ°ÏìµÄÐÐÊý</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,½«¶ÔÏóÊý×éµÄÖµ¸³¸ø´æ´¢¹ý³Ì²ÎÊý,
|
/// ´Ë·½·¨ÐèÒªÔÚ²ÎÊý»º´æ·½·¨ÖÐ̽Ë÷²ÎÊý²¢Éú³É²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// Õâ¸ö·½·¨Ã»ÓÐÌṩ·ÃÎÊÊä³ö²ÎÊýºÍ·µ»ØÖµ.
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®/param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅäµ½´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÊÜÓ°ÏìµÄÐÐÊý</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
|
{
|
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param>
|
/// <param name="commandText">T´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
|
{
|
if (connection == null) throw new ArgumentNullException("connection");
|
|
LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
|
connection.ConnectionString, commandType.ToString(), commandText));
|
// ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
SqlCommand cmd = new SqlCommand();
|
bool mustCloseConnection = false;
|
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
|
|
// Finally, execute the command
|
int retval = cmd.ExecuteNonQuery();
|
|
LogHelper.Debug("SQL", string.Format("Ö´Ðнá¹û:{0}", retval));
|
|
// Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ.
|
cmd.Parameters.Clear();
|
if (mustCloseConnection)
|
connection.Close();
|
return retval;
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,½«¶ÔÏóÊý×éµÄÖµ¸³¸ø´æ´¢¹ý³Ì²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐдøÊÂÎñµÄSqlCommand.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý.:
|
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý/returns>
|
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
|
{
|
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐдøÊÂÎñµÄSqlCommand(Ö¸¶¨²ÎÊý).
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
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;
|
}
|
|
/// <summary>
|
/// Ö´ÐдøÊÂÎñµÄSqlCommand(Ö¸¶¨²ÎÊýÖµ).
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ
|
/// ʾÀý:
|
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÊÜÓ°ÏìµÄÐÐÊý</returns>
|
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·½·¨
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
|
{
|
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamters²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
|
{
|
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
|
|
LogHelper.Debug("SQL", "Á¬½Ó×Ö·û´®:" + connectionString + "\r\n²éѯSQL:" + commandText);
|
// ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó.
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
connection.Open();
|
|
// µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨.
|
return ExecuteDataset(connection, commandType, commandText, commandParameters);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö±½ÓÌṩ²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ.
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
|
{
|
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
|
}
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊý,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
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);
|
|
// ´´½¨SqlDataAdapterºÍDataSet.
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
{
|
DataSet ds = new DataSet();
|
|
// Ìî³äDataSet.
|
da.Fill(ds);
|
LogHelper.Debug("SQL", "²é³ö¼Ç¼Êý:" + ds.Tables[0].Rows.Count);
|
cmd.Parameters.Clear();
|
|
if (mustCloseConnection)
|
connection.Close();
|
|
return ds;
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊäÈë²ÎÊýºÍ·µ»ØÖµ.
|
/// ʾÀý.:
|
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="transaction">ÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
|
{
|
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">ÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
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");
|
|
LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
|
transaction.Connection.ConnectionString, commandType.ToString(), commandText));
|
// Ô¤´¦Àí
|
SqlCommand cmd = new SqlCommand();
|
bool mustCloseConnection = false;
|
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
|
|
// ´´½¨ DataAdapter & DataSet
|
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
|
{
|
DataSet ds = new DataSet();
|
da.Fill(ds);
|
cmd.Parameters.Clear();
|
return ds;
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊäÈë²ÎÊýºÍ·µ»ØÖµ.
|
/// ʾÀý.:
|
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="transaction">ÊÂÎñ</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns>
|
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 Êý¾ÝÔĶÁÆ÷
|
|
/// <summary>
|
/// ö¾Ù,±êʶÊý¾Ý¿âÁ¬½ÓÊÇÓÉSqlHelperÌṩ»¹ÊÇÓɵ÷ÓÃÕßÌṩ
|
/// </summary>
|
private enum SqlConnectionOwnership
|
{
|
/// <summary>ÓÉSqlHelperÌṩÁ¬½Ó</summary>
|
Internal,
|
/// <summary>Óɵ÷ÓÃÕßÌṩÁ¬½Ó</summary>
|
External
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷.
|
/// </summary>
|
/// <remarks>
|
/// Èç¹ûÊÇSqlHelper´ò¿ªÁ¬½Ó,µ±Á¬½Ó¹Ø±ÕDataReaderÒ²½«¹Ø±Õ.
|
/// Èç¹ûÊǵ÷Óö¼´ò¿ªÁ¬½Ó,DataReaderÓɵ÷Óö¼¹ÜÀí.
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÊÂÎñ,»òÕßΪ 'null'</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParameters²ÎÊýÊý×é,Èç¹ûûÓвÎÊýÔòΪ'null'</param>
|
/// <param name="connectionOwnership">±êʶÊý¾Ý¿âÁ¬½Ó¶ÔÏóÊÇÓɵ÷ÓÃÕßÌṩ»¹ÊÇÓÉSqlHelperÌṩ</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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);
|
}
|
|
// Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ..
|
// HACK: There is a problem here, the output parameter values are fletched
|
// when the reader is closed, so if the parameters are detached from the command
|
// then the SqlReader can´t set its values.
|
// When this happen, the parameters can´t be used again in other command.
|
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;
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
|
{
|
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é(new SqlParameter("@prodid", 24))</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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 we fail to return the SqlDatReader, we need to close the connection ourselves
|
if (connection != null) connection.Close();
|
throw;
|
}
|
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
|
{
|
return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
|
{
|
return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
|
}
|
|
/// <summary>
|
/// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">T´æ´¢¹ý³ÌÃû</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
|
{
|
return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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);
|
}
|
|
/// <summary>
|
/// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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 ·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
|
{
|
// Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨
|
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
|
{
|
// Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨
|
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
|
{
|
if (connection == null) throw new ArgumentNullException("connection");
|
|
LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
|
connection.ConnectionString, commandType.ToString(), commandText));
|
// ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
SqlCommand cmd = new SqlCommand();
|
|
bool mustCloseConnection = false;
|
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
|
|
// Ö´ÐÐSqlCommandÃüÁî,²¢·µ»Ø½á¹û.
|
object retval = cmd.ExecuteScalar();
|
|
// Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ.
|
cmd.Parameters.Clear();
|
|
if (mustCloseConnection)
|
connection.Close();
|
|
return retval;
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
|
{
|
// Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨
|
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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ÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
SqlCommand cmd = new SqlCommand();
|
bool mustCloseConnection = false;
|
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
|
|
// Ö´ÐÐSqlCommandÃüÁî,²¢·µ»Ø½á¹û.
|
object retval = cmd.ExecuteScalar();
|
|
// Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ.
|
cmd.Parameters.Clear();
|
return retval;
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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))
|
{
|
// PPull the parameters for this stored procedure from the parameter cache ()
|
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ÔĶÁÆ÷
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
|
{
|
// Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨
|
return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
|
{
|
if (connection == null) throw new ArgumentNullException("connection");
|
|
bool mustCloseConnection = false;
|
// ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
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;
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ using "FOR XML AUTO"</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
|
{
|
// Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨
|
return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
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ÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
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;
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns>
|
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 Ìî³äÊý¾Ý¼¯
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)</param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯.Ö¸¶¨ÃüÁî²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
public static void FillDataset(SqlConnection connection, CommandType commandType,
|
string commandText, DataSet dataSet, string[] tableNames)
|
{
|
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
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);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
|
string commandText,
|
DataSet dataSet, string[] tableNames)
|
{
|
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
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);
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ.
|
/// </summary>
|
/// <remarks>
|
/// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý.
|
///
|
/// ʾÀý:
|
/// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
|
/// </remarks>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// [˽Óз½·¨][ÄÚ²¿µ÷ÓÃ]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏó/ÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,DataSet/TableNames/SqlParameters.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param>
|
/// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param>
|
/// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param>
|
/// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é
|
/// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)
|
/// </param>
|
/// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param>
|
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ÃüÁî,²¢½øÐÐÔ¤´¦Àí
|
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 ¸üÐÂÊý¾Ý¼¯
|
/// <summary>
|
/// Ö´ÐÐÊý¾Ý¼¯¸üе½Êý¾Ý¿â,Ö¸¶¨inserted, updated, or deletedÃüÁî.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
|
/// </remarks>
|
/// <param name="insertCommand">[×·¼Ó¼Ç¼]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param>
|
/// <param name="deleteCommand">[ɾ³ý¼Ç¼]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param>
|
/// <param name="updateCommand">[¸üмǼ]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param>
|
/// <param name="dataSet">Òª¸üе½Êý¾Ý¿âµÄDataSet</param>
|
/// <param name="tableName">Òª¸üе½Êý¾Ý¿âµÄDataTable</param>
|
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");
|
|
// ´´½¨SqlDataAdapter,µ±²Ù×÷Íê³ÉºóÊÍ·Å.
|
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
|
{
|
// ÉèÖÃÊý¾ÝÊÊÅäÆ÷ÃüÁî
|
dataAdapter.UpdateCommand = updateCommand;
|
dataAdapter.InsertCommand = insertCommand;
|
dataAdapter.DeleteCommand = deleteCommand;
|
|
// ¸üÐÂÊý¾Ý¼¯¸Ä±äµ½Êý¾Ý¿â
|
dataAdapter.Update(dataSet, tableName);
|
|
// Ìá½»ËùÓиı䵽Êý¾Ý¼¯.
|
dataSet.AcceptChanges();
|
}
|
}
|
#endregion
|
|
#region CreateCommand ´´½¨Ò»ÌõSqlCommandÃüÁî
|
/// <summary>
|
/// ´´½¨SqlCommandÃüÁî,Ö¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏó,´æ´¢¹ý³ÌÃûºÍ²ÎÊý.
|
/// </summary>
|
/// <remarks>
|
/// ʾÀý:
|
/// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="sourceColumns">Ô´±íµÄÁÐÃû³ÆÊý×é</param>
|
/// <returns>·µ»ØSqlCommandÃüÁî</returns>
|
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);
|
|
// ½«Ô´±íµÄÁе½Ó³Éäµ½DataSetÃüÁîÖÐ.
|
for (int index = 0; index < sourceColumns.Length; index++)
|
commandParameters[index].SourceColumn = sourceColumns[index];
|
|
// Attach the discovered parameters to the SqlCommand object
|
AttachParameters(cmd, commandParameters);
|
}
|
|
return cmd;
|
}
|
#endregion
|
|
#region ExecuteNonQueryTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎïµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý.
|
/// </summary>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns>
|
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");
|
|
// Sf the row has values, the store procedure parameters must be initialized
|
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)
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns>
|
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");
|
|
//Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns>
|
///
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet.
|
/// </summary>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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)
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎïµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader.
|
/// </summary>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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)
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ.
|
/// </summary>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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)
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØXmlReaderÀàÐ͵Ľá¹û¼¯.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØXmlReaderÀàÐ͵Ľá¹û¼¯.
|
/// </summary>
|
/// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param>
|
/// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns>
|
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");
|
|
// Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯.
|
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
|
|
}
|
|
/// <summary>
|
/// SqlHelperParameterCacheÌṩ»º´æ´æ´¢¹ý³Ì²ÎÊý,²¢Äܹ»ÔÚÔËÐÐʱ´Ó´æ´¢¹ý³ÌÖÐ̽Ë÷²ÎÊý.
|
/// </summary>
|
public sealed class SqlHelperParameterCache
|
{
|
#region ˽Óз½·¨,×Ö¶Î,¹¹Ô캯Êý
|
// ˽Óй¹Ô캯Êý,·ÁÖ¹À౻ʵÀý»¯.
|
private SqlHelperParameterCache() { }
|
|
// Õâ¸ö·½·¨Òª×¢Òâ
|
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
|
|
/// <summary>
|
/// ̽Ë÷ÔËÐÐʱµÄ´æ´¢¹ý³Ì,·µ»ØSqlParameter²ÎÊýÊý×é.
|
/// ³õʼ»¯²ÎÊýֵΪ DBNull.Value.
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param>
|
/// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
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();
|
// ¼ìË÷cmdÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊýÐÅÏ¢,²¢Ìî³äµ½cmdµÄParameters²ÎÊý¼¯ÖÐ.
|
SqlCommandBuilder.DeriveParameters(cmd);
|
connection.Close();
|
// Èç¹û²»°üº¬·µ»ØÖµ²ÎÊý,½«²ÎÊý¼¯ÖеÄÿһ¸ö²ÎÊýɾ³ý.
|
if (!includeReturnValueParameter)
|
{
|
cmd.Parameters.RemoveAt(0);
|
}
|
|
// ´´½¨²ÎÊýÊý×é
|
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
|
// ½«cmdµÄParameters²ÎÊý¼¯¸´ÖƵ½discoveredParametersÊý×é.
|
cmd.Parameters.CopyTo(discoveredParameters, 0);
|
|
// ³õʼ»¯²ÎÊýֵΪ DBNull.Value.
|
foreach (SqlParameter discoveredParameter in discoveredParameters)
|
{
|
discoveredParameter.Value = DBNull.Value;
|
}
|
return discoveredParameters;
|
}
|
|
/// <summary>
|
/// SqlParameter²ÎÊýÊý×éµÄÉî²ã¿½±´.
|
/// </summary>
|
/// <param name="originalParameters">Ôʼ²ÎÊýÊý×é</param>
|
/// <returns>·µ»ØÒ»¸öͬÑùµÄ²ÎÊýÊý×é</returns>
|
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 »º´æ·½·¨
|
|
/// <summary>
|
/// ×·¼Ó²ÎÊýÊý×éµ½»º´æ.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òSQLÓï¾ä</param>
|
/// <param name="commandParameters">Òª»º´æµÄ²ÎÊýÊý×é</param>
|
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;
|
}
|
|
/// <summary>
|
/// ´Ó»º´æÖлñÈ¡²ÎÊýÊý×é.
|
/// </summary>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param>
|
/// <param name="commandText">´æ´¢¹ý³ÌÃû»òSQLÓï¾ä</param>
|
/// <returns>²ÎÊýÊý×é</returns>
|
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 ¼ìË÷Ö¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯
|
|
/// <summary>
|
/// ·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯
|
/// </summary>
|
/// <remarks>
|
/// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ.
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
|
{
|
return GetSpParameterSet(connectionString, spName, false);
|
}
|
|
/// <summary>
|
/// ·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯
|
/// </summary>
|
/// <remarks>
|
/// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ.
|
/// </remarks>
|
/// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û.</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// [ÄÚ²¿]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó).
|
/// </summary>
|
/// <remarks>
|
/// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ.
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
|
{
|
return GetSpParameterSet(connection, spName, false);
|
}
|
|
/// <summary>
|
/// [ÄÚ²¿]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó)
|
/// </summary>
|
/// <remarks>
|
/// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ.
|
/// </remarks>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="includeReturnValueParameter">
|
/// ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý
|
/// </param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// [˽ÓÐ]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó)
|
/// </summary>
|
/// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param>
|
/// <param name="spName">´æ´¢¹ý³ÌÃû</param>
|
/// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param>
|
/// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns>
|
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 ²ÎÊý¼¯¼ìË÷½áÊø
|
|
}
|
}
|
|
//Èç¹ûÒª»ñÈ¡Á¬½ÓÊý¾ÝÁ¬½Ó¶ÔÏó»ò×Ö·û´®µÄ»°£¬ÏÈÒªÐÞ¸ÄSQLHelperÀàÖÐGetConnSting() ·½·¨ÖеÄConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;²ÅÄܵ÷ÓÃ
|
