一个好的Sqlserver底层数据库类

来源：互联网发布：懒惰知乎编辑：程序博客网时间：2024/04/29 08:29

using System;using System.Data;using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; /// <summary> /// Database 的摘要说明 /// </summary> public sealed class Data {

public static string connectionString; static Data() { connectionString = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString; }

#region ExecuteNonQuery

/// <summary> /// execute the sql and return the count of line /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql) { int count = -1; if (String.IsNullOrEmpty(sql)) return count; return ExecuteNonQuery(sql, (SqlParameter[])null); }

/// <summary> /// 执行sql语句 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql, SqlParameter[] parameters) { CommandType commandType = CommandType.Text; return ExecuteNonQuery(commandType, sql, parameters); }

/// <summary> /// /// </summary> /// <param name="commandType"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] parameters) { int count = -1; if (string.IsNullOrEmpty(commandText) && parameters == null) return count; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) {

try { PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, parameters); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return count;

} catch { connection.Close(); count = -1; throw; }

} } }

#endregion

#region ExecuteScalar /// <summary> /// 执行sql语句 /// </summary> /// <param name="commandText">sql</param> /// <returns></returns> public static object ExecuteScalar(string commandText) { CommandType commandType = CommandType.Text; object ec = ExecuteScalar(commandType, commandText); return ec; }

/// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteScalar(commandType, commandText, (SqlParameter[])null); }

/// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection connection = new SqlConnection(connectionString)) {

// Create a command and prepare it for execution using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

// Execute the command & return the results object retval = cmd.ExecuteScalar();

// Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } catch { connection.Close(); throw; } } } }

#endregion

#region ExecuteDataSet

public static DataSet ExecuteDataSet(string sql) { if (String.IsNullOrEmpty(sql)) return null; return ExecuteDataSet(sql, (SqlParameter[])null); }

/// <summary> /// /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static DataSet ExecuteDataSet(string sql, params SqlParameter[] parameters) { if (string.IsNullOrEmpty(sql) && parameters == null) return null; return ExecuteDataSet(CommandType.Text, sql, parameters); }

/// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset( CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { // Create a command and prepare it for execution using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

// Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc da.Fill(ds);

// Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear();

// Return the dataset return ds; } } catch { connection.Close(); throw; } } } }

#endregion

#region RunProc

/// <summary> /// 执行一个没有参数的,只返回影响行数的存储过程。(注意,不能接收return value) /// </summary> /// <param name="procName"></param> /// <returns></returns> public static int RunProc(string procName) { return ExecuteNonQuery(CommandType.StoredProcedure, procName, (SqlParameter[])null); }

/// <summary> /// 执行一个带参数的,只返回影响行数的存储过程。(注意,不能接收return value) /// </summary> /// <param name="procName"></param> /// <param name="prams"></param> /// <returns></returns> public static int RunProc(string procName, SqlParameter[] prams) { return ExecuteNonQuery(CommandType.StoredProcedure, procName, prams); }

/* /// <summary> /// 执行一个不带参数的,返回SqlDataReader的存储过程。 /// </summary> /// <param name="procName"></param> /// <returns></returns> public static void RunProc(string procName, out SqlDataReader reader) { reader = ExecuteReader( CommandType.StoredProcedure, procName, (SqlParameter[])null); }

/// <summary> /// 执行一个带参数的,返回SqlDataReader的存储过程。 /// </summary> /// <param name="procName"></param> /// <param name="prams"></param> /// <returns></returns> public static void RunProc(string procName, SqlParameter[] prams, out SqlDataReader reader) { reader = ExecuteReader( CommandType.StoredProcedure, procName, prams); } */

/// <summary> /// 执行一个不带参数的,返回DataSet的存储过程。 /// </summary> /// <param name="procName"></param> /// <returns></returns> public static void RunProc(string procName, out DataSet ds) { ds = ExecuteDataSet(CommandType.StoredProcedure, procName, (SqlParameter[])null); }

/// <summary> /// 执行一个带参数的,返回DataSet的存储过程。 /// </summary> /// <param name="procName"></param> /// <param name="prams"></param> /// <returns></returns> public static void RunProc(string procName, SqlParameter[] prams, out DataSet ds) { ds = ExecuteDataSet(CommandType.StoredProcedure, procName, prams); }

/// <summary> /// 执行一个不带参数的,返回结果集第一行第一列的数据。为object型,通过类型强制转换得到。 /// 例如 System.Datetime dt = (System.Datetime)RunProc("GetDate"); /// </summary> /// <param name="procName">存储过程名称</param> /// <returns></returns> public static void RunProc(string procName, out object obj) { obj = ExecuteScalar(CommandType.StoredProcedure, procName, (SqlParameter[])null); }

/// <summary> /// 执行一个带参数的,返回结果集第一行第一列的数据。为object型,通过类型强制转换得到。 /// 例如 System.Datetime dt = (System.Datetime)RunProc("GetDate"); /// </summary> /// <param name="procName"></param> /// <param name="prams"></param> /// <returns></returns> public static void RunProc(string procName, SqlParameter[] prams, out object obj) { obj = ExecuteScalar(CommandType.StoredProcedure, procName, prams); }

/* /// <summary> /// 执行一个不带参数的,返回xmlReader结果集的存储过程。 /// </summary> /// <param name="procName"></param> /// <returns></returns> public static void RunProc(string procName, out XmlReader xmlReader) { xmlReader = ExecuteXmlReader(CommandType.StoredProcedure, procName, (SqlParameter[])null); }

/// <summary> /// 执行一个带参数的,返回xmlReader结果集的存储过程。 /// </summary> /// <param name="procName"></param> /// <param name="prams"></param> /// <returns></returns> public static void RunProc(string procName, SqlParameter[] prams, out XmlReader xmlReader) { xmlReader = ExecuteXmlReader( CommandType.StoredProcedure, procName, prams); } * */

#endregion

#region PrepareCommand

/// <summary> /// Prepare a command for execution /// </summary> /// <param name="cmd">SqlCommand object</param> /// <param name="conn">SqlConnection object</param> /// <param name="trans">SqlTransaction object</param> /// <param name="cmdType">Cmd type e.g. stored procedure or text</param> /// <param name="cmdText">Command text, e.g. Select * from Products</param> /// <param name="cmdParms">SqlParameters to use in the command</param>

public static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { 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) { connection.Open(); }

// Associate the connection with the command command.Connection = connection;

// Set the command text (stored procedure name or SQL statement) command.CommandText = commandText;

// If we were provided a transaction, assign it 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; }

// Set the command type command.CommandType = commandType;

// Attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }

/// <summary> /// 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. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">An array of SqlParameters to be added to command</param> public 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); } } } }

#endregion

#region Make SqlParameters /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); }

/// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); }

/// <summary> /// Make stored procedure param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Direction">Parm direction.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param;

if (Size > 0) param = new SqlParameter(ParamName, DbType, Size); else param = new SqlParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } #endregion }