public void updateAddress(int ssn, String address)
 {
     MakeDataConnection dataConnection = new MakeDataConnection();
     SqlConnection conn = dataConnection.makeConnection();
     try
     {
         conn.Open();
         SqlCommand getName = new SqlCommand("SELECT name FROM Customer where ssn = " + ssn + " AND isDelete = 0", conn);
         SqlDataReader reader = null;
         reader = getName.ExecuteReader();
         reader.Read();
         String name = Convert.ToString(reader["name"]);
         reader.Close();
         SqlCommand sqlCmd = conn.CreateCommand();
         sqlCmd.CommandText = "UPDATE Customer SET isDelete = 1 WHERE ssn = " + ssn + " AND isDelete = 0;"+
             "INSERT INTO Customer VALUES (" + ssn + ",'" + name + "','" + address + "', CURRENT_TIMESTAMP,0)";
         sqlCmd.ExecuteNonQuery();
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         conn.Close();
     }
 }
コード例 #2
0
 public void creditMoney(int ssn, String accNo, String branchCode, String money)
 {
     MakeDataConnection dataConnection = new MakeDataConnection();
     SqlConnection conn = dataConnection.makeConnection();
     try
     {
         conn.Open();
         SqlCommand getBalance = new SqlCommand("SELECT balance FROM account where accNo = '"+accNo+"' AND isDelete = 0",conn);
         SqlDataReader reader = null;
         reader = getBalance.ExecuteReader();
         reader.Read();
         int currentBalance = Convert.ToInt32(reader["balance"]);
         int newBalance = currentBalance - Convert.ToInt32(money);
         reader.Close();
         //add data for accManagment
         SqlCommand sqlCmd = conn.CreateCommand();
         sqlCmd.CommandText = "UPDATE account SET isDelete = 1 WHERE accNo = " + accNo + " AND isDelete = 0;"+
         "UPDATE AccManagement SET isDelete = 1 WHERE ssn = " + ssn + " AND isDelete = 0;"+
         "INSERT INTO AccManagement (ssn,accNo,credit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + money + ", CURRENT_TIMESTAMP,0);"+
         "INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + newBalance + ", CURRENT_TIMESTAMP,0)";
         sqlCmd.ExecuteNonQuery();
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         conn.Close();
     }
 }
コード例 #3
0
        public void startAcc(int ssn, String name, String address, String contactNo, String debit, String accNo,String branchCode)
        {
            MakeDataConnection dataConnection = new MakeDataConnection();
            SqlConnection conn = dataConnection.makeConnection();
            try
            {
                conn.Open();
                //check ssn is available or not in customer
                SqlCommand getRecord = new SqlCommand("select * from customer where ssn = "+ssn+" and isDelete = 0",conn);
                SqlDataReader reader = null;
                reader = getRecord.ExecuteReader();
                if (!reader.Read())
                {
                    //add new customer in to db
                    reader.Close();
                    SqlCommand cmd = new SqlCommand("INSERT INTO customer values(" + ssn + ",'" + name + "','" + address + "', CURRENT_TIMESTAMP,0)", conn);
                    cmd.ExecuteNonQuery();
                    //add contact for new customer
                    //SqlCommand addContacts = new SqlCommand("INSERT INTO CustomerContacts values(" + ssn + ",'" + contactNo + "', CURRENT_TIMESTAMP,0)", conn);
                    //addContacts.ExecuteNonQuery();
                    //add data for accManagment
                    SqlCommand addAccManagement = new SqlCommand("INSERT INTO AccManagement (ssn,accNo,debit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
                    addAccManagement.ExecuteNonQuery();
                    SqlCommand addAccount = new SqlCommand("INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
                    addAccount.ExecuteNonQuery();
                }
                else
                {
                    reader.Close();
                    SqlCommand addAccManagement = new SqlCommand("INSERT INTO AccManagement (ssn,accNo,debit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
                    addAccManagement.ExecuteNonQuery();
                    SqlCommand addAccount = new SqlCommand("INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
                    addAccount.ExecuteNonQuery();
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
コード例 #4
0
        //public CreditDebitHistory(int ssn, String accNo, String starTime, String endTime)
        //{
        //    getHistoyr(ssn, accNo,starTime,endTime);
        //}
        public DataTable getHistory(int ssn, String accNo, String starTime, String endTime)
        {
            DataTable table = new DataTable();
            MakeDataConnection dataConnection = new MakeDataConnection();
            SqlConnection conn = dataConnection.makeConnection();
            try
            {
                conn.Open();
                //SqlCommand getHistory = new SqlCommand("SELECT credit,debit, transactionTime as time FROM AccManagement WHERE ssn= "+ssn+" AND accNo='"+accNo+"' AND transactionTime<'"+endTime+"' AND transactionTime>'"+starTime+"'", conn);
                //SqlDataReader reader = null;
                //reader = getHistory.ExecuteReader();
                //Console.WriteLine("{0}\t\t{1}\t\t{2}", "Credit", "Debit", "Time");
                //while (reader.Read())
                //{
                //    //Console.WriteLine(Convert.ToString(reader["credit"]), Convert.ToString(reader["debit"]));

                //    Console.WriteLine("|{0}|{1}|{2}", reader["credit"], reader["debit"], reader["time"]);
                //}
                //reader.Close();
                String command = "SELECT credit,debit, transactionTime as time FROM AccManagement WHERE ssn= " + ssn + " AND accNo='" + accNo + "' AND transactionTime<'" + endTime + "' AND transactionTime>'" + starTime + "'";
                using (SqlDataAdapter a = new SqlDataAdapter(command, conn))
                {
                    // 3
                    // Use DataAdapter to fill DataTable
                    //DataTable t = new DataTable();
                    a.Fill(table);

                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();

            }
            return table;
        }