C# SQLite insert not working

Question

I am trying to insert some data into an SQLite file but when I try to do so nothing gets added to the file, it's the first time I deal with SQLite so not quite sure I'm doing it right. Please ask if any other information is needed.

Here is the source code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SQLite;

namespace WpfApplication1
{

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            Readlines();
        }

        public void Readlines()
        {
            string[] lines = System.IO.File.ReadAllLines(@"D:\Projects\GFKMVC\WpfApplication1\WpfApplication1\Opgave001.txt");
            Shift shift = new Shift();
            SQLiteConnection.CreateFile("TimePlan.sqlite");
            SQLiteConnection dbcon;
            dbcon = new SQLiteConnection("Data Source=TimePlan.sqlite;Version=3;");
            dbcon.Open();
            //string DropTable = "DROP DATABASE TimePlane.sqlite";
            //SQLiteCommand dropCom = new SQLiteCommand(DropTable, dbcon);
            //dropCom.ExecuteNonQuery();
            string CreateTable = "CREATE TABLE Shifts (EmployeeID INT, Date DATETIME, StartTime DATETIME, EndTime DATETIME, Break INT)";
            SQLiteCommand createCom = new SQLiteCommand(CreateTable, dbcon);
            createCom.ExecuteNonQuery();

            foreach (string line in lines)
            {
                string[] fields = line.Split(';');
                shift.EmployeeID = Int32.Parse(fields[0]);
                DateTime dt = DateTime.ParseExact(fields[1], "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
                shift.Date = dt;
                DateTime st = DateTime.ParseExact(fields[2], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
                shift.StartTime = st;
                DateTime et = DateTime.ParseExact(fields[3], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
                shift.EndTime = et;
                shift.Break = Int32.Parse(fields[4]);
                string InsertSql = "INSERT INTO Shifts (EmployeeID, Date, StartTime, EndTime, Break) values ("+shift.EmployeeID+","+shift.StartTime+","+shift.EndTime+","+shift.Break+")";
                SQLiteCommand InsertCom = new SQLiteCommand(InsertSql, dbcon);
                InsertCom.ExecuteNonQuery();
            }
            dbcon.Close();
        }
    }
}

Did you get any error message? Exceptions? How do you check if the table is empty or not? — Steve
– Steve, Commented Nov 29, 2015 at 23:16
Check to see if any of the parameter values you are passing to the sql string such as shift.EmployeeID, shift.StartTime etc are NULL — Harsh
– Harsh, Commented Nov 29, 2015 at 23:27
@Steve I get this: SQL logic error or missing database near "06": syntax error. I checked by locating the db file in my system and checking it via a sqlite browser. — Matt Baech
– Matt Baech, Commented Nov 29, 2015 at 23:44
Probably this error is caused by your string concatenation of the insert values. Something is not in the correct format and the insert fails. You need to learn how to use a parameterized query — Steve
– Steve, Commented Nov 29, 2015 at 23:46

Steve · Accepted Answer · 2015-11-30 00:08:07Z

2

A parameterized query could help a lot avoiding problems in parsing input values (and just for fun read about Sql Injection here)

foreach (string line in lines)
{
    string[] fields = line.Split(';');
    shift.EmployeeID = Int32.Parse(fields[0]);
    DateTime dt = DateTime.ParseExact(fields[1], "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
    shift.Date = dt;
    DateTime st = DateTime.ParseExact(fields[2], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
    shift.StartTime = st;
    DateTime et = DateTime.ParseExact(fields[3], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
    shift.EndTime = et;
    shift.Break = Int32.Parse(fields[4]);
    string InsertSql = @"INSERT INTO Shifts 
                        (EmployeeID, Date, StartTime, EndTime, Break) 
                        values ($eid, $date, $stime, $etime, $break)";

    SQLiteCommand InsertCom = new SQLiteCommand(InsertSql, dbcon);
    InsertCom.Parameters.Add("$eid", DbType.Int32).Value = shift.EmployeeID;
    InsertCom.Parameters.Add("$date", DbType.Date).Value = shift.Date ;
    InsertCom.Parameters.Add("$stime", DbType.Time).Value = shift.StartTime ;
    InsertCom.Parameters.Add("$etime", DbType.Time).Value = shift.EndTime;
    InsertCom.Parameters.Add("$break", DbType.Int32).Value = shift.Break;
    InsertCom.ExecuteNonQuery();
}

In this way, the work to transform your values in the correct format expected by the database engine is no more in the hands of the formatting rules of the string concatenation but is the database engine itself that receives the date and time values and use them as it likes without error in conversions.

As a by-product, your query is a lot more clear and less prone to errors like the one you have made missing to pass the date that was expected as second value.

edited Nov 30, 2015 at 0:08

answered Nov 29, 2015 at 23:56

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Baech Over a year ago

Thanks for the great example! I know about SQL injections, but this app won't take any user input, so I am not so worried there

Steve Over a year ago

@MattBaech please test the two parameters of type Time. I have no way to test it here, in case of errors try using directly the DbType.Date also for them

Matt Baech Over a year ago

well, first of all DbType is not recognized within SQLite, so had to import 'System.Data;' When I run it i get the following error: Insufficient parameters supplied to the command

Matt Baech Over a year ago

Ah, you wrote $sdate and $edate instead of $stime and $etime, and I just blindly copied... Thanks, after I changed it it worked perfectly.

Steve Over a year ago

Uh, it's late here, time to go to bed. See you again on SO.

|

Ramgy Borja · Accepted Answer · 2017-09-13 05:37:39Z

Another way of inserting using SQLite

foreach (string line in lines)
{
    string[] fields = line.Split(';');
    shift.EmployeeID = Int32.Parse(fields[0]);
    DateTime dt = DateTime.ParseExact(fields[1], "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
    shift.Date = dt;
    DateTime st = DateTime.ParseExact(fields[2], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
    shift.StartTime = st;
    DateTime et = DateTime.ParseExact(fields[3], "HHmm", System.Globalization.CultureInfo.InvariantCulture);
    shift.EndTime = et;
    shift.Break = Int32.Parse(fields[4]);

    using (var con = new SQLiteConnection { ConnectionString = "ConnectionString" })
    {
        using (var command = new SQLiteCommand { Connection = con })
        {
           con.Open();

           try
           {
              command.CommandText = @"INSERT INTO Shifts 
                                      (EmployeeID, Date, StartTime, EndTime, Break) 
                                      VALUES (@eid, @date, @stime, @etime, @break)";
             command.Parameters.AddWithValue("@eid", shift.EmployeeID);
             command.Parameters.AddWithValue("@date", shift.Date);
             command.Parameters.AddWithValue("@stime", shift.StartTime);
             command.Parameters.AddWithValue("@etime", shift.EndTime);
             command.Parameters.AddWithValue("@break", shift.Break);
             command.ExecuteNonQuery();
             command.Parameters.Clear();
           }
           catch(SQLiteException ex)
           {
              throw ex; 
           }

        }
    }

}

Collectives™ on Stack Overflow

C# SQLite insert not working

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related