Insert Data in SQL2005 table using ASPnet

[mimitan]

Hi everyone,

I have a form page in ASPnet when the Submit button is clicked, a stored procedure in SQL is called and the pararmeters of data in this form (such as first and Last name and so on) will be passed and inserted into a SQL table of a UserInfo DB.

Here is a segment of code in the Button_click procedure:

Session("date") = DateText.Text
Session("fname") = FirstNameText.Text
Session("lname") = LastNameText.Text




Dim Conn As String = "Data Source=SERVER1;Initial Catalog=UserInfo;Persist Security Info=True;User ID=sa;Password=password"
Dim sysobj As New System.Data.SqlClient.SqlConnection(Conn)
Dim cmd As New System.Data.SqlClient.SqlCommand("InsertInto_tblUserInfo", sysobj)

cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@date", System.Data.SqlDbType.DateTime))
cmd.Parameters("@date").Value = Session("date")
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar))
cmd.Parameters("@fname").Value = Session("fname")
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lname", System.Data.SqlDbType.VarChar))
cmd.Parameters("@lname").Value = Session("lname")
.
.
.
sysobj.Open()
cmd.ExecuteNonQuery()
sysobj.Close()
End Sub
This procedure works but my question is: Is there a better way or a faster way to insert or update data into a SQL2005 DB table?
Thanks for your help

 

[jmeckley]

Is there a better way or a faster way to insert or update data into a SQL2005 DB table?

At a minimum I would move the connection string to the web.config. then use ConfigurationManager to get the value from the web.config. this will prevent your code from being littered with instances of connection strings.

i would also centralize the creation of the database connection and command objects using the factory pattern.

it's also a best practice to dispose of connections when you are done with them.

DateTime date;
using(IDbConnection connection = MyConnectionFactory.Create())
{
   IDbCommand command = connection.CreateCommand();
   command.Text = "InsertInto_tblUserInfo";

   IDbParameter parameter = command.CreateParameter("date");
   parameter.Value = date;
   command.Parameters.Add(parameter);

   command.ExecuteNonQuery();
}

In shis example MyConnectionFactory creates and opens the connection. Note, i am programming to interfaces not concreate objects. The using statement will automatically close/dispose of the connection (even if an exception is thrown).

As for faster. Premature optimization can destroy an otherwise rich domain model. Most of the optimization related to database interaction is done on the database; indexes, FK, PK, clusting. Forum962 would have more info on that.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AlexCuse]

You should also never have an application logging into your server as "sa".

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page