SqlDbHelper

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

/// <summary>
/// 针对 SQL Server 数据库操作的通用类
/// 作者：周金桥
/// </summary>
public class SqlDbHelper
{
    /// <summary>
    /// 设置数据库链接字符串
    /// </summary>
    public string connectionString=ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
   
    /// <summary>
    /// 构造函数
    /// </summary>
    public SqlDbHelper() { }

    public DataTable ExecuteDataTable(string sql, CommandType commandType, params SqlParameter[] parameters)
    {
        DataTable data = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(data);
            }
        }
        return data;
    }

    public DataTable ExecuteDataTable(string sql, CommandType commandType)
    {
        return ExecuteDataTable(sql, commandType, null);
    }

    public DataTable ExecuteDataTable(string sql)
    {
        return ExecuteDataTable(sql, CommandType.Text, null);
    }

    public SqlDataReader ExecuteReader(string sql, CommandType commandType, params SqlParameter[] parameters)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand(sql, connection);
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        connection.Open();
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public SqlDataReader ExecuteReader(string sql, CommandType commandType)
    {
        return ExecuteReader(sql, commandType, null);
    }

    public SqlDataReader ExecuteReader(string sql)
    {
        return ExecuteReader(sql, CommandType.Text, null);
    }

    public object ExecuteScalar(string sql, CommandType commandType, params SqlParameter[] parameters)
    {
        object result = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                connection.Open();
                result = command.ExecuteScalar();
            }
        }
        return result;
    }

    public object ExecuteScalar(string sql, CommandType commandType)
    {
        return ExecuteScalar(sql, commandType, null);
    }

    public object ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, CommandType.Text, null);
    }

    public int ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] parameters)
    {
        int count = 0;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                connection.Open();
                count = command.ExecuteNonQuery();
            }
        }
        return count;
    }

    public int ExecuteNonQuery(string sql, CommandType commandType)
    {
        return ExecuteNonQuery(sql, commandType, null);
    }

    public int ExecuteNonQuery(string sql)
    {
        return ExecuteNonQuery(sql, CommandType.Text, null);
    }

    public DataTable GetTables()
    {
        DataTable data = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            data = connection.GetSchema("Tables");
        }
        return data;
    }
}