* Function Description: backs up and recovers SQL Server databases.
* Author: Liu gongxun;
* Version: v0.1 (C #2.0); time:
* When using SQL Server, reference the sqldmo. dll component in the COM component.
* When using access, browse to add reference to the following two DLL
* Reference c: \ Program Files \ common files \ System \ ADO \ msadox. dll, which contains the ADOX namespace
* Reference c: \ Program Files \ common files \ System \ ADO \ msjro. dll, which contains the jro namespace
**************************************** ****************************************/
Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. IO;
Using ADOX; // The namespace contains the class (method) for creating access -- Solution ==> reference ==> add reference ==> find. dll
Using jro; // The namespace contains the class (method) for compressing access)
Namespace EC
{
/// <Summary>
/// Database recovery and backup
/// </Summary>
Public class sqlbackobject
{
Public sqlbackobject ()
{
//
// Todo: add the constructor logic here
//
}
# Region SQL database backup
/// <Summary>
/// SQL database backup
/// </Summary>
/// <Param name = "serverip"> SQL Server IP address or (localhost) </param>
/// <Param name = "loginname"> database logon name </param>
/// <Param name = "loginpass"> database logon password </param>
/// <Param name = "dbname"> database name </param>
/// <Param name = "backpath"> backup path </param>
Public static void sqlback (string serverip, string loginname, string loginpass, string dbname, string backpath)
{
Sqldmo. Backup obackup = new sqldmo. backupclass ();
Sqldmo. sqlserver osqlserver = new sqldmo. sqlserverclass ();
Try
{
Osqlserver. loginsecure = false;
Osqlserver. Connect (serverip, loginname, loginpass );
Obackup. Database = dbname;
Obackup. Files = backpath;
Obackup. backupsetname = dbname;
Obackup. backupsetdescription = "database backup ";
Obackup. initialize = true;
Obackup. sqlbackup (osqlserver );
}
Catch (exception E)
{
Throw new exception (E. tostring ());
}
Finally
{
Osqlserver. Disconnect ();
}
}
# Endregion
# Region SQL Restore database
/// <Summary>
/// SQL Restore database
/// </Summary>
/// <Param name = "serverip"> SQL Server IP address or (localhost) </param>
/// <Param name = "loginname"> database logon name </param>
/// <Param name = "loginpass"> database logon password </param>
/// <Param name = "dbname"> name of the database to be restored </param>
/// <Param name = "backpath"> database backup path </param>
Public static void sqldbrestore (string serverip, string loginname, string loginpass, string dbname, string backpath)
{
Sqldmo. Restore orestore = new sqldmo. restoreclass ();
Sqldmo. sqlserver osqlserver = new sqldmo. sqlserverclass ();
Try
{
Osqlserver. loginsecure = false;
Osqlserver. Connect (serverip, loginname, loginpass );
Orestore. Action = sqldmo. sqldmo_restore_type.sqldmorestore_database;
Orestore. Database = dbname;
Orestore. Files = backpath;
Orestore. filenumber = 1;
Orestore. replacedatabase = true;
Orestore. sqlrestore (osqlserver );
}
Catch (exception E)
{
Throw new exception (E. tostring ());
}
Finally
{
Osqlserver. Disconnect ();
}
}
# Endregion
# Region creates an Access database based on the specified file name
/// <Summary>
/// Create data based on the specified file name
/// </Summary>
/// <Param name = "dbpath"> absolute path + file name </param>
Public static void createaccess (string dbpath)
{
If (file. exists (dbpath) // check whether the database already exists
{
Throw new exception ("the target database already exists and cannot be created ");
}
Dbpath = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + dbpath;
// Create a catalogclass object instance
ADOX. catalogclass cat = new ADOX. catalogclass ();
// Use the create method of the catalogclass object to create an Access database
Cat. Create (dbpath );
}
# Endregion
# Region compressed Access Database
/// <Summary>
/// Compress the ACCESS database
/// </Summary>
/// <Param name = "dbpath"> absolute database path </param>
Public static void compactaccess (string dbpath)
{
If (! File. exists (dbpath ))
{
Throw new exception ("the target database does not exist and cannot be compressed ");
}
// Declare the temporary database name
String temp = datetime. Now. year. tostring ();
Temp + = datetime. Now. Month. tostring ();
Temp + = datetime. Now. Day. tostring ();
Temp + = datetime. Now. Hour. tostring ();
Temp + = datetime. Now. Minute. tostring ();
Temp + = datetime. Now. Second. tostring () + ". Bak ";
Temp = dbpath. substring (0, dbpath. lastindexof ("\") + 1) + temp;
// Define the connection string of the temporary database
String temp2 = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + temp;
// Define the connection string of the target database
String dbpath2 = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + dbpath;
// Create an instance of the jetengineclass object
Jro. jetengineclass Jt = new jro. jetengineclass ();
// Use the compactdatabase method of the jetengineclass object to compress and restore the database
JT. compactdatabase (dbpath2, temp2 );
// Copy the temporary database to the target database (overwrite)
File. Copy (temp, dbpath, true );
// Delete the temporary database
File. Delete (temp );
}
# Endregion
# Region backup Access Database
/// <Summary>
/// Back up the ACCESS database
/// </Summary>
/// <Param name = "srcpath"> absolute path of the database to be backed up </param>
/// <Param name = "aimpath"> absolute path of the backup database </param>
/// <Returns> </returns>
Public static void backup (string srcpath, string aimpath)
{
If (! File. exists (srcpath ))
{
Throw new exception ("the source database does not exist and cannot be backed up ");
}
Try
{
File. Copy (srcpath, aimpath, true );
}
Catch (ioexception IXP)
{
Throw new exception (IXP. tostring ());
}
}
# Endregion
# Region restore access database
/// <Summary>
/// Restore the ACCESS database
/// </Summary>
/// <Param name = "bakpath"> absolute path of the backup database </param>
/// <Param name = "dbpath"> absolute path of the database to be restored </param>
Public static void recoveraccess (string bakpath, string dbpath)
{
If (! File. exists (bakpath ))
{
Throw new exception ("the backup database does not exist and cannot be restored ");
}
Try
{
File. Copy (bakpath, dbpath, true );
}
Catch (ioexception IXP)
{
Throw new exception (IXP. tostring ());
}
}
# Endregion
}
}
========================================================== ========================================================== ====================
Please add reference Microsoft ADO Ext. 2.7 for DDL and Securit...
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using ADOX;
/// <Summary>
/// File: Create access DB
/// Autor: Wang yahui
/// Createtime: 2008-03-17
/// </Summary>
Public class createdb
{
/// <Summary>
/// Create access DB
/// </Summary>
/// <Param name = "path"> dB file path </param>
Public createdb (string path)
{
// For the convenience of testing, the database name should be a random name to prevent you from Restarting IIS to delete the database if it is not successfully added.
String dbname = path + datetime. Now. millisecond. tostring () + ". mdb ";
ADOX. catalogclass cat = new ADOX. catalogclass ();
Cat. Create ("provider = Microsoft. Jet. oledb.4.0; Data Source =" + dbname + ";");
// create the studentpaper table
ADOX. tableclass TBL = new ADOX. tableclass ();
TBL. parentcatalog = cat;
TBL. name = constant. studentpaper;
// Add the studentid field
ADOX. columnclass studentid = new ADOX. columnclass ();
studentid. parentcatalog = cat;
studentid. type = ADOX. datatypeenum. adwchar; // The field type must be set first
studentid. name = "studentid";
studentid. properties ["jet oledb: Allow zero length"]. value = false; // whether it can be null
// Col. properties ["autoincrement"]. value = true; // Add an Automatically increasing field
TBL. columns. append (studentid, ADOX. datatypeenum. adwchar, 20);
// Add the papertype field
ADOX. columnclass papertype = new ADOX. columnclass ();
papertype. parentcatalog = cat;
papertype. type = ADOX. datatypeenum. adwchar;
papertype. name = "papertype";
papertype. properties ["jet oledb: Allow zero length"]. value = false;
TBL. columns. append (papertype, ADOX. datatypeenum. adwchar, 1);
// Add the omranswer field
ADOX. columnclass omranswer = new ADOX. columnclass ();
omranswer. parentcatalog = cat;
omranswer. type = ADOX. datatypeenum. adwchar;
omranswer. name = "omranswer";
omranswer. properties ["jet oledb: Allow zero length"]. value = false;
TBL. columns. append (omranswer, ADOX. datatypeenum. adwchar, 255);
// Add the omranswer Field
ADOX. columnclass imagestudentid = new ADOX. columnclass ();
Imagestudentid. parentcatalog = cat;
Imagestudentid. type = ADOX. datatypeenum. adwchar;
Imagestudentid. Name = "imagestudentid ";
Imagestudentid. properties ["jet oledb: Allow zero length"]. value = false;
TBL. Columns. append (imagestudentid, ADOX. datatypeenum. adwchar, 6 );
/// Set the primary key
TBL. Keys. append ("primarykey", ADOX. keytypeenum. adkeyprimary, "studentid ","","");
Cat. Tables. append (TBL );
TBL = NULL;
Cat = NULL;
} // End function createdb ()
}
If the error "invalid type" is reported when you create a database table, see the following table to check whether the data types of the fields are consistent:
Constant |
Value |
Jet 3.51 |
JET 4.0 |
Sqls 7.0 |
Adbinary |
128 |
Yes |
Yes |
Yes |
Adboolean |
11 |
Yes |
Yes |
Yes |
Adchar |
129 |
Yes |
No |
Yes |
Adcurrency |
6 |
Yes |
Yes |
Yes |
Addate |
7 |
Yes |
Yes |
No |
Addouble |
5 |
Yes |
Yes |
Yes |
Adguid |
72 |
Yes |
Yes |
Yes |
Adinteger |
3 |
Yes |
Yes |
Yes |
Adlongvarbinary |
205 |
Yes |
Yes |
Yes |
Adlongvarchar |
201 |
Yes |
No |
Yes |
Adlongvarwchar |
203 |
No |
Yes |
Yes |
Adnumeric |
131 |
No |
Yes (with information )* |
Yes (with information )* |
Adsingle |
4 |
Yes |
Yes |
Yes |
Adsmallint |
2 |
Yes |
Yes |
Yes |
Adunsignedtinyint |
17 |
Yes |
Yes |
Yes |
Advarbinary |
204 |
Yes |
Yes |
Yes |
Advarchar |
200 |
Yes |
No |
Yes |
Advarwchar |
202 |
No |
Yes |
Yes |
Adwchar |
130 |
No |
Yes |
Yes |
Addbtimestamp |
135 |
No |
No |
Yes |