DBHelper(C#)

C# 中常用的连接数据库（三层架构调用 SQL2005）：

 

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

namespace DAL
{
   public static class DBHepler
    {
       private static SqlConnection connection;
       public static SqlConnection Connection   //数据库连接
       {
           get
           {
               string connectiontostring = ConfigurationSettings.AppSettings["Sqlconnection"].ToString();
               connection = new SqlConnection(connectiontostring);
               if (connection == null)
               {
                   connection.Open();
               }
               else if(connection.State == ConnectionState.Closed)
               {
                   connection.Open();
               }
               else if (connection.State == ConnectionState.Broken)
               {
                   connection.Close();
                   connection.Open();
               }
               return connection;
           }
          
       }

       public static SqlDataReader GetReader(string procsql)//执行一个无参的存储过程，返回一个带有结果集的datareader
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
           return reader;//返回一个SQLdatareader,reader连接没关闭
       }

       public static SqlDataReader GetReader(string procsql, params SqlParameter[] vlause)//执行一个有参的存储过程，返回一个带有结果集的datareader
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddRange(vlause);
           SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
           return reader;
       }

       public static SqlDataReader GetReader(string peocsql, string NO)//执行一个有参的(string类型的),返回一个带有结果集的Datareader
       {
           SqlCommand cmd = new SqlCommand(peocsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(NO);
           SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
           return reader;
       }

       public static int ExecutCommand(string procsql)//执行一个无参的存储过程(删，改)返回影响行数
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           int result = cmd.ExecuteNonQuery();
           connection.Close();
           return result;
       }
       public static int ExecutCommand(string procsql, params SqlParameter[] vlause)//执行一个存储过程（增加）,传进一个sqlparameter数组，返回受影响行数
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddRange(vlause);
           int result = cmd.ExecuteNonQuery();
           connection.Close();
           return result;
       }

       public static int GetScalar(string procsql)//执行一个无参的存储过程，返回首行首列的内容
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           int result = Convert.ToInt32(cmd.ExecuteScalar());
           connection.Close();
           return result;
       }
       public static int GetScalar(string procsql, params SqlParameter[] vlause)//执行一个有参的存储过程，返回首行首列的值
       {
           SqlCommand cmd = new SqlCommand(procsql, Connection);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(vlause);
           int result = Convert.ToInt32(cmd.ExecuteScalar());
           connection.Close();
           return result;
      
       }

       public static DataTable GetDataTable(string procsql)//执行一个无参的存储过程，返回一个table
       {
           SqlDataReader reader = GetReader(procsql);
           DataTable table = new DataTable();
           table.Load(reader);
           reader.Close();
           return table;
       }

       public static DataTable GetDataTable(string procsql, params SqlParameter[] vlause)//执行一个带参的存储过程，返回一个table
       {
           SqlDataReader reader = GetReader(procsql, vlause);
           DataTable table = new DataTable();
           table.Load(reader);
           reader.Close();
           return table;

       }

       public static DataTable GetDataTable(string procsql, string NO)//rv执行一个有参的(string类型的)存储过程,返回一个table
       {
           SqlDataReader reader = GetReader(procsql, NO);
           DataTable table = new DataTable();
           table.Load(reader);
           reader.Close();
           return table;
      
       }
    } 
}