ADO数据访问助手类SQLServeHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace test
{
    /// <summary>
    /// 封装对数据库的操作
    /// </summary>
    public sealed class SQLServerDALHelper
    {
        private static string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        ///<summary>
        ///执行sql语句
        ///</summary>
        ///<param name="sql">sql语句</param>
        public static void ExecuteSQLNonQurey(string sql)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(sql,connection);
            command.ExecuteNonQuery();
            connection.Close();
        }
        ///<summary>
        ///执行sql语句返回的DataReader
        ///</summary>
        ///<param name="sql">sql语句</param>
        ///<returns>datareader</returns>
        public static SqlDataReader ExecteSQlReader( string sql)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(sql,connection);
            SqlDataReader sqlReader = command.ExecuteReader();
            return sqlReader;
        }
        ///<summary>
        ///执行sql语句
        ///</summary>
        ///<param name= "sql">sql语句</param>
        ///<returns>object</returns>
        public static object ExecuteSQLScalar(string sql)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(sql,connection);
            command.CommandType = CommandType.Text;
            return command.ExecuteScalar();
        }
        ///<summary>
        ///执行存储过程
        ///</summary>
        ///<param name = "stroedProcedureName">存储过程名称</param>
        ///<param name = "parameters">参数</param>
        ///<returns>object</returns>
        public static object ExecuteProceDureSaclr(string storedProcedureName,IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(storedProcedureName,connection);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteScalar();
        }



        ///<summary>
        ///执行存储过程
        ///</summary>
        ///<param name="storedProcedureName">存储过程名称</param>
        ///<param name="parameters">无返回值类型</param>
        public static void ExecuteProcedureNonQuery(string storedProcedureName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(storedProcedureName,connection);
            command.CommandType =CommandType.StoredProcedure;
            //command.Connection = connection;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            command.ExecuteNonQuery();
            connection.Close();
        }
        ///<summary>
        ///执行存储过程
        ///</summary>
        ///<param name="storedProcedureName">存储过程名</param>
        ///<param name="parametes">参数</param>
        ///<returns>sqldatareader</returns>
        public static SqlDataReader ExecuteProcedureReader(string storedProcedureName,IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(storedProcedureName,connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameters);
                }
            }
            SqlDataReader sqlReader = command.ExecuteReader();
            return sqlReader;
        }
    }
}

 

数据访问助手将常用的数据库操作方法 ExecuteScalar()、ExecuteNonQuery()和ExecuteReader()分为SQL语句和存储过程两种实现方式。

通过SQL语句实现时，可以使用如下类似的代码：

SQLSERVERHelper.ExecuteSQlNonQuery(sql);//sql为SQL语句

通过存储过程是现实，可以使用如下类似代码：

SQLServerHelper.ExecuteSQLNonQuery(proc,parameters);//proc存储过程名，参数