Solved

C# create database from text backup

Posted on 2011-02-27
7
625 Views
Last Modified: 2012-08-13
I'm getting ready to deploy my new app and am having a hard time getting the database deployed.

I was hoping to "generate scripts" from within MSSQl 2008R2 and deploy them back using C#.  I can use the created script within MSSQL to create the DB but it won't allow me to use the same script within C#.

How I'm doing it...

I created a .TXT file wtih the DB script in the project then loading that into a string.

StreamReader _textStreamReader;
            Assembly _assembly;

            _assembly = Assembly.GetExecutingAssembly();
            _textStreamReader = new StreamReader(_assembly.GetManifestResourceStream("SP.DBCreate.txt"));
            string szQuery = _textStreamReader.ReadToEnd();

            try
            {
                using (OleDbConnection aConnection = new OleDbConnection("Provider=SQLNCLI10;Server=localhost;Database=Master;Uid=sa;pwd=mypass"))
                {
                    aConnection.Open();
                    using (OleDbCommand dbCommand = new OleDbCommand(szQuery, aConnection))
                        dbCommand.ExecuteNonQuery();
                }
            }
            catch (OleDbException e)
            {
                string szMsg = "CheckSQL/CreateTables:" + e.Errors[0].Message;
                MiscDBFunc.WriteLogEntry(szMsg, 200);
            }

Open in new window


I'm getting an error/exception on the db call...

"Incorrect syntax near 'GO'"...

When  I did the create scripts it put in "GO"'s after each of the SQL lines.

Any idea what's going on or how I can get this to work?

Thanks
  sj
0
Comment
Question by:sej69
7 Comments
 
LVL 14

Expert Comment

by:robasta
ID: 34994622
0
 
LVL 14

Assisted Solution

by:robasta
robasta earned 100 total points
ID: 34994629
>>GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

thats why we need to split the commands and run them seperately

from here: http://msdn.microsoft.com/en-us/library/ms188037.aspx
0
 
LVL 11

Accepted Solution

by:
Sudhakar Pulivarthi earned 400 total points
ID: 34994818
hi,

Please use this code after you read the script data which will remove the comments and GO statements and generates a valid SQL statements for execution. It worked for me and i am using it.
// Prefix the namespace to point to the resource.
string scriptData = null;

                using (StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(SP.DBCreate.txt)))
                {
                    // Get the whole script once.
                    scriptData = reader.ReadToEnd();
                }

                // Remove all the GO statements.
                scriptData = scriptData.Replace("GO", "");
                // Replace Comments with empty string.
                scriptData = Regex.Replace(scriptData, "([/*][*]).*([*][/])", "");
                // Replace Chain of spaces with one Space.
                scriptData = Regex.Replace(scriptData, "\\s{2,}", " ");

// Here execute the script on dbconnection

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:Jacco
ID: 34995475
Here is a thing I found.

Be sure to reference the following DLLs:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

I also had to set my app to .NET version 2.0 in stead of 4.0 but don't know if that  is really required.
var cn = new SqlConnection("Data Source=yourServer;Initial Catalog=yourDatabase;Integrated Security=True");
            Server srvr = new Server(new ServerConnection(cn));
            string s = "alter table yourTable add test varchar(25);\r\nGO\r\nalter table yourTable add test1 varchar(25);";
            srvr.ConnectionContext.ExecuteNonQuery(s);

Open in new window

0
 
LVL 10

Expert Comment

by:Jacco
ID: 34995505
The above parses the GOes correctly.

It uses sqlsmo classes :-)
0
 

Author Closing Comment

by:sej69
ID: 34996980
You guys rock!  The code worked beautifully.  For future postarity I also needed to include
using System.Text.RegularExpressions;

To use the regex in the statements listed.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 34997099
Did you try my solution?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question