using System;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.Windows;
using System.Configuration;

namespace 不动产证保管系统
{

    #region sqlite帮助类
    /// <summary>
    /// SQLITE 帮助类
    /// </summary>
    class SQLiteHelper
    {
        public static SQLiteHelper mInstance = null;
        public String mConnectdbPath = $"Data Source={ConfigurationManager.ConnectionStrings["conn"].ConnectionString}";
        public static Object mLockObject = new Object();

        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLiteHelper()
        {
        }

        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static SQLiteHelper GetInstance()
        {
            if (mInstance == null)
            {
                lock (mLockObject)
                {
                    mInstance = new SQLiteHelper();
                }
            }

            return mInstance;
        }


        /// <summary>  
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(String sql, SQLiteParameter[] paramters)
        {
            int affectedRows = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                        {
                            cmd.CommandText = sql;
                            if (paramters != null)
                            {
                                cmd.Parameters.AddRange(paramters);
                            }

                            affectedRows = cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return affectedRows;
        }

        /// <summary>  
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(String sql)
        {
            int affectedRows = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(conn))
                        {
                            cmd.CommandText = sql;
                            affectedRows = cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return affectedRows;
        }

        /// <summary>  
        /// 执行一个查询语句，返回一个包含查询结果的DataTable  
        /// </summary>  
        /// <param name="sql">要执行的查询语句</param>  
        /// <returns></returns>  
        public DataTable ExecuteQueryDataTable(String sql)
        {
            DataTable data = null;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                        {
                            SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                            data = new DataTable();

                            adapter.Fill(data);
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {

                return null;   //临时加上去的

                throw new Exception(e.Message);
            }

            return data;
        }
        /*
        public List<RecordJaveBean> ExecuteQuery(String sql)
        {
            try
            {
                mList.Clear();
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
                    using (SQLiteDataReader reader = cmdRead.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            mList.Add(new RecordJaveBean(reader.GetString(1), reader.GetString(2), reader.GetString(3)));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return mList;
        }
        */
        public void ExecuteInsert(String sql)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();

                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
                    cmdRead.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        public void ExecuteDelegate(String sql)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();

                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
                    cmdRead.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }


        /// <summary>  
        /// 对SQLite数据库获取当家查询结果集的记录总数
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <returns></returns>  
        public int ExecuteGetResultTotalLine(String sql)
        {
            int lines = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(conn))
                        {
                            cmd.CommandText = sql;
                            lines = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return lines;
        }

    }




    #endregion
}