C# IDBManager.ExecuteDataSet方法代码示例

        public DataSet FetchDataSet(string criteria)
        {
            DataSet ds = new DataSet();
            string qryString = "SELECT * FROM viewFALIST ORDER BY FA";

            _user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
            _dbmgr = new DBManager(_user.plantDBStr);
            _dbmgr.ConnectionString = _user.plantDBStr;

            try
            {
                _dbmgr.Open();
                ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbmgr.Dispose();
            }
            return ds;
        }

        public DataSet FetchDataSet(string criteria)
        {
            _user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];

            DataSet ds = new DataSet();
            string qryString;

            switch (_user.plant)
            {
                case "BLN":
                case "TFAC":
                    qryString = "SELECT * FROM viewROUTELIST_FR ORDER BY NODE";
                    break;
                default:
                    qryString = "SELECT * FROM viewROUTELIST ORDER BY NODE";
                    break;
            }

            _user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
            _dbmgr = new DBManager(_user.plantDBStr);
            _dbmgr.ConnectionString = _user.plantDBStr;

            try
            {
                _dbmgr.Open();
                ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbmgr.Dispose();
            }
            return ds;
        }

    protected void btnlogin_Click(object sender, EventArgs e)
    {
        int iRecordsFetched = 0;
        int iTempGroupID = 0;
        m_db = new DBManager(DataProvider.Oracle);

        if (m_db == null) {
            Response.Redirect("Error_page.aspx?ERROR=UNABLE_CREATE_DBOBJ");
        }

        ///''''''''''''If user clicked on login button check for username,password and selecttype are filled or not if they are not empty then proceed

        if (txtuser.Value.Length != 0 & txtpwd.Value.Length != 0) {
            m_db.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            m_db.Open();

            if (m_db.Connection == null) {
                ///''''''' If Failed to Eastablish database Connection Show Error Page to the User

                //  strstacktrace = HttpUtility.UrlEncode(m_db.Exception_str)
                strstacktrace = "";
                Response.Redirect("Error_page.aspx?ERROR=DATABASE_CONNECTION_FAILED&MESSAGE=" + strstacktrace);

            }

            ///''''''''''' Form the query to get the login userdetails from tbl_usermaster

            //     strQry = "select tbl_usermaster.userid,tbl_usermaster.usertypeflag,tbl_usermaster.webaccountflag,usersmemberslink.memberid,upper(username) as username,grp.groupid,grp.groupname from tbl_usermaster inner join  groupe as grp on tbl_usermaster.groupid=grp.groupid inner join usersmemberslink on tbl_usermaster.userid=usersmemberslink.userid where username='" & txtuser.Value.Trim() & "' and password='" & txtpwd.Value.Trim() & "'   and tbl_usermaster.activated=1 and tbl_usermaster.webaccountflag=1"
            //strQry = "select usermaster.userid,usermaster.usertypeflag,usermaster.webaccountflag,upper(username) as username,grp.groupid,grp.groupname from usermaster inner join  groupe as grp on usermaster.groupid=grp.groupid where username='" + txtuser.Value.Trim() + "' and password='" + txtpwd.Value.Trim() + "' and usermaster.activated=1 and usermaster.webaccountflag=1";
            //strQry = "select usermaster.userid,upper(username) as username from usermaster ";// where username='" + txtuser.Value.Trim() + "' and password='" + txtpwd.Value.Trim() + "'";
            strQry = string.Format("select users.userid,upper(username) as username,groupid from users where username='{0}' and userpassword='{1}'", txtuser.Value, txtpwd.Value);
            objDataset = m_db.ExecuteDataSet(CommandType.Text, strQry);

            if (objDataset == null) {
                m_db.Close();
                m_db.Dispose();
                // strstacktrace = HttpUtility.UrlEncode(m_db.Exception_str)
                Response.Redirect("Error_page.aspx?ERROR=RECORDSET_FETCH_FAILED&MESSAGE=" + strstacktrace);
            }

            ///'''''''''''''' get the no of records that exsist for the framed query

            iRecordsFetched = int.Parse(objDataset.Tables[0].Rows.Count.ToString());

            ///''''''''''''' if no records present give msg to the user alerting him that he is invalid user

            if (iRecordsFetched == 0) {

                txtuser.Focus();

            } else {
                ///'''''''''''' if records present get all the userdetails and store them inside a session

                CUSERDetails objUserDetails = new CUSERDetails();
                objUserDetails.iUserID = Convert.ToInt32(objDataset.Tables[0].Rows[0]["userid"].ToString());
                objUserDetails.strUserName = objDataset.Tables[0].Rows[0]["username"].ToString();
                objUserDetails.iGroupID = Convert.ToInt32(objDataset.Tables[0].Rows[0]["groupid"]);
                //objUserDetails.strGroupName = objDataset.Tables[0].Rows[0]["groupname"].ToString();
                objUserDetails.iMemberID = "1";
                //objDataset.Tables(0).Rows(0)("memberid").ToString()
                //objUserDetails.iRooterFlag = Convert.ToInt32(objDataset.Tables[0].Rows[0]["usertypeflag"]);
                //objUserDetails.iWebAccountFlag = Convert.ToInt32(objDataset.Tables[0].Rows[0]["webaccountflag"]);

                //objUserDetails.iUserID = CType(objDataset.Tables(0).Rows(0)("userid").ToString(), Integer)
                //objUserDetails.strUserName = objDataset.Tables(0).Rows(0)("username").ToString()
                //objUserDetails.iGroupID = 4 ' CType(objDataset.Tables(0).Rows(0)("groupid"), Integer)
                //objUserDetails.strGroupName = objDataset.Tables(0).Rows(0)("groupname").ToString()
                //objUserDetails.iMemberID = 1 'objDataset.Tables(0).Rows(0)("memberid").ToString()
                //objUserDetails.iRooterFlag = 1 'CType(objDataset.Tables(0).Rows(0)("usertypeflag"), Integer)
                //objUserDetails.iWebAccountFlag = 1 'CType(objDataset.Tables(0).Rows(0)("webaccountflag"), Integer)
                Session["sesuUserDetails"] = objUserDetails;

                objDataset.Dispose();
                // Free dataset
                m_db.Close();
                // Free SQL Connection

                iTempGroupID = objUserDetails.iGroupID;

                objUserDetails = null;
                m_db = null;

                //Response.Redirect("~/frmNewConnection.aspx");
               Response.Redirect("SBMUpload.aspx");

                }

            }
    }

        /// <summary>
        /// Fetches Dataset
        /// </summary>
        /// <param name="criteria">Prep_Only, Prep and Chkd, Show All</param>
        /// <returns>Dataset</returns>
        public DataSet FetchDataSet(string criteria)
        {
            DataSet ds = new DataSet();
            string qryString;
            switch (criteria)
            {
                case "Prep Only":
                    qryString = "SELECT * FROM viewCOMPLIST_LITE WHERE PREP_DATE IS NOT NULL AND CHKD_DATE IS NULL ORDER BY COMP";
                    break;
                case "Prep and Chkd":
                    qryString = "SELECT * FROM viewCOMPLIST_LITE WHERE PREP_DATE IS NOT NULL AND CHKD_DATE IS NOT NULL ORDER BY COMP";
                    break;
                case "Show All":
                default:
                    qryString = "SELECT * FROM viewCOMPLIST_LITE ORDER BY COMP";
                    break;
            }

            // create database connection
            _user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
            _dbmgr = new DBManager(_user.plantDBStr);
            _dbmgr.ConnectionString = _user.plantDBStr;

            try
            {
                _dbmgr.Open();
                ds = (DataSet)_dbmgr.ExecuteDataSet(CommandType.Text, qryString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbmgr.Dispose();
            }
            return ds;
        }