【难题】打包部署数据库时如何将创建表和存储过程同时打包
在打包数据库时候遇到一个问题，使用installer类 调用sql.txt文本时 sql文本里面的sql语句如果同时有create table 和 create procedure会提示create procedure 必须放在第一行 也就是说创建表和存储过程同时存在是不行的 不知道为什么？
我现在有个解决办法，就是把每个存储过程建一个sql文本文件，然后调用。但是这样太麻烦了感觉，有没有好的解决办法？？？？
下面是我的例子:
//Creates the database.
ExecuteSql( "MASTER ", "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N ' "
+ strDBName + " ') CREATE DATABASE " + strDBName);

//Creates the tables.
ExecuteSql(strDBName, GetSql( "SqlTable.txt "));
ExecuteSql(strDBName, GetSql( "Procedure1.txt "));
ExecuteSql(strDBName, GetSql( "Procedure2.txt "));
ExecuteSql(strDBName, GetSql( "Procedure3.txt "));

[解决办法]
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration.Install;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace install
{
/// <summary>
/// Installer1 的摘要说明。
/// </summary>
[RunInstaller(true)]
public class Installer1 : System.Configuration.Install.Installer
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;
public Installer1()
{
// 该调用是设计器所必需的。
InitializeComponent();
// TODO: 在 InitializeComponent 调用后添加任何初始化
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region 组件设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
components = new System.ComponentModel.Container();
}
#endregion
private string GetSql(string Name)
{
// //调用osql执行脚本
//
// System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();
//
// sqlProcess.StartInfo.FileName = "osql.exe ";
//
// sqlProcess.StartInfo.Arguments = String.Format( " -U {0} -P {1} -d {2} -i {3}db.sql ", this.Context.Parameters[ "user "], this.Context.Parameters[ "pwd "], "master ", this.Context.Parameters[ "targetdir "]);
//
// sqlProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
//
// sqlProcess.Start();
//
// sqlProcess.WaitForExit() ;//等待执行
//
// sqlProcess.Close();
try
{
// Assembly Asm = Assembly.GetExecutingAssembly();
// System.IO.FileInfo FileInfo = new System.IO.FileInfo(Asm.Location);
// string path=FileInfo.DirectoryName+@ "\ "+Name;
string path=this.Context.Parameters[ "targetdir "]+Name;
FileStream fs=new FileStream(path,FileMode.Open,FileAccess.Read,FileShare.Read);

StreamReader reader = new StreamReader(fs,System.Text.Encoding.Default);
//System.Text.Encoding.ASCII;
return reader.ReadToEnd();
}
catch (Exception ex)
{
Console.Write( "In GetSql: "+ex.Message);
throw ex;
}
}
private void ExecuteSql(string DataBaseName,string Sql)
{
SqlConnection sqlConnection1=new SqlConnection();
sqlConnection1.ConnectionString =string.Format( "server={0}; user id={1}; password={2}; Database=master ",this.Context.Parameters[ "server "],this.Context.Parameters[ "user "],this.Context.Parameters[ "pwd "]);
System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql,sqlConnection1);
try
{
Command.Connection.Open();
Command.Connection.ChangeDatabase(DataBaseName);

Command.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.Write( "In exception handler : "+ex.Message);
}
finally
{
Command.Connection.Close();
}
}
protected void AddDBTable(string strDBName)
{
try
{
ExecuteSql( "master ", "CREATE DATABASE "+ strDBName);
ExecuteSql(strDBName,GetSql( "sql.txt "));
ExecuteSql( "master ", "exec sp_addlogin 'myoamaster ', 'myoamaster ', ' "+strDBName+ " ',Null,Null ");
ExecuteSql(strDBName, "EXEC sp_grantdbaccess 'myoamaster ', 'myoamaster ' ");
ExecuteSql(strDBName, "exec sp_addrolemember 'db_owner ', 'myoamaster ' ");
}
catch(Exception ex)
{
Console.Write( "In exception handler : "+ex.Message);
}
}
public override void Install(System.Collections.IDictionary stateSaver)
{
base.Install(stateSaver);
AddDBTable(this.Context.Parameters[ "dbname "]);
}
}
}