SqlLite C.R.U.D Class

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Windows.Forms;
 
 
namespace Systems.Database
{
   public class SqlLite
    {
 
        public SQLiteConnection dbConnection;
        public void CreateSQLiteDB(string name )
 
        {
            SQLiteConnection.CreateFile(name);
        }
        /// <summary>
        /// Connections to database.
        /// </summary>
        /// <param name="Directory"> DB 폴더 및 DB 파일이 모두 포함된 경로를 넘겨 받는다.</param>
        /// <param name="DbFileName">Name of the database file.</param>
        /// <autogeneratedoc />
        /// TODO Edit XML Comment Template for ConnectionToDB
        public void ConnectionToDB(string DirectoryAndDbFileName)
 
        {
           
          //  var Directory = Application.StartupPath;
            // MessageBox.Show(Directory , "Application.StartupPath : DataBaseSqlLite");
           // string paremtFileDir = System.IO.Path.GetDirectoryName(Directory);
            try
            {
             
           
                dbConnection = new SQLiteConnection(@"Data Source= " + DirectoryAndDbFileName);
 
                dbConnection.Open();
            }
            catch (Exception exception)
            {
 
                MessageBox.Show(exception.ToString() + "DataBaseSqlLite" + DirectoryAndDbFileName, "ConnectionToDB()");
                throw;
            }
        }
 
        public void DisconnectionToDB()
 
        {
            if (dbConnection != null) dbConnection.Close();
            dbConnection.Dispose();
            GC.Collect();
        }
 
        public void DisposeSQLite(SQLiteCommand command)
        {
            command.Dispose();
 
        }
 
        public void DisposeSQLite(SQLiteCommand command, SQLiteDataReader rd)
        {
            command.Dispose();
            rd.Close();
 
            DisconnectionToDB();
        }
 
 
        public void CreateTableCamera01(string name = "Test.db")
 
        {
            StringBuilder Sqlquery = new StringBuilder();
            Sqlquery.Append("DROP TABLE tblSettings_Cam01 " +
                            @"CREATE TABLE `tblSettings_Cam01` (`SettingsID`  TEXT NOT NULL,
                                                    `SettingsName`            TEXT NOT NULL,
                                                    `CreateTimeStamp`        INTEGER NOT NULL,
                                                    `ModifyTimeStamp`        INTEGER NOT NULL,
                                                    `AlignMarkPatternPath`    TEXT,
                                                    `AlignMarkLocation`    TEXT NOT NULL,
                                                    `ImageResolutionMD`    REAL NOT NULL,
                                                    `ImageResolutionTD`    REAL NOT NULL,
                                                    `LEDIntensity`        INTEGER NOT NULL,
                                                    PRIMARY KEY(`SettingsID`))");
            SQLiteCommand command = new SQLiteCommand(Sqlquery.ToString(), dbConnection);
            {
 
                command.CommandText = Sqlquery.ToString();
 
                command.CommandType = CommandType.Text;
 
 
                command.ExecuteNonQuery();
 
                DisposeSQLite(command);
            }
        }
        public void CreateTable(string Sqlquery)
 
        {
            SQLiteCommand command = new SQLiteCommand(Sqlquery, dbConnection);
            {
 
                command.CommandText = Sqlquery.ToString();
 
                command.CommandType = CommandType.Text;
 
 
                command.ExecuteNonQuery();
 
                DisposeSQLite(command);
            }
        }
 
        public List<String> GetSelectResult(string sQuery)
 
        {
          List<String> ResultList = new List<string>();
 
 
            try
 
            {
             
 
 
                SQLiteCommand command = new SQLiteCommand(sQuery, dbConnection);
                command.CommandText = sQuery;
 
                command.CommandType = CommandType.Text;
 
 
 
 
                using (SQLiteDataReader rd = command.ExecuteReader())
                {
 
 
                    while (rd.Read())
                    {
                      //  List<string> tempRow = new List<string>();
                        for (int i = 0; i < rd.FieldCount; i++)
                        {
                            ResultList.Add(Convert.ToString(rd.GetValue(i)));
                        }
                        // ResultList.Add(tempRow);
 
                    }
 
 
                    DisposeSQLite(command, rd);
                }
 
                return ResultList;
            }
            catch (Exception ex)
            {
                String msgInnerExAndStackTrace =  String.Format("{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message,
                    arg1: ex.InnerException, ex.StackTrace);
 
                return null;
            }
 
        }
 
        public void GetAllDataFromTable(string sQuery, ref DataTable dt)
        {
            //SQLite 연결
            SQLiteCommand command = new SQLiteCommand(sQuery, dbConnection);
            command.CommandText = sQuery;
 
            command.CommandType = CommandType.Text;
 
 
 
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
 
            //datatable 생성하고 그 테이블에 데이터를 받아온다.
            dt = new DataTable();
            adapter.Fill(dt);
 
            //객체 삭제
            adapter.Dispose();
            command.Dispose();
          //  DisposeSQLite(command, rd);
        }
    }
}
 

System.Data.SQLite

System.Data.SQLite: Downloads Page