how i Attach DB while making Installer in Visual 2008

I prefer to embed and execute the SQL script i.e. my Database.sql file in the setup itself.

So when you are creating a Windows Application Deployment project, right click on the startup project in your solution ( NOT the setup project), add the new item, Installer.cs.

Add the database.sql file to the solution> your startup module.

Right-click the database.sql> Properties> Build Action > Embedded Resource

Nextly, override the Install method

public override void Install(System.Collections.IDictionary stateSaver)
{
base.Install(stateSaver);

.

.

.

Read the SQL file from the installed assembly

{

// Read the SQL file as a string
System.Reflection.Assembly Asm = System.Reflection.Assembly.GetExecutingAssembly();
Stream scriptStream= Asm.GetManifestResourceStream(Asm.GetName().Name + “.” + “database.sql”);
StreamReader reader = new StreamReader(scriptStream);

Regex regex = new Regex(“^GO”,RegexOptions.IgnoreCase | RegexOptions.Multiline);
return regex.Split(reader.ReadToEnd());

}

The main problem with executing the SQL script programatically is that , the ‘GO’ keyword is not recognised by the SQL Server. The GO is the SQL Server Management studio delimiter to execute the commands in batches and is not a SQL Command in itself.

So after reading the file , we will remove the ‘GO’s.

Now we have a string[] with each of the SQL command stored seperately in a array. We will read each of them , one by one , and execute them seperately.

SqlConnection sqlConnection=new SqlConnection();
sqlConnection.ConnectionString=connectionString;
string[] sSQLCommands= GetSqlCommands(“Database.sql”);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.Connection.Open();
foreach(string sSQLCommand in sSQLCommands)
{
if(sSQLCommand.Length>0)
{
sqlCommand.CommandText = sSQLCommand;
sqlCommand.CommandType = CommandType.Text;
try
{
sqlCommand.ExecuteNonQuery();
}
catch(SqlException ex)
{
throw ex;
}
}
}
sqlCommand.Connection.Close();
}
catch( SqlException ex)
{
throw ex;
}


In case you need to rollback on a exception throw the install exception which will automatically rollback the setup.

throw new InstallException(“Missing database script file”);

NOTE : If the solution works for you please mark as answer and vote it up so that other people can save timing in searching.