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 StoredProcedureExample3 : System.Web.UI.Page

{

DBConnect objDB = new DBConnect();

SqlCommand objCommand = new SqlCommand();

string strSQL;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

PopulateLists();

DisplayAccounts();

}

}

protected void btnTransferFunds_Click(object sender, EventArgs e)

{

int FromAccountID = int.Parse(ddlFromAccount.SelectedItem.Value);

int ToAccountID = int.Parse(ddlToAccount.SelectedItem.Value);

double amount = 0;

if (double.TryParse(txtAmount.Text, out amount))

{

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

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandText = "TransferFunds"; // 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("@FromAccount", FromAccountID)

// objCommand.Parameters.AddWithValue("@ToAccount", ToAccountID)

// objCommand.Parameters.AddWithValue("@Amount", CDbl(txtAmount.Text))

SqlParameter inputParameter = new SqlParameter("@FromAccount", FromAccountID);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Int;

inputParameter.Size = 4; // 4-bytes

objCommand.Parameters.Add(inputParameter);

inputParameter = new SqlParameter("@ToAccount", ToAccountID);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Int;

inputParameter.Size = 4; // 8-bytes

objCommand.Parameters.Add(inputParameter);

inputParameter = new SqlParameter("@Amount", amount);

inputParameter.Direction = ParameterDirection.Input;

inputParameter.SqlDbType = SqlDbType.Float;

inputParameter.Size = 8; // 8-bytes

objCommand.Parameters.Add(inputParameter);

int returnValue = objDB.DoUpdateUsingCmdObj(objCommand);

if (returnValue > 0)

lblMessage.Text = "The funds were sucessfully transferred.";

else

lblMessage.Text = "A problem occurred and the funds weren't transferred.";

DisplayAccounts();

}

else

{

lblMessage.Text = "You must enter a numerical value for the amount!";

}

}

private void DisplayAccounts()

{

objCommand = new SqlCommand();

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

objCommand.CommandType = CommandType.StoredProcedure;

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

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

DataSet myDS = objDB.GetDataSetUsingCmdObj(objCommand);

gvAccounts.DataSource = myDS;

gvAccounts.DataBind();

}

private void PopulateLists()

{

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

objCommand.CommandType = CommandType.StoredProcedure;

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

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

DataSet myDS = objDB.GetDataSetUsingCmdObj(objCommand);

ddlFromAccount.DataSource = myDS;

ddlFromAccount.DataTextField = "CustomerName";

ddlFromAccount.DataValueField = "AccountID";

ddlFromAccount.DataBind();

ddlToAccount.DataSource = myDS;

ddlToAccount.DataTextField = "CustomerName";

ddlToAccount.DataValueField = "AccountID";

ddlToAccount.DataBind();

}

}

}