Programming is Passion,Software Development is an Adventure- Willy David Jr

Programming is Passion,Software Development is an Adventure- Willy David Jr

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! Smile

 

Comments

jokiz said:

next thing you should practice in your code is using OOP, perhaps you have a Registrar class which accepts string values for customer attributes and not Textbox instances

# June 26, 2007 6:54 PM

bonskijr said:

agree on jokiz's suggestion..if for some reason the Phone should be entered using MaskEdit Control, you'd have to search/replace every calling procedure..

and maybe you can do without the passing of the stored proc name, and put it inside the RegisterCustomer class

# June 26, 2007 10:13 PM

lamia said:

One thing that is good with stored procedures is that you can have multiple transactions. I failed to realize this in my past projects and inlined all the SQL queries which made it a maintenance hell.

# June 26, 2007 10:13 PM

jokiz said:

sp with multiple transactions, hmmm, that's too complicated for normal scenarios, i've never had a requirement such as that

# June 26, 2007 10:51 PM

willydavidjr said:

Is this what you mean sir?

I'll declare my registrar class and get customer name:

public class Registrar

{

protected string _Name;

public string Name

{

get{return _Name;}

set{_Name=value;}

}

public Registrar()

{

this._x="";

}

And when I initialized it:

Registrar myRegistrar=new Registrar();

myRegistrar.Name=Textbox1.text;

-Willy

# June 26, 2007 11:49 PM

lamia said:

BTW, rumors spread here in my company that they are looking for exceptional young people (by exceptional, I mean that's a very stupid term they came up with) to be trained with C#.Net.

If anyone here thinks he's exceptional(the stupid term, yeah) then I advise you to forward your resume to me.

@Cruizer

It happened in my previous project where I have to insert into multiple tables and delete and then select and then delete, insert, etc.

It wasn't our design to begin with so thinking of it right now, I think it could've been better if we used stored procs back then.

# June 27, 2007 2:12 AM

jokiz said:

maybe u mean multiple action queries with sp and not multiple transaction

# June 27, 2007 3:14 AM

lamia said:

Sorry jokiz, my bad. And why the hell did I say it was for cruizer when he's not even made a post!!??

# June 27, 2007 6:56 AM

cruizer said:

oo nga lamia, ano ba kinalaman ko dyan. he he napa-post tuloy ako! :P

# June 27, 2007 5:05 PM

lamia said:

I think I was really not myself yesterday! Lolz!

# June 27, 2007 10:27 PM

jokiz said:

hmmm, my comment was lost, anyway here it is again.

@willy

who cares about the registrar's name, :p. i graduated college without knowing his/her name.  i believe in your context, name should be a property of Customer. Registrar class perhaps will have a Register method which accepts a Customer instance.

All assignments to the Customer attribute will be done from the UI code

# June 28, 2007 2:17 AM

willydavidjr said:

In my part, I knew my registrar name, because he was my professor 3 times in assembly language. A multi-tasking professor! =)

# June 28, 2007 6:22 PM

ann said:

www.deed.com <a href="www.deed.com">deed</a> [url=www.deed.com]deed[/url]

# September 1, 2007 8:50 AM

zxevil163 said:

vNUqQq Hi from Russia!

# March 16, 2008 3:42 PM

zxevil163 said:

vNUqQq Hi from Russia!

# March 16, 2008 3:44 PM

zxevil163 said:

vNUqQq Hi from Russia!

# March 16, 2008 3:44 PM

zxevil172 said:

IH7MHW      Were a U from?

# March 27, 2008 6:22 PM

zxevil193 said:

Brad Paisley, brad paisley lyrics, brad paisley online lyrics, brad paisley i\x27m still a guy, brad paisley song, brad paisley new song, brad paisley fishing song, brad paisley music, brad paisley alcohol, brad paisley ticks, brad paisley whiskey lullaby,  <a href="akonringtones.webs28.com">Brad">akonringtones.webs28.com Paisley ringtones</a> brad paisley, brad paisley lyrics, brad paisley songs, brad paisley online, brad paisley letter to me, brad paisley 5th gear, brad paisley i'm still a guy lyrics, brad paisley concerts, brad paisley tabs, brad paisley myspace,  [url=akonringtones.webs28.com]Brad Paisley ringtones[/url]

# April 9, 2008 1:29 PM

cooltunesi4 said:

Brad Paisley , brad paisley lyrics , brad paisley online lyrics , brad paisley i\x27m still a guy , brad paisley song , brad paisley new song , brad paisley fishing song , brad paisley music , brad paisley alcohol , brad paisley ticks , brad paisley whiskey lullaby , <a href="http://cooltunesi.webs28.com/?">Brad">cooltunesi.webs28.com Paisley ringtones</a> brad paisley , brad paisley lyrics , brad paisley songs , brad paisley online , brad paisley letter to me , brad paisley 5th gear , brad paisley i'm still a guy lyrics , brad paisley concerts , brad paisley tabs , brad paisley myspace ,  [url=http://cooltunesi.webs28.com/?]Brad Paisley ringtones[/url]

# April 10, 2008 11:01 AM

Memmorium said:

     Good idea!

P.S. A U realy girl?

# April 11, 2008 7:22 AM

asdkjhuewr24 said:

<a href="asdkjhuewr2.webs28.com">asdkjhuewr2</a>">asdkjhuewr2.webs28.com .... You'd think that with mammoths, saber-tooth tigers, and large, screeching birds you wouldn't need much more to deliver an entertaining romp through yester-epoch, but 10,000 B.C. proves that merely having an exotic setting as your premise won't get you over a mundane plot and more mundane characters. The film begins with a blue-eyed girl coming to live with a clan of "manuk" (that's "mammoth" to you and me) hunters after her tribe is wiped out by what appear to be the bad guys from Conan the Barbarian. The tribe elder (Mona Hammond) declares that this girl is part of some prophecy while the son of the tribe's #1 hunter looks on....[url=asdkjhuewr2.webs28.com]asdkjhuewr2[/url]

<a href="asdkjhuewr2.freehostplace.com .... That little boy grows up to be D'Leh (Steven Strait), but not before his dad runs off for no apparent reason, leaving his son an outcast. The little girl grows up to be Evolet (Camilla Belle), on whom D'Leh has a pretty serious crush, but, tribes being what they are, he must take down a woolly manuk in order to win her. Seriously. D'Leh actually accomplishes this goal, albeit accidentally, but no sooner is he confessing the truth about his ill-gotten laurels than the marauders from Evolet's past come along and ruin it all by kidnapping just about everyone in the tribe, including Evolet. The rest of the movie is D'Leh, with some help from the current #1 hunter Tic'Tic (Cliff Curtis) and others, trying to track down and retrieve his beloved.....[url=asdkjhuewr2.freehostplace.com]asdkjhuewr2[/url]

# April 17, 2008 4:38 AM

asdkjhuewr24 said:

<a href="asdkjhuewr2.webs28.com">asdkjhuewr2</a>">asdkjhuewr2.webs28.com .... You'd think that with mammoths, saber-tooth tigers, and large, screeching birds you wouldn't need much more to deliver an entertaining romp through yester-epoch, but 10,000 B.C. proves that merely having an exotic setting as your premise won't get you over a mundane plot and more mundane characters. The film begins with a blue-eyed girl coming to live with a clan of "manuk" (that's "mammoth" to you and me) hunters after her tribe is wiped out by what appear to be the bad guys from Conan the Barbarian. The tribe elder (Mona Hammond) declares that this girl is part of some prophecy while the son of the tribe's #1 hunter looks on....[url=asdkjhuewr2.webs28.com]asdkjhuewr2[/url]

<a href="asdkjhuewr2.freehostplace.com .... That little boy grows up to be D'Leh (Steven Strait), but not before his dad runs off for no apparent reason, leaving his son an outcast. The little girl grows up to be Evolet (Camilla Belle), on whom D'Leh has a pretty serious crush, but, tribes being what they are, he must take down a woolly manuk in order to win her. Seriously. D'Leh actually accomplishes this goal, albeit accidentally, but no sooner is he confessing the truth about his ill-gotten laurels than the marauders from Evolet's past come along and ruin it all by kidnapping just about everyone in the tribe, including Evolet. The rest of the movie is D'Leh, with some help from the current #1 hunter Tic'Tic (Cliff Curtis) and others, trying to track down and retrieve his beloved.....[url=asdkjhuewr2.freehostplace.com]asdkjhuewr2[/url]

# April 17, 2008 4:39 AM

Вечерний макияж глаз фото said:

I not understend what U want

# May 15, 2008 12:48 AM

секс said:

давайте займемся этим!

# May 16, 2008 1:37 AM

wildquaker said:

Try putting in a Begin and Commit method for exeucting SPs. Along with these is the Rollback method. These three are very similar to the BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN keywords.

# July 6, 2008 7:18 AM

willydavidjr said:

Thanks for that wildquaker! I'll take note of it. =)

# July 6, 2008 8:26 AM

wildquaker said:

No problem. FYI Beginning transaction (SqlConnection.BeginTransaction()) is found under the SqlConnection class after its defining its constructor. Commit is under SqlTransaction (SqlTransaction.Commit()). Rollback is also under SqlTransaction. Don't forget to dispose (SqlTransaction.Dispose()) for Commit and Rollback.

# July 7, 2008 10:43 PM