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:
- First statement checks whether the database name �
MyDatabase
� already exists in the database.
- 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.
- 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)
� the connection. It is more efficient to
� ExecuteNonQuery when � data is
myConnection.Open()
myCmd.ExecuteNonQuery()
MyConnection.Close()
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
mstrConn = MY_MSDE_CONNECTION_STRING
� Connecting to MSDE
Else
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.
Dim myCmd As New SqlCommand(mySQL, myConnection)
Try
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 =
"BEGIN" & vbCrLf & _
"DROP PROCEDURE MyStoredProc" & vbCrLf & _
"END"
� 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.