Eternalistic Designs

Fun With GetSchemaTable

Moving Data From MS SQL 2005 Express Edition to MySQL

Aug 07, 2007
0 comments
Submitted By: Justin Stanley
Filed Under:
  • Delicious
  • Digg
  • Reddit
  • Facebook
  • Google
  • Yahoo
  • Technorati
  • stumbleupon
Share our glory:

Couple of weeks ago, I was doing some work on a personal Nerdly project on one of my laptops. That particular machine has a copy of SQL Server 2005 Express Edition already installed, and I often use that for my development needs. Has a good number of the same features that the full-blown SQL Server 2005 editions carry, but it's free to use, distribute, etc. Hard to beat that.

Anyway, using Microsoft's SQL is all well and good for most of my work projects, but this personal project is going to live on my webhost's boxes, and my cheap webhosting account doesn't include MS SQL databases. Needed a way to get the data from my MS SQL 2005 Express Edition database into my MySQL database.

The Express Edition can only create MSSQL backup files out of the box. You can download a watered down version of the Import/Export Wizard for some simple importing/exporting, and that might have worked if I could connect to the host's MySQL database remotely using an ODBC driver. Unfortunately, their server wouldn't allow connections from outside their subnet.

Way I figured, that gave me three options:

  1. I could download and install MySQL locally, move my data from MSSQL to MySQL with the aforementioned watered down Import/Export Wizard, dump the contents from the local MySQL, and import into the hosted one with PHPMyAdmin or from the command line.
  2. I could use the SQL Import/Export Wizard to export each table into a CSV, get my Find-and-Replace on, modify that output to create an INSERT statement for each line, then use PHPMyAdmin to Import the data.
  3. I could script something to do Option 2 for me.

I went with Option 3. Already had Visual Studio 2003 installed on my workstation, so I figured writing some VB.NET code to do what I needed would be pretty easy. Take into account that I didn't feel like installing yet another database on this workstation and couple it with the fact that the kind of tedium in Option 2 would have likely caused me to stab out my own eyes and, well, it's a no-brainer.

So here's the setup... I have a simple Windows form with a single Text Field called txtTableName and a button called btnExport. The procedure that handles btnExport's click event is attached, if you want it, but it's really pretty simple. You give it the name of the table, it loops through the names of the columns in that table, and in constructs a list of SQL INSERT statements for each record in that table.

To me, the coolest part about this is that you don't need to know the names of all the columns in the table in order for it to create the output file. Instead, it uses Visual Basic's GetSchemaTable method to find that info for you.

With GetSchemaTable, you can find out all sorts of good info about a table's schema, from the column names and ordinals to whether it accepts NULLs or if it's an identity column. Each column in the table you're curious about ends up being a single record in the DataTable returned by GetSchemaTable, so it's pretty easy to just loop through all those rows to find out the information you need.

In this snippet, I loop through all the rows in the DataTable just to find the column name. I then put that column name into an array that I use later to both query the table I'm interested in and generate the INSERT lines.

For example, let's say I have a NERD table with three columns: NAME, AGE, and NERDLINESS. The DataTable returned by the GetSchemaTable method on that table would have three rows, one for each column. As I loop through the rows, I find the ColumnName column and insert it's value into my array (around line 19 below).

Finding the column names is the hardest part. After that, it's just a matter of querying the original table, putting the results from each row into another array, then spitting all of it out with the appropriate SQL keywords. Line 82 shows you how the INSERT statements are created.

Just a couple of things to keep in mind if you want to use this. First, you need to change your connection string to match your own database. That's on line 21. You'll also want to change line 5 to a path that actually exists on your machine.

Finally, if I really wanted to make this cool, I'd make it a little more user-friendly. For instance, maybe having a dropdown list of all the table names instead of having the user enter one manually. Or having the option to have it pull all data from all tables into one file at once. Both those would be pretty trivial at this point, but this did what I needed it to do. Still, let me know if you found this useful at all.

   1:      Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
   2:  
   3:          'Declaring a couple of strings we'll use later.
   4:  
   5:          Dim strOutputPath As String = "c:\nerds\"
   6:          Dim strSelectQuery As String = "SELECT * FROM "
   7:  
   8:  
   9:          'Require the user to enter a name of some kind.
  10:  
  11:          If txtTableName.Text = "" Then
  12:              MessageBox.Show("Please enter table name.")
  13:              Exit Sub
  14:          Else
  15:              strSelectQuery &= txtTableName.Text 'Finish our SELECT statement to use the tablename specified.
  16:              strOutputPath &= txtTableName.Text & ".sql"
  17:          End If
  18:  
  19:          'Create our SQLConnection.  I used a locally hosted SQL 2005 Express DB.  Your results will vary.
  20:          Dim sqlConn As New SqlConnection
  21:          sqlConn.ConnectionString = "workstation id=(local);packet size=4096;integrated security=SSPI;data source=nerdcomputer;" & _
  22:          "persist security info=False;initial catalog=nerddb"
  23:  
  24:          'Create our SQLCommand now that we have the query built.
  25:          Dim sqlSelectCommand As New SqlCommand(strSelectQuery, sqlConn)
  26:  
  27:          sqlConn.Open()
  28:  
  29:          Try
  30:              'First need to find out all the column names.  Executing this reader to get
  31:              'that info and put the resulting data into a DataTable.
  32:              Dim dtrReader As SqlDataReader = sqlSelectCommand.ExecuteReader(CommandBehavior.KeyInfo)
  33:              Dim tblSchema As DataTable = dtrReader.GetSchemaTable
  34:              Dim rowSchema As DataRow
  35:  
  36:  
  37:              'Each column in our table is represented as a row returned by GetSchemaTable.
  38:              'The array holding the column names is the same length as the number of rows in the table.
  39:              Dim intRowCount As Integer = tblSchema.Rows.Count
  40:              Dim strColumnNamesArray(intRowCount - 1) As String
  41:              Dim strColumnNames As String
  42:              Dim intColumnCounter As Integer
  43:              Dim intRowsWritten As Integer
  44:  
  45:  
  46:              'Putting the names of all the columns into an array for future use.
  47:              For Each rowSchema In tblSchema.Rows
  48:                  strColumnNamesArray(intColumnCounter) = rowSchema("ColumnName").ToString
  49:                  intColumnCounter += 1
  50:              Next
  51:              dtrReader.Close() 'Closing the reader used to get the schema info.
  52:  
  53:              'Joining the array into one string to construct our output line.
  54:              strColumnNames = Join(strColumnNamesArray, ",")
  55:  
  56:              'Now that we have the names of the columns, need the values for each row.
  57:              Dim strColumnNameValue As String
  58:              Dim strColumnOutput As String
  59:  
  60:              'Opening our reader, this time looking for the data in the table rather than the
  61:              'table schema.
  62:              dtrReader = sqlSelectCommand.ExecuteReader
  63:  
  64:              'Getting ready to write to our output file.
  65:              Dim wtrOutput As StreamWriter = New StreamWriter(strOutputPath)
  66:              While dtrReader.Read
  67:  
  68:                  'Building another part of our output string.
  69:                  'In each returned row, we loop through the column names and add the associated
  70:                  'value to our string.
  71:                  For Each strColumnNameValue In strColumnNamesArray
  72:                      strColumnOutput &= "'" & dtrReader.Item(strColumnNameValue).ToString.Trim & "',"
  73:                  Next
  74:  
  75:                  'The above loop adds a final comma.  Stripping that off.
  76:                  strColumnOutput = strColumnOutput.Substring(0, strColumnOutput.Length - 1)
  77:  
  78:                  'Constructing the actual INSERT query that will be written to the output file.
  79:                  'strColumnNames is the comma separated column names we made earlier.
  80:                  'strColumOutput is the string with the values contained in those columns.
  81:  
  82:                  Dim strOutputLine As String = "INSERT INTO " & txtTableName.Text & " " _
  83:                  & "(" & strColumnNames & ") " _
  84:                  & "VALUES (" & strColumnOutput & ");"
  85:  
  86:                  'Write that INSERT statement to our text file.
  87:                  wtrOutput.WriteLine(strOutputLine)
  88:  
  89:                  intRowsWritten += 1
  90:  
  91:                  'Clear the string names.  If skipped, additional lines are appended to
  92:                  'the previous line instead of being a line of their own.
  93:                  strColumnOutput = ""
  94:                  strOutputLine = ""
  95:  
  96:              End While
  97:  
  98:              'Close our file and our SQL connections.
  99:              wtrOutput.Close()
 100:              sqlConn.Close()
 101:  
 102:              'I like to know when it's done.
 103:              MessageBox.Show(intRowsWritten.ToString & " rows were written.")
 104:  
 105:  
 106:          Catch ex As Exception
 107:              MessageBox.Show(ex.Message)
 108:  
 109:          End Try
 110:  
 111:      End Sub



References:

MSDN: SQLDataReader.GetSchemaTable Method
MSDN: String.Join method
  • Delicious
  • Digg
  • Reddit
  • Facebook
  • Google
  • Yahoo
  • Technorati
  • stumbleupon
Make people smarter:

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <div><span><br /><blockquote><table><thead><th><tr><td><form><input><h1><h2> <h3> <h4> <h5> <h6> <img> <p> <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd><i>
  • Lines and paragraphs break automatically.
  • You may post block code using <blockcode [type="language"]>...</blockcode> tags. You may also post inline code using <code [type="language"]>...</code> tags.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.

More information about formatting options