C # Train of Thought and Algorithm for automatically attaching a database after generating an installation file

Using system;
Using system. Collections. Generic;
Using system. Windows. forms;
Using system. Data. sqlclient;
Using system. Data;
Using system. serviceprocess;

Namespace adminzjc. databasecontrol
{
/// <Summary>
/// Database Operation Control
/// </Summary>
Public class databasecontrol
{
/// <Summary>
/// Database connection string
/// </Summary>
Public String connectionstring;

/// <Summary>
/// SQL statement/Stored Procedure
/// </Summary>
Public String strsql;

/// <Summary>
/// Instantiate a database connection object
/// </Summary>
Private sqlconnection conn;

/// <Summary>
/// Instantiate a new database operation object comm
/// </Summary>
Private sqlcommand comm;

/// <Summary>
/// Name of the database to be operated
/// </Summary>
Public String databasename;

/// <Summary>
/// Complete address of the database file
/// </Summary>
Public String database_mdf;

/// <Summary>
/// Complete address of the Database Log File
/// </Summary>
Public String database_ldf;

/// <Summary>
/// Backup file name
/// </Summary>
Public String databaseofbackupname;

/// <Summary>
/// Backup file path
/// </Summary>
Public String databaseofbackuppath;

/// <Summary>
/// Create/modify databases and tables
/// </Summary>
Public void databaseandtablecontrol ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();

Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = strsql;
Comm. commandtype = commandtype. text;
Comm. executenonquery ();

MessageBox. Show ("database operation successful! "," Message prompt ", messageboxbuttons. OK, messageboxicon. information );
}
Catch (exception ex)
{
MessageBox. Show (ex. message, "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Finally
{
Conn. Close ();
}
}

/// <Summary>
/// Additional database
/// </Summary>
Public void adddatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();

Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "sp_attach_db ";

Comm. Parameters. Add (New sqlparameter (@ "dbname", sqldbtype. nvarchar ));
Comm. Parameters [@ "dbname"]. value = databasename;
Comm. Parameters. Add (New sqlparameter (@ "filename1", sqldbtype. nvarchar ));
Comm. Parameters [@ "filename1"]. value = database_mdf;
Comm. Parameters. Add (New sqlparameter (@ "filename2", sqldbtype. nvarchar ));
Comm. Parameters [@ "filename2"]. value = database_ldf;

Comm. commandtype = commandtype. storedprocedure;
Comm. executenonquery ();

MessageBox. Show ("attached database succeeded", "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Catch (exception ex)
{
MessageBox. Show (ex. message, "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Finally
{
Conn. Close ();
}
}

/// <Summary>
/// Separate the database
/// </Summary>
Public void deletedatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();

Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = @ "sp_detach_db ";

Comm. Parameters. Add (New sqlparameter (@ "dbname", sqldbtype. nvarchar ));
Comm. Parameters [@ "dbname"]. value = databasename;

Comm. commandtype = commandtype. storedprocedure;
Comm. executenonquery ();

MessageBox. Show ("database separation succeeded", "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Catch (exception ex)
{
MessageBox. Show (ex. message, "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Finally
{
Conn. Close ();
}
}

/// <Summary>
/// Back up the database
/// </Summary>
Public void backupdatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();

Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "use master; backup database @ dbname to disk = @ backupname ;";

Comm. Parameters. Add (New sqlparameter (@ "dbname", sqldbtype. nvarchar ));
Comm. Parameters [@ "dbname"]. value = databasename;
Comm. Parameters. Add (New sqlparameter (@ "backupname", sqldbtype. nvarchar ));
Comm. Parameters [@ "backupname"]. value = @ databaseofbackuppath + @ databaseofbackupname;

Comm. commandtype = commandtype. text;
Comm. executenonquery ();

MessageBox. Show ("succeeded in backing up the database", "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Catch (exception ex)
{
MessageBox. Show (ex. message, "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Finally
{
Conn. Close ();
}
}

/// <Summary>
/// Restore the database
/// </Summary>
Public void replacedatabase ()
{
Try
{
String backupfile = @ databaseofbackuppath + @ databaseofbackupname;
Conn = new sqlconnection (connectionstring );
Conn. open ();

Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "use master; restore Database @ databasename from disk = @ backupfile with replace ;";

Comm. Parameters. Add (New sqlparameter (@ "databasename", sqldbtype. nvarchar ));
Comm. Parameters [@ "databasename"]. value = databasename;
Comm. Parameters. Add (New sqlparameter (@ "backupfile", sqldbtype. nvarchar ));
Comm. Parameters [@ "backupfile"]. value = backupfile;

Comm. commandtype = commandtype. text;
Comm. executenonquery ();

MessageBox. Show ("restored database succeeded", "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Catch (exception ex)
{
MessageBox. Show (ex. message, "message prompt", messageboxbuttons. OK, messageboxicon. information );
}
Finally
{
Conn. Close ();
}
}
}
}

/*
/// Call example:

Restore database
Private void button0_click (Object sender, eventargs E)
{
Databasecontrol DBC = new databasecontrol ();
DBC onstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBC. databasename = "mydatabase ";
DBC. databaseofbackupname = @ "back. Bak ";
DBC. databaseofbackuppath = @ "D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \";
DBC. replacedatabase ();
}

Additional database
Private void button1_click_1 (Object sender, eventargs E)
{
Databasecontrol DBC = new databasecontrol ();
DBC onstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBC. databasename = "mydatabase ";
DBC. database_mdf = @ "D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ mydatabase_data.mdf ";
DBC. database_ldf = @ "D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ mydatabase_log.ldf ";
DBC. adddatabase ();
}

Back up database
Private void button2_click (Object sender, eventargs E)
{
Databasecontrol DBC = new databasecontrol ();
DBC onstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBC. databasename = "mydatabase ";
DBC. databaseofbackupname = @ "back. Bak ";
DBC. databaseofbackuppath = @ "D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \";
DBC. backupdatabase ();
}

Detaching a database
Private void button3_click (Object sender, eventargs E)
{
Databasecontrol DBC = new databasecontrol ();
DBC onstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBC. databasename = "mydatabase ";
DBC. deletedatabase ();
}