Building SQL Tables at Runtime
Firing DDL Statements with ADO.NET
One of the more common questions I see on the newsgroups is whether or not it's possible to issueData Definition Language [DDL] queries with ADO.NET and if so, how to do it. In this article, I'm going to show you how to do it with various scenarios. In the first situation, we'll use a Hard Coded Table Definition and build a Table in SQL Server 2000 with it. Then, we'll allow the user to specify a .sql file , we'll read in the contents, and build the table from there. Finally, we'll use .NET's Embedded Resources to load a SQL Definition and create a file from it.
A few of the concepts I'm going to illustrate are using Reflection to enumerate your assembly's resources, using an App.config file via System.Configuration to store application settings, using an Enum to make your code easier to use and reading from a text file. I'm going to walk through a few things before getting into the project because dealing with them in isolation should help you to understand how you can put them all together into a useful project.
Configuration Files:
You have many options for storing things like Connection Strings for databases or user settings. In the past, it was common to use an .ini file or write to the registry. However, writing to the registry is something that you can't take for granted these days. There's too much spyware out there that hides out in the registry for responsible network administrators to allow unhindered access to the registry. So .NET came up with the concept of .Config files. They are essentiallly XML files that allow you to add custom tags/settings and provide a convenient method of accessing your settings. Since this application is going to ultimately fire a DDL command against the SQL Server 2000 database, we'll need a connection string and I chose to store it in a .CONFIG File.
So, the first thing I did was select Project, Add New Item the Visual Studio .NET menu. Then I chose Application Configuration. From there, VS automatically added a file named app.config to my project. When the project compiles, a file with the same name as my executable with ".config" will be built in the same directory as the executable. Although you can't edit .config files from within your program, you can open them in Notepad/Word etc and edit them there. This is what gives them much of their flexibility. Anyway, I added the following tag to my config file so the whole file looks like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="connectString" value="integrated security=SSPI;data source=xxxxx;initeal catalog=DBName" />
appSettings>
configuration> |
The root node "<?xml version="1.0" encoding="utf-8" ?> and root tags "<configuration>" were added for me by Visual Studio .NET. I added in the appSettings tag myself. I added a key named 'connectString' where you should specify the connection string that you want to use. I have a module level variable in my form named cn which is a SqlConnection object (this could just as easily be ODBC, Oracle or any other provider). When I want to use it, I instantiate it using the Constructor which takes a connection string as a parameter. Then I use the Configuration class to extract my connection string:
cn = New SqlConnection(ConfigurationSettings.AppSettings.Item("connectString").ToString)
'the last part will translate into ="integrated security=SSPI;data source=xxxxx;initeal catalog=DBName" |
So now we have a valid connection string and a convenient way to store it.
Embedded Resources:
Embedded Resources provide a way to include files in your application without having to maintain them separately. They are a very convenient way to store images for instance, that your application will use because you don't have to place them in a separate folder, don't have to worry about pointing registry settings to them and don't have to worry about people overwriting or deleting them. To include an embedded resource, all you need to do is (From Visual Studio .NET) select Project, Add Existing Item, the browse to the file(s) you want to add and select Open. You will then see the file(s) you selected added to your list of project items. Now, in order to use them you'll first need to do one thing. Click on the item and the select the Properties box. From there, the first item you'll see in the Property Grid is "Build Action". You must set this to Embedded Resource before you can effectively use it as an Embedded Resource (somehow that shouldn't come as a big shocker). Once this is done, you can reference it using Reflection . So, I added a file called TableTest.sql to my project, changed its Build Action to Embedded Resource and now I'm ready to use it. There are a few ways to read a given file, but one of the more common is to access it via a stream object. Here's an exapmle of how to access it:
| System.Reflection.Assembly.GetEntryAssembly.GetManifestResourceStream(ResourceName)) |
In this instance, ResourceName is passed into a function that I created and we've retrieved it from another application of Reflection which I'll get to in a moment. The way you reference it is AssemblyName.FileName so in this case, assuming our application was called WindowsApplication6 (yes I know, not very original) ResourceName would have to be "WindowsApplication6.TestTable.sql". You can hard code this directly, but in this application, the user will have the option to get the table definition from an Embedded Resource, so I have a UI tool which will list all embedded resources for the user (actually, I filter them out so only files ending in .sql are included, but you don't have to use any filter at all or you could use one specific to your app).
Now, assuming that the resource is WindowsApplication6.TestTable.sql, here's the function that would extract the Create Table script for us:
Public Function GetContentsFromEmbeddedResource(ByVal ResourceName As String) As String
Dim FileReader As StreamReader
Dim FileContents As String
FileReader = New StreamReader(System.Reflection.Assembly.GetEntryAssembly.GetManifestResourceStream(ResourceName))
Try
FileContents = FileReader.ReadToEnd
Catch ex As IOException
Debug.Assert(False, ex.ToString)
FileContents = String.Empty
Finally
FileReader.Close()
End Try
Return FileContents
End Function |
So all we are doing is creating a StreamReader object, and reading in the contents of the embedded resource into it. I declared a string object called FileContents to hold the contents of the script. (It goes without saying that if the script in the file isn't valid, we aren't going to be able to create the table later on but we'll still be able to get this far).
Now, if it were up to the user to know and remember the names of all of the embedded resources in the application, we'd be in trouble (in my case, we'd probably be in trouble if it were up to the programmer to remember this). So, when the user hits the Embedded Resource button, we'll pop up a form with a list box and display each of the embedded resources ending with a .sql extension (Remember, this is just my way of doing it, you don't need the file to end in .sql or anything else, just make sure you adjust your filters to match it). I set the Ok and Cancel buttons on this form to the form's Accept and Cancel buttons respectively. I also declare a property of the form so if the user selects something, I can set the property and reference it elsewhere. Here's the code for the property:
Private _SelectedResource As String
Public Property SelectedResource() As String
Get
Return _SelectedResource
End Get
Set(ByVal Value As String)
_SelectedResource = Value
End Set
End Property
|
When the form loads, I'm going to enumerate the embedded resources and list them in a ListBox . I have used the Path Class to extract the file extension which I'm checking as my filter. You don't have to use the Path class but it makes things easier in many instances
Private Sub Resources_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'This will enumerate all of the resources in the assmebly. We'll load them each into
' the listbox. We will check the extension of each item and only load it if it's
' a SQL File. If your SQL File will end in some other extension or not have an extension
' you should modify this as appropriate
Dim assem As [Assembly] = Me.GetType.Assembly
Dim ResourceName As String
For Each ResourceName In assem.GetManifestResourceNames
'Even though this is an embedded resource, we can still
'use the GetExtension method of the Path Class to
'extract the extensino
If Path.GetExtension(ResourceName).Equals(".sql") Then
lb.Items.Add(ResourceName)
End If
Next
End Sub
|
Ok, so when the form loads, we'll walk through the Assembly's ManifestResourceNames and if they happend to end in .sql, they get loaded into the listbox. Now, if they user makes a selection and hits OK, the form's SelectedResource will be set to the item in the listbox. Just to be safe, we'll verify that the SelectedIndex property of the listbox is greater than -1 (which means something has been selected). Since btnClose is set as the form's Cancel Button, we don't need to add any code to it, however if the user selects Ok then we want to set the property and return control to the caller:
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
If lb.SelectedIndex > -1 Then
Me.SelectedResource = lb.SelectedItem.ToString
End If
End Sub |
At this point, we've pretty much set up all of the groundwork for the application to work. So far, we have most of the code listed, but there are two other methods we've ingored. If the user selects the Hard Coded value button, I've got a code snippet that uses a StringBuilder object to build a Create Table Statement and then return a String object.
Public Function GetSqlStatement() As String
'This is just the contents of the SQL File that I used for this project
'If you want to have a SQL Definition file built for you, simply go to your
'Database in Enterprise Manager, select All Tasks and Generate SQL Script.
'There are many options like whether or not to generate Drop statements
'that's up to you if you want to include them. You can also go to Query Analyzer
'Go to the table, select Edit and the table def will appear. From there, you can
'just save the file.
Dim sb As New System.Text.StringBuilder
sb.Append("CREATE TABLE [dbo].[Tbl_FacilitiesTest] (")
sb.Append("[Facility] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,")
sb.Append("[Facility_Initials] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,")
sb.Append("[Dept_Number] [int] NOT NULL ,")
sb.Append("[BillingType] [smallint] NOT NULL ,")
sb.Append("[Dictation_Code] [tinyint] NULL ")
sb.Append(") ON [PRIMARY]")
Return sb.ToString
End Function |
The only other function we need to cover now is letting the user specify a file and use the read it and execute it. I used a standard OpenFileDialog box and since we only wanted to show files ending in .sql, I set its Filter property to the following:
| od.Filter = "Sql Files (*.sql)|*.sql" |
Now, all I do is show the OpenFileDialog, and if the DialogResult isn't Cancel, then I'll use the FileName property of the Dialog in the same way I used the SelectedResource property of my EmbeddedResourceDialog. Once I get the filename, I pass it to my function which returns the Sql Script for a given file. For the sake of simplicity, I'm assuming that file is valid if it ends in Sql. In a professional application this would be unacceptable, and I would probably use a Regular Expression to perform a cursory validation of the SQL Commands. However that would be a project into itself so I'm being lazy and assuming we have a good script:
Public Function GetFileContentsFrom(ByVal FullPath As String) As String
Dim FileContents As String
Dim FileReader As StreamReader
Try
FileReader = New StreamReader(FullPath)
FileContents = FileReader.ReadToEnd()
Return FileContents
Catch Ex As IOException
Debug.Assert(False, Ex.ToString)
Return String.Empty
Finally
FileReader.Close()
End Try
End Function |
This works essentially the same way as GetFileFromEmbeddedResource and in practice, I would have come up with an overloaded method that could take a stream or a file instead of treating them seperately as I did here. Overloading is a rather basic concept so I opted for a more verbose approach for the sake of illustration.
The only other thing that I have to cover before wrapping everything together is the use of an Enumeration. Enums have many benefits including: 1) They are value types 2) They have Intellisense support. If you look through most of Visual Studio, enums are used liberally and they make things much easier to read even if Intellisense wasn't supported. It's a lot easier to remember DialogResult.Ok then to remember that Ok maps to 12 or some other number. But, if you declare an enum outside of your class, it can be seen all over your project provided it's accessibilty level is Public. With that in mind, you can specify your enum in a function definition and everyttime you make a call to that function, Intellisense will appear and help you fill in the necessary pieces. Anyway, I had three basic choices in the project, creating a table from HardCoded values, from a File in the filesystem or from an EmbeddedResource. So I created an enum which corresponds to these choices:
Public Enum TargetType As Integer
HardCodedValue
FilePath
EmbeddedResource
End Enum |
With this in place, we're ready to finish up. I have one basic method BuildTable which is called from any of the three buttons. Like I mentioned earlier, I should probably have used overloading and reduced the code base by a few lines, but that doesn't really affect the intent of this article:
Public Sub BuildTable(ByVal FilePath As String, ByVal Target As TargetType)
Dim sql As String
Dim KeepGoing As Boolean = False
'Below is the Appsetting Tag that's stored in the App.Config file. To do this
' all I did was go to Project, Add New Item, then chose Application Configurate.
'By Default, it named the file App.Config and included it in the project.
'Thereafter, I just Imported System.Configuration and I can reference the
'connection string below by using the AppSettings collection.
cn = New SqlConnection(ConfigurationSettings.AppSettings.Item("connectString").ToString)
'<appSettings>
' <add key="connectString" value="integrated security=SSPI;data source=xx;initial catalog=DBName" />
'
Select Case Target
Case TargetType.FilePath
sql = GetFileContentsFrom(FilePath)
Case TargetType.HardCodedValue
sql = GetSqlStatement()
Case TargetType.EmbeddedResource
sql = GetContentsFromEmbeddedResource(FilePath)
End Select
Dim cmd As New SqlCommand(sql, cn)
Try
If cn.State <> ConnectionState.Open Then cn.Open()
KeepGoing = True
Catch ex As SqlException
Debug.Assert(False, ex.ToString)
End Try
If Not KeepGoing Then
'Alert the user and ask them what they want to do, we cna't
'go any further until we have a good connection. This should
'be in a while loop but I'm lazy this morning..
Else
Try
Dim i As Integer = cmd.ExecuteNonQuery
Catch ex As SqlException
Debug.Assert(False, ex.ToString)
Finally
If cn.State <> ConnectionState.Closed Then cn.Close()
End Try
cn.Dispose()
cmd.Dispose()
End If
End Sub |
Now, behind each of the buttons I include the following calls:
Private Sub btnHardCoded_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHardCoded.Click
BuildTable(String.Empty, TargetType.HardCodedValue)
End Sub |
Private Sub btnFromResource_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFromResource.Click
Dim ResourceForm As New Resources
ResourceForm.ShowDialog()
BuildTable(ResourceForm.SelectedResource, TargetType.EmbeddedResource)
End Sub |
Private Sub btnFromFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFromFile.Click
'Since Dialogs return a dialog result, I called ShowDialog directly within a Select Case statement
'In this instance, a OpenFileDialog can only return Ok or Cancel so I could have gotten away with
'using an If/Else statmement. However, you may want to roll your own Dialogs sometime and this is
'a clean and efficient way of handling this situation when you may have mutltiple possible return values
Select Case od.ShowDialog
Case DialogResult.Cancel
Exit Sub
Case Else
BuildTable(od.FileName, TargetType.FilePath)
End Select
End Sub |
Now the funny part of this whole thing is that to actually create a Table from a SQL Script, you really only need 2 lines of code other then declarations and exception handlers. That's right, Connection.Open and Command.ExecuteNonQuery is all that it takes provided you've set all of the appropriate properties in your construtors. However, the whole point of Object Oriented Programming is code reuse. And in order to reuse code, you need loose coupling. Hard Coding values is the antithesis of loose coupling. Moreover, if your routine can only support one set of assumptions, it won't be reusable in any situation where other assumptions are being made. As such, I wrote this with three possible scenarios. You could take it up another level and put in support for reading from an XML file or a Web Service.
In conclusion, this article walked you through creating a Database table with ADO.NET. Although that's the ultimate point of the article, the code to do it is trivial. Moreover, just as we used DDL to create a table, you can fire System Stored Procedures, DBCC commands etc from ADO.NET, so the limitations are only those put on you by imagination and user permissions. To accomplish this task we walked through using Configuration files and Reflection. It wouldn't be to hard to build on this class to provide support for passing in a datatable and having your class build the DDL statement. If I have some free time, I'll add another method to it.