SQLite Update for B4P

Discussion in 'Basic4ppc Wishlist' started by berndgoedecke, Feb 11, 2012.

  1. berndgoedecke

    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
     
  2. Erel

    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:
    Code:
    //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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice