July 16, 2008

Inserting data using parameters to stored procedures

Using other SQL statements such as INSERT, UPDATE or DELETE follow the same 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)

string username = ... // get username from user
string password = ... // get password from user
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.
if (rows == 1)
{
MessageBox.Show("Create new user SUCCESS!");
}
else
{
MessageBox.Show("Create new user FAILED!");
}

We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.
DELETE FROM Products WHERE ProductID > 50

This will delete all products whose product ID is greater than 50 and will return the number of rows deleted.

No comments: