• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

C# create database from text backup

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
sej69
Asked:
sej69
2 Solutions
 
robastaCommented:
0
 
robastaCommented:
>>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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
JaccoCommented:
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
 
JaccoCommented:
The above parses the GOes correctly.

It uses sqlsmo classes :-)
0
 
sej69Author Commented:
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
 
JaccoCommented:
Did you try my solution?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now