?using System;
using System.Data;
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;
using System.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// SqlData 的摘要說明
/// </summary>
public class SqlData
{
    //主要的變量
    public static readonly string ConnectionString =
        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlConnection myConnection = null;
    SqlDataAdapter myAdapter=null;
    DataSet myDateSet = null;
	public SqlData()
	{
		//
		// TODO: 在此處添加構造函數邏輯
		//
        myConnection = new SqlConnection(ConnectionString);
	}
    public void SqlDataClose()
    {
        myConnection.Close();
    }
    //
    //主要功能：判斷在TableName表中的名為ColumnName行字符串Str是否為存在，當存在反回1，不存在返回2，讀數據庫錯誤返回0
    //主要作用：用于只有一個主鍵的判斷
    //
    public int CheckStringUnique(String TableName, String ColumnName, String Str)
    {
        try
        {
            // String conString = "select" + ColumnName + "FROM" + TableName+"where "+ColumnName+"='"+Str+"'";
            //String conString = "SELECT PASSNO FROM TAB_PASSINFO where PASSNO='" + Str + "'";
            String conString = "SELECT " + ColumnName + " FROM " + TableName + " where " + ColumnName + "='" + Str + "'";
            myAdapter = new SqlDataAdapter(conString, myConnection);
            myDateSet = new DataSet();
            myAdapter.Fill(myDateSet, "info");
            myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable Table = myDateSet.Tables[0];
            if (Table.Rows.Count != 0)
            {
                return 1;
            }
            else
            {
                return 2;
            }
        }
        catch (SqlException E)
        {
            return 0;
        }
    }
    //
    //主要功能：判斷在TableName表中的名為ColumnName1與ColumnName2兩行字符串Str1，Str2是否為存在，當存在反回1，不存在返回2，讀數據庫錯誤返回0
    //主要作用：用于兩個主鍵的判斷
    //
    public int CheckStringUnique(String TableName, String ColumnName1, String ColumnName2, String Str1, String Str2)
    {
        try
        {
            // String conString = "select" + ColumnName + "FROM" + TableName+"where "+ColumnName+"='"+Str+"'";
            //String conString = "SELECT PASSNO FROM TAB_PASSINFO where PASSNO='" + Str + "'";
            String conString = "SELECT " + ColumnName1 + "," + ColumnName2 + " FROM " + TableName + " where " + ColumnName1 + "='" + Str1 + "' AND " + ColumnName2 + "='" + Str2 + "'";
            myAdapter = new SqlDataAdapter(conString, myConnection);
            myDateSet = new DataSet();
            myAdapter.Fill(myDateSet, "info");
            myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable Table = myDateSet.Tables[0];
            if (Table.Rows.Count != 0)
            {
                return 1;
            }
            else
            {
                return 2;
            }
        }
        catch (SqlException E)
        {
            return 0;
        }
    }

    //
    //主要功能：判斷在TableName表中的名為ColumnName[]兩行字符串Str[]是否為存在，NumOfColumn表示主鍵的個數，當存在反回1，不存在返回2，讀數據庫錯誤返回0
    //主要作用：用于多個主鍵的判斷
    //
    public int CheckStringUnique(String TableName, String[] ColumnName, String[] Str, int NumOfColumn)
    {
        try
        {
            //String conString = "SELECT " + ColumnName1 + "," + ColumnName2 + " FROM " + TableName + " where " + ColumnName1 + "='" + Str1 + "' AND " + ColumnName2 + "='" + Str2 + "'";
            String conString = "SELECT * FROM " + TableName + " WHERE ";
            for (int i = 0; i < NumOfColumn; i++)
            {
                if (i != NumOfColumn - 1)
                {
                    conString += ColumnName[i] += "='" + Str[i] + "' AND ";
                }
                else
                {
                    conString += ColumnName[i] += "='" + Str[i] + "'";
                }
            }
            myAdapter = new SqlDataAdapter(conString, myConnection);
            myDateSet = new DataSet();
            myAdapter.Fill(myDateSet, "info");
            myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable Table = myDateSet.Tables[0];
            if (Table.Rows.Count != 0)
            {
                return 1;
            }
            else
            {
                return 2;
            }
        }
        catch (SqlException E)
        {
            return 0;
        }
    }
    //
    //主要功能:向一個名為TableName表中插入一行，行的基本字符串集合為InsertStr[],NumOfColumn為表的列數
    //
    public bool InsertRow(String TableName, String[] InsertStr, int NumOfColumn)
    {
        try
        {
            myConnection.Open();
            string InsertString = "INSERT INTO " + TableName + " Values ('";
            for (int i = 0; i < NumOfColumn; i++)
            {
                if (i != NumOfColumn - 1)
                {
                    InsertString += InsertStr[i] + "','";
                }
                else
                {
                    InsertString += InsertStr[i] + "')";
                }
            }
            SqlCommand Command = new SqlCommand(InsertString, myConnection);
            Command.CommandType = CommandType.Text;
            Command.ExecuteNonQuery();
            return true;
        }
        catch (SqlException E)
        {
            return false;
        }
    }
    //
    //主要功能:向一個名為TableName表中插入一行，行的基本字符串集合為InsertStr[],NumOfColumn為表的列數
    //主要注意：當表中的各列屬性有不為String的可以用這個函數
    //
    public bool InsertRow(string TableName, object[] InsertObject, int NumOfColumn)
    {
        try
        {
            string InsertString = "INSERT INTO " + TableName + " Values (";
            for (int i = 0; i < NumOfColumn; i++)
            {
                if (InsertObject[i].GetType().ToString().ToLower() == "system.string")
                {
                    if (i != NumOfColumn - 1)
                    {
                        InsertString += "'" + InsertObject[i] + "',";
                    }
                    else
                    {
                        InsertString += "'" + InsertObject[i] + "'";
                    }
                }
                else
                {
                    if (i != NumOfColumn - 1)
                    {
                        InsertString += InsertObject[i] + ",";
                    }
                    else
                    {
                        InsertString += InsertObject[i];
                    }
                }
            }
            InsertString += ")";
            myConnection.Open();
            SqlCommand Command = new SqlCommand(InsertString, myConnection);
            Command.CommandType = CommandType.Text;
            Command.ExecuteNonQuery();
            return true;
        }
        catch (SqlException E)
        {
            return false;
        }
    }
    //
    //主要功能:更新TableName中列為WhereCloumn=WhereStr，某行多列UpdateCloumn[]的基本字符串集合為UpdateStr[],NumOfColumn為表的列數
    //主要作用;更新一個主鍵為WhereCloumn=WhereStr的整行數據