从 XML 数据写入 SQLite 数据库 [英] Write to SQLite database from XML data
本文介绍了从 XML 数据写入 SQLite 数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给定以下代码来导出数据库中的每个表:
Given the following code to export each table in the database:
string strSql = "SELECT * FROM " + tableName;
SqliteConnection sqlCon = new SqliteConnection("Data Source=" + dbPath);
using (SqliteCommand sqlComm = new SqliteCommand(strSql, sqlCon) { CommandType = CommandType.Text })
{
var da = new SqliteDataAdapter(sqlComm);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].WriteXml(Path.Combine(syncPath, tableName + "_4.xml"));
}
我正在尝试使用以下内容将 XML 导入回数据库:
I'm trying to import the XML back into the database with the following:
SqliteConnection sqlCon = new SqliteConnection("Data Source=" + dataPath + "/Empty.db3");
sqlCon.Open();
DataSet ds = new DataSet();
ds.ReadXml(Path.Combine(syncPath, tableName + "_4.xml"));
DataTable dt = ds.Tables[0];
string keyField = dt.Columns[0].ColumnName;
dt.PrimaryKey = new DataColumn[] { dt.Columns[keyField] };
var adapterForTable1 = new SqliteDataAdapter("Select * from " + tableName, sqlCon);
adapterForTable1.AcceptChangesDuringFill = false;
var builderForTable1 = new SqliteCommandBuilder(adapterForTable1);
adapterForTable1.Update(ds, tableName);
sqlCon.Close();
但我收到错误消息:没有基表时不支持动态 SQL 生成.
我该如何解决这个问题?
But I get the error: Dynamic SQL generation is not supported with no base table.
How do I fix this?
推荐答案
放弃更新选项,写了这个:
Abandoned the Update option and wrote this:
SqliteConnection sqlCon = new SqliteConnection("Data Source=" + dataPath + "/Empty.db3");
sqlCon.Open();
SqliteCommand sqlCmd = new SqliteCommand(sqlCon);
DataSet ds = new DataSet();
ds.ReadXml(Path.Combine(syncPath, tableName + "_4.xml"), XmlReadMode.ReadSchema);
foreach(DataTable dt in ds.Tables)
{
//Get field names
string sqlString = "INSERT into " + tableName + " (";
string valString = "";
var sqlParams = new string[dt.Rows[0].ItemArray.Count()];
int count = 0;
foreach(DataColumn dc in dt.Columns)
{
sqlString += dc.ColumnName + ", ";
valString += "@" + dc.ColumnName + ", ";
sqlParams[count] = "@" + dc.ColumnName;
count++;
}
valString = valString.Substring(0, valString.Length - 2);
sqlString = sqlString.Substring(0, sqlString.Length - 2) + ") VALUES (" + valString + ")";
sqlCmd.CommandText = sqlString;
foreach(DataRow dr in dt.Rows)
{
for (int i = 0; i < dr.ItemArray.Count(); i++)
{
sqlCmd.Parameters.AddWithValue(sqlParams[i], dr.ItemArray[i] ?? DBNull.Value);
}
sqlCmd.ExecuteNonQuery();
}
}
这篇关于从 XML 数据写入 SQLite 数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文