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

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.