Insert XML Files into Databases Using Xml2OleDb
page 2 of 4
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 43378/ 51

Creating the Database Table

[Download Code]
This next routine checks to see if the table exists in the database and creates the table if it does not exist. Start by creating an OleDb database connection using the connection string provided when the web form is submitted. Open the database connection and retrieve database schema information for only the table we are looking for and place it in a DataTable. In this example the table name is the name of the XML file without the extension.

// Create an OleDb database connection using the connection string
// provided when the web form is submitted
OleDbConnection oledbConn = new OleDbConnection(textBoxOleDb.Text);
// Open the database connection
oledbConn.Open();
// Retrieve database schema information for only the table we are looking for
// and place it in a DataTable.
// In this example the table name is the name of the XML file without the extension
DataTable schemaTable =      
    oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
    new object[] {null, null, tableName, "TABLE"});

Using the DataTable that contains the database schema check to see if the table exists. If the table exists in the schema there will be one row in the DataTable. If the table does not exist in the schema, the DataTable row count will be zero.

// Check to see if the table exists in the database schema
// If the table exists in the schema there will be 1 row in the DataTable
// If the table does not exist in the schema the DataTable row count will be zero
if(schemaTable.Rows.Count < 1)

If the table does not exist in the database, create it. Make the create table SQL command by iterating through the XML file's columns. This way the database columns will have the same name as the XML file. In this example we create all columns as CHAR (text/string) data type with a length of 100. This simplifies the code, because not all data types are supported by all OleDb databases. Once the SQL command is created, it is executed against the database using the ExecuteNonQuery method of the OleDbCommand.

// Make the create table SQL command by iterating through the XML file's
// columns. This way the database columns will have the same name as the XML file.
sqlCmd = "create table " + tableName + " (";
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
    // This adds each column as a text/string type with a length of 100
    sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + " 
        char(100),";
}
// Remove the last ","
sqlCmd  = sqlCmd .Substring(0,sqlCmd.Length - 1) + ");";
// Create and execute the create table command
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
oledbCmd.ExecuteNonQuery();


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-03 4:42:29 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search