I have a big SQL text file where I have a lot of SQL commands to create tables, columns, etc.
Example row(s):
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'xcal_views') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE xcal_views (lid INT NOT NULL);
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'xcal_views_actors') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE xcal_views_actors (lid INT NOT NULL);
GO
IF NOT EXISTS ( SELECT * FROM dbo.syscolumns, dbo.sysobjects WHERE [dbo].[syscolumns].[name] = 'xlactor' AND [dbo].[sysobjects].[id] = [dbo].[syscolumns].[id] AND [dbo].[sysobjects].[id] = object_id(N'xcal_views_actors') AND OBJECTPROPERTY([dbo].[sysobjects].[id], N'IsUserTable') = 1 )
ALTER TABLE [dbo].[xcal_views_actors] ADD xlactor INT NULL;
GO
IF NOT EXISTS ( SELECT * FROM dbo.syscolumns, dbo.sysobjects WHERE [dbo].[syscolumns].[name] = 'lparentid' AND [dbo].[sysobjects].[id] = [dbo].[syscolumns].[id] AND [dbo].[sysobjects].[id] = object_id(N'xcal_views_actors') AND OBJECTPROPERTY([dbo].[sysobjects].[id], N'IsUserTable') = 1 )
ALTER TABLE [dbo].[xcal_views_actors] ADD lparentid INT NULL;
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE parent_obj = (SELECT id FROM dbo.sysobjects WHERE id = object_id(N'xcal_views_actors') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) AND OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[xcal_views_actors]
ADD CONSTRAINT [CT_00000501] PRIMARY KEY CLUSTERED (lid ASC);
GO
IF NOT EXISTS ( SELECT * FROM [sys].[indexes] i INNER JOIN [sys].[objects] o ON o.object_id = i.object_id AND o.name = 'xcal_views_actors' WHERE i.name = 'parent_id' )
CREATE INDEX parent_id ON xcal_views_actors (lparentid ASC)
GO
Between each command I have a GO in extra line to separate the commands.
If I run the whole patch.sql file from SQL Server Management Studio all commands are executed and works fine.
In .NET I read the whole text file, then split them with 'GO' and execute each SQL command against the database.
Now the strange thing: some of the commands don't get executed. And I can't find out why.
This is the method that does the job:
private static void patchDatabase(string connection, string sqlfile)
{
var defaultEncoding = Encoding.Default;
using (FileStream fs = File.OpenRead(sqlfile))
{
defaultEncoding = TextFileEncodingDetector.DetectTextFileEncoding(fs, defaultEncoding, 1024);
}
//Console.WriteLine(string.Format("File {0} using encoding: {1}",sqlfile, defaultEncoding));
var dbPatch = new StreamReader(sqlfile, defaultEncoding);
string sqlPatch = dbPatch.ReadToEnd();
dbPatch.Close();
string[] stringSeparators = new[] {"GO"};
string[] sqlPatches = sqlPatch.Split(stringSeparators, StringSplitOptions.None);
if (connection != null && sqlPatch.Length > 0)
{
Console.WriteLine(string.Format("Executing {0} statements from {1}", sqlPatches.Length, sqlfile));
using (var cnn = new SqlConnection(connection))
{
cnn.Open();
foreach (var sql in sqlPatches)
{
if (String.IsNullOrEmpty(sql))
continue; // Not a real sql statement, use next
using (var cmd = new SqlCommand(sql, cnn))
{
try
{
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
//int results = cmd.ExecuteNonQuery();
//if (results < 1)
// Console.WriteLine(String.Format("Failed:\nResult: {0}\n{1}",results, sql));
}
catch (Exception ex)
{
Console.WriteLine("Execution error!\n\n" + sql + "\n\n\n" + ex);
}
}
}
cnn.Close();
}
}
}
It looks like my function splutters...
My current textfile has around 6.000+ lines.
Any idea what I do wrong?
