//version: 1.60
//ref: SQLNative\Desktop\System.Data.SQLite.dll
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Reflection;
using System.Drawing;
using System.IO;
using System.Data.SQLite;
namespace B4PSQL
{
public class Connection : IDisposable
{
private SQLiteConnection connection;
private SQLiteTransaction transaction = null;
public double DLLVersion
{
get { return 1.60; }
}
public Connection()
{
connection = new SQLiteConnection();
}
public object Value
{
get { return connection; }
set { connection = (SQLiteConnection)value; }
}
public void Open(string ConnectionString)
{
Open(ConnectionString, null);
}
public void Open(string ConnectionString, string password)
{
connection.ConnectionString = ConnectionString;
if (password != null)
connection.SetPassword(password);
connection.Open();
}
public void ChangePassword(string password)
{
connection.ChangePassword(password);
}
public void RemovePassword()
{
connection.ChangePassword((string)null);
}
public void Close()
{
connection.Close();
}
public void BeginTransaction()
{
transaction = connection.BeginTransaction();
}
public void EndTransaction()
{
transaction.Commit();
}
public void CreateSQLTable(DataGrid Table, string SQLTableName)
{
Type t2 = Table.GetType();
FieldInfo fi = t2.GetField("dataTable");
DataTable dt = (DataTable)fi.GetValue(Table);
string columns = "", build = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
string type;
if (dt.Columns[i].DataType == typeof(double))
type = "REAL";
else
type = "TEXT";
columns += "\'" + dt.Columns[i].ColumnName + "\' " + type + ",";
build += ":p" + i.ToString() + ",";
}
columns = columns.Remove(columns.Length - 1, 1);
build = build.Remove(build.Length - 1, 1);
using (SQLiteCommand command = new SQLiteCommand(connection))
{
using (SQLiteTransaction trans = this.connection.BeginTransaction())
{
command.CommandText = "CREATE TABLE \'" + SQLTableName + "\' (" + columns + ")";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO \'" + SQLTableName + "\' VALUES (" + build + ")";
SQLiteParameter[] p = new SQLiteParameter[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
p[i] = new SQLiteParameter("p" + i.ToString());
command.Parameters.Add(p[i]);
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
p[c].Value = dt.Rows[r][c];
command.ExecuteNonQuery();
}
trans.Commit();
}
}
}
public void Dispose()
{
connection.Close();
}
}
public class Command : IDisposable
{
private SQLiteCommand command;
private double scaleX = 1;
public Command(string CommandText, object Connection)
{
command = new SQLiteCommand(CommandText, (SQLiteConnection)Connection);
scaleX = (double)System.Threading.Thread.GetData(System.Threading.Thread.GetNamedDataSlot("scaleX"));
}
public string CommandText
{
get { return command.CommandText; }
set { command.CommandText = value; }
}
public int ExecuteNonQuery()
{
return command.ExecuteNonQuery();
}
public object ExecuteReader()
{
return command.ExecuteReader();
}
public string BytesToBLOB(Byte[] Data)
{
StringBuilder sb = new StringBuilder(Data.Length * 2 + 6);
sb.Append("x\'");
for (int i = 0; i < Data.Length; i++)
{
sb.Append(((int)Data[i]).ToString("x2"));
}
sb.Append("'");
return sb.ToString();
}
public string FileToBLOB(string File)
{
byte[] buffer = null;
using (FileStream fs = new FileStream(File, FileMode.Open))
{
buffer = new byte[fs.Length];
fs.Read(buffer, 0, (int)fs.Length);
}
return BytesToBLOB(buffer);
}
public void AddParameter(string Name)
{
command.Parameters.Add(new SQLiteParameter("@" + Name));
}
public void SetParameter(string Name, string Value)
{
command.Parameters["@" + Name].Value = Value;
}
public void SetNullParameter(string Name)
{
command.Parameters["@" + Name].Value = null;
}
void IDisposable.Dispose()
{
command.Dispose();
}
public void ExecuteTable(DataGrid Table, int Maximum)
{
Type t2 = Table.GetType();
FieldInfo fi = t2.GetField("dataTable");
DataTable dt = (DataTable)fi.GetValue(Table);
Table.DataSource = null;
dt.DefaultView.RowFilter = "";
dt.DefaultView.Sort = "";
dt.Columns.Clear();
dt.Rows.Clear();
Table.TableStyles[0].GridColumnStyles.Clear();
using (SQLiteDataReader r = command.ExecuteReader())
{
object[] nullReplace = new object[r.FieldCount];
for (int i = 0; i < r.FieldCount; i++)
{
string colName = r.GetName(i);
DataGridColumnStyle dg = NewColumnStyle(colName);
dg.Width = (int)(75 * scaleX);
Table.TableStyles[0].GridColumnStyles.Add(dg);
Type t = r.GetFieldType(i);
if (t == typeof(string) || t == typeof(char) || t == typeof(bool) || t.IsArray || t == typeof(DateTime)
|| t == typeof(Guid))
{
dt.Columns.Add(colName, typeof(string));
nullReplace[i] = "";
}
else
{
dt.Columns.Add(colName, typeof(double));
nullReplace[i] = 0.0;
}
}
int i2 = 1;
object[] o = new object[r.FieldCount];
while (r.Read() && i2++ != Maximum)
{
r.GetValues(o);
for (int i = 0; i < o.Length; i++)
{
if (DBNull.Value.Equals(o[i]))
o[i] = nullReplace[i];
}
DataRow dr = dt.NewRow();
dr.ItemArray = o;
dt.Rows.Add(dr);
}
dt.EndLoadData();
Table.DataSource = dt.DefaultView;
r.Close();
}
}
private DataGridTextBoxColumn NewColumnStyle(string name)
{
DataGridTextBoxColumn dg = new DataGridTextBoxColumn();
dg.MappingName = name;
dg.HeaderText = name;
return dg;
}
}
public class DataReader : IDisposable
{
private SQLiteDataReader reader;
public DataReader()
{
}
public object Value
{
get { return reader; }
set { reader = (SQLiteDataReader)value; }
}
public bool ReadNextRow()
{
return reader.Read();
}
public string GetValue(int Index)
{
return reader.GetValue(Index).ToString();
}
public byte[] GetBytes(int Index)
{
long size = reader.GetBytes(Index, 0, null, 0, 0);
byte[] buffer = new byte[size];
reader.GetBytes(Index, 0, buffer, 0, (int)size);
return buffer;
}
public Bitmap GetImage(int Index)
{
Bitmap b = null;
using (MemoryStream ms = new MemoryStream())
{
byte[] buffer = GetBytes(Index);
ms.Write(buffer, 0, buffer.Length);
b = new Bitmap(ms);
}
return b;
}
public int FieldCount
{
get { return reader.FieldCount; }
}
public bool IsDBNull(int index)
{
return reader.IsDBNull(index);
}
public void Close()
{
reader.Close();
}
public void Dispose()
{
if (reader != null)
reader.Close();
}
}
}
