-----------------------------------数据访问层----------------------------
//===============================================================================
//数据访问层中最基本的应用程序块(来自微软Microsoft Application Blocks for .NET)
//提供所有有关操作SQL SERVER操作数据库的功能
//乔高峰 2005-04-27
//===============================================================================
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Windows.Forms;
namespace DAL
{
///
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient
///
public sealed class SqlHelper
{
//联接字符串
public static readonly string CONN_STRING = ConfigurationSettings.AppSettings["SQLConnString"] ;
#region private utility methods & constructors
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new SqlHelper()"
private SqlHelper()
{
}
///
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
///
/// The command to which the parameters will be added
/// An array of SqlParameters to be added to command
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 )
{
// Check for derived output value with no value assigned
if ( ( p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input ) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
///
/// This method assigns dataRow column values to an array of SqlParameters
///
/// Array of SqlParameters to be assigned values
/// The dataRow used to hold the stored procedure's parameter values
private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
// Do nothing if we get no data
return;
}
int i = 0;
// Set the parameters values
foreach(SqlParameter commandParameter in commandParameters)
{
// Check the parameter name
if( commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1 )
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName ) );
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///
/// This method assigns an array of values to an array of SqlParameters
///
/// Array of SqlParameters to be assigned values
/// Array of objects holding the values to be assigned
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}
// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
// Iterate through the SqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// 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];
}
}
}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8781179/viewspace-924775/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8781179/viewspace-924775/