DBHelperSQLLite

1.IDBHelper.cs

    interface IDBHelper
    {
        void initDB();
        void closeDB();
        void ExecSQL(String execSql);
        DataTable ExecQuery(String querySql);
    }

2.ZDBHelperSQLLite.cs

    class ZDBHelperSQLLite : IDBHelper
    {
        String dataSource = "";
        SQLiteConnection connection = null;
        public void initDB(String dbPath)
        {
            dataSource = "Data Source =" + dbPath();
            connection = new SQLiteConnection(dbFile);
            connection.Open();
            initTables();
            CreateFileInfoTable();
        }
        
        public void closeDB()
        {
            connection.Close();
        }

        private void initTables()
        {
            String sqlCreate01 = "CREATE TABLE IF NOT EXISTS visitlog(user_id varchar(64), visit_path varchar(256), visit_time integer);";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(connection);
            cmdCreateTable.CommandText = sqlCreate01;
            cmdCreateTable.ExecuteNonQuery();
        }
  
        public void ExecSQL(String execSql)
        {
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand cmdExec = new SQLiteCommand(execSql, connection, transaction );
            cmdExec.ExecuteNonQuery();
            transaction.Commit();
        }

        public DataTable ExecQuery(String querySql)
        {
            SQLiteCommand cmdQuery = new SQLiteCommand(querySql, connection);
            SQLiteDataReader reader = cmdQuery.ExecuteReader();

            DataTable datatable = new DataTable();
            DataTable schemaTable = reader.GetSchemaTable();
            foreach (DataRow myRow in schemaTable.Rows)
            {
                DataColumn myDataColumn = new DataColumn();
                myDataColumn.DataType = myRow.GetType();
                myDataColumn.ColumnName = myRow[0].ToString();
                datatable.Columns.Add(myDataColumn);
            }
            schemaTable = null;

            while (reader.Read())
            {
                DataRow myDataRow = datatable.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    myDataRow[i] = reader[i].ToString();
                }
                datatable.Rows.Add(myDataRow);
                myDataRow = null;
            }
            reader.Close();

            return datatable;
        }
    }