Click here to Skip to main content
15,902,032 members
Articles / Desktop Programming / MFC
Article

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

Rate me:
Please Sign up or sign in to vote.
1.75/5 (15 votes)
25 Apr 20044 min read 89.9K   42   7
This article explains step by step 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:

VB
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:

VB
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:

VB
MyConnection =  "Server=localhost;" & _
                  "DataBase=;" & _
                  "Integrated Security=SSPI"
VB
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.

VB
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:

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

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

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

VB
' 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
Binoy is a software developer of Information Technology Division in Memphis,TN.

Comments and Discussions

 
GeneralThis code is crap Pin
stapes4-Aug-08 7:00
stapes4-Aug-08 7:00 
QuestionProblem with Odbc objects Pin
Jaime14117325-Jul-06 3:57
Jaime14117325-Jul-06 3:57 
GeneralGrain of Salt Pin
granadaCoder10-Sep-04 3:13
granadaCoder10-Sep-04 3:13 
Any reader (especially new developers out there) should take this article with a grain of salt.

As in. I think the author had a reasonable principel he's putting forth, but the code samples are a little flakey.

Also. There is an issue when you try to use the "GO" statement in the dynamically created tsql code.

Go to google.com and type in
CREATE "must be the first statement in a query batch"
(just as I wrote it there)
and you will find there is issues when you are trying to do several things seperated by the GO word.
As in, when you use query analyser, having multiple batches seperated by the "GO" phrase.
When you try to do this using the method listed here, you will have issues.

Try something simple (for the above) like

mysql = "declare @s varchar(2)"
mysql += vbcrlf & "GO"
mysql += vbcrlf & "declare @v varchar(2)"
mysql += vbcrlf & "GO"
mysql += vbcrlf & "declare @i int"
mysql += vbcrlf & "GO"

then try to run that sql against the method above.

It will fail.

And I agree with the previous comment that this has nothing to do with Whidbey specifically. ???



My warning is that you shouldn't see the above method as a "cure all", because you're going to run into some issues with the CREATE command, and the "GO" word.

..
GeneralRe: Grain of Salt Pin
granadaCoder10-Sep-04 3:33
granadaCoder10-Sep-04 3:33 
GeneralUsing Schemas to create all the t-sql code. Pin
TerryRus17-Jun-04 22:51
TerryRus17-Jun-04 22:51 
Generalthis article has nothing to do with whidbey Pin
Anonymous11-Jun-04 5:04
Anonymous11-Jun-04 5:04 
GeneralRe: this article has nothing to do with whidbey Pin
Anonymous12-Jun-04 19:51
Anonymous12-Jun-04 19:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.