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 <= ))

            {

                MessageBox.Show("数据库名不能为空");

                return;

            }

            if ((sqlList == null) || (sqlList.Count <= ))

            {

                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 = ;

            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 = ;

            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 = ;

            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;

        }

    }

}