Page 225 - Open Soource Technologies 304.indd

Open Source Technologies

Notes 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.

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.

Stored procedures offer developers a lot of flexibility with many features not available using
standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The
combination of these two allows us to create very powerful appliations rapidly.

220 LOVELY PROFESSIONAL UNIVERSITY