SQLite Helper Class in C#

Question

Here is a SQLite Helper Class I created. Any input appreciated, including security and simplification.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SQLiteHelper
{
    public class EntryList
    {
        public EntryList()
        {
            ColumnName = new List<string>();
            DbType = new List<DbType>();
            Content = new List<string>();
        }
        public List<string> ColumnName { set; get; }
        public List<DbType> DbType { set; get; }
        public List<string> Content { set; get; }


    }
    public class ListWithName
    {
        public ListWithName()
        {
            SubItems = new List<string>();
        }
        public string Text { set; get; }
        public List<string> SubItems { set; get; }
    }
    public class ColumnProperties
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string DataType { get; set; }
        public string Content { get; set; }
        public bool AllowNull { get; set; }
    }
    public class Column : IEnumerable<ColumnProperties>
    {
        public Column()
        {
            cols = new List<ColumnProperties>();
        }
        private List<ColumnProperties> cols = new List<ColumnProperties>();
        public IEnumerator<ColumnProperties> GetEnumerator()
        {
            return this.cols.GetEnumerator();
        }
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
        public void Add(string Name)
        {
            ColumnProperties ml = new ColumnProperties();
            ml.Name = Name;
            ml.DataType = "VARCHAR";
            ml.AllowNull = true;
            cols.Add(ml);
        }
        public void Add(string Name, string DataType)
        {
            ColumnProperties ml = new ColumnProperties();
            ml.Name = Name;
            ml.DataType = DataType;
            ml.AllowNull = true;
            cols.Add(ml);
        }
        public void Add(string Name, string DataType, bool AllowNulls)
        {
            ColumnProperties ml = new ColumnProperties();
            ml.Name = Name;
            ml.DataType = DataType;
            ml.AllowNull = AllowNulls;
            cols.Add(ml);
        }
        public void Add(string Name, string DataType, bool AllowNulls, string ID)
        {
            ColumnProperties ml = new ColumnProperties();
            ml.Name = Name;
            ml.DataType = DataType;
            ml.ID = ID;
            ml.AllowNull = AllowNulls;
            cols.Add(ml);
        }
        public int Count
        {
            get { return cols.Count; }
        }

    }

  public class Table
    {
      public Table()
      {
          Columns = new Column();
      }
        public string Name { set; get; }
        public Column Columns { set; get; } 

    }
    class SQLiteHelper
    {
        SQLiteDataReader DataReader;
        public string DatabaseFile { set; get; }
        public string Password { set; get; }
        SQLiteConnection DataBaseConnnection = new SQLiteConnection();
        private void SetConnection()
        {
            if (DataBaseConnnection.State== System.Data.ConnectionState.Open)
            {
                DataBaseConnnection.Close();
            }
            if (Password != null)
            {
                DataBaseConnnection.ConnectionString = @"Data Source=" + DatabaseFile + "; Password=" + Password + ";";
                DataBaseConnnection.Open();
            }
            if (Password == null)
            {
                DataBaseConnnection.ConnectionString = @"Data Source=" + DatabaseFile + ";";
                DataBaseConnnection.Open();
            }
        }
        public void CreateDatabase()
        {
            SetConnection();
            SQLiteConnection.CreateFile(DatabaseFile);            
            if (Password !=null)
            {
                DataBaseConnnection.SetPassword(Password);
            }
        }
        public void CreateTable(Table Table)
        {
            try
            {
                SetConnection();
                string firstLine = "CREATE TABLE IF NOT EXISTS [" + Table.Name + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
                StringBuilder queryBuilder = new StringBuilder();
                queryBuilder.Append(firstLine);
                foreach (var item in Table.Columns)
                {
                    string nl = "";
                    if (item.AllowNull) nl = "NULL";
                    else nl = "NOT NULL";

                    queryBuilder.Append("[" + item.Name + "] " + item.DataType + " " + nl + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
                sqliteCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
                MessageBox.Show(queryBuilder.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                frmPassword fp = new frmPassword();
                if (fp.ShowDialog() == DialogResult.OK)
                {
                    Password = fp.txtPass.Text;
                    CreateTable(Table);
                }
            }

        }
        public void CreateTable(string TableName, string[] ColumnNames, bool[] AllowNulls, DbType[] DbTypes)
        {
            TableName = RemoveSpecialCharacters(TableName);
            try
            {
                SetConnection();
                string firstLine = "CREATE TABLE IF NOT EXISTS [" + TableName + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
                StringBuilder queryBuilder = new StringBuilder();
                queryBuilder.Append(firstLine);
                for (int i = 0; i < ColumnNames.Length; i++)
                {
                    string nl = "";
                    if (AllowNulls[i]) nl = "NULL";
                    else nl = "NOT NULL";

                    queryBuilder.Append("[" + ColumnNames[i] + "] " + DbTypes[i] + " " + nl + ", ");

                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
                sqliteCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
                MessageBox.Show(queryBuilder.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                frmPassword fp = new frmPassword();
                if (fp.ShowDialog() == DialogResult.OK)
                {
                    Password = fp.txtPass.Text;
                    CreateTable(TableName, ColumnNames, AllowNulls, DbTypes);
                }
            }

        }
        public void DeleteTable(string TableName)
        {
            try
            {
                SetConnection();
                SQLiteCommand sqliteCommand = new SQLiteCommand("DROP TABLE IF EXISTS " + TableName, DataBaseConnnection);
                sqliteCommand.ExecuteNonQuery();
                SQLiteCommand VacuumCommand = new SQLiteCommand("vacuum;", DataBaseConnnection);
                VacuumCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                frmPassword fp = new frmPassword();
                if (fp.ShowDialog() == DialogResult.OK)
                {
                    Password = fp.txtPass.Text;
                    DeleteTable(TableName);
                }
            }

        }
        public List<string> GetTableNames()
        {
            List<string> tables = new List<string>();
            try
            {
                SetConnection();
                SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT NAME FROM sqlite_master WHERE TYPE='table' ORDER BY NAME", DataBaseConnnection);
                DataReader = sqliteCommand.ExecuteReader();
                while (DataReader.Read())
                {
                    if (DataReader.HasRows)
                    {
                        tables.Add(DataReader[0].ToString());
                    }
                }
                DataBaseConnnection.Close();

            }
            catch (Exception ex)
            {
               MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            return tables;
        }
        public Column GetColumnsFromTableName(string TableName)
        {
            TableName = RemoveSpecialCharacters(TableName);
            Column cols = new Column();
            try
            {
                SetConnection();
                SQLiteCommand sqliteCommand = new SQLiteCommand("PRAGMA table_info('" + TableName + "');", DataBaseConnnection);
                DataReader = sqliteCommand.ExecuteReader();
                while (DataReader.Read())
                {
                    ColumnProperties ml = new ColumnProperties();
                    ml.ID = DataReader[0].ToString();
                    ml.Name = DataReader[1].ToString();
                    ml.DataType = DataReader[2].ToString();
                    bool nl = false;
                    if (DataReader[3].ToString() == "0") nl = true;
                    if (DataReader[3].ToString() == "1") nl = false;
                    ml.AllowNull = nl;
                    cols.Add(DataReader[1].ToString(), DataReader[2].ToString(), nl, DataReader[0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            return cols;

        }
        public void CreateEntry(string TableName, EntryList EntryList)
        {
            TableName = RemoveSpecialCharacters(TableName);
            try
            {
                SetConnection();
                StringBuilder queryBuilder = new StringBuilder();
                queryBuilder.Append("insert into " + TableName + " (");
                foreach (var item in EntryList.ColumnName)
                {
                    queryBuilder.Append(item + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                queryBuilder.Append(" values (");
                foreach (var item in EntryList.ColumnName)
                {
                    queryBuilder.Append("@" + item + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);

                for (int i = 0; i < EntryList.ColumnName.Count; i++)
                {
                    sqliteCommand.Parameters.Add("@" + EntryList.ColumnName[i], EntryList.DbType[i]).Value = EntryList.Content[i];
                }
                sqliteCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
        }
        public void CreateEntry(string TableName, object[] Content)
        {
            TableName = RemoveSpecialCharacters(TableName);
            try
            {
                SetConnection();
                StringBuilder queryBuilder = new StringBuilder();
                queryBuilder.Append("insert into " + TableName + " (");
                foreach (var item in GetColumnsFromTableName(TableName))
                {
                    queryBuilder.Append(item.Name + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                queryBuilder.Append(" values (");
                foreach (var item in GetColumnsFromTableName(TableName))
                {
                    queryBuilder.Append("@" + item.Name + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(")");
                SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
                List<string> colsNames = new List<string>();
                foreach (var item in GetColumnsFromTableName(TableName))
                {
                    colsNames.Add(item.Name);
                }

                for (int i = 0; i < GetColumnsFromTableName(TableName).Count; i++)
                {
                    sqliteCommand.Parameters.Add("@" + colsNames[i], DbType.Object).Value = Content[i];
                }
                sqliteCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
        }
        public void DeleteEntry(string TableName, string ColumnName, string Equals)
        {
            TableName = RemoveSpecialCharacters(TableName);
            ColumnName = RemoveSpecialCharacters(ColumnName);
            Equals = RemoveSpecialCharacters(Equals);
            try
            {
                SetConnection();
                SQLiteCommand sqliteCommand = new SQLiteCommand("DELETE FROM " + TableName + " WHERE " + ColumnName + "=@" + ColumnName, DataBaseConnnection);
                sqliteCommand.Parameters.Add("@" + ColumnName, DbType.Object).Value = Equals;
                sqliteCommand.ExecuteNonQuery();
                SQLiteCommand VacuumCommand = new SQLiteCommand("vacuum;", DataBaseConnnection);
                VacuumCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
            }
            catch (Exception ex)
            {
                 MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
        }
        public void UpdateEntry(string TableName, EntryList EntryList, string ColumnName, string Equals)
        {
            TableName = RemoveSpecialCharacters(TableName);
            ColumnName = RemoveSpecialCharacters(ColumnName);
            Equals = RemoveSpecialCharacters(Equals);

            try
            {
                SetConnection();
                StringBuilder queryBuilder = new StringBuilder();
                queryBuilder.Append("update " + TableName + " set ");
                foreach (var item in EntryList.ColumnName)
                {
                    queryBuilder.Append(item + "=@" + item + ", ");
                }
                queryBuilder.Remove(queryBuilder.Length - 2, 2);
                queryBuilder.Append(" ");
                queryBuilder.Append(" WHERE " + ColumnName + "='" + Equals + "'");
                SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);

                for (int i = 0; i < EntryList.ColumnName.Count; i++)
                {
                    sqliteCommand.Parameters.Add("@" + EntryList.ColumnName[i], EntryList.DbType[i]).Value = EntryList.Content[i];
                }
                sqliteCommand.ExecuteNonQuery();
                DataBaseConnnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);                
            }

        }
        public List<ListWithName> GetEntries(string TableName)
        {
            TableName = RemoveSpecialCharacters(TableName);
            List<ListWithName> listLvi = new List<ListWithName>();
            try
            {
                SetConnection();
                SQLiteCommand sqliteCommand = new SQLiteCommand("select * from " + TableName, DataBaseConnnection);
                DataReader = sqliteCommand.ExecuteReader();
                while (DataReader.Read())
                {
                    ListWithName lwn = new ListWithName();
                    lwn.Text = DataReader[0].ToString();
                    for (int i = 1; i < DataReader.FieldCount; i++)
                    {
                        lwn.SubItems.Add(DataReader[i].ToString());
                    }
                    listLvi.Add(lwn);
                }
            }
            catch (Exception ex)
            {
                 MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }

            return listLvi;
        }
        public List<ListWithName> SearchDatabase(string ColumnName, string SearchKeyWord)
        {
            ColumnName = RemoveSpecialCharacters(ColumnName);
            SearchKeyWord = RemoveSpecialCharacters(SearchKeyWord);
            List<ListWithName> listLwn = new List<ListWithName>();
            try
            {
                foreach (var table in GetTableNames())
                {
                    if (table != "sqlite_sequence")
                    {
                        SetConnection();
                        SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + ColumnName + " LIKE @searchKey", DataBaseConnnection);
                        sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = "%" + SearchKeyWord + "%";
                        DataReader = sqliteCommand.ExecuteReader();
                        if (DataReader.HasRows)
                        {
                            while (DataReader.Read())
                            {
                                ListWithName lwn = new ListWithName();
                                lwn.Text = DataReader[0].ToString();
                                for (int i = 1; i < DataReader.FieldCount; i++)
                                {
                                    lwn.SubItems.Add(DataReader[i].ToString());
                                }
                                listLwn.Add(lwn);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            return listLwn;
        }
        public List<ListWithName> SearchDatabase(string SearchKeyWord)
        {
            SearchKeyWord = RemoveSpecialCharacters(SearchKeyWord);
            List<ListWithName> listLwn = new List<ListWithName>();
            try
            {
                foreach (var table in GetTableNames())
                {
                    foreach (var col in GetColumnsFromTableName(table))
                    {
                        if (table != "sqlite_sequence")
                        {
                            SetConnection();
                            SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + col.Name + " LIKE @searchKey", DataBaseConnnection);
                            sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = "%" + SearchKeyWord + "%"; ;
                            DataReader = sqliteCommand.ExecuteReader();
                            if (DataReader.HasRows)
                            {
                                while (DataReader.Read())
                                {
                                    ListWithName lwn = new ListWithName();
                                    lwn.Text = DataReader[0].ToString();
                                    for (int i = 1; i < DataReader.FieldCount; i++)
                                    {
                                        lwn.SubItems.Add(DataReader[i].ToString());
                                    }
                                    listLwn.Add(lwn);
                                }
                            }
                        } 
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            return listLwn;
        }
        public static string RemoveSpecialCharacters(string str)
        {
            StringBuilder sb = new StringBuilder();
            foreach (char c in str)
            {
                if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '.' || c == '_')
                {
                    sb.Append(c);
                }
            }
            return sb.ToString();
        }

    }
}

Please see Can I edit my own question to include suggested changes from answers? — ChrisW
– ChrisW, Commented Feb 27, 2014 at 21:42

Vogel612 · Accepted Answer · 2014-02-27 10:03:44Z

Some things that jump at me for the first look.

Your DataType should definitely not be a String but some Enum. This makes passing invalid arguments to the function almost impossible. something like the following should do:
```
 public enum DbDataTypes{
     VARCHAR, TIMESTAMP, LONG, INT, DOUBLE, DATETIME, [...]
 }
```
I personally prefer to have the overloaded methods with less parameters call the higher overloaded ones:
```
public void Add(string name){
   Add(name, DbDataType.VARCHAR, true);
}
```
The singular in Column class makes one think you have only one column, when in fact you have several. Chose a more speaking name like ColumnList
I don't like that you add the ID column as primary key for every table you create. Mapping tables as the most common example often do not have a column named ID. They also usually have a combined primary key.
You don't check the size of the passed arrays in your CreateTable method. This may lead to difficult to debug IndexOutOfRange exceptions

you might want to refactor your entryList. it probably will be better to have:

public class EntryList<Entry> : IList<T> {

}
public class Entry{
    public string ColumnName {get; set;}
    public DbType DataType {get; set;}
    public string Value {get; set;}
}

that way you ensure to always have a corresponding number of ColumnName, DataType and Value.

Also you should chose one naming convention to follow. some of your variables are in camelCase some in MixedCase.
Finally you really should move this to multiple files. You could create partial classes to have some more overview of what functionality is where. Luckily, as opposed to java, the filename can be different from the classname ;)

Thanks for your detailed feedback, 1. I've enumerated the DataTypes. 2. Done that too :) 3. Changed that too. 4. I've always done that, since most of my applications depend on that. 5. I use foreach, I could be wrong but I think that will be fine since I haven't had any errors. 6. That doesn't work in my case. 7. renamed the variables. 8. It's all in one class one file, for portability reasons, since all you have to do is add one file to the project, or create one class and paste the code in it. Again, thanks for your feedback. — NetInfo
– NetInfo, Commented Feb 27, 2014 at 21:50
@NetInfo what do you mean by 6. "doesn't work in my case" ? is it just that it's not applicable due to project circumstances or are you having trouble applying the changes? Btw. you're welcome, it was a joy analyzing ;) — Vogel612
– Vogel612, Commented Feb 27, 2014 at 21:53
Because I still need to access ColumnName, DbType and Content in other scenarios, plus I think I'm just being a bit lazy trying to change it lol, thanks again, I really appreciate it. — NetInfo
– NetInfo, Commented Feb 27, 2014 at 22:06
Oh n plus I've added a method inside that class "Add(string columnName, DbType dbType, string content)". — NetInfo
– NetInfo, Commented Feb 27, 2014 at 22:09

Jamal · Accepted Answer · 2015-01-28 22:10:25Z

0

I think this method:

RemoveSpecialCharacters(string str)

can be replaced by:

Regex.Replace(input, @"[^.\w]",string.Empty)

It will be better and you won't need to create special method.

edited Jan 28, 2015 at 22:10

Jamal

35.2k13 gold badges134 silver badges238 bronze badges

answered Jan 28, 2015 at 22:01

user63983

1

Add a comment |

Stack Exchange Network

SQLite Helper Class in C#

2 Answers 2

You must log in to answer this question.

Hot Network Questions

SQLite Helper Class in C#

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions