using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace _1200gFtp
{
    public class SqliteOptions_sql
    {
        public SqlConnection sqliteConn = null;
        /// <summary>
        /// 让此类创建一个单例类
        /// </summary>
        public static SqliteOptions_sql _instance = null;//申明一个EcgDrawing对象，复制Null
        private static readonly object lockHelper = new object();

        public static SqliteOptions_sql CreateInstance()
        {
            if (_instance == null)
            {
                lock (lockHelper)
                {
                    if (_instance == null)
                        _instance = new SqliteOptions_sql();
                }
            }
            return _instance;
        }

        public SqliteOptions_sql()
        {
            GetSqliteConnection();
        }


        /// <summary>
        /// DES加密
        /// </summary>
        /// <param name="data">加密数据</param>
        /// <param name="key">8位字符的密钥字符串</param>
        /// <param name="iv">8位字符的初始化向量字符串</param>
        /// <returns></returns>
        public  string DESEncrypt(string data, string key, string iv)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv);

            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            int i = cryptoProvider.KeySize;
            MemoryStream ms = new MemoryStream();
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);

            StreamWriter sw = new StreamWriter(cst);
            sw.Write(data);
            sw.Flush();
            cst.FlushFinalBlock();
            sw.Flush();
            return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
        }



        /// <summary>
        /// DES解密
        /// </summary>
        /// <param name="data">解密数据</param>
        /// <param name="key">8位字符的密钥字符串(需要和加密时相同)</param>
        /// <param name="iv">8位字符的初始化向量字符串(需要和加密时相同)</param>
        /// <returns></returns>
        public  string DESDecrypt(string data, string key, string iv)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv);

            byte[] byEnc;
            try
            {
                byEnc = Convert.FromBase64String(data);
            }
            catch
            {
                return null;
            }

            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            MemoryStream ms = new MemoryStream(byEnc);
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
            StreamReader sr = new StreamReader(cst);
            return sr.ReadToEnd();
        }


        /// <summary>
        /// 获得sqlite数据库连接
        /// </summary>
        /// <param name="dbpath"></param>
        /// <returns></returns>
        public bool GetSqliteConnection()
        {
            try
            {
                //给数据库连接类赋值
                string ConnectionStr = File.ReadAllText(Application.StartupPath + @"\conn.dll");
                ConnectionStr = DESDecrypt(ConnectionStr, "51220151", "51220151");
                SqlConnection conn = new SqlConnection(ConnectionStr);
                sqliteConn = conn;
            }
            catch { return false; }
            return true;
        }

        /// <summary>
        /// 执行sqlite查询语句，并返回一个 DataTable
        /// </summary>
        /// <param name="sqliteStr"></param>
        /// <returns></returns>
        public DataTable ExcuteSqlite(string sqliteStr)
        {
            SqlDataAdapter sqliteDp = new SqlDataAdapter(sqliteStr, sqliteConn);
            DataSet sqliteds = new DataSet();
            try
            {
                //if (sqliteConn.State == ConnectionState.Open)
                //    sqliteConn.Close();         
                sqliteDp.SelectCommand.CommandTimeout = 600000;       
                sqliteDp.Fill(sqliteds);
                return sqliteds.Tables[0];
            }
            catch { }
            return new DataTable();
        }

        /// <summary>
        /// Sqlite 添加数据的操作函数
        /// </summary>
        /// <param name="sqliteSQL"></param>
        /// <returns></returns>
        public bool SqliteAdd(string sqliteSQL)
        {
            try
            {
                if (sqliteConn.State == ConnectionState.Closed)
                    sqliteConn.Open();
                SqlCommand sqliteCmd = new SqlCommand(sqliteSQL, sqliteConn);
                sqliteCmd.ExecuteNonQuery();
                sqliteCmd.Dispose();
                sqliteConn.Close();
            }
            catch { return false; }
            return true;
        }

        /// <summary>
        /// Sqlite 删除数据的操作函数
        /// </summary>
        /// <param name="sqliteSQL"></param>
        /// <returns></returns>
        public bool SqliteDelete(string sqliteSQL)
        {
            try
            {
                if (sqliteConn.State == ConnectionState.Closed)
                    sqliteConn.Open();
                SqlCommand sqliteCmd = new SqlCommand(sqliteSQL, sqliteConn);
                sqliteCmd.CommandTimeout = 3600;
                sqliteCmd.ExecuteNonQuery();
                sqliteCmd.Dispose();
                sqliteConn.Close();
            }
            catch { return false; }
            return true;
        }

        /// <summary>
        /// Sqlite 更新数据的操作函数
        /// </summary>
        /// <param name="sqliteSQL"></param>
        /// <returns></returns>
        public bool SqliteUpdate(string sqliteSQL)
        {
            try
            {
                if (sqliteConn.State == ConnectionState.Closed)
                    sqliteConn.Open();
                SqlCommand sqliteCmd = new SqlCommand(sqliteSQL, sqliteConn);
                sqliteCmd.ExecuteNonQuery();
                sqliteCmd.Dispose();
                sqliteConn.Close();
            }
            catch { return false; }
            return true;
        }

        /// <summary>
        /// 执行SQL语句函数
        /// </summary>
        /// <param name="sqlcmd">SQL语句</param>
        /// <param name="paras">SQL语句中的参数组</param>
        /// <returns>返回受影响记录条数</returns>
        public int ExecuteSql(string sqlcmd, params SqlParameter[] paras)
        {

            SqlCommand cmd = new SqlCommand(sqlcmd, sqliteConn);
            if (sqliteConn.State == ConnectionState.Closed)
            {
                sqliteConn.Open();
            }

            foreach (SqlParameter p in paras)
            {
                cmd.Parameters.Add(p);
            }

            int cnt = cmd.ExecuteNonQuery();
            sqliteConn.Close();
            return cnt;
        }




        /// <summary>
        /// Sqlite 执行数据库的多条语句,
        /// </summary>
        /// <param name="sqliteSQL"></param>
        /// <returns></returns>
        public bool SqliteExecuteNonQuery(string[] sqliteSQL)
        {

            if (sqliteConn.State == ConnectionState.Closed)
            {
                sqliteConn.Open();
            }
            SqlTransaction sqlTran = sqliteConn.BeginTransaction();
            try
            {
                foreach (string sql in sqliteSQL)
                {
                    SqlCommand sqliteCmd = new SqlCommand(sql, sqliteConn, sqlTran);
                    sqliteCmd.ExecuteNonQuery();
                    sqliteCmd.Dispose();
                }
                sqlTran.Commit();
            }
            catch
            {
                sqlTran.Rollback();
                return false;
            }
            finally
            {

                sqliteConn.Close();
            }
            return true;
        }

    }
}