How do I back up an Access database in ASP.

Source: Internet
Author: User
Tags compact connectionstrings


public void Create (string mdbpath)
{
if (file.exists (Mdbpath))//check if 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 must enter a password to open
Mdbpath = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mdbpath;
Creates an instance of a Catalogclass object,
ADOX. Catalogclass cat = new ADOX. Catalogclass ();
Create an Access database using the Create method of the Catalogclass object
Cat. Create (Mdbpath);
}


Compact repair Access database, Mdbpath for database absolute path
public void Compact (string mdbpath)
{
if (! File.exists (Mdbpath))//check if the database already exists
{
throw new Exception ("The target database does not exist, cannot be compressed");
}
Declaring the name of the staging database
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 for the staging database
String temp2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + temp;
Define the connection string for the target database
String mdbPath2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mdbpath;
Create an instance of a Jetengineclass object
JRO. Jetengineclass JT = new JRO. Jetengineclass ();
Use the CompactDatabase method of the Jetengineclass object to compress the repair database
Jt.compactdatabase (MdbPath2, TEMP2);
Copy the staging database to the target database (overwrite)
File.Copy (temp, Mdbpath, true);
Finally delete the staging database
File.delete (temp);
}

Backup database, MDB1, absolute path of source database; MDB2: Absolute path to 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 the absolute path to the backup database, mdb2 the current database absolute path
public void Recover (string mdb1, String mdb2)
{
if (! File.exists (MDB1))
{
throw new Exception ("Backup database does not exist");
}
Try
{
File.Copy (MDB1, MDB2, true);
}
catch (IOException ixp)
{
throw new Exception (IXP. ToString ());
}
}

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

Create an Access database and table using ADOX

Using System;
Using ADOX;

Namespace Webportal
{
<summary>
A summary description of the CREATEACCESSDB.
For different versions of ADO, you need to add a different reference
Please add a reference to Microsoft ADO Ext. 2.7 for DDL and Security
Please add a reference to 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)
{
To facilitate testing, the database name uses a more random name to prevent the addition of unsuccessful additions and also the need to restart IIS to delete the database.
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 + "has been created successfully!) ");
ADOX. Tableclass tbl = new ADOX. Tableclass ();
Tbl. ParentCatalog = cat;
Tbl. Name= "MyTable";

Add an auto-growing field
ADOX. Columnclass col = new ADOX. Columnclass ();
Col. ParentCatalog = cat;
Col. Type=adox. Datatypeenum.adinteger; field type must be set 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 Primary key
Tbl. Keys.append ("PrimaryKey", ADOX. Keytypeenum.adkeyprimary, "id", "", "" ");
Cat. Tables.append (TBL);

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

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

<summary>
The designer supports the required method-do not use the Code editor to modify
The contents of this method.
</summary>
private void InitializeComponent ()
{
This. Load + = new System.EventHandler (this. Page_Load);
}
#endregion
}
}


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

Yesterday a friend asked a question, the customer wants an access format of data, now the case is that the program has been implemented to export Excel, the problem turned into Excel how to turn to access, which is relatively easy to achieve, the office itself has this function, but the problem is, Excel exported from a Web page is not very compatible with access, which is one thing, there is another aspect is to let customers do this conversion work is not too good, in order to improve the user experience, it is best to use code to achieve.
Can you turn it into Access all at once? This is a question I think about, if there is, the customer will save a lot of effort, but found some relevant information, did not find the GridView directly into Access, that is, the client is more difficult to achieve. Think of here, the client is difficult to implement, the server should be good to implement it, the data in my database into an Access file, and then the customer downloaded again, this is relatively easy to achieve.
Main idea: In the service side, the data in a data, generate access files, download to the client.
Step into the implementation of:
First: Create an Access file
Second: Copy the database format and create a new table in the Access file
Third: Copy data into an Access file
IV: implementation of the download
Create an Access file
Originally the syntax for creating an Access file is relatively simple
First, the namespaces using ADOX are introduced; 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;");
This allows you to create a data.mdb Access file under the specified D drive. The file is created, but it leads to some follow-up problems, because when we implement the download, we need to use the Response.WriteFile (file), at this time the Response.WriteFile () request is to independently enjoy this file operation permissions, But the process that was created at the time was never released (if anyone has a good way to tell me how to release it), there's a lot more to consider, and it's possible to create an Access file or release the file at the same time. This will cause a run-time error, prompting the file to be exclusive to other processes.
In order to solve this problem, but also related to prevent the release of the process of other problems, here I ba a bend to achieve, first create a data.mdb stored in the App_Data folder of the site, all the clients to export the Access table, all here to copy one copy, The new file is then manipulated. Also create a new download folder to store the user's downloaded access files.
The code is as follows:
Copies the Access data table without moving the string newfile=datetime. Now.tostring ("Mmddhhmmss") + ". mdb";
File.Copy (Server.MapPath ("~") + @ "\app_data\data.mdb", Server.MapPath ("~") + @ "\app_data\" + newfile), copy database format, Create a new table in an Access file here, I am generating a table of access from SQL Server raw tables, and of course, you can do some of the functions with the relevant tools, but if the other's database is not SQL Se

RVer may not be very useful. Choosing SQL Server here is just a demo database.
We know that creating a table has several features, table name, field name, field type, field size, and here we have to isolate the table's features from SQL Server, then create an Access table, and if it is a different database, convert the corresponding data type format accordingly.
The code is implemented as follows:
Create a two connection string 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, one used to connect to the SQL Server database, are used to connect to the Access database we created.
To view the code for the guided library:
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 compose 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 more critical, "select Column_name,data_type,character_maximum_length from Information_schema.columns where Table_name= ' kh ', this SQL query statement is from the original table field name, field type, field size query out, of course, different database, this statement is not nearly the same.
Copy data to an Access file now that we've created the data table format, we're going to take 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 is a method of adapter, Update, the DataTable can be rowstate to add the data submitted to the database, it is relatively less effort to do.
In, to put the data access file, move to the download folder under File.move (Server.MapPath ("~") + @ "\app_data\" +newfile, Server.MapPath ("~") + @ "\ Download\ "+ newfile);
Implementation download
Response.Clear ();
Response.ClearHeaders ();
Response.Charset = "Utf-8";
Response.Buffer = false;
This. EnableViewState = false;
Response.ContentType = "Application/vnd.openxmlformats-officedocument.wordprocessingml.document";
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 generic code and is not described here.
The whole solution is to combine file operations, data manipulation, and file downloads organically to achieve the user's need to download access files.
In fact, sometimes we go to achieve a problem, the overall is a certain difficulty, but when we think of ways, through the decomposition of the problem to achieve, relatively simple to achieve.

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.