asp.net用储存过程名为参数的DBHelper类（增删改查）

来源：互联网发布：rainmeter 知乎编辑：程序博客网时间：2024/05/16 08:46

using System;
using System.Collections.Generic;
using System.Text;
using MyHRAdmin.Model;
using System.Data;
using System.Data.SqlClient;
namespace MyHRAdmin.DAL
{
public class DBHelper
{
private static SqlConnection connection;

public static SqlConnection Connection
{

            get
            {
                string con = "Data Source=.;Initial Catalog=HRAdmin;Integrated Security=True";
                if (connection == null)
                {
                    connection = new SqlConnection(con);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        //通过sql，存储参数获取reader，判断是否存在(登录)
      /// <summary>
      /// 重要的
      /// </summary>
      /// <param name="sqName"></param>
      /// <param name="value"></param>
      /// <returns></returns>
      public static SqlDataReader GetReader(string sqName,params SqlParameter[] value)
      {
          SqlCommand com = new SqlCommand(sqName,Connection);
          com.Parameters.AddRange(value);
          com.CommandText = sqName;
          com.CommandType = CommandType.StoredProcedure;
          SqlDataReader reader = com.ExecuteReader();
          return reader;
      }

      //通过sql，存储参数获取表格（查询）
      public static DataTable GetTable(string sqName,params SqlParameter []value)
      {
          SqlCommand com = new SqlCommand(sqName, Connection);
          DataSet ds = new DataSet();
          com.CommandText = sqName;
          com.CommandType = CommandType.StoredProcedure;
          com.Parameters.AddRange(value);
          SqlDataAdapter da = new SqlDataAdapter(com);
          da.Fill(ds);
          return ds.Tables[0];
      }
      public static DataTable GetTable(string sqName)
      {
          SqlCommand com = new SqlCommand(sqName, Connection);
          DataSet ds = new DataSet();
          com.CommandText = sqName;
          com.CommandType = CommandType.StoredProcedure;
          //com.Parameters.AddRange(value);
          SqlDataAdapter da = new SqlDataAdapter(com);
          da.Fill(ds);
          return ds.Tables[0];
      }
      //通过sql，存储参数获取第一行第一列（）
      public static int GetScalar(string sqName, params SqlParameter[] value)
      {
          SqlCommand com = new SqlCommand(sqName, Connection);
          com.CommandText = sqName;
          com.Parameters.AddRange(value);
          com.CommandType = CommandType.StoredProcedure;
          int result = Convert.ToInt32(com.ExecuteScalar());
          return result;

      }
      public static int GetScalar(string sqName,bool Bvalue, params SqlParameter[] value)
      {
          int result=0;
          if (Bvalue)
          {
              SqlCommand com = new SqlCommand(sqName, Connection);
              com.CommandText = sqName;
              com.Parameters.AddRange(value);
              com.CommandType = CommandType.StoredProcedure;
              SqlParameter par = com.Parameters.Add("@IDENTITY", SqlDbType.Int);
              par.Direction = ParameterDirection.ReturnValue;
              com.ExecuteNonQuery();
              result = Convert.ToInt32(par.Value);

          }
      return result;
      }
      //通过sql，存储参数获取受影响的行数（增）
      public static int GetTheChange(string sqName, params SqlParameter[] value)
      {
          SqlCommand com = new SqlCommand(sqName, Connection);
          com.CommandText = sqName;
          com.Parameters.AddRange(value);
          com.CommandType = CommandType.StoredProcedure;
          int result = com.ExecuteNonQuery();
          return result;
      }

}
}