private void ExecCENativeSQL(string sqlScript, SqlCeConnection sqlConnection)
{
string[] sqlScriptChunks = sqlScript
.Replace(" ASC,", ",")
.Replace(" ASC\r\n", "\r\n")
.Replace(" CLUSTERED", "")
.Replace("[dbo].", "")
.Replace("GetUTCDate()", "GetDate()")
.Replace("GETUTCDATE()", "GETDATE()")
.Replace("[nvarchar](max)", "[nvarchar](4000)")
.Replace("nvarchar(max)", "nvarchar(4000)")
.Replace("[varbinary](max)", "[varbinary](8000)")
.Replace("varbinary(max)", "varbinary(8000)")
.Replace("CONVERT(VARBINARY(MAX)", "CONVERT(VARBINARY")
.Replace("WITH CHECK", "")
.Split(new string[] { "\nGO", "\nGo", "\ngo" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string sqlScriptChunk in sqlScriptChunks)
{
// only execute if script doesn't contain trigger code and is more than blank space/lines
if
(
(!sqlScriptChunk.Contains(" TRIGGER ")) &&
(sqlScriptChunk.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(" ", "") != "")
)
{
try
{
using (SqlCeCommand cmd = new SqlCeCommand(sqlScriptChunk, sqlConnection))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
// ensure full text of SQL chunk is included in any error details
// - useful for when testing translation to CE-compatible script
throw new Exception("ExecCENativeSQL Chunk:\n\n" + sqlScriptChunk, ex);
}
}
}
}
