How to copy DataTable contents into SQLite table?
-
Hi, My DataTable has 40 columns. I have created a Database and a table with 40 columns for SQLite programmatically. I want to copy all my contents in DataTable into SQLite table. I used this class code:
public class SQLiteFunction
{
//Path of database
public string databaseName { get; set; }
public string dataTableName { get; set; }private DataTable dt = new DataTable(); public SQLiteFunction() { dt.TableName = dataTableName; } public void Create\_db() { if (!File.Exists(databaseName)) { using (var sqlite = new SQLiteConnection(@"Data Source=" + databaseName)) { sqlite.Open(); string script = File.ReadAllText(@"CreateTable.sql"); SQLiteCommand command = new SQLiteCommand(script, sqlite); command.ExecuteNonQuery(); sqlite.Close(); } } } public void InsertData() { SQLiteConnection con = new SQLiteConnection(@"Data Source=" + databaseName); con.Open(); SQLiteDataAdapter dAdapter = new SQLiteDataAdapter("SELECT FROM MyTable", con); SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(dAdapter); dAdapter.Fill(dt); } }When I use InserData() method, there is no runtime error but my SQLite table is still empty. Please help me.
-
Hi, My DataTable has 40 columns. I have created a Database and a table with 40 columns for SQLite programmatically. I want to copy all my contents in DataTable into SQLite table. I used this class code:
public class SQLiteFunction
{
//Path of database
public string databaseName { get; set; }
public string dataTableName { get; set; }private DataTable dt = new DataTable(); public SQLiteFunction() { dt.TableName = dataTableName; } public void Create\_db() { if (!File.Exists(databaseName)) { using (var sqlite = new SQLiteConnection(@"Data Source=" + databaseName)) { sqlite.Open(); string script = File.ReadAllText(@"CreateTable.sql"); SQLiteCommand command = new SQLiteCommand(script, sqlite); command.ExecuteNonQuery(); sqlite.Close(); } } } public void InsertData() { SQLiteConnection con = new SQLiteConnection(@"Data Source=" + databaseName); con.Open(); SQLiteDataAdapter dAdapter = new SQLiteDataAdapter("SELECT FROM MyTable", con); SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(dAdapter); dAdapter.Fill(dt); } }When I use InserData() method, there is no runtime error but my SQLite table is still empty. Please help me.
Your InsertData method is reading data from the DEL - a SELECT query - instead of trying to write data to the DB - an INSERT query. Have a look here: DataAdapter.Update(DataSet) Method (System.Data.Common) | Microsoft Docs[^]
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Your InsertData method is reading data from the DEL - a SELECT query - instead of trying to write data to the DB - an INSERT query. Have a look here: DataAdapter.Update(DataSet) Method (System.Data.Common) | Microsoft Docs[^]
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I changed the InserData() method:
public void InsertData() { SQLiteConnection con = new SQLiteConnection(@"Data Source=" + databaseName); con.Open(); SQLiteDataAdapter dAdapter = new SQLiteDataAdapter("INSERT INTO MyTable", con); SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(); cmdBuilder.DataAdapter = dAdapter; dAdapter.Update(dt); con.Close(); }It still doesn't work. Note that DataTable headers and column names in SQLite table are the same. What query should I use?
-
Your InsertData method is reading data from the DEL - a SELECT query - instead of trying to write data to the DB - an INSERT query. Have a look here: DataAdapter.Update(DataSet) Method (System.Data.Common) | Microsoft Docs[^]
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Is there any way to transfer them from DataTable to SQLite table at once and quickly? I can use For loop but my data is huge (40 columns and around 30000 rows).
-
Is there any way to transfer them from DataTable to SQLite table at once and quickly? I can use For loop but my data is huge (40 columns and around 30000 rows).
Yes. And if you follow the link I gave you it tells you how to!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Yes. And if you follow the link I gave you it tells you how to!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Yes. And if you follow the link I gave you it tells you how to!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Solved. Thanks/ ;)
-
In the Link you provided, there is a string parameter for queryString. I need to INSERT data. What query should I use for transferring all cell information into SQLite?
It looks to me like the query string should be the SELECT query that pulls the data you want to transfer.
The difficult we do right away... ...the impossible takes slightly longer.
