detaildbconn.cs

酒店管理系统

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.SqlClient;
using System.Collections;
using System.Text;
using System.Data.OleDb;

/// <summary>
/// ReservationDBconn 的摘要说明
/// </summary>
public class detailDBconn
{
    //数据库适配器控件．
    private SqlDataAdapter dataAdapter;
   
    //sql connection
    private SqlConnection dbconnection;
    //sql 命令
    private SqlCommand updateComm;
    private SqlCommand insertComm;
    private SqlCommand deleteComm;
    private SqlCommand selectComm;

    private SqlCommand forCommAdapter;
    private SqlCommand procedureCall;

    //用以获取通过查询语句(select)获得的数据集
    private SqlDataAdapter CommandDataAdapter;
  
    //data  reader;
    public SqlDataReader reservationDataReader;

    enum CommandType { none, insert, update, delete, select,dataset, procedureCall};

    CommandType commandtype;

    private string error;
    private bool beopened;


    /// <summary>
    /// 获取错误信息
    /// </summary>
    public string errorMessage
    {
        get
        {
            return error;
        }
    }
    /// <summary>
    /// 数据库是否开
    /// </summary>
    public bool isOpen
    {
        get
        {
            return beopened;
        }
    }

    #region commoand set@get
    /// <summary>
    /// 设定插入命令
    /// </summary>
    public string insertCommand
    {
        get
        {
            return insertComm.CommandText;
        }
        set
        {
            insertComm.CommandText = value;
            commandtype = CommandType.insert;
        }
    }

    public string deleteCommand
    {
        get
        {
            return deleteComm.CommandText;
        }
        set
        {
            deleteComm.CommandText = value;
            commandtype = CommandType.delete;
        }
    }

    /// <summary>
    /// 选择命令
    /// </summary>
    public string selectCommand
    {
        get
        {
            return selectComm.CommandText;
        }
        set
        {
            selectComm.CommandText = value;
            commandtype = CommandType.select;
        }
    }

    public string selectDataSetCommand
    {
        get
        {
            return CommandDataAdapter.SelectCommand.CommandText;
        }
        set
        {
            commandtype = CommandType.dataset;
            CommandDataAdapter.SelectCommand.CommandText = value;
        }
    }
    #endregion

    private SqlDataReader getSelectReader()
    {
        if (reservationDataReader != null)
        {
            if (reservationDataReader.IsClosed == false)
            {
                reservationDataReader.Close();
                reservationDataReader = null;
            }
        }
        reservationDataReader = selectComm.ExecuteReader();
        return reservationDataReader;
    }

    public DataSet getSelectDataSet()
    {
        try
        {
            CommandDataAdapter.SelectCommand.Connection = dbconnection;
            DataSet ds = new DataSet();
            CommandDataAdapter.Fill(ds);
            return ds;
        }
        catch (Exception e)
        {
            error = e.Message;
            return null;
        }
    }
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procedurename">存储过程名</param>
    /// <param name="parmInfo">参数名</param>
    /// <param name="parms">参数值</param>
    /// <param name="retValue">返回值</param>
    /// <returns>查询结果集</returns>
    public DataTable CallStoredProcedure
        (string procedurename,string[] parmInfo,object[] parms,ref int retValue)
    {
        DataSet ds = new DataSet();
        try
        {
            procedureCall.CommandText = procedurename;
            procedureCall.Parameters.Clear();
            for (int i = 0; i < parmInfo.Length && i < parms.Length; ++i)
            {
                procedureCall.Parameters.Add(new SqlParameter(parmInfo[i], parms[i]));
            }

            SqlParameter retv = new SqlParameter("return", SqlDbType.Int);
            retv.Direction = ParameterDirection.ReturnValue;
            procedureCall.Parameters.Add(retv);

          
            dataAdapter.SelectCommand = procedureCall;
            dataAdapter.Fill(ds);

            retValue = (int)(procedureCall.Parameters["return"].Value);
        }
        catch (Exception e)
        {
            error = e.Message;
            retValue = -1;
        }

        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

    /// <summary>
    /// 执行语句
    /// </summary>
    /// <returns></returns>
    public bool executeCommand()
    {
        bool returnvalue=false;
        if (commandtype == CommandType.none)
            return false;
        if (reservationDataReader != null)
        {
            if (reservationDataReader.IsClosed == false)
                reservationDataReader.Close();
            reservationDataReader = null;
        }
        SqlTransaction trans = dbconnection.BeginTransaction();
        if (commandtype == CommandType.select||commandtype==CommandType.procedureCall
            ||commandtype==CommandType.dataset)
        {
            return false;
        }
        int affectline = -1;//受影响行数
        try
        {
            
            //增，删，改　事务．
            switch (commandtype)
            {
                case CommandType.insert: insertComm.Transaction = trans; break;
                case CommandType.delete: deleteComm.Transaction = trans; break;
                case CommandType.update: updateComm.Transaction = trans; break;
            }
            switch (commandtype)
            {
                case CommandType.insert: affectline = insertComm.ExecuteNonQuery(); break;
                case CommandType.delete: affectline = deleteComm.ExecuteNonQuery(); break;
                case CommandType.update: affectline = updateComm.ExecuteNonQuery(); break;
            }
        }
        catch (InvalidOperationException e)
        {
            StringBuilder errortext = new StringBuilder();
            errortext.Append("invalidoperationexception when trying to ");
            switch (commandtype)
            {
                case CommandType.insert: errortext.Append("insert"); break;
                case CommandType.delete: errortext.Append("delete"); break;
                case CommandType.update: errortext.Append("update"); break;
            }
            errortext.Append(",details:" + e.Message);
            error = errortext.ToString();
            return false;
        }
        catch (SqlException e)
        {
            StringBuilder errortext = new StringBuilder();
            errortext.Append("SqlException when trying to ");
            switch (commandtype)
            {
                case CommandType.insert: errortext.Append("insert"); break;
                case CommandType.delete: errortext.Append("delete"); break;
                case CommandType.update: errortext.Append("update"); break;
            }
            errortext.Append(",details:" + e.Message);
            error = errortext.ToString();
            return false;
        }
        finally
        {
            //提交事务
            if (affectline == 1)
            {
                switch (commandtype)
                {
                    case CommandType.insert: insertComm.Transaction.Commit(); break;
                    case CommandType.delete: deleteComm.Transaction.Commit(); break;
                    case CommandType.update: updateComm.Transaction.Commit(); break;
                }
                returnvalue = true;
            }
             //回滚
            else
            {
                switch (commandtype)
                {
                    case CommandType.insert: insertComm.Transaction.Rollback(); break;
                    case CommandType.delete: deleteComm.Transaction.Rollback(); break;
                    case CommandType.update: updateComm.Transaction.Rollback(); break;
                }
                returnvalue = false;
            }
        }
        return returnvalue;
    }
    
	public detailDBconn()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
        intConn();
	}
    /// <summary>
    /// 初始化连接．包括变量初始化等．
    /// </summary>
    public void intConn()
    {
        string connectionString
        = "Data Source=ibm-c47157ea7c4;AttachDbFilename='C:\\Documents and Settings\\Angel Wu\\My Documents\\for study 2006(1)\\软件过程\\HotelManagementSystem\\App_Data\\Hotel1.mdf';Integrated Security=True";
        dbconnection = new SqlConnection(connectionString);
        dataAdapter = new SqlDataAdapter();

        selectComm = new SqlCommand();
        insertComm = new SqlCommand();
        deleteComm = new SqlCommand();
        updateComm = new SqlCommand();
        procedureCall = new SqlCommand();
        procedureCall.CommandType = System.Data.CommandType.StoredProcedure;

        dataAdapter.DeleteCommand = deleteComm;
        dataAdapter.InsertCommand = insertComm;
        dataAdapter.SelectCommand = selectComm;
        dataAdapter.UpdateCommand = updateComm;

        deleteComm.Connection = dbconnection;
        insertComm.Connection = dbconnection;
        selectComm.Connection = dbconnection;
        updateComm.Connection = dbconnection;
        procedureCall.Connection = dbconnection;


        commandtype = CommandType.none;
        reservationDataReader = null;

        forCommAdapter = new SqlCommand();
        CommandDataAdapter = new SqlDataAdapter();
        CommandDataAdapter.SelectCommand = forCommAdapter;
    }
    /// <summary>
    ///  打开数据库连接
    /// </summary>
    public void openDB()
    {
        try
        {
            dbconnection.Open();
            beopened = true;
        }
        catch (Exception e)
        {
            error = e.Message;
        }
    }
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void closeDB()
    {
        if (reservationDataReader != null)
        {
            if (reservationDataReader.IsClosed == false)
            {
                reservationDataReader.Close();
                reservationDataReader = null;
            }
        }
        dbconnection.Close();
        beopened = false;
    }

    public string updateCommand
    {
        get
        {
            return updateComm.CommandText;
        }
        set
        {
            updateComm.CommandText = value;
            commandtype = CommandType.update;
        }
    } 
}