Solved

C# create database from text backup

Posted on 2011-02-27
7
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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