Insert Data using Parameters through Stored Procedure.

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.