Working with Stored Procedures in ADO.NET
Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013
This lesson describes how to use Stored Procedures in ADO.NET
Instead of hard-coding the SQL statements in the code, it is an advisable practice to use stored procedures, that are a collection of SQL statements which allows you to do a task repeatedly. A stored procedure simply replaces the hard-coded statements and provide several advantages including better performance, increased scalability, higher productivity, better security and ease of use.
Imagine you have the following SQL query:
SELECT ID FROM aspnet_Users WHERE Username = @Username
If you pass that to SQL Server using ExecuteReader on SqlCommand, SQL Server will have to compile the code before it can run it, in much the same way that VB.NET application has to be compiled before it can be executed. Well this compilation takes up SQL Server's time, so it is easy to conclude that if you can reduce the amount of compilation that SQL Server has to do, database performance would increase.
Meaning, you create a procedure, store it in the database, and because the procedure is recognized and understood ahead of time, it can be compiled ahead of time and ready for use in your application.
Another important benefit of using stored procedures is that it is generally safer, because without stored procedures, there is always the temptation to build the SQL statement by concatenating strings. It is very dangerous, particularly if some of those strings are user generated so the resulting SQL is invalid or malicious. For example, if you have a text box where a user could type in username, that you then concatenate into a query, using code such as:
Dim query As String = _ "SELECT ID FROM Users WHERE Username LIKE '%" + Username.Text + "%'"
Though this looks innocent enough, you could get into trouble very easily if the user enters something like:
Acme;DELETE * FROM Users
In this case the Acme would complete the intended SQL statement. However the semicolon indicated that a new sql statement is coming, so your database will get the statment DELETE * FROM Users.
Using stored procedures can help prevent an attack like this from happening.
To create a stored procedure, you can use the tools in Visual Studio .NET or you can use the tools in SQL Server Management Studio.
This example creates a stored procedure that returns columns from the Users table for a given ID. The SQL to do this looks like the following:
CREATE PROCEDURE ProcedureName ( -- Add the parameters for the stored procedure here @UserID int ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT UserName, Password, Email FROM aspnet_Users WHERE UserId = @UserID END GO
As you see when using stored procedures, you typically have to be able to provide parameters into the stored procedure and use them from within code.
Dim ConnectionString As String = _ "Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True" Dim connection As New SqlConnection(ConnectionString) Try connection.Open() ' declare and create a sql command Dim command As SqlCommand = connection.CreateCommand ' define how the command text will be interpreted command.CommandType = CommandType.StoredProcedure ' set the sql statements command.CommandText = "ProcedureName" ' add a SqlParameter command.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = Convert.ToInt32(lblID.Text) Dim reader As SqlDataReader = command.ExecuteReader ' check if such user exists If reader.HasRows Then ' such user exists While reader.Read Me.Username.Text = reader("UserName").ToString End While End If ' release the resources reader.Close() command.Dispose() Catch ex As Exception Console.Write(ex.Message) Finally connection.Close() End Try