How to back up the Access database in Asp.net ?, Asp. netaccess

How to back up the Access database in Asp.net ?, Asp. netaccess

Public void Create (string mdbPath)
{
If (File. Exists (mdbPath) // check whether the database already Exists
{
Throw new Exception ("the target database already exists and cannot be created ");
}
// You can add a password so that the created database can be opened only after the password is entered.
MdbPath = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + mdbPath;
// Create an instance of the CatalogClass object,
ADOX. CatalogClass cat = new ADOX. CatalogClass ();
// Use the Create method of the CatalogClass object to Create an ACCESS database
Cat. Create (mdbPath );
}


/// Compress and fix the ACCESS database. The mdbPath is the absolute path of the database.
Public void Compact (string mdbPath)
{
If (! File. Exists (mdbPath) // check whether the database already Exists
{
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 = mdbPath. Substring (0, mdbPath. 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 mdbPath2 = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + mdbPath;
// 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 (mdbPath2, temp2 );
// Copy the temporary database to the target database (overwrite)
File. Copy (temp, mdbPath, true );
// Delete the temporary database
File. Delete (temp );
}

/// Backup database, mdb1, absolute path of the source database; mdb2: absolute path of the target database
Public void Backup (string mdb1, string mdb2)
{
If (! File. Exists (mdb1 ))
{
Throw new Exception ("the source database does not exist ");
}
Try
{
File. Copy (mdb1, mdb2, true );
}
Catch (IOException ixp)
{
Throw new Exception (ixp. ToString ());
}
}

/// Restore the database. mdb1 indicates the absolute path of the backup database, and mdb2 indicates the absolute path of the current database.
Public void Recover (string mdb1, string mdb2)
{
If (! File. Exists (mdb1 ))
{
Throw new Exception ("the backup database does not exist ");
}
Try
{
File. Copy (mdb1, mdb2, true );
}
Catch (IOException ixp)
{
Throw new Exception (ixp. ToString ());
}
}

========================================================== ========================================================== ======================================

Use ADOX to create Access databases and tables

Using System;
Using ADOX;

Namespace WebPortal
{
/// <Summary>
/// Summary of CreateAccessDB.
/// For different versions of ADO, you need to add different references.
/// Add reference Microsoft ADO Ext. 2.7 for DDL and Security
/// Add reference Microsoft ADO Ext. 2.8 for DDL and Security
/// </Summary>
Public class CreateAccessDB: System. Web. UI. Page
{
Private void Page_Load (object sender, System. EventArgs e)
{
// 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 = "D: \ NewMDB" + DateTime. Now. Millisecond. ToString () + ". mdb ";
ADOX. CatalogClass cat = new ADOX. CatalogClass ();
Cat. Create ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + dbName + ";");
Response. Write ("Database:" + dbName + "already created! ");
ADOX. TableClass tbl = new ADOX. TableClass ();
Tbl. ParentCatalog = cat;
Tbl. Name = "MyTable ";

// Add an Automatically increasing field
ADOX. ColumnClass col = new ADOX. ColumnClass ();
Col. ParentCatalog = cat;
Col. Type = ADOX. DataTypeEnum. adInteger; // you must set the field Type first.
Col. Name = "id ";
Col. Properties ["Jet OLEDB: Allow Zero Length"]. Value = false;
Col. Properties ["AutoIncrement"]. Value = true;
Tbl. Columns. Append (col, ADOX. DataTypeEnum. adInteger, 0 );

// Add a text field
ADOX. ColumnClass col2 = new ADOX. ColumnClass ();
Col2.ParentCatalog = cat;
Col2.Name = "Description ";
Col2.Properties ["Jet OLEDB: Allow Zero Length"]. Value = false;
Tbl. Columns. Append (col2, ADOX. DataTypeEnum. adVarChar, 25 );

// Set the primary key
Tbl. Keys. Append ("PrimaryKey", ADOX. KeyTypeEnum. adKeyPrimary, "id ","","");
Cat. Tables. Append (tbl );

Response. Write ("<br> database table:" + tbl. Name + "created successfully! ");
Tbl = null;
Cat = null;
}

# Code generated by region Web Form Designer
Override protected void OnInit (EventArgs e)
{
//
// CODEGEN: This call is required by the ASP. NET Web form designer.
//
InitializeComponent ();
Base. OnInit (e );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void InitializeComponent ()
{
This. Load + = new System. EventHandler (this. Page_Load );
}
# Endregion
}
}


========================================================== ========================================================== ======================================

Yesterday, a friend asked a question. The customer wanted an Access-format data. Now, the program has been used to export the Excel file. How can the problem be converted to Excel and converted to Access, this is relatively easy to implement. The office itself has this function, but the problem is that the Excel exported from the web page cannot be compatible with Access, another aspect is that it is not very good for the customer to do this conversion. To improve the user experience, it is best to use the code.
Can it be converted to Access at once? This is my question. If there is one, the customer will save a lot of effort, but I found some relevant information and could not directly turn the GridView into Access, that is, it is difficult to implement the client. The client is hard to implement here. The server should implement it well. First, convert the data in my database into an access file, and then download the data from the client. This is relatively easy to implement.
The main idea is to generate an access file and download the data from the server to the customer service provider.
Step-by-step implementation:
1. Create an Access File
2. Copy the database format and create a new table in the Access file.
Third, copy the data to the Access file.
Fourth: Download
Create an Access File
The syntax for creating an Access file is relatively simple.
First, introduce the namespace using ADOX. The namespace is located in the Interop. ADOX. dll file, ADOX. CatalogClass cat = new ADOX. CatalogClass ();
Cat. Create (@ "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = D: \ Data. mdb ;");
In this way, you can create an Access file for Data. mdb under the specified D disk. The file has been created, but it has caused some problems in the future, because we need to use Response when downloading. writeFile (file), at this time the Response. writeFile () requires independent permission to operate on this file, but the process at the early stage of creation cannot be released (if anyone has a good way to tell me how to release it ), you have to consider multiple operations here. It is possible that you need to create an Access file or release the file at the same time. In this case, a running error occurs, prompting that the file is exclusive to other processes.
To solve this problem and prevent other problems brought about by the release of the process, I made a brief bend to create a Data first. mdb is stored in the App_Data folder of the website. In the later stage, all clients want to export Access tables, copy them here, and then operate on the new files. In addition, create a new DownLoad folder to store the Access file downloaded by the user.
The Code is as follows:
// Copy the Access data table without moving string newfile = DateTime. Now. ToString ("MMddhhmmss") + ". mdb ";
File. Copy (Server. MapPath ("~ ") + @" \ App_Data \ data. mdb ", Server. MapPath ("~ ") + @" \ App_Data \ "+ newfile); copy the database format and create a new table in the Access file. Here, I generate an Access table from a table in SQL Server. Of course, you can use related tools to complete some functions, but if the other database is not SQL Se

Rver may not work well. Selecting SQL Server is just a demo database.
We know that creating a table has several elements, including the table name, field name, field type, and field size. Here, we must first find these elements from SQL Server, create an Access table. For other databases, convert the Data Type format accordingly.
The code is implemented as follows:
Create two connection strings in web. config
<Add name = "AccessConStr" connectionString = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = | DataDirectory | \ data. mdb; Persist Security Info = True; "providerName =" System. data. oleDb "/>
<Add name = "SqlConStr" connectionString = "server =.; database = cum_data; uid = sa; pwd = sa;" providerName = "System. Data. SqlClient"/>
These two characters are used to connect to the SQL Server database and the Access database we created.
To view the code of the import database:
String SqlConstr = ConfigurationManager. ConnectionStrings ["SqlConStr"]. ConnectionString;
String SQL = "select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'kh '";
SqlConnection con = new SqlConnection (SqlConstr );
SqlCommand cmd = new SqlCommand (SQL, con );
String AccSql = ""; // SQL statement used to generate an Access Table
Try
{
Con. Open ();
SqlDataReader DR = cmd. ExecuteReader ();
While (DR. Read ())
{
If (DR. GetValue (2). ToString () = "")
{
AccSql + = DR. GetValue (0). ToString () + "" + DR. GetValue (1). ToString () + ",";
}
Else
{
AccSql + = DR. getValue (0 ). toString () + "" + DR. getValue (1 ). toString () + "(" + DR. getValue (2 ). toString () + "),";
}
}
}
Catch
{}
Finally
{
Con. Close ();
}
AccSql = AccSql. Substring (0, AccSql. Length-1 );
String AccConstr = ConfigurationManager. ConnectionStrings ["AccessConStr"]. ConnectionString. Replace ("data. mdb", newfile );
OleDbConnection CON = new OleDbConnection (AccConstr );
OleDbCommand CMD = new OleDbCommand ();
CMD. Connection = CON;
Try
{
CON. Open ();
CMD. CommandText = "create table kh (" + AccSql + ")";
CMD. ExecuteNonQuery ();
}
Catch
{}
Finally
{

CON. Close ();
}
In the above Code, the SQL statement is critical. "select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'kh '", this SQL query statement queries the field name, field type, and field size of the original table. Of course, this statement is not nearly the same for different databases.
Copy the data to the Access file. Now, the data table format has been created. Next, import the data to Access.
The Code is as follows:
SqlDataAdapter DA = new SqlDataAdapter ("select * from kh", SqlConstr );
DataTable DT = new DataTable ();
DA. Fill (DT );
Foreach (DataRow DRR in DT. Rows)
{
DRR. SetAdded ();
}
OleDbDataAdapter ODA = new OleDbDataAdapter ("select * from kh", AccConstr );
OleDbCommandBuilder cmb = new OleDbCommandBuilder (ODA );
ODA. Update (DT );
Here we use a method of Adapter, Update, which can submit the data in the rowable RowState as Add to the database, which is more effort-saving.
In, you need to Move the Access File with data to the File. Move (Server. MapPath ("~ ") + @" \ App_Data \ "+ newfile, Server. MapPath ("~ ") + @" \ DownLoad \ "+ newfile );
Download
Response. Clear ();
Response. ClearHeaders ();
Response. Charset = "UTF-8 ";
Response. Buffer = false;
This. EnableViewState = false;
Response. ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.doc ument ";
Response. ContentEncoding = System. Text. Encoding. UTF8;
Response. AppendHeader ("Content-Disposition", "attachment; filename =" + newfile );
Response. WriteFile (Server. MapPath ("~ ") + @" \ DownLoad \ "+ newfile, true );
Response. Flush ();
Response. Close ();
Response. End ();
The download code is a common code, which is not described here.
The whole solution is to combine file operations, data operations, and file downloads to meet the user's need to download Access files.
In fact, sometimes it is difficult to implement a problem, but when we try to solve the problem through decomposition, it is relatively simple to implement.

In ASPNET, how does an Access database in the webpage background make a backup?

It is mainly about database statements. As long as the statements are skilled, there is no problem. Database Backup statement, which database to back up, and the backup address. If you want to manually write the backup statement, check whether the statement is correct.

Aspnet implements the backup and recovery of (ACCESS) Databases

I have a mailbox.