using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Utilities;

using System.Data;

using System.Data.SqlClient;

namespace Database

{

public partial class StoredProcedureExample2 : System.Web.UI.Page

{

DBConnect objDB = new DBConnect();

SqlCommand objCommand = new SqlCommand();

string strSQL;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

// Set the SQLCommand object's properties for executing a Stored Procedure

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandText = "GetAccounts"; // identify the name of the stored procedure to execute

// Execute the stored procedure using the DBConnect object and the SQLCommand object

ddlAccounts.DataSource = objDB.GetDataSetUsingCmdObj(objCommand);

ddlAccounts.DataTextField = "CustomerName";

ddlAccounts.DataValueField = "AccountID";

ddlAccounts.DataBind();

}

}

protected void ddlAccounts_SelectedIndexChanged(object sender, EventArgs e)

{

int id;

id = int.Parse(ddlAccounts.SelectedItem.Value);

// Set the SQLCommand object's properties for executing a Stored Procedure

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandText = "GetAccountByID"; // identify the stored procedure to execute

// Pass an input parameter value to the Stored Procedure that is used for the @theID built-in parameter

// objCommand.Parameters.AddWithValue("@theID", id)

SqlParameter inputParameter = new SqlParameter("@theID", id);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Int;

inputParameter.Size = 4; // 4-bytes

objCommand.Parameters.Add(inputParameter);

// Execute stored procedure using DBConnect object and the SQLCommand object

gvAccount.DataSource = objDB.GetDataSetUsingCmdObj(objCommand);

gvAccount.DataBind();

}

protected void btnDeposit_Click(object sender, EventArgs e)

{

int id;

double balance;

id = int.Parse(ddlAccounts.SelectedItem.Value);

// Set the SQLCommand object's properties for executing a Stored Procedure

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandText = "MakeDeposit"; // identify the stored procedure to execute

// Pass an input parameter value to the Stored Procedure that is used for the @theID built-in parameter

// objCommand.Parameters.AddWithValue("@theID", id)

// objCommand.Parameters.AddWithValue("@theAmount", double.Parse(txtAmount.Text))

SqlParameter inputParameter = new SqlParameter("@theID", id);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Int;

inputParameter.Size = 4; // 4-bytes

objCommand.Parameters.Add(inputParameter);

inputParameter = new SqlParameter("@theAmount", double.Parse(txtAmount.Text));

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Float;

inputParameter.Size = 8; // 4-bytes

objCommand.Parameters.Add(inputParameter);

objDB.DoUpdateUsingCmdObj(objCommand);

strSQL = "SELECT Balance FROM Account WHERE AccountID = " + id;

objDB.GetDataSet(strSQL);

balance = (double)objDB.GetField("Balance", 0);

lblMessage.Text = "The new balance is " + balance.ToString("C2");

}

protected void btnWithdraw_Click(object sender, EventArgs e)

{

int id;

double balance;

id = int.Parse(ddlAccounts.SelectedItem.Value);

// Set the SQLCommand object's properties for executing a Stored Procedure

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandText = "MakeWithdrawal"; // identify the stored procedure to execute

// Pass an input parameter value to the Stored Procedure that is used for the @theID built-in parameter

// objCommand.Parameters.AddWithValue("@theID", id)

// objCommand.Parameters.AddWithValue("@theAmount", double.Parse(txtAmount.Text))

SqlParameter inputParameter = new SqlParameter("@theID", id);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Int;

inputParameter.Size = 4; // 4-bytes

objCommand.Parameters.Add(inputParameter);

inputParameter = new SqlParameter("@theAmount", double.Parse(txtAmount.Text));

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Float;

inputParameter.Size = 8; // 4-bytes

objCommand.Parameters.Add(inputParameter);

objDB.DoUpdateUsingCmdObj(objCommand);

strSQL = "SELECT Balance FROM Account WHERE AccountID = " + id;

objDB.GetDataSet(strSQL);

balance = (double)objDB.GetField("Balance", 0);

lblMessage.Text = "The new balance is " + balance.ToString("C2");

}

}

}