none
Using a simple SQLite database RRS feed

  • Question

  • I'm trying to set up a simple database in SQLite, containing a couple of tables with associated data. I have absolutely no idea where to start, though - I'm familiar with the basic SQLite syntax and in fact have a pre-existing database I'm planning to use, but I don't know how to open or interact with it in C#/VS. Can anyone explain the basics to me, or point me to a reliable resource I can use to research it myself?

    {also, I've installed and added the reference to System.Data.SQLite;}


    C# newbie, learning on the go. I will probably ask a lot of followup questions about any answers already given, so fair warning and all.


    • Edited by TheQuinch Sunday, August 5, 2012 7:11 AM
    Sunday, August 5, 2012 7:07 AM

Answers

  • SQLiteConnection cnn;
    var connectionString = "data source=\"" + fullPath + "\"";
    cnn = new SQLiteConnection(connectionString);
                    cnn.Open();
    
    
    
    //-------------------
    
            internal int GetLastRowId(SQLiteConnection cnn)
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT last_insert_rowid()";
                        cmd.ExecuteNonQuery();
                        int lastID = Convert.ToInt32(cmd.ExecuteScalar());
    
                        return lastID;
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
                return 0;
            }
    
    
    
    
            public void GetStats(out int numRows)
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT COUNT(*) as cnt from User";
                        cmd.CommandType = System.Data.CommandType.Text;
    
                        SQLiteDataReader reader;
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            numRows = Convert.ToInt32(reader["cnt"]);
                            return;
                        }
    
                        numRows = 0;
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                    numRows = 0;
                }
            }
    
    
    
            public void Vacuum()
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = "VACUUM";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
            }
    
    
    
    
    
            private void InsertUser(User user)
            {
                try
                {
    
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"INSERT INTO [User] ([UserId], [VIP], [warnCounter], [kickCounter], [banned], [country], 
    [userStatusType], [totalVisits], [LastVipDate], [waitingMessage], [waitingMessageRecurrence], [messageVisibilityType]) 
    VALUES (@UserId, @VIP, @warnCounter, @kickCounter, @banned, @country, @userStatusType, @totalVisits, @LastVipDate, 
    @waitingMessage, @waitingMessageRecurrence, @messageVisibilityType)";
    
                        cmd.Parameters.Add(new SQLiteParameter("@UserId") { Value = user.UserId, });
                        cmd.Parameters.Add(new SQLiteParameter("@VIP") { Value = user.VIP });
                        cmd.Parameters.Add(new SQLiteParameter("@warnCounter") { Value = user.warnCounter });
                        cmd.Parameters.Add(new SQLiteParameter("@kickCounter") { Value = user.kickCounter });
                        cmd.Parameters.Add(new SQLiteParameter("@banned") { Value = user.banned });
                        cmd.Parameters.Add(new SQLiteParameter("@country") { Value = user.country });
                        cmd.Parameters.Add(new SQLiteParameter("@userStatusType") { Value = user.userStatusType });
                        cmd.Parameters.Add(new SQLiteParameter("@totalVisits") { Value = user.totalVisits });
                        cmd.Parameters.Add(new SQLiteParameter("@LastVipDate") { Value = user.LastVipDate });
                        cmd.Parameters.Add(new SQLiteParameter("@waitingMessage") { Value = user.waitingMessage });
                        cmd.Parameters.Add(new SQLiteParameter("@waitingMessageRecurrence") { Value = user.waitingMessageRecurrence });
                        cmd.Parameters.Add(new SQLiteParameter("@messageVisibilityType") { Value = user.messageVisibilityType });
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
            }
    
    
    
    
            internal UserData GetDetailsFromUserId(long userId)
            {
                var data = new UserData { Name = "Unknown", SideId = 0, UserId = 0 };
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"select UserId, Name, Team from Characters where UserId=@userId";
                        cmd.Parameters.Add(new SQLiteParameter("@userId") { Value = userId });
                        cmd.CommandType = System.Data.CommandType.Text;
    
                        SQLiteDataReader reader;
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            data.UserId = Convert.ToInt64(reader["UserId"]);
                            data.Name = reader["Name"].ToString();
                            data.SideId = Convert.ToInt32(reader["Team"]);
                        }
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
                return data;
            }

    Here's a bunch of stuff from my SQLite database layer. At the top is the connection, you can do this once and then reuse the connection in each of your calls.

    GetLastRowId gets the autoincremented id of the last insert.

    GetStats shows pulling one count value out

    Vacuum is to compact your dfatabase, very handy

    Insert User shows a full save into database with parameters

    getDetailsFromuserId shows a full extract back into a class

    Hope that helps! :D

    Regards,
    Pete


    #PEJL


    Sunday, August 5, 2012 12:11 PM
    Moderator

All replies

  • Here´s an excellent article. 

    http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

    You can find help with your connectionstring here. 

    http://www.connectionstrings.com/

    And more specific on SQL Lite - thou concepts are the same

    http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/

    Good luck.


    //SFP


    • Edited by -SFP- Sunday, August 5, 2012 11:09 AM Further information
    Sunday, August 5, 2012 11:07 AM
  • SQLiteConnection cnn;
    var connectionString = "data source=\"" + fullPath + "\"";
    cnn = new SQLiteConnection(connectionString);
                    cnn.Open();
    
    
    
    //-------------------
    
            internal int GetLastRowId(SQLiteConnection cnn)
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT last_insert_rowid()";
                        cmd.ExecuteNonQuery();
                        int lastID = Convert.ToInt32(cmd.ExecuteScalar());
    
                        return lastID;
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
                return 0;
            }
    
    
    
    
            public void GetStats(out int numRows)
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT COUNT(*) as cnt from User";
                        cmd.CommandType = System.Data.CommandType.Text;
    
                        SQLiteDataReader reader;
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            numRows = Convert.ToInt32(reader["cnt"]);
                            return;
                        }
    
                        numRows = 0;
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                    numRows = 0;
                }
            }
    
    
    
            public void Vacuum()
            {
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = "VACUUM";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
            }
    
    
    
    
    
            private void InsertUser(User user)
            {
                try
                {
    
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"INSERT INTO [User] ([UserId], [VIP], [warnCounter], [kickCounter], [banned], [country], 
    [userStatusType], [totalVisits], [LastVipDate], [waitingMessage], [waitingMessageRecurrence], [messageVisibilityType]) 
    VALUES (@UserId, @VIP, @warnCounter, @kickCounter, @banned, @country, @userStatusType, @totalVisits, @LastVipDate, 
    @waitingMessage, @waitingMessageRecurrence, @messageVisibilityType)";
    
                        cmd.Parameters.Add(new SQLiteParameter("@UserId") { Value = user.UserId, });
                        cmd.Parameters.Add(new SQLiteParameter("@VIP") { Value = user.VIP });
                        cmd.Parameters.Add(new SQLiteParameter("@warnCounter") { Value = user.warnCounter });
                        cmd.Parameters.Add(new SQLiteParameter("@kickCounter") { Value = user.kickCounter });
                        cmd.Parameters.Add(new SQLiteParameter("@banned") { Value = user.banned });
                        cmd.Parameters.Add(new SQLiteParameter("@country") { Value = user.country });
                        cmd.Parameters.Add(new SQLiteParameter("@userStatusType") { Value = user.userStatusType });
                        cmd.Parameters.Add(new SQLiteParameter("@totalVisits") { Value = user.totalVisits });
                        cmd.Parameters.Add(new SQLiteParameter("@LastVipDate") { Value = user.LastVipDate });
                        cmd.Parameters.Add(new SQLiteParameter("@waitingMessage") { Value = user.waitingMessage });
                        cmd.Parameters.Add(new SQLiteParameter("@waitingMessageRecurrence") { Value = user.waitingMessageRecurrence });
                        cmd.Parameters.Add(new SQLiteParameter("@messageVisibilityType") { Value = user.messageVisibilityType });
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
            }
    
    
    
    
            internal UserData GetDetailsFromUserId(long userId)
            {
                var data = new UserData { Name = "Unknown", SideId = 0, UserId = 0 };
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"select UserId, Name, Team from Characters where UserId=@userId";
                        cmd.Parameters.Add(new SQLiteParameter("@userId") { Value = userId });
                        cmd.CommandType = System.Data.CommandType.Text;
    
                        SQLiteDataReader reader;
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            data.UserId = Convert.ToInt64(reader["UserId"]);
                            data.Name = reader["Name"].ToString();
                            data.SideId = Convert.ToInt32(reader["Team"]);
                        }
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
                return data;
            }

    Here's a bunch of stuff from my SQLite database layer. At the top is the connection, you can do this once and then reuse the connection in each of your calls.

    GetLastRowId gets the autoincremented id of the last insert.

    GetStats shows pulling one count value out

    Vacuum is to compact your dfatabase, very handy

    Insert User shows a full save into database with parameters

    getDetailsFromuserId shows a full extract back into a class

    Hope that helps! :D

    Regards,
    Pete


    #PEJL


    Sunday, August 5, 2012 12:11 PM
    Moderator
  • It does, thanks. A question, though - how would I pull up multiple rows from the table, say in the form of a 2D array?

    C# newbie, learning on the go. I will probably ask a lot of followup questions about any answers already given, so fair warning and all.

    Sunday, August 5, 2012 7:32 PM
  • You could also use a DataSet/DataTable (rather than an Array):

    // use a DataAdaper instead of a DataReader
    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 5, 2012 8:54 PM
    Moderator
  • It does, thanks. A question, though - how would I pull up multiple rows from the table, say in the form of a 2D array?

    C# newbie, learning on the go. I will probably ask a lot of followup questions about any answers already given, so fair warning and all.

    Damn, sorry, the only scenario I forgot to include! Here you go...

     

            private List<UserNote> GetUserNotes(long userId)
            {
                var notes = new List<UserNote>();
                try
                {
                    using (SQLiteCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = @"SELECT * FROM [userNotes] WHERE [userId] = @userId";
                        cmd.Parameters.Add(new SQLiteParameter("@userId") { Value = userId });
                        cmd.CommandType = System.Data.CommandType.Text;
    
                        SQLiteDataReader reader;
                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            var un = new UserNote
                            {
                                note = reader["note"].ToString(),
                                noteId = Convert.ToInt32(reader["noteId"])
                            };
    
                            if (reader["dateNoted"] != DBNull.Value)
                                un.dateNoted = DateTime.Parse(reader["dateNoted"].ToString());
    
                            if (reader["noteType"] != DBNull.Value)
                                un.noteType = (NoteType)Convert.ToInt32(reader["noteType"]);
                            else
                                un.noteType = NoteType.Normal;
    
                            notes.Add(un);
                        }
                    }
                }
                catch (Exception exc)
                {
                    DoException(exc);
                }
    
                return notes;
            }

    This builds a List<UserNote> collection.
    This also shows how to handle columns that may be null. You test first for DBNull.Value.
    NoteType is an Enum.

     

    Please mark both as answers if they answered your questions ;D

     

    Best regards,
    Pete


    #PEJL


    Sunday, August 5, 2012 10:20 PM
    Moderator