using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// SqlDB 的摘要说明
/// </summary>
public class SqlDB
{

    //连接对象
    SqlConnection conn;

    //构造方法，获取web.config中的连接字符串

    public SqlDB()
        : this(ConfigurationManager.ConnectionStrings["sqldb"].ConnectionString)
    {

    }

    //重写构造方法，实例
    public SqlDB(string connectionString)
    {
        conn = new SqlConnection(connectionString);
    }

    /// <summary>
    /// 用于执行sql语句
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>DataTable</returns>
    public DataTable Search(string sql)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
        try
        {
            sda.Fill(dt);
        }
        catch (SqlException se)
        {
            System.Diagnostics.Debug.WriteLine(se.Message);
        }
        finally
        {
            conn.Dispose();
            sda.Dispose();
            conn.Close();
        }

        return dt;
    }

    /// <summary>
    /// 用于执行sql语句
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>bool</returns>
    public bool Execute(string sql)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);

        try
        {
            if (cmd.ExecuteNonQuery() > 0)
                return true;
        }
        catch (SqlException se)
        {
            System.Diagnostics.Debug.WriteLine(se.Message);
        }
        finally
        {
            conn.Dispose();
            cmd.Dispose();
            conn.Close();
        }
        return false;
    }

    /// <summary>
    /// 无参存储过程执行方法
    /// </summary>
    /// <param name="ProcName">存储过程名</param>
    /// <returns>SqlDataReader数据</returns>
    public DataTable SearchProc(string procName)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        DataTable dt = new DataTable();

        SqlDataAdapter sda = new SqlDataAdapter(procName, conn);

        try
        {
            sda.Fill(dt);
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex.Message);
        }
        finally
        {
            conn.Dispose();
            sda.Dispose();
            conn.Close();
        }
        return dt;
    }

    /// <summary>
    /// 重写带参存储过程
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="procParms">存储过程参数</param>
    /// <returns>SqlDataReader数据</returns>
    public DataTable SearchProc(string procName, SqlParameter[] procParams)
    {

        if (conn.State != ConnectionState.Open)
            conn.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand(procName, conn);
        sda.SelectCommand.CommandType = CommandType.StoredProcedure;

        if (procParams != null)
        {
            foreach (SqlParameter procParam in procParams)
                sda.SelectCommand.Parameters.Add(procParam);

        }

        try
        {
            sda.Fill(dt);
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex.Message);
        }
        finally
        {
            conn.Dispose();
            sda.Dispose();
            conn.Close();
        }
        return dt;
    }

    /// <summary>
    /// 无返回值存储过程执行方法

    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="procParms">存储过程参数数组</param>
    /// <returns>bool</returns>
    public bool ExecuteProc(string procName, SqlParameter[] procParams)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        SqlCommand cmd = new SqlCommand(procName, conn);
        cmd.CommandType = CommandType.StoredProcedure;

        if (procParams != null)
        {
            foreach (SqlParameter procParm in procParams)
                cmd.Parameters.Add(procParm);
        }
        //try
        //{
        int i;
        i = cmd.ExecuteNonQuery();
        if (i > 0)
            return true;
        else
            return false;
    //}
    //catch (Exception ex)
    //{
        //System.Diagnostics.Debug.WriteLine(ex.Message);
    //}
    //finally
    //{
    //    conn.Dispose();
    //    cmd.Dispose();
    //    conn.Close();

    //}
    return false;
    }

}