CREATE/DROP stored procedure on SQL Server from VB.NET application

Home

Programming

ADO.NET

This application creates a stored procedure on a SQL Server and drops it. Result is written to a textbox.

1) Create a form
2) Add 2 button and 1 TextBox controls to the form
3) Set the following properties for the textbox [Name=txtResult, Multiline = true]
4) Set the first button properties [Name=txtCreate, Text=”CREATE PROCEDURE”]
5) Set the second button properties [Name=txtDrop, Text=”DROP PROCEDURE”]
6) Double click the button btnCreate and add the following code:

        Dim conn As New SqlConnection
        conn.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog = Northwind;Integrated Security=True"

        Try
            'create command
            Dim cmd As SqlCommand = conn.CreateCommand()

            'specify stored procedure to execute
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _
            "CREATE PROC [dbo].[Select_All_Employees] " _
            & "AS " _
            & "SELECT EmployeeID, FirstName, LastName " _
            & "FROM Employees "

            Dim cmdexec As SqlCommand = New SqlCommand(cmd.CommandText, conn)

            'execute command
            conn.Open()
            cmdexec.ExecuteNonQuery()
            txtResult.Text = "Stored Procedure: Select_All_Employees created!"
        Catch ex As Exception
            txtResult.Text &= vbCrLf & ex.Message

        End Try

7) Double click the button btnDrop and add the following code:

        Dim conn As New SqlConnection
        conn.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog = Northwind;Integrated Security=True"
        Try
            'create command
            Dim cmd As SqlCommand = conn.CreateCommand()

            'specify stored procedure to execute
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _
            "DROP PROC [dbo].[Select_All_Employees] "

            Dim cmdexec As SqlCommand = New SqlCommand(cmd.CommandText, conn)

            'execute command
            conn.Open()
            cmdexec.ExecuteNonQuery()
            txtResult.Text &= vbCrLf & "Stored Procedure: Select_All_Employees dropped!"
        Catch ex As Exception
            txtResult.Text &= vbCrLf & ex.Message
        End Try

    End Sub

Next >

About Techinforoad.com