[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

C# create database from text backup

Posted on 2011-02-27
7
Medium Priority
?
631 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

656 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