[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

C# create database from text backup

Posted on 2011-02-27
7
Medium Priority
?
632 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 400 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 1600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

872 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