sej69
asked on
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.
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
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);
}
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
must be the line breaks, check this solution: http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.d ll
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ Microsoft. SqlServer. Connection Info.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.
Be sure to reference the following DLLs:
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\
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);
The above parses the GOes correctly.
It uses sqlsmo classes :-)
It uses sqlsmo classes :-)
ASKER
You guys rock! The code worked beautifully. For future postarity I also needed to include
using System.Text.RegularExpress ions;
To use the regex in the statements listed.
using System.Text.RegularExpress
To use the regex in the statements listed.
Did you try my solution?