C#数据访问层主类.(SqlHelper)SQLDataAccess.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;

namespace dal
{
   /// <summary>
   /// 数据访问层 by www.csframework.com 
   /// </summary>
   public class SQLDataAccess
   {
      //Sql Server连接字符串
      private string _ConnectionString = string.Empty;
      
      private static SQLDataAccess _Instance = null;
      
      //数据访问层实例<单件模式>
      public static SQLDataAccess Instance //实例
      {
         get
         {
            if (_Instance == null) _Instance = new SQLDataAccess();
            return _Instance;
         }
      }
      
      private SQLDataAccess()//私有构造器
      {
         _ConnectionString = "server=192.168.0.3;database=mydata;uid=sa;pwd=123;";
      }
      
      public SqlConnection createConnection()//创建一个连接对象
      {
         SqlConnection conn = new SqlConnection(_ConnectionString);
         conn.Open();
         return conn;
      }
      
      //获取一条记录
      public DataRow getDataRow(string sql)
      {
         DataTable dt = getSQLTable(sql, "MyTable");
         if (dt != null && dt.Rows.Count > 0)
         return dt.Rows[0];
         else
         return null;
      }
      
      //获取一条记录
      public DataRow getDataRow(SqlCommand command)
      {
         DataTable dt = getSQLTable(command, "MyTable");
         if (dt != null && dt.Rows.Count > 0)
         return dt.Rows[0];
         else
         return null;
      }
      
      //获取一个数据表
      public DataTable getSQLTable(string selectSQL, string tableName)
      {
         SqlConnection connection = this.createConnection();
         try
         {
            DataTable table = new DataTable(tableName);
            SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, connection);
            adapter.Fill(table);
            this.CloseConnection(connection);
            return table;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //获取一个数据表
      public DataTable getSQLTable(SqlCommand command, string tableName)
      {
         SqlConnection connection = command.Connection;
         try
         {
            if (command.Connection == null) command.Connection = this.createConnection();
            DataTable table = new DataTable(tableName);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(table);
            this.CloseConnection(connection);
            return table;
         }
         catch (System.Exception ex)
         {
            if (connection.State != ConnectionState.Closed) connection.Close();
            throw ex;
         }
      }
      
      //获取一个数据集
      public DataSet getDataset(string selectSQL)
      {
         SqlConnection connection = this.createConnection();
         try
         {
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, connection);
            adapter.Fill(ds);
            this.CloseConnection(connection);
            return ds;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //获取一个数据集
      public DataSet getDataset(SqlCommand command)
      {
         SqlConnection connection = command.Connection;
         try
         {
            if (command.Connection == null) command.Connection = this.createConnection();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            this.CloseConnection(connection);
            return ds;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //执行SQL语句.参数:sql语句,返回整数值为判断所影响的行数
      public int executeSQL(string cmdText)
      {
         int iValue = -1;
         SqlConnection connection = this.createConnection();
         try
         {
            SqlCommand command = new SqlCommand(cmdText, connection);
            iValue = command.ExecuteNonQuery();
            this.CloseConnection(connection);
            return iValue;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //执行SQL语句.参数:sql带有参数的sql语句。如:where userid=@userid
      //返回整数值为判断所影响的行数
      public int executeSQL(string sql, params SqlParameter[] values)
      {
         SqlCommand cmd = new SqlCommand(sql);
         cmd.Parameters.AddRange(values);
         return executeSQL(cmd);
      }
      
      //执行SQL命令
      public int executeSQL(SqlCommand command)
      {
         SqlConnection connection = command.Connection;
         try
         {
            if (command.Connection == null) command.Connection = this.createConnection();
            int iValue = command.ExecuteNonQuery();
            this.CloseConnection(connection);
            return iValue;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //执行SQL命令返回第一行第一列的值
      public object executeScalar(SqlCommand command)
      {
         SqlConnection connection = command.Connection;
         try
         {
            if (command.Connection == null) command.Connection = this.createConnection();
            object ret = command.ExecuteScalar();
            this.CloseConnection(connection);
            return ret;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //执行SQL语句返回第一行第一列的值
      public object executeScalar(string cmdText)
      {
         SqlConnection connection = this.createConnection();
         try
         {
            SqlCommand command = new SqlCommand(cmdText, connection);
            object ret = command.ExecuteScalar();
            this.CloseConnection(connection);
            return ret;
         }
         catch (System.Exception ex)
         {
            this.CloseConnection(connection);
            throw ex;
         }
      }
      
      //关闭SQL连接
      private void CloseConnection(SqlConnection connection)
      {
         if (connection == null) return;
         if (connection.State != ConnectionState.Closed) connection.Close();
      }
      
   }
}

public class Tester
{
   public void TestDAL()
   {
      //获取DataSet
      DataSet user = SQLDataAccess.Instance.getDataset("select * from users");
      
      //执行SQL statement
      int i = SQLDataAccess.Instance.executeSQL("delete users where userid=’csframework'");
      
      //返回一条记录
      DataRow row = SQLDataAccess.Instance.getDataRow("select * from users where userid=’csframework’");      
   }
}