DB通用类：Sqlite通用类库

     Sqlite通用类库

     

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace CommonLib
{
    public static class DB_Sqlite
    {
        public static void CreateDB(string datasource,ArrayList sqlList)
        {
            if ((datasource == null) || (datasource == "") || (datasource.Length <= 0))
            {
                MessageBox.Show("数据库名不能为空");
                return;
            }
            if ((sqlList == null) || (sqlList.Count <= 0))
            {
                MessageBox.Show("创建数据库表的语句不能为空");
                return;
            }

            try
            {
                if (!File.Exists(@datasource))
                {
                    System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
                    System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                    System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                    connstr.DataSource = datasource;
                    //connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
                    conn.ConnectionString = connstr.ToString();
                    conn.Open();
                    System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();

                    foreach(Object OO in sqlList)
                    {
                        cmd.CommandText = OO.ToString();
                        cmd.Connection = conn;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("SQLite数据库:" + ex.ToString());
            }
        }
        public static Int32 Fun_ExeUpdate(string strSql, string datasource)
        {
            Int32 nExcnt = 0;
            try
            {
                System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                conn = new System.Data.SQLite.SQLiteConnection();
                connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                //connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护

                conn.ConnectionString = connstr.ToString();
                conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
                cmd.CommandText = strSql;
                nExcnt = cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                #region 已经注释的代码
                //using (SQLiteConnection connection = new SQLiteConnection(connstr.ToString ()))
                //{
                //    connection.Open();
                //    using (SQLiteCommand command=new SQLiteCommand (strSql ,connection  ))
                //    {
                //        command.ExecuteNonQuery();
                //        command.Dispose();

                //    } 

                //    connection.Close();

                //} 
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show("SQLite数据库:" + ex.ToString());
            }
            return nExcnt;
        }

        public static Object Fun_ExeScaler(string strSql, string datasource)
        {
            Object nExcnt = 0;
            try
            {
                System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                conn = new System.Data.SQLite.SQLiteConnection();
                connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                //connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护

                conn.ConnectionString = connstr.ToString();
                conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
                cmd.CommandText = strSql;
                nExcnt = cmd.ExecuteScalar();
                cmd.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("SQLite数据库:" + ex.ToString());
            }
            return nExcnt;
        }

        public static Int32 Fun_ExeUpdate_TR(ref ArrayList strColl, string datasource)
        {
            Int32 nExcnt = 0;
            SQLiteTransaction tran = null;
            try
            {
                System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                //connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connstr.ToString();
                conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                foreach (object obj in strColl)
                {
                    cmd.CommandText = obj.ToString();
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }

            return nExcnt;
        }

        public static DataTable Fun_Quary(string strsql, string datasource)
        {
            DataTable data = new DataTable();
            try
            {
                System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();

                conn = new System.Data.SQLite.SQLiteConnection();
                connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                //connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connstr.ToString();
                conn.Open();
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
                cmd.CommandText = strsql;
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                adapter.Fill(data);
                cmd.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("SQLite数据库:" + ex.ToString());
            }
            return data;
        }
    }
}

 

转载于:https://www.cnblogs.com/hhhh2010/p/6557141.html