从sqlite读取数据到C#然后到sqlite
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16137697/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
read data from sqlite into C# then to sqlite
提问by TongZZZ
Reproducible Example:
可重现的例子：
sqlite db test3.s3db has one table with name "MathRec":
sqlite db test3.s3db 有一张名为“MathRec”的表：
name score
Bill 2
Mary 3
John 3
Code:
代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
namespace ConsoleApplication7
{
class Program
{
static void Main(string[] args)
{
string fullPath = "C:\Users\Desktop\dataset\test3.s3db";
SQLiteConnection conread = new SQLiteConnection("Data Source=" + fullPath);
conread.Open();
string selectSQL = "SELECT * FROM MathRec";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL, conread);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();
DataSet ds = new DataSet();
DataTable dt = new DataTable("MathRec");
dt.Load(dataReader);
ds.Tables.Add(dt);
// Create a table in the database to receive the information from the DataSet
string fullPath2 = "C:\Users\\Desktop\dataset\test4.s3db";
SQLiteConnection conwrite = new SQLiteConnection("Data Source=" + fullPath2);
conwrite.Open();
SQLiteCommand cmd = new SQLiteCommand(conwrite);
cmd.CommandText = "DROP TABLE IF EXISTS MathRec";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE MathRec(name text , score integer)";
cmd.ExecuteNonQuery();
SQLiteDataAdapter adaptor = new SQLiteDataAdapter("SELECT * from MathRec", conwrite);
adaptor.InsertCommand = new SQLiteCommand("INSERT INTO MathRec VALUES(:name, :score)", conwrite);
adaptor.InsertCommand.Parameters.Add("name", DbType.String, 0, "name");
adaptor.InsertCommand.Parameters.Add("score", DbType.Int32, 0, "score");
adaptor.Update(ds, "MathRec");
}
}
}
Questions: Table MathRec is created in test4.s3db with column names: name and socre, but the table is empty with no records inserted.
问题：在test4.s3db中创建了表MathRec，列名：name和socre，但是表是空的，没有插入记录。
Help is needed!
需要帮助！
Please don't ask me why I am just copying one db to another, because I am testing one part of the code for a bigger project, where I will do calculations to the Dataset in the middle step in the future.
请不要问我为什么只是将一个数据库复制到另一个数据库，因为我正在为一个更大的项目测试代码的一部分，我将在未来的中间步骤中对数据集进行计算。
Thanks!
谢谢！
To simplify the question:
为了简化问题：
This works (use datatable and adapter to update the original sqlite table):
这是有效的（使用数据表和适配器更新原始的 sqlite 表）：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace ConsoleApplication7
{
class Program
{
static void Main(string[] args)
{
string fullPath = "C:\Users\data\test.db";
SQLiteConnection conread = new SQLiteConnection("Data Source=" + fullPath);
conread.Open();
SQLiteDataAdapter DB = new SQLiteDataAdapter("SELECT speed, dist FROM Cars2", conread);
DataSet DS = new DataSet();
DB.Fill(DS, "NewCars");
object[] rowVals = new object[2];
rowVals[0] = 10;
rowVals[1] = 20;
DS.Tables["NewCars"].Rows.Add(rowVals);
DB.InsertCommand = new SQLiteCommand("INSERT INTO Cars2 (speed, dist)
" + " VALUES (:speed, :dist)", conread);
DB.InsertCommand.Parameters.Add("speed", DbType.Double, 0, "speed");
DB.InsertCommand.Parameters.Add("dist", DbType.Double, 20, "dist");
DB.Update(DS, "NewCars");
}
}
}
And this works (create a new sqlite table from old one):
这有效（从旧表创建一个新的sqlite表）：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace ConsoleApplication7
{
class Program
{
static void Main(string[] args)
{
string fullPath = "C:\Users\data\test.db";
SQLiteConnection conread = new SQLiteConnection("Data Source=" + fullPath);
conread.Open();
SQLiteCommand cmd = new SQLiteCommand(conread);
cmd.CommandText = "DROP TABLE IF EXISTS Cars";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE Cars (speed REAL , dist REAL)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Cars SELECT * from DS";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
But this one is what I wanted (use datatable and adapter to create new table in sqlite) and is not working:
但这是我想要的（使用数据表和适配器在 sqlite 中创建新表）并且不起作用：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace ConsoleApplication7
{
class Program
{
static void Main(string[] args)
{
string fullPath = "C:\Users\data\test.db";
SQLiteConnection conread = new SQLiteConnection("Data Source=" + fullPath);
conread.Open();
SQLiteDataAdapter DB = new SQLiteDataAdapter("SELECT speed, dist FROM Cars2", conread);
DataSet DS = new DataSet();
DB.Fill(DS, "NewCars");
object[] rowVals = new object[2];
rowVals[0] = 10;
rowVals[1] = 20;
DS.Tables["NewCars"].Rows.Add(rowVals);
SQLiteDataAdapter DB2 = new SQLiteDataAdapter("SELECT speed, dist FROM Cars3", conread);
DB2.InsertCommand = new SQLiteCommand("INSERT INTO Cars3 (speed, dist)
" + " VALUES (:speed, :dist)", conread);
DB2.InsertCommand.Parameters.Add("speed", DbType.Double, 0, "speed");
DB2.InsertCommand.Parameters.Add("dist", DbType.Double, 20, "dist");
DB2.Update(DS, "NewCars");
}
}
}
Please help!!!
请帮忙！！！
采纳答案by TongZZZ
I found the answer!
我找到了答案！
Adapter.Update can only be used to update the original table in the database and not to save the datatable in a new one. Please refer to the thread for answers:
Adapter.Update 只能用于更新数据库中的原始表，而不能将数据表保存在新的表中。请参阅线程以获取答案：
Cheers!
干杯!
回答by CL.
The easiest way to copy one table to another database is to attach the second database to the first connection, and copy the data directly:
将一个表复制到另一个数据库的最简单方法是将第二个数据库附加到第一个连接，直接复制数据：
ATTACH '...\test4.s3db' AS 'test4';
DROP TABLE IF EXISTS test4.MathRec;
CREATE TABLE test4.MathRec(name text , score integer);
INSERT INTO test4.MathRec SELECT * FROM MathRec;
If you want to do calculations, you might be able to do them in SQL, in the SELECTstatement.
If not, you have to read the data with a SELECTand then execute one INSERTstatement for each record to be written. (In that case, you don't need the ATTACH.)
如果您想进行计算，您可以在 SQL 中的SELECT语句中进行计算。如果不是，则必须使用 a 读取数据，SELECT然后INSERT对要写入的每条记录执行一条语句。（在这种情况下，您不需要ATTACH.）
