C# Connection.GetConnString方法代码示例

    public static ArrayList GetAllTrns_ByCustId(int CustId)
    {
        ArrayList TransList = new ArrayList();

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT * FROM transactions WHERE t_cid='" + CustId + "' ORDER BY t_id DESC");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();
            Transactions trns = null;

            while (reader.Read())
            {
                int Tr_Id = reader.GetInt32(0);
                int Tr_CustId = reader.GetInt32(1);
                string Tr_Nartn = reader.GetString(2);
                string Tr_ChqRefNo = reader.GetString(3);
                string Tr_WithdAmt = reader.IsDBNull(4) ? null : reader.GetString(4);
                string Tr_DpoAmt = reader.IsDBNull(5) ? null : reader.GetString(5);
                decimal Tr_Blnc = reader.GetDecimal(6);
                DateTime Tr_Data = reader.GetDateTime(7);

                trns = new Transactions(Tr_Id, Tr_CustId, Tr_Nartn, Tr_ChqRefNo, Tr_WithdAmt, Tr_DpoAmt, Tr_Blnc, Tr_Data);
                TransList.Add(trns);
            }
            return TransList;
        }
    }

    protected void ButtonSubmit_Click(object sender, EventArgs e)
    {
        string CustId = Request.QueryString["CustId"];

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            int ac_HolderId = Convert.ToInt32(CustId);
            decimal ac_Blnc = Convert.ToDecimal(TextBoxBlnc.Text);
            string ac_AltDate = DateTime.Now.ToString();

            string query = string.Format("INSERT INTO account(ac_cid, ac_type, ac_blnc, ac_date) VALUES('" + ac_HolderId + "', '" + DropDownListAcType.SelectedValue + "', '" + ac_Blnc + "', '" + ac_AltDate + "')");
            SqlComm.CommandText = query;
            SqlComm.ExecuteNonQuery();

            string SetStatus = string.Format("UPDATE cust_details SET c_status='1' WHERE c_id='" + ac_HolderId + "'");
            SqlComm.CommandText = SetStatus;
            SqlComm.ExecuteNonQuery();

            General GetNewCode = new General();
            string ChqRefNo = GetNewCode.GenerateCode();
            string Nartion = "Cash";
            string NowDate = DateTime.Now.ToString();

            string queryFrTrns = string.Format("INSERT INTO transactions(t_cid, t_nartion, t_refNo, t_deposit, t_blnc, t_date) VALUES('" + ac_HolderId + "', '" + Nartion + "', '" + ChqRefNo + "', '" + ac_Blnc + "', '" + ac_Blnc + "', '" + NowDate + "')");
            SqlComm.CommandText = queryFrTrns;
            SqlComm.ExecuteNonQuery();

            Response.Redirect("~/Admin/customer.aspx?CustId="+ CustId + "");
        }
    }

    public static Admin GetAll_DataById(int id)
    {
        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT id, name, email, password FROM admin WHERE id='" + id + "'");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();
            Admin adminDataByEmail = null;

            while (reader.Read())
            {
                int Id = reader.GetInt32(0);
                string Name = reader.GetString(1);
                string Email = reader.GetString(2);
                string Pass = reader.GetString(3);

                adminDataByEmail = new Admin(Id, Name, Email, Pass);
            }
            return adminDataByEmail;
        }
    }

    public static ArrayList GetAll_NBReqs()
    {
        ArrayList NBReqsList = new ArrayList();

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT * FROM net_banking WHERE nb_status = '0' ORDER BY nb_id DESC");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();

            while (reader.Read())
            {
                int NB_Id = reader.GetInt32(0);
                int NBC_Id = reader.GetInt32(1);
                DateTime NB_reqData = reader.GetDateTime(3);
                string NB_status = reader.GetString(4);

                NetBanking NBReqs = new NetBanking(NB_Id, NBC_Id, NB_reqData, NB_status);
                NBReqsList.Add(NBReqs);
            }
            return NBReqsList;
        }
    }

    public static ArrayList GetAll_Cust()
    {
        ArrayList AllCustList = new ArrayList();

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT * FROM cust_details ORDER BY c_id DESC");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();

            while (reader.Read())
            {
                int Cust_Id = reader.GetInt32(0);
                string Cust_Name = reader.GetString(1);
                string Cust_Gender = reader.GetString(2);
                string Cust_Mobile = reader.GetString(7);
                string Cust_status = reader.GetString(9);

                Customers AllCustByStatus = new Customers(Cust_Id, Cust_Name, Cust_Gender, Cust_Mobile, Cust_status);
                AllCustList.Add(AllCustByStatus);
            }
            return AllCustList;
        }
    }

    protected void ButtonSubmit_Click(object sender, EventArgs e)
    {
        int CustID = Convert.ToInt32(TextBoxCustId.Text);

        Customers ChckCustID = new Customers();
        bool ChckCust = ChckCustID.ChckCustById(CustID);
        bool ChckCustNB = ChckCustID.ChckCustNetBankingById(CustID);

        if(ChckCust != true)
        {
            LabelError.Text = "You enter wrong customer ID.";
        }
        else if (ChckCustNB != false)
        {
            LabelError.Text = "You already apply for it.";
        }
        else if (TextBoxPass.Text.Length < 6)
        {
            LabelError.Text = "Your password must be at least 6 characters.";
        }
        else if (TextBoxPass.Text != TextBoxConfirmPass.Text)
        {
            LabelError.Text = "Your confirm password did not match.";
        }
        else
        {
            Connection con = new Connection();
            string strConnString = con.GetConnString();
            using (SqlConnection SqlCon = new SqlConnection(strConnString))
            {
                SqlCommand SqlComm = new SqlCommand("", SqlCon);
                SqlCon.Open();

                string ReqDate = DateTime.Now.ToString();
                string Status = "0";

                string query = string.Format("INSERT INTO net_banking(nb_cid, nb_pass, nb_date, nb_status) VALUES('" + CustID + "', '" + TextBoxPass.Text + "', '" + ReqDate + "', '" + Status + "')");
                SqlComm.CommandText = query;
                SqlComm.ExecuteNonQuery();

                /*string SetStatus = string.Format("UPDATE cust_details SET c_net_banking='1' WHERE c_id='" + CustID + "'");
                SqlComm.CommandText = SetStatus;
                SqlComm.ExecuteNonQuery();*/
            }

            Panel1.Visible = false;
            Panel2.Visible = true;
        }
    }

    protected void ButtonCustLogin_Click(object sender, EventArgs e)
    {
        string CustID = TextBoxCustId.Text;
        string CustPass = TextBoxPass.Text;

        Customers ChckCustID = new Customers();
        bool ChckCust = ChckCustID.ChckCustById(Convert.ToInt32(CustID));
        bool ChckCustNB = ChckCustID.ChckCustNetBankingById(Convert.ToInt32(CustID));
        bool ChckCustNBSt = ChckCustID.ChckCustNBStById(Convert.ToInt32(CustID));

        if (ChckCust != true)
        {
            LabelError.Text = "You entered wrong customer ID.";
        }
        else if (ChckCustNB != true)
        {
            LabelError.Text = "1st aplly for it.";
        }
        else if (ChckCustNBSt != true)
        {
            LabelError.Text = "Your request stil panding.";
        }
        else
        {
            Connection con = new Connection();
            string strConnString = con.GetConnString();
            using (SqlConnection SqlCon = new SqlConnection(strConnString))
            {
                SqlCommand SqlComm = new SqlCommand("", SqlCon);
                SqlCon.Open();

                string query = string.Format("SELECT nb_pass FROM net_banking WHERE nb_cid='" + Convert.ToInt32(CustID) + "'");
                SqlComm.CommandText = query;
                string dbPass = (String)SqlComm.ExecuteScalar();

                if (dbPass == CustPass)
                {
                    Session["Cust_Id"] = Convert.ToInt32(CustID);
                    Response.Redirect("~/users/Default.aspx");
                }
                else
                {
                    LabelError.Text = "Wrong password.";
                }
            }
        }
    }

    protected void ChckCustId()
    {
        string CustId = Request.QueryString["CustId"];

        if (CustId != null)
        {
            Customers ChckCust = new Customers();
            bool result = ChckCust.ChckCustById(Convert.ToInt32(CustId));

            if (result != false)
            {
                Panel1.Visible = false;
                Panel2.Visible = true;

                Connection con = new Connection();
                string strConnString = con.GetConnString();
                using (SqlConnection SqlCon = new SqlConnection(strConnString))
                {
                    SqlCommand SqlComm = new SqlCommand("", SqlCon);
                    SqlCon.Open();

                    string query = string.Format("SELECT COUNT(*) FROM account WHERE ac_cid='" + Convert.ToInt32(CustId) + "'");
                    SqlComm.CommandText = query;
                    int ac_result = (int)SqlComm.ExecuteScalar();

                    if (ac_result == 0)
                    {
                        Panel3.Visible = true;
                        Panel2.Visible = false;
                    }
                }
            }
            else
            {
                Panel1.Visible = true;
                Panel2.Visible = false;
            }
        }
        else
        {
            Response.Redirect("Default.aspx");
        }
    }

    protected void ButtonPayBill_Click(object sender, EventArgs e)
    {
        int CustId = Convert.ToInt32(Session["Cust_Id"]);

        Customers GetCustDataById = Customers.GetCust_DataById(CustId);
        decimal CurntBlnc = GetCustDataById.Cust_AcBlnc;
        decimal BillPayAmt = Convert.ToDecimal(TextBoxAmt.Text);

        if (BillPayAmt > CurntBlnc)
        {
            LabelError.Text = "Low balance.";
        }
        else
        {
            Connection con = new Connection();
            string strConnString = con.GetConnString();
            using (SqlConnection SqlCon = new SqlConnection(strConnString))
            {
                SqlCommand SqlComm = new SqlCommand("", SqlCon);
                SqlCon.Open();

                string Nartion = "Bill pay MitrNo.-"+ TextBoxMitter.Text;
                string NowDate = DateTime.Now.ToString();

                General GetNewCode = new General();
                string ChqRefNo = GetNewCode.GenerateCode();

                decimal NewBlnc = CurntBlnc - BillPayAmt;

                string queryFrTrns = string.Format("INSERT INTO transactions(t_cid, t_nartion, t_refNo, t_withdrawal, t_blnc, t_date) VALUES('" + CustId + "', '" + Nartion + "', '" + ChqRefNo + "', '" + BillPayAmt + "', '" + NewBlnc + "', '" + NowDate + "')");
                SqlComm.CommandText = queryFrTrns;
                SqlComm.ExecuteNonQuery();

                string SetStatus = string.Format("UPDATE account SET ac_blnc='" + NewBlnc + "' WHERE ac_cid='" + CustId + "'");
                SqlComm.CommandText = SetStatus;
                SqlComm.ExecuteNonQuery();

                Panel1.Visible = false;
                Panel2.Visible = true;
            }
        }
    }

    protected void ButtonSubmit_Click(object sender, EventArgs e)
    {
        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string req_date = DateTime.Now.ToString();
            string NetBanking = "0";
            string Cust_Status = "0";

            string query = string.Format("INSERT INTO cust_details(c_name, c_gender, c_state, c_city, c_pin, c_email, c_mobNo, c_net_banking, c_status, c_date) VALUES('" + TextBoxCustName.Text + "', '" + DropDownListGender.SelectedValue + "', '" + TextBoxState.Text + "', '" + TextBoxCity.Text + "', '" + TextBoxPin.Text + "', '" + TextBoxEmail.Text + "', '" + TextBoxMobile.Text + "', '" + NetBanking + "', '" + Cust_Status + "', '" + req_date + "')");
            SqlComm.CommandText = query;
            SqlComm.ExecuteNonQuery();

            Response.Redirect("~/Admin/Default.aspx");
        }
    }

    protected void ButtonAdminLogin_Click(object sender, EventArgs e)
    {
        string adminEmail = TextBoxEmailId.Text;
        string adminPass = TextBoxPass.Text;

        Connection con = new Connection();
        string strConnString = con.GetConnString();

        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT COUNT(*) FROM admin WHERE email='"+ adminEmail +"'");
            SqlComm.CommandText = query;
            int result = (int)SqlComm.ExecuteScalar();

            if (result == 1)
            {
                query = string.Format("SELECT password FROM admin WHERE email='"+ adminEmail +"'");
                SqlComm.CommandText = query;
                string dbPass = (String)SqlComm.ExecuteScalar();

                if (dbPass == adminPass)
                {
                    Admin adminDataByEmail = Admin.GetAll_DataByEmail(adminEmail);
                    Session["id"] = adminDataByEmail.Id;

                    Response.Redirect("~/Admin/Default.aspx");
                }
                else
                {
                    LabelError.Text = "Wrong password.";
                }
            }
            else if (result == 0)
            {
                LabelError.Text = "Email ID does not exists.";
            }
        }
    }

    protected void ButtonAllowNB_Click(object sender, EventArgs e)
    {
        int CustID = Convert.ToInt32(Request.QueryString["cust_id"]);

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("UPDATE net_banking SET nb_status='1' WHERE nb_cid='" + CustID + "'");
            SqlComm.CommandText = query;
            SqlComm.ExecuteNonQuery();

            string SetStatus = string.Format("UPDATE cust_details SET c_net_banking='1' WHERE c_id='" + CustID + "'");
            SqlComm.CommandText = SetStatus;
            SqlComm.ExecuteNonQuery();

            Response.Redirect("~/Admin/net_banking.aspx?cust_id=" + CustID + "");
        }
    }

    public static NetBanking NBData_ByCustId(int CustId)
    {
        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT * FROM net_banking WHERE nb_cid='" + CustId + "'");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();
            NetBanking NBDataByCustId = null;

            while (reader.Read())
            {
                int NB_Id_2 = reader.GetInt32(0);
                int NB_Cid = reader.GetInt32(1);
                string NB_Pass = reader.GetString(2);
                DateTime NB_reqDate = reader.GetDateTime(3);
                string NB_Status = reader.GetString(4);

                NBDataByCustId = new NetBanking(NB_Id_2, NB_Cid, NB_Pass, NB_reqDate, NB_Status);

            }
            return NBDataByCustId;
        }
    }

    public bool ChckCustNetBankingById(int CustId)
    {
        bool value;

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT COUNT(*) FROM net_banking WHERE nb_cid='" + CustId + "'");
            SqlComm.CommandText = query;
            int result = (int)SqlComm.ExecuteScalar();

            if (result != 1)
            {
                value = false;
            }
            else
            {
                value = true;
            }
        }
        return value;
    }

    public static Customers GetLight_DataById(int CustId)
    {
        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            string query = string.Format("SELECT * FROM cust_details WHERE c_id='" + CustId + "'");
            SqlComm.CommandText = query;
            SqlDataReader reader = SqlComm.ExecuteReader();
            Customers GetCustLightDataById = null;

            while (reader.Read())
            {
                int Cust_Id = reader.GetInt32(0);
                string Cust_Name = reader.GetString(1);
                string Cust_Gender = reader.GetString(2);
                string Cust_Mobile = reader.GetString(7);
                string Cust_status = reader.GetString(9);

                GetCustLightDataById = new Customers(Cust_Id, Cust_Name, Cust_Gender, Cust_Mobile, Cust_status);

            }
            return GetCustLightDataById;
        }
    }