Article 1 - Dynamic creation of an SQL Server database, tables and stored procedure through Whidbey (or VB.NET)

Introduction

This article explains step by step dynamic creation of a SQL Server database, tables and stored procedure through Whidbey (or VB.NET).

Steps to create the Database Application

1. Database Connection Classes and its Parameters

The SqlConnection class allows you to communicate with SQL Server which is implemented through importing the class System.Data.SqlClient, which is shown below:

Imports System.Data.SqlClient 

The SqlConnection constructor accepts a connection string as an argument. This connection string uses Integrated Security, which means that you must have a login in SQL Server, or be part of the Administrators group for this to work.

The SqlConnection class can be declared as shown below:

Dim myDBSQLConnection As New SqlConnection(MyConnection)

You need to be initialize connection strings for connecting to the database for both SQL connection and MSDE connection. This way if SQL DB connection fails, it will look through the MSDE connection to connect the database. The line of code for declaration of these connections are as shown below:

MyConnection =  "Server=localhost;" & _
                  "DataBase=;" & _
                  "Integrated Security=SSPI"

Protected Const MY_MSDE_CONNECTION_STRING As String = _
                 "Server=(local)\;" & _
                 "DataBase=;" & _
                 "Integrated Security=SSPI"

2. Declare and Initialize the SqlCommand Object

A SqlCommand object is used to execute the SQL commands. The following lines of code explains how to declare a command object called “SqlCommand” in .NET environment.

Dim cmd As New SqlCommand(strSQL, MyConnection)

You can also declare mySQL string variable to declare the database creation DDL statements, which you can see below:

Dim mySQL As String = _
      "IF EXISTS (" & _
      "SELECT * " & _
      "FROM master..sysdatabases " & _
      "WHERE Name = 'MyDatabase')" & vbCrLf & _
      "DROP DATABASE MyDatabase " & vbCrLf & _
      "CREATE DATABASE MyDatabase "

The above SQL Statement do the following operations:

1. First statement checks whether the database name “MyDatabase” already exists in the database.
2. Next step is to delete the myDatabase, if it already exists. If it doesn’t find any database, still the command executes with no database.
3. Third step is to create a new database called “myDatabase”.

3. Open the Connection, Execute the query for creating the database

Once you successfully create the SqlCommand, next task is to open the connection with the connection string and mySQL SQL parameter string, execute the command and close the connection.

The following lines of code explain how to open the connection, execute the command and close the database connection from SQL Server database.

MyConnection.Open()
cmd.ExecuteNonQuery()
MyConnection.Close()

Please note that the ExecuteNonQuery is used here for executing the database because it makes sense to use this command for more efficient to ExecuteNonQuery when data is not being returned.

You can use ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables); or to change the data in a database, without using a DataSet, by executing UPDATE, INSERT, or DELETE statements.

You can also use ExecuteNonQuery to execute multiple SQL statements if the underlying ODBC driver supports this functionality. In this case, the return value is the number of rows affected by all statements in the command.

Although ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

4. Catching SQL Exception Errors

Catching a SQL exception error is another way to avoid the SQL Server connection problems. The try … Catch … Exception statement provides a mechanism for catching SQL exceptions that occur during execution of a block. Furthermore, the try statement provides the ability to specify a block of code that is always executed when control leaves the try statement. This way, we can track the associate SQL exception errors and thus provides to fix the problem of SQL database connection.

The following line of code explains about this.

  Try

      Dim myCmd As New SqlCommand(strSQL, MyConnection)


       ' Open the connection, execute the command, and close
       ‘ the connection. It is more efficient to
       ‘ ExecuteNonQuery when ‘ data is
       ' not being returned.

      myConnection.Open()
      myCmd.ExecuteNonQuery()
      MyConnection.Close()

      ' Data has been successfully submitted

      MessageBox.Show("New Database has been ' successfully created !", _
                      " Database Creation Completed", MessageBoxButtons.OK, _
                      MessageBoxIcon.Information)
 Catch sqlExc As SqlException
      MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
      MessageBoxButtons.OK, MessageBoxIcon.Error)

 Catch exc As Exception
        If mstrConn = MyConnection Then
            ' Couldn't connect to SQL Server.  Now try MSDE.
            mstrConn = MY_MSDE_CONNECTION_STRING
            ‘ Connecting to MSDE
        Else
            ' Unable to connect to SQL Server or MSDE
            MessageBox.Show("SQL Or MSDE Connection Failed", _
                 "Connection Failed!", MessageBoxButtons.OK,
                 MessageBoxIcon.Error)
            End
        End If
End Try

5. Creating Database Tables/Store Procedure

Once you successfully opens the connection, next step is to create database tables, stored procedures of creating database views.

' A SqlCommand object is used to execute the SQL commands.
Dim myCmd As New SqlCommand(mySQL, myConnection)

Try
  ' Creating tables
  cmd.CommandText = "USE MyDatabase" & vbCrLf & _
    "if exists (select * from dbo.sysobjects " & _
    "where id = object_id(N'[dbo].[MyTable]') " & _
    " and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & vbcrlf & _
    "drop table [dbo].[Customer] " & vbcrlf & "GO " & vbcrlf & _
    "CREATE TABLE [dbo].[Customer] ( " & vbcrlf & _
    "[CustomerID] [int] IDENTITY (1, 1) NOT NULL ," & vbcrlf & _
    "[NameLast] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,," & _
    vbcrlf & _
    "[NameFirst] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,," & _
    vbcrlf & _
    "[NameMiddle] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,," &_
    vbcrlf & _
    "[Prefix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
    vbcrlf & _
    "[Suffix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
    vbcrlf & _
    "[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
    vbcrlf & _
    "[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"" & _
    vbcrlf & _
    "[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , " & _
    vbcrlf & _
    "[State] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , " & _
    vbcrlf & _
    "[Zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , " & _
    vbcrlf & _
    "[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) " & _
    vbcrlf & _
    "ON [PRIMARY] " & vbcrlf & _
    "GO"

  ' Open the connection, execute the command, and close ‘ the connection.
  ' It is more efficient to ExecuteNonQuery when data ‘
  ‘ is not being returned.


  myConnection.Open() 
  mycmd.ExecuteNonQuery()
  myConnection.close()

  MessageBox.Show("Database Table ‘MyTable" & _
         "successfully created.", " Creation" & _ "Status", _

  MessageBoxButtons.OK,MessageBoxIcon.Information)

Catch sqlExc As SqlException
  MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
     MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

‘ Creating stored procedures
Try
  cmd.CommandText = "USE MyDatabase" & vbCrLf & _
     "IF EXISTS (" & _
     "SELECT * " & _
     "FROM MyDatabase.dbo.sysobjects " & _
     "WHERE Name = ‘MyStoredProc’ " & _
     "AND TYPE = 'p')" & vbCrLf & _
     "BEGIN" & vbCrLf & _
     "DROP PROCEDURE MyStoredProc" & vbCrLf & _
     "END"

  ' Open the connection, execute the command, and close ‘ the connection.
  ' It is more efficient to ExecuteNonQuery when data ‘
  ‘ is not being returned.

  myConnection.Open()
  mycmd.ExecuteNonQuery()
  myConnection.close()

  MessageBox.Show("Stored Procedure ‘MyStoredProc" & _
          "successfully created.", " Creation" & _ "Status", _

  MessageBoxButtons.OK,MessageBoxIcon.Information)

Catch sqlExc As SqlException
  MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
  MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Requirements

• Microsoft Visual Studio.Whidbey Ver 8.0 Or Microsoft Visual Studio .NET Professional or greater.
• Windows 2000 or Windows XP.

Summary

From this article, you found that how to open a new database connection using SQL Server database. Also, it focused on creating dynamic database, tables and stored procedure in SQL Server. The usage of exception handling mechanism really helps to track the custom sqlException error.

My next article is going to explain how it will then queried to fill a Dataset and to a DataGrid for display after the dynamic creation is complete.

Thank You

You can contact me at here.