using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System.Security.Cryptography;
using System.Text;

  /// <summary>
    /// 数据访问抽象基础类  
    /// </summary>
public abstract class DbHelperSQL
{
    public DbHelperSQL()
    {

    }
    
    //定义连接字符串。
    protected static string ConnectionString =ConfigurationManager.ConnectionStrings["connstr"].ToString();
    protected static SqlConnection Connection;
    //定义数据库的打开和关闭方法
    protected static void Open()
    {
        if (Connection == null)
        {
            Connection = new SqlConnection(ConnectionString);
        }
        if (Connection.State.Equals(ConnectionState.Closed))
        {
            Connection.Open();
        }
    }
    protected static void Close()
    {
        if (Connection != null)
        {
            Connection.Close();
        }
    } 
   

    // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
    public static int ExecuteCommand(String SqlString)
    {
        int count = -1;
        Open();
        try
        {
            SqlCommand cmd = new SqlCommand(SqlString, Connection);
            count = cmd.ExecuteNonQuery();
        }
        catch
        {
            count = -1;
        }
        finally
        {
            Close();
        }
        return count;
    }
    // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
    public static int ExecuteCommand(string strSql, params SqlParameter[] values)
    {
        int count = -1;
        Open();
        try
        {
            SqlCommand cmd = new SqlCommand(strSql, Connection);
            cmd.Parameters.AddRange(values);
            count = cmd.ExecuteNonQuery();

        }
        catch
        {
            count = -1;
        }
        finally
        {
            Close();
        }
        return count;



    }

    //绑定到GridView
    public static void BindGridView(string SqlString, GridView MyGvData)
    {
        MyGvData.DataSource = GetDataSet(SqlString);
        MyGvData.DataBind();
    }
    //绑定到DropDownList，设定Text和value显示
    public static void BindDropDownList2(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
    {
        SqlDataReader MyReader = GetDataReader(SqlString);
        while (MyReader.Read())
        {
            ListItem MyItem = new ListItem();
            MyItem.Text = MyReader[TextStr].ToString();
            MyItem.Value = MyReader[ValueStr].ToString();
            MyDDL.Items.Add(MyItem);
        }
        MyReader.Close();
    }
    //绑定到DropDownList，设定Text和value显示
    public static void BindDropDownList(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
    {
        SqlDataReader MyReader = GetDataReader(SqlString);
        MyDDL.Items.Clear();
        while (MyReader.Read())
        {
            ListItem MyItem = new ListItem();
            MyItem.Text = MyReader[TextStr].ToString();
            MyItem.Value = MyReader[ValueStr].ToString();
            MyDDL.Items.Add(MyItem);
        }
        MyReader.Close();
    }
    //绑定到DropDownList，设定Text和value显示
    public static void BindItemList(string SqlString, ListBox MyDDL, string TextStr, string ValueStr)
    {
        SqlDataReader MyReader = GetDataReader(SqlString);
        MyDDL.Items.Clear();
        while (MyReader.Read())
        {
            ListItem MyItem = new ListItem();
            MyItem.Text = MyReader[TextStr].ToString();
            MyItem.Value = MyReader[ValueStr].ToString();
            MyDDL.Items.Add(MyItem);
        }
        MyReader.Close();
    }
    //绑定到DropDownList，设定Text和value显示
    public static void BindDropDownListAddEmpty(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
    {
        SqlDataReader