sqldata.cs.svn-base

大学英语学习系统

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的整行数据