Friday, February 29, 2008

Insert Stored Procedure using C#

If you are fresher to C# coding and SQL Procedure,this article will be a great useful for you to enchance your knowledge and getting ideas on how a simple application being developed.Source code for this application available for Code behind method as well as straight forward method.

Step #1,

Start your SQL SERVER on your local computer.
Goto -> Enterprise Manager - Locate your desired database or create new database

Create New Table and give the name "Secure "
add columns username and password.make it username as primary key.
Now you need to create StoredProcedure for this table for inserting record through C#.
Click on Database node,it will expand and shows its object,right click on 4th Object,storedprocedure - > New StoredProcedure -it will open up the Pop up window,type it exactly like this below

@username varchar(12),
@password varchar(10)
Insert into Secure values(@username,@password)

and make sure all syntax are spelled correctly by clicking"Check syntax " button.

Step # 2,
In visual studio open your existing website or create new one.
Add a new form and make sure the language for the page will be "C#"

Add 2 textboxes and naming them user_name and pass_word and a button
Double click on this button,on click event handler write these code in order to execute the StoredProcedure for inserting the user input in to your database.

protected void Button1_Click(object sender, EventArgs e)
SqlConnection sc = new SqlConnection("Data Source=(Local);Initial Catalog=Login;user id=sa;pwd=yourpassword");
SqlCommand smd = new SqlCommand("InsertSP", sc);
smd.CommandType = CommandType.StoredProcedure;
smd.Parameters.Add("@username", SqlDbType.VarChar).Value = Request.Form["user_name"];
smd.Parameters.Add("@password", SqlDbType.VarChar).Value = Request.Form["pass_word"];
int r = smd.ExecuteNonQuery();
if (r == 1)
Literal1.Visible = true;
Literal1.Text = "Successfully added!!";
Literal1.Visible = true;
Literal1.Text = "user name already exist !";