using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLiteHelper
{
public enum dbDataTypes
{
INT,
INTEGER,
TINYINT,
SMALLINT,
MEDIUMINT,
BIGINT,
[Description("UNSIGNED BIG INT")]
UNSIGNEDBIGINT,
INT2,
INT8,
CHARACTER,
VARCHAR,
[Description("VARYING CHARACTER")]
VARYINGCHARACTER,
NCHAR,
[Description("NATIVE CHARACTER")]
NATIVECHARACTER,
NVARCHAR,
TEXT,
CLOB,
BLOB,
REAL,
DOUBLE,
[Description("DOUBLE PRECISION")]
DOUBLEPRECISION,
FLOAT,
NUMERIC,
DECIMAL,
BOOLEAN,
DATE,
DATETIME
}
public class EntryList
{
public EntryList()
{
ColumnNamesColumnName = new List<string>();
DbTypesDbType = new List<DbType>();
ContentsContent = new List<string>();
}
public void Add(string columnName, DbType dbType, string content)
{
ColumnNames.Add(columnName);
DbTypes.Add(dbType);
Contents.Add(content);
}
public List<string> ColumnNamesColumnName { set; get; }
public List<DbType> DbTypesDbType { set; get; }
public List<string> ContentsContent { 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 dbDataTypesstring DataType { get; set; }
public string Content { get; set; }
public bool AllowNull { get; set; }
}
public class ColumnListColumn : IEnumerable<ColumnProperties>
{
public ColumnListColumn()
{
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) { Add(name, dbDataTypes.VARCHAR, true); }
public void Add(string name, dbDataTypes dataType) { Add(name, dataType, true); }
public void Add(string name, dbDataTypes dataType, bool allowNulls) { Add(name, dataType, allowNulls); }
public void Add(string name, dbDataTypes dataType, bool allowNulls, string idName)
{
ColumnProperties ml = new ColumnProperties();
ml.Name = name;Name;
ml.DataType = dataType;
ml.ID = id;"VARCHAR";
ml.AllowNull = allowNulls;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 ColumnListColumn();
}
public string Name { set; get; }
public ColumnListColumn Columns { set; get; }
}
class SQLiteHelper
{
SQLiteDataReader dataReader;DataReader;
public string DatabaseFile { set; get; }
public string Password { set; get; }
SQLiteConnection DataBaseConnnection = new SQLiteConnection();
private void SetConnection()
{
if (DataBaseConnnection.State ==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=null)
{
DataBaseConnnection.SetPassword(Password);
}
}
public void CreateTable(Table tableTable)
{
try
{
SetConnection();
string firstLine = "CREATE TABLE IF NOT EXISTS [" + tableTable.Name + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(firstLine);
foreach (var item in tableTable.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(tableTable);
}
}
}
public void CreateTable(string tableNameTableName, string[] columnNamesColumnNames, bool[] allowNullsAllowNulls, DbType[] dbTypesDbTypes)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
try
{
SetConnection();
string firstLine = "CREATE TABLE IF NOT EXISTS [" + tableNameTableName + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(firstLine);
for (int i = 0; i < columnNamesColumnNames.Length; i++)
{
string nl = "";
if (allowNulls[i]AllowNulls[i]) nl = "NULL";
else nl = "NOT NULL";
queryBuilder.Append("[" + columnNames[i]ColumnNames[i] + "] " + dbTypes[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(tableNameTableName, columnNamesColumnNames, allowNullsAllowNulls, dbTypesDbTypes);
}
}
}
public void DeleteTable(string tableNameTableName)
{
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("DROP TABLE IF EXISTS " + tableNameTableName, 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(tableNameTableName);
}
}
}
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);
dataReaderDataReader = sqliteCommand.ExecuteReader();
while (dataReaderDataReader.Read())
{
if (dataReaderDataReader.HasRows)
{
tables.Add(dataReader[0]DataReader[0].ToString());
}
}
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
return tables;
}
public ColumnListColumn GetColumnsFromTableName(string tableNameTableName)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
ColumnListColumn cols = new ColumnListColumn();
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("PRAGMA table_info('" + tableNameTableName + "');", DataBaseConnnection);
dataReaderDataReader = sqliteCommand.ExecuteReader();
while (dataReaderDataReader.Read())
{
ColumnProperties ml = new ColumnProperties();
ml.ID = dataReader[0]DataReader[0].ToString();
ml.Name = dataReader[1]DataReader[1].ToString();
ml.DataType = (dbDataTypes)Enum.Parse(typeof(dbDataTypes), dataReader[2]DataReader[2].ToString());
bool nl = false;
if (dataReader[3]DataReader[3].ToString() == "0") nl = true;
if (dataReader[3]DataReader[3].ToString() == "1") nl = false;
ml.AllowNull = nl;
cols.Add(dataReader[1]DataReader[1].ToString(), (dbDataTypes)Enum.Parse(typeof(dbDataTypes), dataReader[2]DataReader[2].ToString()), nl, dataReader[0]DataReader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
return cols;
}
public void CreateEntry(string tableNameTableName, EntryList entryListEntryList)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("insert into " + tableNameTableName + " (");
foreach (var item in entryListEntryList.ColumnNamesColumnName)
{
queryBuilder.Append(item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in entryListEntryList.ColumnNamesColumnName)
{
queryBuilder.Append("@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < entryListEntryList.ColumnNamesColumnName.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryListEntryList.ColumnNames[i]ColumnName[i], entryListEntryList.DbTypes[i]DbType[i]).Value = entryListEntryList.Contents[i];Content[i];
}
sqliteCommand.ExecuteNonQuery();
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
public void CreateEntry(string tableNameTableName, object[] contentContent)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("insert into " + tableNameTableName + " (");
foreach (var item in GetColumnsFromTableName(tableNameTableName))
{
queryBuilder.Append(item.Name + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in GetColumnsFromTableName(tableNameTableName))
{
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(tableNameTableName))
{
colsNames.Add(item.Name);
}
for (int i = 0; i < GetColumnsFromTableName(tableNameTableName).Count; i++)
{
sqliteCommand.Parameters.Add("@" + colsNames[i], DbType.Object).Value = content[i];Content[i];
}
sqliteCommand.ExecuteNonQuery();
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
public void DeleteEntry(string tableNameTableName, string columnNameColumnName, string eQualsEquals)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
columnNameColumnName = RemoveSpecialCharacters(columnNameColumnName);
eQualsEquals = RemoveSpecialCharacters(eQualsEquals);
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("DELETE FROM " + tableNameTableName + " WHERE " + columnNameColumnName + "=@" + columnNameColumnName, DataBaseConnnection);
sqliteCommand.Parameters.Add("@" + columnNameColumnName, DbType.Object).Value = eQuals;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 tableNameTableName, EntryList entryListEntryList, string columnNameColumnName, string eQualsEquals)
{
tableNameTableName = RemoveSpecialCharacters(tableNameTableName);
columnNameColumnName = RemoveSpecialCharacters(columnNameColumnName);
eQualsEquals = RemoveSpecialCharacters(eQualsEquals);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("update " + tableNameTableName + " set ");
foreach (var item in entryListEntryList.ColumnNamesColumnName)
{
queryBuilder.Append(item + "=@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(" ");
queryBuilder.Append(" WHERE " + columnNameColumnName + "='" + eQualsEquals + "'");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < entryListEntryList.ColumnNamesColumnName.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryListEntryList.ColumnNames[i]ColumnName[i], entryListEntryList.DbTypes[i]DbType[i]).Value = entryListEntryList.Contents[i];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);
dataReaderDataReader = sqliteCommand.ExecuteReader();
while (dataReaderDataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = dataReader[0]DataReader[0].ToString();
for (int i = 1; i < dataReaderDataReader.FieldCount; i++)
{
lwn.SubItems.Add(dataReader[i]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 columnNameColumnName, string searchKeyWordSearchKeyWord)
{
columnNameColumnName = RemoveSpecialCharacters(columnNameColumnName);
searchKeyWordSearchKeyWord = RemoveSpecialCharacters(searchKeyWordSearchKeyWord);
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 " + columnNameColumnName + " LIKE @searchKey", DataBaseConnnection);
sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = "%" + searchKeyWordSearchKeyWord + "%";
dataReaderDataReader = sqliteCommand.ExecuteReader();
if (dataReaderDataReader.HasRows)
{
while (dataReaderDataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = dataReader[0]DataReader[0].ToString();
for (int i = 1; i < dataReaderDataReader.FieldCount; i++)
{
lwn.SubItems.Add(dataReader[i]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 searchKeyWordSearchKeyWord)
{
searchKeyWordSearchKeyWord = RemoveSpecialCharacters(searchKeyWordSearchKeyWord);
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 = "%" + searchKeyWordSearchKeyWord + "%"; ;
dataReaderDataReader = sqliteCommand.ExecuteReader();
if (dataReaderDataReader.HasRows)
{
while (dataReaderDataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = dataReader[0]DataReader[0].ToString();
for (int i = 1; i < dataReaderDataReader.FieldCount; i++)
{
lwn.SubItems.Add(dataReader[i]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();
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLiteHelper
{
public enum dbDataTypes
{
INT,
INTEGER,
TINYINT,
SMALLINT,
MEDIUMINT,
BIGINT,
[Description("UNSIGNED BIG INT")]
UNSIGNEDBIGINT,
INT2,
INT8,
CHARACTER,
VARCHAR,
[Description("VARYING CHARACTER")]
VARYINGCHARACTER,
NCHAR,
[Description("NATIVE CHARACTER")]
NATIVECHARACTER,
NVARCHAR,
TEXT,
CLOB,
BLOB,
REAL,
DOUBLE,
[Description("DOUBLE PRECISION")]
DOUBLEPRECISION,
FLOAT,
NUMERIC,
DECIMAL,
BOOLEAN,
DATE,
DATETIME
}
public class EntryList
{
public EntryList()
{
ColumnNames = new List<string>();
DbTypes = new List<DbType>();
Contents = new List<string>();
}
public void Add(string columnName, DbType dbType, string content)
{
ColumnNames.Add(columnName);
DbTypes.Add(dbType);
Contents.Add(content);
}
public List<string> ColumnNames { set; get; }
public List<DbType> DbTypes { set; get; }
public List<string> Contents { 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 dbDataTypes DataType { get; set; }
public string Content { get; set; }
public bool AllowNull { get; set; }
}
public class ColumnList : IEnumerable<ColumnProperties>
{
public ColumnList()
{
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) { Add(name, dbDataTypes.VARCHAR, true); }
public void Add(string name, dbDataTypes dataType) { Add(name, dataType, true); }
public void Add(string name, dbDataTypes dataType, bool allowNulls) { Add(name, dataType, allowNulls); }
public void Add(string name, dbDataTypes 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 ColumnList();
}
public string Name { set; get; }
public ColumnList 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();
}
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();
}
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 ColumnList GetColumnsFromTableName(string tableName)
{
tableName = RemoveSpecialCharacters(tableName);
ColumnList cols = new ColumnList();
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 = (dbDataTypes)Enum.Parse(typeof(dbDataTypes), 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(), (dbDataTypes)Enum.Parse(typeof(dbDataTypes), 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.ColumnNames)
{
queryBuilder.Append(item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in entryList.ColumnNames)
{
queryBuilder.Append("@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < entryList.ColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryList.ColumnNames[i], entryList.DbTypes[i]).Value = entryList.Contents[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.ColumnNames)
{
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.ColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryList.ColumnNames[i], entryList.DbTypes[i]).Value = entryList.Contents[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();
}
}
}
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();
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLiteHelper
{
public enum dbDataTypes
{
INT,
INTEGER,
TINYINT,
SMALLINT,
MEDIUMINT,
BIGINT,
[Description("UNSIGNED BIG INT")]
UNSIGNEDBIGINT,
INT2,
INT8,
CHARACTER,
VARCHAR,
[Description("VARYING CHARACTER")]
VARYINGCHARACTER,
NCHAR,
[Description("NATIVE CHARACTER")]
NATIVECHARACTER,
NVARCHAR,
TEXT,
CLOB,
BLOB,
REAL,
DOUBLE,
[Description("DOUBLE PRECISION")]
DOUBLEPRECISION,
FLOAT,
NUMERIC,
DECIMAL,
BOOLEAN,
DATE,
DATETIME
}
public class EntryList
{
public EntryList()
{
ColumnNameColumnNames = new List<string>();
DbTypeDbTypes = new List<DbType>();
ContentContents = new List<string>();
}
public void Add(string columnName, DbType dbType, string content)
{
ColumnNames.Add(columnName);
DbTypes.Add(dbType);
Contents.Add(content);
}
public List<string> ColumnNameColumnNames { set; get; }
public List<DbType> DbTypeDbTypes { set; get; }
public List<string> ContentContents { 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 stringdbDataTypes DataType { get; set; }
public string Content { get; set; }
public bool AllowNull { get; set; }
}
public class ColumnColumnList : IEnumerable<ColumnProperties>
{
public ColumnColumnList()
{
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 Namename)
{
ColumnProperties ml = new ColumnPropertiesAdd();
ml.Name = Name;
ml.DataType = "VARCHAR";
name, mldbDataTypes.AllowNull = true;
VARCHAR, cols.Add(mltrue);
}
public void Add(string Namename, stringdbDataTypes DataTypedataType)
{
ColumnProperties ml = new ColumnPropertiesAdd();
ml.Name = Name;
ml.DataType = DataType;
ml.AllowNull = true;
name, dataType, cols.Add(mltrue);
}
public void Add(string Namename, stringdbDataTypes DataTypedataType, bool AllowNullsallowNulls)
{
ColumnProperties ml = new ColumnPropertiesAdd();
ml.Name = Name;
ml.DataType = DataType;
ml.AllowNull = AllowNulls;
name, dataType, cols.Add(mlallowNulls);
}
public void Add(string Namename, stringdbDataTypes DataTypedataType, bool AllowNullsallowNulls, string IDid)
{
ColumnProperties ml = new ColumnProperties();
ml.Name = Name;name;
ml.DataType = DataType;dataType;
ml.ID = ID;id;
ml.AllowNull = AllowNulls;allowNulls;
cols.Add(ml);
}
public int Count
{
get { return cols.Count; }
}
}
public class Table
{
public Table()
{
Columns = new ColumnColumnList();
}
public string Name { set; get; }
public ColumnColumnList Columns { set; get; }
}
class SQLiteHelper
{
SQLiteDataReader DataReader;dataReader;
public string DatabaseFile { set; get; }
public string Password { set; get; }
SQLiteConnection DataBaseConnnection = new SQLiteConnection();
private void SetConnection()
{
if (DataBaseConnnection.State==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= null)
{
DataBaseConnnection.SetPassword(Password);
}
}
public void CreateTable(Table Tabletable)
{
try
{
SetConnection();
string firstLine = "CREATE TABLE IF NOT EXISTS [" + Tabletable.Name + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(firstLine);
foreach (var item in Tabletable.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(Tabletable);
}
}
}
public void CreateTable(string TableNametableName, string[] ColumnNamescolumnNames, bool[] AllowNullsallowNulls, DbType[] DbTypesdbTypes)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
try
{
SetConnection();
string firstLine = "CREATE TABLE IF NOT EXISTS [" + TableNametableName + "] ([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(firstLine);
for (int i = 0; i < ColumnNamescolumnNames.Length; i++)
{
string nl = "";
if (AllowNulls[i]allowNulls[i]) nl = "NULL";
else nl = "NOT NULL";
queryBuilder.Append("[" + ColumnNames[i]columnNames[i] + "] " + DbTypes[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(TableNametableName, ColumnNamescolumnNames, AllowNullsallowNulls, DbTypesdbTypes);
}
}
}
public void DeleteTable(string TableNametableName)
{
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("DROP TABLE IF EXISTS " + TableNametableName, 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(TableNametableName);
}
}
}
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);
DataReaderdataReader = sqliteCommand.ExecuteReader();
while (DataReaderdataReader.Read())
{
if (DataReaderdataReader.HasRows)
{
tables.Add(DataReader[0]dataReader[0].ToString());
}
}
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
return tables;
}
public ColumnColumnList GetColumnsFromTableName(string TableNametableName)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
ColumnColumnList cols = new ColumnColumnList();
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("PRAGMA table_info('" + TableNametableName + "');", DataBaseConnnection);
DataReaderdataReader = sqliteCommand.ExecuteReader();
while (DataReaderdataReader.Read())
{
ColumnProperties ml = new ColumnProperties();
ml.ID = DataReader[0]dataReader[0].ToString();
ml.Name = DataReader[1]dataReader[1].ToString();
ml.DataType = DataReader[2](dbDataTypes)Enum.Parse(typeof(dbDataTypes), dataReader[2].ToString());
bool nl = false;
if (DataReader[3]dataReader[3].ToString() == "0") nl = true;
if (DataReader[3]dataReader[3].ToString() == "1") nl = false;
ml.AllowNull = nl;
cols.Add(DataReader[1]dataReader[1].ToString(), DataReader[2](dbDataTypes)Enum.Parse(typeof(dbDataTypes), dataReader[2].ToString()), nl, DataReader[0]dataReader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
return cols;
}
public void CreateEntry(string TableNametableName, EntryList EntryListentryList)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("insert into " + TableNametableName + " (");
foreach (var item in EntryListentryList.ColumnNameColumnNames)
{
queryBuilder.Append(item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in EntryListentryList.ColumnNameColumnNames)
{
queryBuilder.Append("@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < EntryListentryList.ColumnNameColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + EntryListentryList.ColumnName[i]ColumnNames[i], EntryListentryList.DbType[i]DbTypes[i]).Value = EntryListentryList.Content[i];Contents[i];
}
sqliteCommand.ExecuteNonQuery();
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
public void CreateEntry(string TableNametableName, object[] Contentcontent)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("insert into " + TableNametableName + " (");
foreach (var item in GetColumnsFromTableName(TableNametableName))
{
queryBuilder.Append(item.Name + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in GetColumnsFromTableName(TableNametableName))
{
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(TableNametableName))
{
colsNames.Add(item.Name);
}
for (int i = 0; i < GetColumnsFromTableName(TableNametableName).Count; i++)
{
sqliteCommand.Parameters.Add("@" + colsNames[i], DbType.Object).Value = Content[i];content[i];
}
sqliteCommand.ExecuteNonQuery();
DataBaseConnnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
public void DeleteEntry(string TableNametableName, string ColumnNamecolumnName, string EqualseQuals)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
ColumnNamecolumnName = RemoveSpecialCharacters(ColumnNamecolumnName);
EqualseQuals = RemoveSpecialCharacters(EqualseQuals);
try
{
SetConnection();
SQLiteCommand sqliteCommand = new SQLiteCommand("DELETE FROM " + TableNametableName + " WHERE " + ColumnNamecolumnName + "=@" + ColumnNamecolumnName, DataBaseConnnection);
sqliteCommand.Parameters.Add("@" + ColumnNamecolumnName, DbType.Object).Value = Equals;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 TableNametableName, EntryList EntryListentryList, string ColumnNamecolumnName, string EqualseQuals)
{
TableNametableName = RemoveSpecialCharacters(TableNametableName);
ColumnNamecolumnName = RemoveSpecialCharacters(ColumnNamecolumnName);
EqualseQuals = RemoveSpecialCharacters(EqualseQuals);
try
{
SetConnection();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("update " + TableNametableName + " set ");
foreach (var item in EntryListentryList.ColumnNameColumnNames)
{
queryBuilder.Append(item + "=@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(" ");
queryBuilder.Append(" WHERE " + ColumnNamecolumnName + "='" + EqualseQuals + "'");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < EntryListentryList.ColumnNameColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + EntryListentryList.ColumnName[i]ColumnNames[i], EntryListentryList.DbType[i]DbTypes[i]).Value = EntryListentryList.Content[i];Contents[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);
DataReaderdataReader = sqliteCommand.ExecuteReader();
while (DataReaderdataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = DataReader[0]dataReader[0].ToString();
for (int i = 1; i < DataReaderdataReader.FieldCount; i++)
{
lwn.SubItems.Add(DataReader[i]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 ColumnNamecolumnName, string SearchKeyWordsearchKeyWord)
{
ColumnNamecolumnName = RemoveSpecialCharacters(ColumnNamecolumnName);
SearchKeyWordsearchKeyWord = RemoveSpecialCharacters(SearchKeyWordsearchKeyWord);
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 " + ColumnNamecolumnName + " LIKE @searchKey", DataBaseConnnection);
sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = "%" + SearchKeyWordsearchKeyWord + "%";
DataReaderdataReader = sqliteCommand.ExecuteReader();
if (DataReaderdataReader.HasRows)
{
while (DataReaderdataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = DataReader[0]dataReader[0].ToString();
for (int i = 1; i < DataReaderdataReader.FieldCount; i++)
{
lwn.SubItems.Add(DataReader[i]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 SearchKeyWordsearchKeyWord)
{
SearchKeyWordsearchKeyWord = RemoveSpecialCharacters(SearchKeyWordsearchKeyWord);
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 = "%" + SearchKeyWordsearchKeyWord + "%"; ;
DataReaderdataReader = sqliteCommand.ExecuteReader();
if (DataReaderdataReader.HasRows)
{
while (DataReaderdataReader.Read())
{
ListWithName lwn = new ListWithName();
lwn.Text = DataReader[0]dataReader[0].ToString();
for (int i = 1; i < DataReaderdataReader.FieldCount; i++)
{
lwn.SubItems.Add(DataReader[i]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();
}
}
}
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();
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLiteHelper
{
public enum dbDataTypes
{
INT,
INTEGER,
TINYINT,
SMALLINT,
MEDIUMINT,
BIGINT,
[Description("UNSIGNED BIG INT")]
UNSIGNEDBIGINT,
INT2,
INT8,
CHARACTER,
VARCHAR,
[Description("VARYING CHARACTER")]
VARYINGCHARACTER,
NCHAR,
[Description("NATIVE CHARACTER")]
NATIVECHARACTER,
NVARCHAR,
TEXT,
CLOB,
BLOB,
REAL,
DOUBLE,
[Description("DOUBLE PRECISION")]
DOUBLEPRECISION,
FLOAT,
NUMERIC,
DECIMAL,
BOOLEAN,
DATE,
DATETIME
}
public class EntryList
{
public EntryList()
{
ColumnNames = new List<string>();
DbTypes = new List<DbType>();
Contents = new List<string>();
}
public void Add(string columnName, DbType dbType, string content)
{
ColumnNames.Add(columnName);
DbTypes.Add(dbType);
Contents.Add(content);
}
public List<string> ColumnNames { set; get; }
public List<DbType> DbTypes { set; get; }
public List<string> Contents { 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 dbDataTypes DataType { get; set; }
public string Content { get; set; }
public bool AllowNull { get; set; }
}
public class ColumnList : IEnumerable<ColumnProperties>
{
public ColumnList()
{
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) { Add(name, dbDataTypes.VARCHAR, true); }
public void Add(string name, dbDataTypes dataType) { Add(name, dataType, true); }
public void Add(string name, dbDataTypes dataType, bool allowNulls) { Add(name, dataType, allowNulls); }
public void Add(string name, dbDataTypes 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 ColumnList();
}
public string Name { set; get; }
public ColumnList 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();
}
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();
}
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 ColumnList GetColumnsFromTableName(string tableName)
{
tableName = RemoveSpecialCharacters(tableName);
ColumnList cols = new ColumnList();
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 = (dbDataTypes)Enum.Parse(typeof(dbDataTypes), 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(), (dbDataTypes)Enum.Parse(typeof(dbDataTypes), 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.ColumnNames)
{
queryBuilder.Append(item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
queryBuilder.Append(" values (");
foreach (var item in entryList.ColumnNames)
{
queryBuilder.Append("@" + item + ", ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
queryBuilder.Append(")");
SQLiteCommand sqliteCommand = new SQLiteCommand(queryBuilder.ToString(), DataBaseConnnection);
for (int i = 0; i < entryList.ColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryList.ColumnNames[i], entryList.DbTypes[i]).Value = entryList.Contents[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.ColumnNames)
{
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.ColumnNames.Count; i++)
{
sqliteCommand.Parameters.Add("@" + entryList.ColumnNames[i], entryList.DbTypes[i]).Value = entryList.Contents[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();
}
}
}
Another search method added where it goes through all the columns. Plus GetTables changed to GetTableNames.
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> GetTablesGetTableNames()
{
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 WhereColumnName, string SearchKeyWord)
{
WhereColumnName = RemoveSpecialCharacters(WhereColumnName);
SearchKeyWord = RemoveSpecialCharacters(SearchKeyWord);
List<ListWithName> listLwn = new List<ListWithName>();
try
{
foreach (var table in GetTablesGetTableNames())
{
if (table != "sqlite_sequence")
{
SetConnection();
// SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + Where + " LIKE '%" + SearchKeyWord + "%'", DataBaseConnnection);
//SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + Where + " LIKE '%@SearchKey%'", DataBaseConnnection);
SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + WhereColumnName + " LIKE @searchKey", DataBaseConnnection);
sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = SearchKeyWord;
//sqliteCommand.Parameters.Add("@COL", DbType.String).Value = Where;
"%" + SearchKeyWord ////sqliteCommand.Parameters.AddWithValue("%@searchKey%",+ 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();
}
}
}
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> GetTables()
{
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 Where, string SearchKeyWord)
{
Where = RemoveSpecialCharacters(Where);
SearchKeyWord = RemoveSpecialCharacters(SearchKeyWord);
List<ListWithName> listLwn = new List<ListWithName>();
try
{
foreach (var table in GetTables())
{
if (table != "sqlite_sequence")
{
SetConnection();
// SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + Where + " LIKE '%" + SearchKeyWord + "%'", DataBaseConnnection);
//SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + Where + " LIKE '%@SearchKey%'", DataBaseConnnection);
SQLiteCommand sqliteCommand = new SQLiteCommand("SELECT * FROM " + table + " WHERE " + Where + " LIKE @searchKey", DataBaseConnnection);
sqliteCommand.Parameters.Add("@searchKey", DbType.String).Value = SearchKeyWord;
//sqliteCommand.Parameters.Add("@COL", DbType.String).Value = Where;
////sqliteCommand.Parameters.AddWithValue("%@searchKey%", 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();
}
}
}
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();
}
}
}
lang-cs