c# - 从 XML 数据写入 SQLite 数据库

Question

给定以下代码导出数据库中的每个表:

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 导入回数据库:

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 生成。我该如何解决这个问题？

Answer 1

放弃了Update选项，写了这个:

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();
                }
            }