Ado Net

Introduction This article explains step by step dynamic creation of a SQL Server database, tables and stored procedure using Visual Studio codename Whidbey and VB.NET. Same procedure works for C# as well.

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 this connections 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 explain show 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 the database name "MyDatabase" already exists in the Database Next step is to delete them Database, 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 explains 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 execute 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 ExceptionErrors 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. northwindConnection.Open() myCmd.ExecuteNonQuery() northwindConnection.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) Exit While

Catch exc As Exception If mstrConn = MyConnection Then ' Couldn't connect to SQL Server. NowtryMSDE. 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/StoreProcedure 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] ( [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