Из одного XML файла получить несколько таблиц в одной БД
Код:
openFileDialog1.Filter = "Acces DB|*.mdb";
openFileDialog1.ShowDialog();
OleDbConnection con = new OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName;
con.Open();
openFileDialog1.Filter = "XML file|*.xml";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
OleDbDataAdapter sda = new OleDbDataAdapter();
DataSet ds = new DataSet();
XmlReader xmlr = XmlReader.Create(openFileDialog1.FileName, new XmlReaderSettings());
ds.ReadXml(xmlr);
int n = ds.Tables[0].Columns.Count;
StringBuilder query = new StringBuilder();
query.Append("CREATE TABLE ");
query.Append(ds.Tables[0].TableName);
query.Append(" ( ");
for (int i = 0; i < n; i++)
{
query.Append("[");
query.Append(ds.Tables[0].Columns[i].ColumnName);
query.Append("] ");
query.Append("varchar(30)");
query.Append(", ");
}
query.Length -= 2;
query.Append(");");
OleDbCommand sqlQuery = new OleDbCommand(query.ToString(), con);
sqlQuery.ExecuteNonQuery();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO ");
sql.Append(ds.Tables[0].TableName);
sql.Append(" ");
sql.Append("( ");
for (int k = 0; k < n; k++)
{
sql.Append("[");
sql.Append(ds.Tables[0].Columns[k].ColumnName);
sql.Append("] ");
sql.Append(", ");
}
sql.Length -= 2;
sql.Append(")");
sql.Append(" values ( ");
for (int j = 0; j < n; j++)
{
sql.Append("'");
sql.Append(ds.Tables[0].Rows[i].ItemArray[j]);
sql.Append("'");
sql.Append(" , ");
}
sql.Length -= 2;
sql.Append(");");
OleDbCommand sqlQuery1 = new OleDbCommand(sql.ToString(), con);
sqlQuery1.ExecuteNonQuery();
}
con.Close();
MessageBox.Show("Access файл создан");
}
}
openFileDialog1.ShowDialog();
OleDbConnection con = new OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName;
con.Open();
openFileDialog1.Filter = "XML file|*.xml";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
OleDbDataAdapter sda = new OleDbDataAdapter();
DataSet ds = new DataSet();
XmlReader xmlr = XmlReader.Create(openFileDialog1.FileName, new XmlReaderSettings());
ds.ReadXml(xmlr);
int n = ds.Tables[0].Columns.Count;
StringBuilder query = new StringBuilder();
query.Append("CREATE TABLE ");
query.Append(ds.Tables[0].TableName);
query.Append(" ( ");
for (int i = 0; i < n; i++)
{
query.Append("[");
query.Append(ds.Tables[0].Columns[i].ColumnName);
query.Append("] ");
query.Append("varchar(30)");
query.Append(", ");
}
query.Length -= 2;
query.Append(");");
OleDbCommand sqlQuery = new OleDbCommand(query.ToString(), con);
sqlQuery.ExecuteNonQuery();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO ");
sql.Append(ds.Tables[0].TableName);
sql.Append(" ");
sql.Append("( ");
for (int k = 0; k < n; k++)
{
sql.Append("[");
sql.Append(ds.Tables[0].Columns[k].ColumnName);
sql.Append("] ");
sql.Append(", ");
}
sql.Length -= 2;
sql.Append(")");
sql.Append(" values ( ");
for (int j = 0; j < n; j++)
{
sql.Append("'");
sql.Append(ds.Tables[0].Rows[i].ItemArray[j]);
sql.Append("'");
sql.Append(" , ");
}
sql.Length -= 2;
sql.Append(");");
OleDbCommand sqlQuery1 = new OleDbCommand(sql.ToString(), con);
sqlQuery1.ExecuteNonQuery();
}
con.Close();
MessageBox.Show("Access файл создан");
}
}
Код:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table1>
<ID>1</ID>
<Colum>111</Colum>
</Table1>
<Table1>
<ID>2</ID>
<Colum>222</Colum>
</Table1>
<Table1>
<ID>3</ID>
<Colum>333</Colum>
</Table1>
<Table2>
<ID>1</ID>
<Colum_2>11</Colum_2>
</Table2>
<Table2>
<ID>2</ID>
<Colum_2>22</Colum_2>
</Table2>
<Table2>
<ID>3</ID>
<Colum_2>33</Colum_2>
</Table2>
<Table2>
<ID>4</ID>
<Colum_2>44</Colum_2>
</Table2>
</NewDataSet>
<NewDataSet>
<Table1>
<ID>1</ID>
<Colum>111</Colum>
</Table1>
<Table1>
<ID>2</ID>
<Colum>222</Colum>
</Table1>
<Table1>
<ID>3</ID>
<Colum>333</Colum>
</Table1>
<Table2>
<ID>1</ID>
<Colum_2>11</Colum_2>
</Table2>
<Table2>
<ID>2</ID>
<Colum_2>22</Colum_2>
</Table2>
<Table2>
<ID>3</ID>
<Colum_2>33</Colum_2>
</Table2>
<Table2>
<ID>4</ID>
<Colum_2>44</Colum_2>
</Table2>
</NewDataSet>
Код:
dataSet1.ReadXml(pathXml.Text); // path to file
dataGridView1.DataSource = dataSet1;
dataGridView1.DataMember = "Table1"; // container name "Table1" ,"Table2"
dataGridView1.DataSource = dataSet1;
dataGridView1.DataMember = "Table1"; // container name "Table1" ,"Table2"