jump to navigation

SQL Database Publishing to ISPs June 14, 2007

Posted by codinglifestyle in ASP.NET, C#, Visual Studio 2010.
Tags: , , , ,
trackback

I’m shocked and appalled I forgot to post this until now.  Recently I put my personal website online and had several SQL tables to import.  Like most ISPs, mine had a query analyzer sufficient for creating tables.  Now, if only I could generate the INSERT statements for my existing data.  Enter the SQL Database Publishing Wizard.  After some googling I found ScottGu’s blog which detailed exactly what I was looking for.

 

This nice add-on hooks in to the Server Explorer in Visual Studio and allows you to export your DB, or selected tables, as a .SQL file.  You can open this in notepad and cut and paste it in the query analyzer to import both tables and data.

Fantastic?  Not quite.  My ISP strikes again!  It seems the line feeds within varchar columns were lost when importing this way.  Maybe you’ll have better luck but I had to persevere.  So now I had the SQL file but the ISP’s import functionality was letting me down.  I know!  Let’s write our own!

I spent so long trying to figure out how to make the ISP do my bidding I almost forgot I was a programmer and it’s fairly trivial to write our own import function.  I swiped most of the code from CodePlex who are working hard to make it easier for us to get our SQL data to our ISPs.  My only change was to use a proper ASPX page with a codebehind.  I used a FileUpload control in conjunction with a Button to import:

    protected void _ButtonImport_Click(object sender, EventArgs e)

    {

        SqlConnection conn = null;                  

        try

        {

            using (StreamReader sr = new StreamReader(_FileUpload.FileContent))

            {

                // Create new connection to database

                conn = new SqlConnection(CONNECTIONSTRING);              

                conn.Open();

 

                while (!sr.EndOfStream)

                {

                    StringBuilder sb = new StringBuilder();

                    SqlCommand cmd = conn.CreateCommand();

                   

                    while (!sr.EndOfStream)

                    {

                        string s = sr.ReadLine();

                        if (s != null && s.ToUpper().Trim().Equals(“GO”))

                        {

                            break;

                        }

                       

                        sb.AppendLine(s);

                    }

 

                    // Execute T-SQL against the target database

                    cmd.CommandText = sb.ToString();

                    cmd.ExecuteNonQuery();

                }

            }

        }

        catch (Exception ex)

        {

            //do something

        }

        finally

        {

            // Close out the connection

            if (conn != null)

            {

                conn.Close();

                conn.Dispose();

            }

        }                      

    }

 

The code will read your SQL file and build up SqlCommands to execute.  Simple!  The only caveat is to use forms authentication or some method of protecting access to this page.  Deleting it when you’re done also works.  So at long last, an import function.  Next time, an export function to keep a copy of my data safe and sound in my own DB.

http://www.codeplex.com/sqlhost

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: