Insert Data using Parameters through Stored Procedure.

Posted by in ADO.NET category on for Intermediate level | Views : 18180 red flag
Rating: 5 out of 5  
 1 vote(s)

The following example illustrates how to insert a new user in a users table that has a username and password field.

Insert Data using Parameters through Stored Procedure.

First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.

CREATE PROCEDURE [dbo].[InsertUser] (
    @Username varchar(50),
    @Password varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)

Note the DataType  of Varchar(50) should be same as we created in a table for a column username and password

string username = textusername.Text;

string password =txtpassword.Text;

SqlConnection conn = new SqlConnection("Data Source=localhost;Database=MyDB;Integrated Security=SSPI"); SqlCommand command = new SqlCommand("InsertUser", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username; command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password; conn.Open(); int rows = command.ExecuteNonQuery(); conn.Close();

First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Posted by: Navalemanoj0405 on: 12/29/2010 | Points: 25
I want to pass multiple parameter dynamically to command.Parameters.Add();
using loop is it possible?

Login to post response

Comment using Facebook(Author doesn't get notification)