Working with Stored Procedures in ADO.NET ado.net tutorials

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
  


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.