Stored Procedure on ASP.NET 2.0
It was almost more than one month since I started my intern as a software developer on my company. Although I have already finished my intern last 2006, I accepted the job because I want to explore the world of software development using Microsoft tools. It was a good experience. To add the fact that I still have no background on a software development team and environment. Our first day was evaluation period. Depending on user's experiences, I still don't have much experiences on web development regarding ASP.NET. I used to be using Flash, CSS, and HTMLs but not ASP.NET. In fact, I prefer Window Forms Apps because I used to be a programmer on winform apps and not web apps. But this ASP.NET interests me a lot. I'd like to learn more on Microsoft Technologies, and I am really a Microsoft passionate person. It was a kind of feeling everytime I used their technologies and provide me the passion to pursue more about my abilities and limitations at its peak. So shifting to classic HTML to ASP.NET is not a big issue and, overall, its a .NET Framework 2.0 technology. On our evaluation period, we started to create a video shop web application. Of course, I started my code on a registration page as:
//Original Code below using SQLDataSource. Code modified:
int x;
SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.Text;
SqlDataSource1.InsertCommand = "INSERT INTO Customer(CustomerID, CustomerName, CustomerAddress, CustomerPhone) VALUES ('" + txtCustomerID.Text + "', '" + txtCustomerName.Text + "', '" + txtCustomerAddress.Text + "', '" + txtCustomerPhone.Text + "')";
try
{
x = SqlDataSource1.Insert();
}
catch
{
Server.Transfer("Error.aspx");
}
finally
{
SqlDataSource1 = null;
}
if (x != 1)
{
Server.Transfer("Error.aspx");
}
else
{
Server.Transfer("Success.aspx");
}
I thought, everything works fine, and they will all do the inside job. But there is one thing I never notice.....stored procedure. My leader on our development team told us to experiment about stored procedure. Why? It connects directly on a database. Just like unbound connection of objects to database. Binding source connection was pretty simple but stored procedure was much better. So the stored procedure for the above code that I did is:
public RegisterCustomer(string SQL,TextBox txtCustomerID, TextBox txtCustomerName, TextBox txtCustomerAddress, TextBox txtCustomerPhone)
{
//
// TODO: Add constructor logic here
//
string ConnString;
ConnString = @"Data Source=.\SQLEXPRESS;" +
@"AttachDbFilename=C:\Documents and Settings\i-wdavid\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\Database.mdf;" +"Integrated Security=True;User Instance=True";
SqlConnection myConnection = new SqlConnection(ConnString);
myConnection.Open();
try
{
SqlCommand myCommand = new SqlCommand(SQL, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter myParameterCustomerID = new SqlParameter("@CustomerID", DbType.Int32);
SqlParameter myParameterCustomerName = new SqlParameter("@CustomerName", DbType.String);
SqlParameter myParameterCustomerAddress = new SqlParameter("@CustomerAddress", DbType.String);SqlParameter myParameterCustomerPhone = new SqlParameter("@CustomerPhone", DbType.String);
myParameterCustomerID.Value = txtCustomerID.Text;
myParameterCustomerName.Value = txtCustomerName.Text;
myParameterCustomerAddress.Value = txtCustomerAddress.Text;
myParameterCustomerPhone.Value = txtCustomerPhone.Text;
myCommand.Parameters.Add(myParameterCustomerID);
myCommand.Parameters.Add(myParameterCustomerName);
myCommand.Parameters.Add(myParameterCustomerAddress);
myCommand.Parameters.Add(myParameterCustomerPhone);
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
// Code you want to bind to a gridview
//if (myReader.HasRows)
//{
// myGridView.DataSource = myReader;
// myGridView.DataBind();
//}
//else
//{
// Label myLabel=new Label();
// myLabel.Text="No rows to display";
//}
}
finally
{
myConnection.Close();
}
And to initialize that:
protected void Button1_Click1(object sender, EventArgs e)
{
try
{
RegisterCustomer myRegisterCustomer = new RegisterCustomer("usp_register", txtCustomerID, txtCustomerName, txtCustomerAddress, txtCustomerPhone); //usp_register is the name of our user stored procedure
}
catch
{
Server.Transfer("Error.aspx");
}
Server.Transfer("Success.aspx");
}
Where usp_register is our stored procedure.
Let's look what usp_register looks:
ALTER PROCEDURE usp_register
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@CustomerID int,
@CustomerName char(50),
@CustomerAddress char(50),
@CustomerPhone char(50)
--@CustomerRentalCost char(50)
AS
INSERT INTO Customer(CustomerID, CustomerName, CustomerAddress, CustomerPhone) VALUES (@CustomerID, @CustomerName, @CustomerAddress, @CustomerPhone)
Well that's all for the registration page. So what do you think professionals and developers out there? Do you think I made the right code? Actually it still depends on a programmer, but basically, I want to learn much more on professionals! And it was fun to work on them! 
