C# 数据库访问类(Sql Lite数据库)

分类:C#     发布:2019-08-09     来源:本站     浏览:724 次
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace HC.Software.IISLog
{
    public static class SqliteDbHelper
    {
        #region 创建数据库

        /// <summary>
        ///     创建数据库
        /// </summary>
        public static void InitDataBase(string path)
        {
            if (!File.Exists(path))
            {
                SQLiteConnection.CreateFile(path);
            }
        }

        /// <summary>
        ///     根据DataTable初始化sqlite数据表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataTable">DataTable</param>
        /// <param name="conn">链接字符串</param>
        public static void InitDataTable(string tableName, DataTable dataTable, string conn)
        {
            string checkTableSql = string.Format(
                @"select count(*) from sqlite_master where type='table' and name='{0}'", tableName);
            bool exsit = Convert.ToInt32(ExecuteScalar(conn, checkTableSql, null)) > 0;

            if (!exsit)
            {
                var fields = new List<string>();
                foreach (DataColumn column in dataTable.Columns)
                {
                    string name = column.ColumnName;
                    if (!fields.Contains(name))
                    {
                        fields.Add(name);
                    }
                }
                var sqlBuilder = new StringBuilder(string.Format("CREATE TABLE [{0}](", tableName));
                foreach (string field in fields)
                {
                    sqlBuilder.Append(field + " NVARCHAR(500),");
                }
                string createTableSql = sqlBuilder.ToString().TrimEnd(',') + ")";
                Execute(createTableSql, conn);
            }
            else
            {
                string clearUpDataSql = "DELETE FROM " + tableName;
                Execute(clearUpDataSql, conn);
            }
        }

        #endregion

        #region IIS日志转换为sql语句

        /// <summary>
        ///     IIS日志转换为sql语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="line"></param>
        public static string GetSqlForLine(string tableName, string[] fields, string line)
        {
            string[] data = line.Split(' ');
            if (fields == null || fields.Length != data.Length)
            {
                return "";
            }
            var strSql = new StringBuilder();
            strSql.Append("INSERT INTO " + tableName + " (");

            #region 键

            string fieldNames = "";
            for (int i = 0; i < fields.Length; i++)
            {
                string field = fields[i].Replace("-", "_").Replace("(", "_").Replace(")", "");
                fieldNames += field + ",";
            }

            #endregion

            strSql.Append(fieldNames.TrimEnd(',') + ")");
            strSql.Append("VALUES(");

            #region 值

            string values = "";
            for (int i = 0; i < fields.Length; i++)
            {
                string value = data[i];
                values += "'" + value + "',";
            }

            #endregion

            strSql.Append(values.TrimEnd(','));
            strSql.Append(")");

            return strSql.ToString();
        }

        /// <summary>
        ///     添加数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sqls"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static void ProcressLog(string tableName, IList<string> sqls, string conn)
        {
            Execute("DELETE FROM " + tableName, conn);
            using (var con = new SQLiteConnection(conn))
            {
                var cmd = new SQLiteCommand(con);
                con.Open();
                SQLiteTransaction tran = con.BeginTransaction();
                try
                {
                    foreach (string sql in sqls)
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    tran.Rollback();
                    throw;
                }

                tran.Commit();
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        ///     获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, string conn)
        {
            var da = new SQLiteDataAdapter(sql, new SQLiteConnection(conn));
            var ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }

        /// <summary>
        ///     执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        private static void Execute(string sql, string conn)
        {
            var con = new SQLiteConnection(conn);
            var cmd = new SQLiteCommand(con) { CommandText = sql };
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        ///     查询单个值
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string commandText, params object[] paramList)
        {
            using (var cn = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = cn.CreateCommand();
                cmd.CommandText = commandText;
                AttachParameters(cmd, commandText, paramList);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                object result = cmd.ExecuteScalar();
                cmd.Dispose();
                cn.Close();
                return result;
            }
        }

        /// <summary>
        ///     Parses parameter names from SQL Statement, assigns values from object array , ? /// and returns fully populated ParameterCollection.
        /// </summary>
        /// <param name="cmd"> </param>
        /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>SQLiteParameterCollection</returns>
        /// <remarks>Class experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>
        private static void AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
        {
            if (paramList == null || paramList.Length == 0) return;
            SQLiteParameterCollection coll = cmd.Parameters;
            string parmString = commandText.Substring(commandText.IndexOf("@", StringComparison.Ordinal));
            // pre-process the string so always at least 1 space after a comma.
            parmString = parmString.Replace(",", " ,");
            // get the named parameters into a match collection
            const string pattern = @"(@)\S*(.*?)\b";
            var ex = new Regex(pattern, RegexOptions.IgnoreCase);
            MatchCollection mc = ex.Matches(parmString);
            var paramNames = new string[mc.Count];
            int i = 0;
            foreach (Match m in mc)
            {
                paramNames[i] = m.Value;
                i++;
            }
            // now let's type the parameters
            int j = 0;
            foreach (object o in paramList)
            {
                var parm = new SQLiteParameter();
                if (o == null)
                {
                    parm.DbType = DbType.Object;
                    parm.ParameterName = paramNames[j];
                    parm.Value = paramList[j];
                    coll.Add(parm);
                    j++;
                    continue;
                }
                Type t = o.GetType();
                switch (t.ToString())
                {
                    case ("DBNull"):
                    case ("Char"):
                    case ("SByte"):
                    case ("UInt16"):
                    case ("UInt32"):
                    case ("UInt64"):
                        throw new SystemException("Invalid data type");
                    case ("System.String"):
                        parm.DbType = DbType.String;
                        parm.ParameterName = paramNames[j];
                        parm.Value = paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Byte[]"):
                        parm.DbType = DbType.Binary;
                        parm.ParameterName = paramNames[j];
                        parm.Value = paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Int32"):
                        parm.DbType = DbType.Int32;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (int)paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Boolean"):
                        parm.DbType = DbType.Boolean;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (bool)paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.DateTime"):
                        parm.DbType = DbType.DateTime;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDateTime(paramList[j]);
                        coll.Add(parm);
                        break;
                    case ("System.Double"):
                        parm.DbType = DbType.Double;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDouble(paramList[j]);
                        coll.Add(parm);
                        break;
                    case ("System.Decimal"):
                        parm.DbType = DbType.Decimal;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDecimal(paramList[j]);
                        break;
                    case ("System.Guid"):
                        parm.DbType = DbType.Guid;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (Guid)(paramList[j]);
                        break;
                    case ("System.Object"):
                        parm.DbType = DbType.Object;
                        parm.ParameterName = paramNames[j];
                        parm.Value = paramList[j];
                        coll.Add(parm);
                        break;
                    default:
                        throw new SystemException("Value is of unknown data type");
                } // end switch
                j++;
            }
        }

        #endregion
    }
}
        

如果觉得文章对您有帮助,您可以对我进行打赏 ¥1.81 元(金额随机^_^,每次刷新金额不同)。

请使用支付宝扫码支付

留言评论

*称  呼:
*联系方式: 方便与您取得联系,推荐使用邮箱。
*内  容:

已有评论

暂无数据

上一篇:JS 返回顶部插件

下一篇:SQL Server 2008 R2 清空数据库中ldf日志文件