C # database operations, including attaching, restoring, backing up, detaching, compressing, creating, modifying, and other common operations

Using system;
Using system. Collections. Generic;
Using system. Data. sqlclient;
Using system. Data;
Namespace adminzjc. databasecontrol
{
/// <Summary>
/// Database operation classes, including attaching, restoring, backing up, detaching, compressing, creating, modifying, and other common operations. Version 1.0
/// The latest version mainly improves the naming conventions and adds the function of renaming a database during restoration.
/// </Summary>
Public class databasehelper
{
/// <Summary>
/// Database connection string
/// </Summary>
Public String connectionstring;
/// <Summary>
/// SQL statement
/// </Summary>
Public String strsql;
/// <Summary>
/// Database connection object
/// </Summary>
Private sqlconnection conn;
/// <Summary>
/// Database operation object comm
/// </Summary>
Private sqlcommand comm;
/// <Summary>
/// Name of the database to be operated
/// </Summary>
Public String databasename;
/// <Summary>
/// Database logic name
/// The logical name is the initial file name after the database is created. It will not change with the database operations (such as backup and restoration) in the future.
/// Different from databasename
/// </Summary>
Public String logicaldatabasename;
/// <Summary>
/// Complete address of the database file
/// </Summary>
Public String database_mdf_address;
/// <Summary>
/// Complete address of the Database Log File
/// </Summary>
Public String database_ldf_address;
/// <Summary>
/// Backup file name
/// </Summary>
Public String databaseofbackupname;
/// <Summary>
/// Backup file path
/// </Summary>
Public String databaseofbackuppath;
/// <Summary>
/// Database compression ratio, 1%-99%
/// </Summary>
Public String percent;
/// <Summary>
/// Perform common operations for creating/modifying databases and tables
/// </Summary>
Public void databaseandtableoperate ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = strsql;
Comm. commandtype = commandtype. text;
Comm. executenonquery ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
/// <Summary>
/// Additional database
/// </Summary>
Public void appenddatabase ()
{
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_address;
Comm. Parameters. Add (New sqlparameter (@ "filename2", sqldbtype. nvarchar ));
Comm. Parameters [@ "filename2"]. value = database_ldf_address;
Comm. commandtype = commandtype. storedprocedure;
Comm. executenonquery ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
/// <Summary>
/// Separate the database
/// </Summary>
Public void detachdatabase ()
{
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 ();
}
Catch (exception ex)
{
Throw (Ex );
}
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 ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();

}
}
/// <Summary>
/// Restore the database
/// </Summary>
Public void restoredatabase ()
{
Try
{
String backupfile = @ databaseofbackuppath + @ databaseofbackupname;
Conn = new sqlconnection (connectionstring );
Conn. open ();
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "use master; restore filelistonly from disk = @ backupfile; restore database @ databasename from disk = @ backupfile with move '"+ logicaldatabasename +" 'to' "+ database_mdf_address + "', move 'ajgl _ baseunit_log 'to' "+ database_ldf_address +" ', stats = 10, 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 ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
/// <Summary>
/// Compress the database
/// </Summary>
Public void compressdatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "DBCC shrinkdatabase (" + databasename + "," + percent + ")";
Comm. commandtype = commandtype. text;
Comm. executenonquery ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
/// <Summary>
/// Set the database to read-only
/// </Summary>
/// <Param name = ""> </param>
Public void readonlydatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "use master; Exec sp_dboption '" + databasename + "', 'read only', 'true '";

Comm. commandtype = commandtype. text;
Comm. executenonquery ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
/// <Summary>
/// Set the database to offline
/// </Summary>
Public void offlinedatabase ()
{
Try
{
Conn = new sqlconnection (connectionstring );
Conn. open ();
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "use master; Exec sp_dboption '" + databasename + "', 'offline', 'true '";

Comm. commandtype = commandtype. text;
Comm. executenonquery ();
}
Catch (exception ex)
{
Throw (Ex );
}
Finally
{
Conn. Close ();
}
}
}
}
# Region call example
# Region btnremove_serverclick detached Database
/// <Summary>
/// Separate the database
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void btndetach_serverclick (Object sender, eventargs E)
{
Try
{
Databasehelper DBH = new databasehelper ();
DBH. connectionstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBH. databasename = "dbname ";
DBH. detachdatabase ();
}
Catch (exception ex)
{
Throw (Ex );
}
}
# Endregion
# Region btnadddb_serverclick additional database
/// <Summary>
/// Additional database
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void btnappenddb_serverclick (Object sender, eventargs E)
{
Try
{
Databasehelper DBH = new databasehelper ();
DBH. connectionstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBH. databasename = "dbname ";
DBH. database_mdf_address = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dbname. MDF ";
DBH. database_ldf_address = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dbname_log.ldf ";
DBH. appenddatabase ();
}
Catch (exception ex)
{
Throw (Ex );
}
}
# Endregion
# Region btnbackup_serverclick backup database
/// <Summary>
/// Back up the database
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void btnbackup_serverclick (Object sender, eventargs E)
{
Try
{
Databasehelper DBH = new databasehelper ();
DBH. connectionstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";

DBH. databasename = "dbname ";
DBH. databaseofbackupname = "back. Bak ";
DBH. databaseofbackuppath = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ back \";
DBH. backupdatabase ();
}
Catch (exception ex)
{
Throw (Ex );
}
}
# Endregion
# Region btnrestore_click Restore database
/// <Summary>
/// Restore the database
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void btnrestore_click (Object sender, eventargs E)
{
Try
{
Databasehelper DBH = new databasehelper ();
DBH. connectionstring = "Data Source = (local); User ID = sa; Password = 123456; initial catalog = Master ";
DBH. logicaldatabasename = "dbname ";
DBH. databasename = "dbname ";
DBH. databaseofbackupname = "back. Bak ";
DBH. database_mdf_address = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dbname. MDF ";
DBH. database_ldf_address = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dbname_log.ldf ";
DBH. databaseofbackuppath = @ "C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ back \";
DBH. restoredatabase ();
}
Catch (exception ex)
{
Throw (Ex );
}
}
# Endregion
# Endregion