SQLite Update for B4P

berndgoedecke

Active Member
Licensed User
Hello Erel,
is it possible do get an Update of the B4P SQLite.dll ?
Version 3.7.4 for example would be more compatible to Androids SQlite and datatransfer to a B4P Desktop Application should be easier.

Best regards

berndgoedecke
 

Erel

Administrator
Staff member
Licensed User
Basic4ppc SQLite support is based on System.Data.SQLite. This project has changed ownership: System.Data.SQLite: About

Here is the code of the wrapper:
B4X:
//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();
        }

    }


}
If anyone is interested, you can take this code and test it with the new version.
 
Top