protected void btnEnter_Click(object sender, EventArgs e)
{
//local variables for new user
string studentName = ddlStudentName.SelectedValue;
//DBConnect object
DBConnect dbobj = new DBConnect();
//good code
//Database Updates
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "AddNewStudent";
// sqlCommand.Parameters.AddWithValue("@StudentID", studentID);
sqlCommand.Parameters.AddWithValue("@Name", studentName);
// sqlCommand.Parameters.AddWithValue("@FieldOfStudy", fieldOfStudy);
dbobj.DoUpdateUsingCmdObj(sqlCommand);
//bad code
//SqlCommand objCommand = new SqlCommand();
//objCommand.CommandType = CommandType.StoredProcedure;
//objCommand.CommandText = "GetRentalCarAgencies";
//objCommand.Parameters.AddWithValue("@city", city);
//objCommand.Parameters.AddWithValue("@state", state);
//DBConnect objDB = new DBConnect();
//return objDB.GetDataSetUsingCmdObj(objCommand);
}
public void AddCustomer(Customer customer)
{
DBConnect objdb = new DBConnect();
string insertCustSQL = "INSERT INTO CUSTOMER (FirstName, LastName, Address, City, State, Zipcode)"
+ "VALUES ('" + customer.FirstName + "','" + customer.LastName + "','" + customer.Address + "','" + customer.City + "','" + customer.State + "','" + customer.Zipcode + "')'";
objdb.DoUpdate(insertCustSQL);
}
protected void Page_Load(object sender, EventArgs e)
{
DBConnect objDB = new DBConnect();
String strSQL = "SELECT * FROM Students_Table";
DataSet myDataSet = objDB.GetDataSet(strSQL);
gvStudents.DataSource = myDataSet;
gvStudents.DataBind();
}
public void AddTPCustomerToDB(string email)
{
DBConnect objDB = new DBConnect();
SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandText = "AddTPCustomer";
objCommand.Parameters.AddWithValue("@email", );
int result = objDB.DoUpdateUsingCmdObj(objCommand);
}
public void loadCourses()
{
DBConnect objDB = new DBConnect();
SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandText = "ShowAllCourses";
DataSet myDS = objDB.GetDataSetUsingCmdObj(objCommand);
gvCourses.DataSource = myDS;
gvCourses.DataBind();
}
public void AddCreditCardAccount(string name, float cardNumber, int expMonth, int expYear, int CSV)
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "AddNewCreditCard";
//Values to go into Account Table
sqlCommand.Parameters.AddWithValue("@Name", name);
sqlCommand.Parameters.AddWithValue("@CardNumber", cardNumber);
sqlCommand.Parameters.AddWithValue("@ExpMonth", expMonth);
sqlCommand.Parameters.AddWithValue("@ExpYear", expYear);
sqlCommand.Parameters.AddWithValue("@CSV", CSV);
objdb.DoUpdateUsingCmdObj(sqlCommand);
}
public void addTransactionLog(string name, float cardNumber, int expMonth, int expYear, int CSV, decimal balance, decimal transactionAmt )
{
// stored procedure "AddTransactionLog"
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "AddTransactionLog";
sqlCommand.Parameters.AddWithValue("@Name", name);
sqlCommand.Parameters.AddWithValue("@CardNumber", cardNumber);
sqlCommand.Parameters.AddWithValue("@ExpMonth", expMonth);
sqlCommand.Parameters.AddWithValue("@ExpYear", expYear);
sqlCommand.Parameters.AddWithValue("@CSV", CSV);
sqlCommand.Parameters.AddWithValue("@Balance", balance);
sqlCommand.Parameters.AddWithValue("@TransactionAmount", transactionAmt);
objdb.DoUpdateUsingCmdObj(sqlCommand);
}
public void registerStudentForClass(string StudentID, string CRN)
{
DBConnect objDB = new DBConnect();
string strSQL = "INSERT INTO [CourseRegistration] (StudentID, CRN) VALUES('" + StudentID + "','" + CRN + "')";
int updatedRows = objDB.DoUpdate(strSQL);
strSQL = "DECLARE @DecrementValue int SET @DecrementValue = 1 UPDATE Course SET NumberOfSeatsAvailable = NumberOfSeatsAvailable - @DecrementValue WHERE CRN='" + CRN + "'";
updatedRows = objDB.DoUpdate(strSQL);
strSQL = "SELECT CreditHours FROM Course WHERE CRN='" + CRN + "'";
DataSet myDS = objDB.GetDataSet(strSQL);
DataRow currRow = objDB.GetRow(myDS, 0);
float courseCredit = float.Parse(currRow["CreditHours"].ToString());
float coursePrice = courseCredit * 10000;
strSQL = "DECLARE @CoursePrice float SET @CoursePrice = " + coursePrice + " UPDATE Students SET TutionOwed = TutionOwed + @CoursePrice WHERE StudentID=" + StudentID;
updatedRows = objDB.DoUpdate(strSQL);
}
public decimal getAccountBalance(string name, float cardNumber)
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "GetAccountBalance";
sqlCommand.Parameters.AddWithValue("@Name", name);
sqlCommand.Parameters.AddWithValue("@CardNumber", cardNumber);
SqlParameter returnParameter = new SqlParameter("@Balance", DbType.Decimal);
returnParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(returnParameter);
// Execute stored procedure using DBConnect object and the SQLCommand object
objdb.GetDataSetUsingCmdObj(sqlCommand);
decimal balance;
balance = decimal.Parse(sqlCommand.Parameters["@Balance"].Value.ToString());
return balance;
}
public void updateName(string name, int AccountID)
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "UpdateAccountName";
sqlCommand.Parameters.AddWithValue("@Name", name);
sqlCommand.Parameters.AddWithValue("@AccountID", AccountID);
objdb.DoUpdateUsingCmdObj(sqlCommand);
}
public void UpdateExpYear(int expYear, int AccountID)
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "UpdateExpYear";
sqlCommand.Parameters.AddWithValue("@ExpYear", expYear);
sqlCommand.Parameters.AddWithValue("@AccountID", AccountID);
objdb.DoUpdateUsingCmdObj(sqlCommand);
}
public int Transaction(string name, float cardNumber,int expMonth, int expYear, int CSV, double transactionAmt)
{
int returnval;
//get Account Balance
decimal balance = getAccountBalance(name, cardNumber);
decimal transaction = decimal.Parse(transactionAmt.ToString());
//if balance is greater than transaction amount, do the transaction
if (balance > transaction)
{
//Update Account balance using stored procedure
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "UpdateAccountBalance";
sqlCommand.Parameters.AddWithValue("@CardNumber", cardNumber);
sqlCommand.Parameters.AddWithValue("@TransactionAmount", transactionAmt);
objdb.DoUpdateUsingCmdObj(sqlCommand); //execute update
//create a log of the transaction
addTransactionLog(name, cardNumber, expMonth, expYear, CSV, balance, transaction);
returnval = 0;
}
else
{
returnval = 1;
}
return returnval;
}
public int getCreditCardCount()
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "GetCreditCardCount";
//stores the value in the variable @theCount
SqlParameter returnParameter = new SqlParameter("@theCount", DbType.Int32);
returnParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(returnParameter);
// Execute stored procedure using DBConnect object and the SQLCommand object
objdb.GetDataSetUsingCmdObj(sqlCommand);
int count;
count = int.Parse(sqlCommand.Parameters["@theCount"].Value.ToString());
return count;
}
public DataSet getAllTransactions()
{
//Update Account balance using stored procedure
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "GetAllTransactions";
DataSet dataset = objdb.GetDataSetUsingCmdObj(sqlCommand);
return dataset;
}
protected void gvCourses_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int rowIndex = e.RowIndex;
int selectedCRN = int.Parse(gvCourses.Rows[rowIndex].Cells[1].Text);
DBConnect objDB = new DBConnect();
SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandText = "DeleteCourse";
objCommand.Parameters.AddWithValue("@CRN", selectedCRN);
objDB.GetDataSetUsingCmdObj(objCommand);
objDB.DoUpdateUsingCmdObj(objCommand);
loadCourses();
}
public DataSet GetAccounts()
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "GetAllAccounts";
DataSet dataset = objdb.GetDataSetUsingCmdObj(sqlCommand);
return dataset;
}
public object GetDataSetUsingCmdObj(DBConnect dbobj)
{
throw new NotImplementedException();
}
public void updateCardNumber(float cardNumber, int AccountID)
{
DBConnect objdb = new DBConnect();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "UpdateCardNumber";
sqlCommand.Parameters.AddWithValue("@CardNumber", cardNumber);
sqlCommand.Parameters.AddWithValue("@AccountID", AccountID);
objdb.DoUpdateUsingCmdObj(sqlCommand);
}
