Author: Andrew Mooney translation: Mencius Chapter E [download the source code
Http://aspalliance.com/download/477/aspalliance477.zip
] XML2OL2OLDB Introduction XML is the best way to share data in the Internet, and data in XML format can be easily integrated into different web applications. But what if you want to insert an XML file into the database? XML2OL2OLDB will explain to the OLEDB database, such as SQL Server, Access, Excel, Visual FoxPro, FoxPro, and DBase, etc. Insert XML files, etc.
First, load the XML file into the DataSet and get the first table. This table is the DataTable we have to join the database; Next, remove the extension of the XML file, the file name removes the extension will be the table name used in our database . If the XML format is inaccurate, an error will be reported, and an example in the source code has an example of XML file format. See Authors.xml
// Load the XML file we submit to DataSetDataSetXml.ReadXML (httpcontext.current.server.mappath (textboxxml.text)); // Get the first table DataTableXML = DataSetXml.tables [0]; // Generate Table Name TableName = TextBoxxml.Text.Substring (0, TextBoxxml.Text.length -4);
Once the XML load is successful, first check if the data table has data (ROWS), then check if the database exists, if you do not exist, create one; then insert the data into the database from XML.
// Check if there is data existence (ROWS) IF (DataTableXML.Rows.count> 0)
Creating a database table Create a database connection to get the database schema information we have to add a table.
// create a database connection, open the database, the database table schema information obtained OleDbConnection oledbConn = new OleDbConnection (textBoxOleDb.Text); oledbConn.Open (); DataTable schemaTable = oledbConn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object [] {null, NULL, TABLENAME, "Table"});
// Check if the table exists, if there is a record IF in DataTable, SChem T.Rows.count <1) sqlcmd = "CREATE TABLE" TABLENAME "("; for (int i = 0; i Add XML Data to Database // Traverse the RowsForeach (DataRow Dr in DataTableXML.ROWS) {String Sqlcmd = "Insert INTO [" Tablename "] ("; // traversing DataTable for column for (INT i = 0; " i Note: When testing, you will download the source code, create an empty database, set the modified permissions Database.mdb, Database.xls, create an empty directory for FoxPro / DBASE Connection String Example: Access: provider = microsoft.jet.Oledb.4.0; data source = c: /data/database.mdb; Excel: provider = microsoft.jet.Oledb.4.0; data source = C: / Data / Database .xls; Extended Properties = Excel 8.0; FoxPro / dBase: Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c: / data; Extended Properties = dBASE IV; SQL Server: Provider = SQLOLEDB; Data Source = localhost; Initial Catalog = Database; User ID = sa; password =;
