SqliteDBHelper

最新推荐文章于 2023-05-17 11:44:10 发布
转载 最新推荐文章于 2023-05-17 11:44:10 发布 · 1.5k 阅读 · 0
文章标签：

#dataset #object #string #null #cmd #command 

namespace MyDatabaseTools
{
    using System;
    using System.Data;
    using System.Text.RegularExpressions;
    using System.Xml;
    using System.IO;
    using System.Collections;
    using System.Data.SQLite;

    /// <summary>  
    /// SQLiteHelper is a utility class similar to "SQLHelper" in MS  
    /// Data Access Application Block and follows similar pattern.  
    /// </summary>  
    public class SqliteDBHelper
    {
        public static readonly string connectionStr = "Data Source=" + Directory.GetCurrentDirectory().Replace(@"\bin\Debug", @"\DB\MyDB.s3db") + ";password=cai123 ";
        //  connectionStr = ConfigurationManager.ConnectionStrings["SQLiteDBconntaion"].ConnectionString;
        /// <summary>  
        /// Creates a new <see cref="SQLiteHelpe connectionStr =r"/> instance. The ctor is marked private since all members are static.  
        /// </summary>  
        private SqliteDBHelper()
        {
        }
        /// <summary>  
        /// Creates the command.  
        /// </summary>  
        /// <param name="connection">Connection.</param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="commandParameters">Command parameters.</param>  
        /// <returns>SQLite Command</returns>  
        public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
            if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); }
            return cmd;
        }
        /// <summary>  
        /// Creates the command.  
        /// </summary>  
        /// <param name="connectionString">Connection string.</param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="commandParameters">Command parameters.</param>  
        /// <returns>SQLite Command</returns>  
        public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters)
        {
            SQLiteConnection cn = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, cn);
            if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters)cmd.Parameters.Add(parm); }
            return cmd;
        }
        /// <summary>  
        /// Creates the parameter.  
        /// </summary>  
        /// <param name="parameterName">Name of the parameter.</param>  
        /// <param name="parameterType">Parameter type.</param>  
        /// <param name="parameterValue">Parameter value.</param>  
        /// <returns>SQLiteParameter</returns>  
        public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
        {
            SQLiteParameter parameter = new SQLiteParameter();
            parameter.DbType = parameterType; parameter.ParameterName = parameterName; parameter.Value = parameterValue;
            return parameter;
        }
        /// <summary>  
        /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values  
        /// </summary>  
        /// <param name="connectionString">SQLite Connection string</param>  
        /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>  
        /// <param name="paramList">object[] array of parameter values</param>  
        /// <returns></returns>  
        public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
        {
            SQLiteConnection cn = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            if (paramList != null) { AttachParameters(cmd, commandText, paramList); }
            DataSet ds = new DataSet();
            if (cn.State == ConnectionState.Closed) cn.Open();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close();
            return ds;
        }
        /// <summary>  
        /// Shortcut method to execute dataset from SQL Statement and object[] arrray of  parameter values  
        /// </summary>  
        /// <param name="cn">Connection.</param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="paramList">Param list.</param>  
        /// <returns></returns>  
        public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            if (paramList != null)
            { AttachParameters(cmd, commandText, paramList); }
            DataSet ds = new DataSet();
            if (cn.State == ConnectionState.Closed) cn.Open();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close();
            return ds;
        }
        /// <summary>  
        /// Executes the dataset from a populated Command object.  
        /// </summary>  
        /// <param name="cmd">Fully populated SQLiteCommand</param>  
        /// <returns>DataSet</returns>  
        public static DataSet ExecuteDataset(SQLiteCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();
            DataSet ds = new DataSet();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds); da.Dispose(); cmd.Connection.Close(); cmd.Dispose();
            return ds;
        }
        /// <summary>  
        /// Executes the dataset in a SQLite Transaction  
        /// </summary>  
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,  /// and Command, all of which must be created prior to making this method call. </param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="commandParameters">Sqlite Command parameters.</param>  
        /// <returns>DataSet</returns>  
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>  
        public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            foreach (SQLiteParameter parm in commandParameters) { cmd.Parameters.Add(parm); }
            if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open();
            DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
            return ds;
        }
        /// <summary>  
        /// Executes the dataset with Transaction and object array of parameter values.  
        /// </summary>  
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,    /// and Command, all of which must be created prior to making this method call. </param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="commandParameters">object[] array of parameter values.</param>  
        /// <returns>DataSet</returns>  
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>  
        public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                          please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters);
            if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open();
            DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
            return ds;
        }
        #region UpdateDataset
        /// <summary>  
        /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.  
        /// </summary>  
        /// <remarks>  
        /// e.g.:    
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");  
        /// </remarks>  
        /// <param name="insertCommand">A valid SQL statement  to insert new records into the data source</param>  
        /// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>  
        /// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>  
        /// <param name="dataSet">The DataSet used to update the data source</param>  
        /// <param name="tableName">The DataTable used to update the data source.</param>  
        public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
            // Create a SQLiteDataAdapter, and dispose of it after we are done  
            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands  
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;
                // Update the dataset changes in the data source  
                dataAdapter.Update(dataSet, tableName);
                // Commit all the changes made to the DataSet  
                dataSet.AcceptChanges();
            }
        }
        #endregion
        /// <summary>  
        /// ShortCut method to return IDataReader  
        /// NOTE: You should explicitly close the Command.connection you passed in as  
        /// well as call Dispose on the Command  after reader is closed.  
        /// We do this because IDataReader has no underlying Connection Property.  
        /// </summary>  
        /// <param name="cmd">SQLiteCommand Object</param>  
        /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>  
        /// <param name="paramList">object[] array of parameter values</param>  
        /// <returns>IDataReader</returns>  
        public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
        {
            if (cmd.Connection == null)
                throw new ArgumentException("命令必须有现场连接。", "cmd");
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rdr;
        }
        /// <summary>  
        /// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values  
        /// </summary>  
        /// <param name="connectionString">SQLite Connection String</param>  
        /// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>  
        /// <param name="paramList">object[] array of parameter values</param>  
        /// <returns></returns>  
        public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)
        {
            SQLiteConnection cn = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed) cn.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose(); cn.Close();
            return result;
        }
        public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params  object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed) cn.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose(); cn.Close();
            return result;
        }
        /// <summary>  
        /// Executes  non-query sql Statment with Transaction  
        /// </summary>  
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,   /// and Command, all of which must be created prior to making this method call. </param>  
        /// <param name="commandText">Command text.</param>  
        /// <param name="paramList">Param list.</param>  
        /// <returns>Integer</returns>  
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>  
        public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params  object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
            if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }
        /// <summary>  
        /// Executes the non query.  
        /// </summary>  
        /// <param name="cmd">CMD.</param>  
        /// <returns></returns>  
        public static int ExecuteNonQuery(IDbCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Connection.Close(); cmd.Dispose();
            return result;
        }
        /// <summary>  
        /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values  
        /// </summary>  
        /// <param name="connectionString">SQLite Connection String</param>  
        /// <param name="commandText">SQL statment with embedded "@param" style parameters</param>  
        /// <param name="paramList">object[] array of param values</param>  
        /// <returns></returns>  
        public static object ExecuteScalar(string connectionString, string commandText, params  object[] paramList)
        {
            SQLiteConnection 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>  
        /// Execute XmlReader with complete Command  
        /// </summary>  
        /// <param name="command">SQLite Command</param>  
        /// <returns>XmlReader</returns>  
        public static XmlReader ExecuteXmlReader(IDbCommand command)
        { // open the connection if necessary, but make sure we   
            // know to close it when we?re done.  
            if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); }
            // get a data adapter    
            SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
            DataSet ds = new DataSet();
            // fill the data set, and return the schema information  
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds);
            // convert our dataset to XML  
            StringReader stream = new StringReader(ds.GetXml());
            command.Connection.Close();
            // convert our stream of text to an XmlReader  
            return new XmlTextReader(stream);
        }
        /// <summary>  
        /// 解析SQL语句的参数的名称，分配对象数组中的值，并返回完全填充的ParameterCollection中。
        /// Parses parameter names from SQL Statement, assigns values from object array ,   
        /// and returns fully populated ParameterCollection.  
        /// </summary>  
        /// <param name="commandText">“参数”式的嵌入参数的SQL语句，</param>  
        /// <param name="paramList">Object []数组参数值</param>  
        /// <returns>SQLite的参数集合</returns>  
        /// <remarks>状态的实验。正则表达式处理大多数问题出现。需要注意的是参数的对象数组必须在SQL语句中的参数名称相同的顺序出现。</remarks>  
        private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params  object[] paramList)
        {
            if (paramList == null || paramList.Length == 0) return null;
            SQLiteParameterCollection coll = cmd.Parameters;
            string parmString = commandText.Substring(commandText.IndexOf("@"));
            // pre-process the string so always at least 1 space after a comma.  
            parmString = parmString.Replace(",", " ,");
            // get the named parameters into a match collection  
            string pattern = @"(@)\S*(.*?)\b";
            Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
            MatchCollection mc = ex.Matches(parmString);
            string[] paramNames = new string[mc.Count];
            // int i = 0;
            int mlg = mc.Count;
            for (int jsc = 0; jsc < mlg; jsc++) { Match m = mc[jsc]; paramNames[jsc] = m.Value; }
            //  foreach (Match m in mc) { paramNames[i] = m.Value; i++; }
            // now let's type the parameters  
            int j = 0;
            Type t = null;
            foreach (object o in paramList)
            {
                t = o.GetType();
                SQLiteParameter parm = new SQLiteParameter();
                switch (t.ToString())
                {
                    case ("DBNull"):
                    case ("Char"):
                    case ("SByte"):
                    case ("UInt16"):
                    case ("UInt32"):
                    case ("UInt64"):
                        throw new SystemException("无效的数据类型");
                    case ("System.String"): parm.DbType = DbType.String; parm.ParameterName = paramNames[j]; parm.Value = (string)paramList[j]; coll.Add(parm);
                        break;
                    case ("System.Byte[]"): parm.DbType = DbType.Binary; parm.ParameterName = paramNames[j]; parm.Value = (byte[])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 = (System.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("价值是未知的数据类型");
                } // end switch  
                j++;
            }
            return coll;
        }
        /// <summary>  
        ///执行非查询类型参数从一个DataRow的
        /// </summary>  
        /// <param name="command">Command.</param>  
        /// <param name="dataRow">Data row.</param>  
        /// <returns>Integer result code</returns>  
        public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
        {
            int retVal = 0;
            //如果该行有值，存储过程的参数必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 设置的参数值
                AssignParameterValues(command.Parameters, dataRow); retVal = ExecuteNonQuery(command);
            }
            else { retVal = ExecuteNonQuery(command); }
            return retVal;
        }
        /// <summary>  
        /// This method assigns dataRow column values to an IDataParameterCollection  
        /// </summary>  
        /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>  
        /// <param name="dataRow">The dataRow used to hold the command's parameter values</param>  
        /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>  
        protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
        {// Do nothing if we get no data  
            if (commandParameters == null || dataRow == null) { return; }
            DataColumnCollection columns = dataRow.Table.Columns;
            int count = commandParameters.Count;
            for (int i = 0; i < count; i++)
            {
                if (commandParameters[i] is IDataParameter)
                {
                    IDataParameter commandParameter = (IDataParameter)commandParameters[i];
                    if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1)
                        throw new InvalidOperationException(string.Format("请提供一个有效的参数名称的参数#{0},参数名称属性具有以下值:'{1}'.", i, commandParameter.ParameterName));
                    if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName];
                    else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                }
            }
            #region MyRegion
            //int i = 0;
            //// Set the parameters values  
            //foreach (IDataParameter commandParameter in commandParameters)
            //{
            //    // Check the parameter name  
            //    if (commandParameter.ParameterName == null ||
            //     commandParameter.ParameterName.Length <= 1)
            //        throw new InvalidOperationException(string.Format("请提供一个有效的参数名称的参数#{0},参数名称属性具有以下值:'{1}'.", i, commandParameter.ParameterName));
            //    if (columns.Contains(commandParameter.ParameterName))
            //        commandParameter.Value = dataRow[commandParameter.ParameterName];
            //    else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
            //        commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
            //    i++;
            //} 
            #endregion
        }
        /// <summary>  
        /// This method assigns dataRow column values to an array of IDataParameters  
        /// </summary>  
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>  
        /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>  
        /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>  
        protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
        { // Do nothing if we get no data  
            if ((commandParameters == null) || (dataRow == null)) { return; }
            DataColumnCollection columns = dataRow.Table.Columns;
            int i = 0;
            // Set the parameters values  
            foreach (IDataParameter commandParameter in commandParameters)
            {
                // Check the parameter name  
                if (commandParameter.ParameterName == null ||
                 commandParameter.ParameterName.Length <= 1)
                    throw new InvalidOperationException(string.Format(
                     "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                     i, commandParameter.ParameterName));
                if (columns.Contains(commandParameter.ParameterName))
                    commandParameter.Value = dataRow[commandParameter.ParameterName];
                else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }
        /// <summary>  
        /// This method assigns an array of values to an array of IDataParameters  
        /// </summary>  
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>  
        /// <param name="parameterValues">Array of objects holding the values to be assigned</param>  
        /// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>  
        protected void AssignParameterValues(IDataParameter[] commandParameters, params  object[] parameterValues)
        {// Do nothing if we get no data  
            if ((commandParameters == null) || (parameterValues == null)) { return; }
            // We must have the same number of values as we pave parameters to put them in  
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }
            // Iterate through the IDataParameters, assigning the values from the corresponding position in the   
            // value array  
            for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)
            {
                if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
                {
                    // If the current array value derives from IDataParameter, then assign its Value property  
                    if (parameterValues[k] is IDataParameter)
                    {
                        IDataParameter paramInstance = (IDataParameter)parameterValues[k];
                        if (paramInstance.Direction == ParameterDirection.ReturnValue) { paramInstance = (IDataParameter)parameterValues[++k]; }
                        if (paramInstance.Value == null) { commandParameters[i].Value = DBNull.Value; }
                        else { commandParameters[i].Value = paramInstance.Value; }
                    }
                    else if (parameterValues[k] == null) { commandParameters[i].Value = DBNull.Value; }
                    else { commandParameters[i].Value = parameterValues[k]; }
                    k++;
                }
            }
        }
    }
}


 

SQLite3 DBHelper
03-05
最近在做一个winform项目，数据库要求用sqlite，结合以前的DBHelper,重新整理了sqlite的DBHelper,优化了数据的打开关闭，以及事务的处理等。
SQLiteDBHelper
08-05
SQLite入门，拜读周公（周金桥）的SQLite总结。将所生成的数据库文件直接拖到SQLiteDeveloper中，就可以像SQL Server Management Studio那样直接访问数据了。
C# Sqlite数据库帮助类（DBHelperSQLite）
LJF的博客
05-17 1617
C# Sqlite帮助类，添加数据库文件判断以及表判断，没有则创建
SQLite DBHelp
weixin_30784141的博客
08-15 156
c#连接SQLite SQLite这个精巧的小数据库，无需安装软件，只需要一个System.Data.SQLite.DLL文件即可操作SQLite数据库。SQLite是一个开源数据库，现在已变得越来越流行，它的体积很小，被广泛应用于各种不同类型的应用中。SQLite已经是世界上布署得最广泛的SQL数据库引擎，被用在无以计数的桌面电脑应用中，还有消费电子设备中，如移动电话、掌上电脑和MP3播放器等...
SQLite DBHelper
01-23
SQLite DBHelper连接数据库的常用方法，希望加载评论
C# 封装数据库访问的SqLiteDBHelper
10-27
最做项目用到了SqlLite数据库，从网上没有找到封装好的SqlLiteDBHelper类，于是就自己写了个，分享给大家。有什么不足之处请多多之处，并加以完善，大家都力量是伟大的嘛！
SQLiteDBHelper入门教程与操作指南
SQLiteDBHelper 是一个常用的类名称，在多种编程语言的SQLite数据库应用中出现，尤其是C#语言中，它通常用于封装和简化SQLite数据库的操作。在给出的文件信息中，我们可以提取以下知识点： 1. **SQLite入门**： ...
已知类:public class SqliteDBHelper : IDisposable { #region 基础参数 public static JsonSerializerSettings settings = new JsonSerializerSettings { ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; private readonly SqliteConnectionStringBuilder _connStr; public string ConnStr => _connStr.ConnectionString; private SqliteConnection _connection; #endregion #region 构造函数 public SqliteDBHelper(string connectionString) { if (string.IsNullOrEmpty(connectionString)) throw new ArgumentException("连接字符串不能为空"); _connStr = new SqliteConnectionStringBuilder(connectionString); _connection = new SqliteConnection(_connStr.ConnectionString); } public SqliteDBHelper(string sqliteFilePathAndName, SqliteOpenMode mode, SqliteCacheMode cacheMode, int defaultTimeout = 30, bool isUseSharePool = true) { _connStr = new SqliteConnectionStringBuilder { DataSource = sqliteFilePathAndName, Mode = mode, Cache = cacheMode, //Pooling = isUseSharePool }; _connection = new SqliteConnection(_connStr.ConnectionString); } public SqliteDBHelper(string sqliteFilePathAndName, string password, SqliteOpenMode mode, SqliteCacheMode cacheMode, int defaultTimeout = 30, bool isUseSharePool = true) { _connStr = new SqliteConnectionStringBuilder { DataSource = sqliteFilePathAndName, Password = password, Mode = mode, Cache = cacheMode, Pooling = isUseSharePool }; _connection = new SqliteConnection(_connStr.ConnectionString); } #endregion #region 数据库管理 private static SqliteDBHelper _instance = null; /// <summary> /// 单例模式 /// </summary> public static SqliteDBHelper Instance { get { if (_instance == null) { var builder = new SqliteConnectionStringBuilder { DataSource = "mydatabase.db", Mode = SqliteOpenMode.ReadWriteCreate }; SqliteDBHelper sqliteDBHelper = new SqliteDBHelper(builder.ToString()); sqliteDBHelper.Open(); _instance = sqliteDBHelper; } return _instance; } } public void Open() => _connection.Open(); public void ExecuteNonQuery(string sql) { using var command = _connection.CreateCommand(); command.CommandText = sql; command.ExecuteNonQuery(); } public void Dispose() => _connection?.Dispose(); public ResultInfo CreateSqliteDatabase(string sqliteFilePathAndName) { var result = new ResultInfo(); try { if (File.Exists(sqliteFilePathAndName)) { result.SetError($"{sqliteFilePathAndName} 文件已存在！"); return result; } string folder = Path.GetDirectoryName(sqliteFilePathAndName); if (!Directory.Exists(folder)) Directory.CreateDirectory(folder); using var connection = new SqliteConnection(_connStr.ConnectionString); //_connection = new SqliteConnection(_connStr.ConnectionString); //connection.Open(); result.SetSuccess($"{sqliteFilePathAndName} 创建成功"); } catch (Exception ex) { result.SetError(ex.Message); } return result; } public ResultInfo ModifyDatabasePassword(string oldPassword, string newPassword) { var result = new ResultInfo(); try { var builder = new SqliteConnectionStringBuilder(ConnStr) { Password = oldPassword }; using var conn = new SqliteConnection(builder.ConnectionString); conn.Open(); using var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT quote($newPassword)"; cmd.Parameters.AddWithValue("$newPassword", newPassword); var quotedNewPassword = (string)cmd.ExecuteScalar(); cmd.CommandText = $"PRAGMA rekey = {quotedNewPassword}"; cmd.ExecuteNonQuery(); _connStr.Password = newPassword; conn.Dispose(); result.SetSuccess("密码修改成功"); } catch (Exception ex) { result.SetError($"修改密码失败: {ex.Message}"); } return result; } public ResultInfo CreateTable(string tableName, List<SqliteFieldInfo> fieldList) { var result = new ResultInfo(); if (string.IsNullOrEmpty(tableName) || fieldList == null || fieldList.Count == 0) { result.SetError("表名或字段列表为空"); return result; } var sb = new StringBuilder(); sb.Append($"CREATE TABLE IF NOT EXISTS {tableName} ( \n"); for (int i = 0; i < fieldList.Count; i++) { var field = fieldList[i]; sb.Append(FormatField(field)); if (i < fieldList.Count - 1) sb.Append(",\n"); } sb.Append("\n);"); try { using var conn = new SqliteConnection(ConnStr); conn.Execute(sb.ToString()); result.SetSuccess($"表 {tableName} 创建成功"); } catch (Exception ex) { result.SetError($"创建表失败: {ex.Message}"); } return result; } public ResultInfo DropTable(string tableName ) { var result = new ResultInfo(); if (string.IsNullOrEmpty(tableName) ) { result.SetError("表名或字段列表为空"); return result; } var sb = new StringBuilder(); sb.Append($"DROP TABLE {tableName} \n"); try { using var conn = new SqliteConnection(ConnStr); conn.Execute(sb.ToString()); result.SetSuccess($"表 {tableName} drop成功"); } catch (Exception ex) { result.SetError($"drop表失败: {ex.Message}"); } return result; } private string FormatField(SqliteFieldInfo field) { if (field == null) return ""; var sb = new StringBuilder(); sb.Append($"{field.Name} {field.DataType}"); if (field.Length > 0) sb.Append($"({field.Length})"); if (field.IsNotEmpty) sb.Append(" NOT NULL"); if (field.IsPrimaryKey) sb.Append(" PRIMARY KEY"); if (field.IsAutoIncrement) sb.Append(" AUTOINCREMENT"); return sb.ToString(); } #endregion #region CRUD 操作 public IDbConnection GetOpenConnection() { var conn = new SqliteConnection(ConnStr); conn.Open(); return conn; } public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters) { using var conn = GetOpenConnection(); using var cmd = new SqliteCommand(sql, (SqliteConnection?)conn); foreach (var param in parameters) { cmd.Parameters.AddWithValue(param.Key, param.Value); } return cmd.ExecuteNonQuery(); } // 创建索引的扩展方法 public void CreateIndex<T>(string columnName) { var tableName = typeof(T).Name; var sql = $"CREATE INDEX IF NOT EXISTS idx_{tableName}_{columnName} ON {tableName}({columnName})"; ExecuteNonQuery(sql, new Dictionary<string, object>()); } // 通用插入方法 /// <summary> /// 插入一条记录 /// </summary> public int Insert<T>(T entity) { var propsraw = typeof(T).GetProperties(); var props = new List<PropertyInfo>(propsraw.Length); foreach(var prop in propsraw) { var sqliteAttr = prop.GetCustomAttribute<SqliteField>(); if (sqliteAttr == null || !sqliteAttr.Ignore) { props.Add(prop); } } var columns = string.Join(",", props.Select(p => p.Name)); var values = string.Join(",", props.Select(p => $"@{p.Name}")); var sql = $"INSERT INTO {typeof(T).Name}({columns}) VALUES({values})"; var parameters = props.ToDictionary(p => $"@{p.Name}", p => p.GetValue(entity)); return ExecuteNonQuery(sql, parameters); } ///// <summary> ///// 更新一条记录 ///// </summary> //public bool Update<T>(T entity) //{ // using var conn = GetOpenConnection(); // return conn.Update(entity); //} ///// <summary> ///// 删除一条记录 ///// </summary> //public bool Delete<T>(T entity) //{ // using var conn = GetOpenConnection(); // return conn.Delete(entity); //} public int Update(string tableName, Dictionary<string, object> setValues, Dictionary<string, object> whereConditions) { var setClause = string.Join(",", setValues.Keys.Select(k => $"{k} = ${k}")); var whereClause = string.Join(" AND ", whereConditions.Keys.Select(k => $"{k} = ${k}")); var sql = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}"; using var conn = GetOpenConnection(); using var cmd = new SqliteCommand(sql, (SqliteConnection?)conn); foreach (var param in setValues.Concat(whereConditions)) { cmd.Parameters.AddWithValue($"${param.Key}", param.Value); // 关键点：参数前缀$ } return cmd.ExecuteNonQuery(); } public int SafeUpdate(string tableName, Dictionary<string, object> setValues, Dictionary<string, object> whereConditions, int retryCount = 3) { for (int i = 0; i < retryCount; i++) { try { return Update(tableName, setValues, whereConditions); } catch (SqliteException ex) when (ex.SqliteErrorCode == 5) { if (i == retryCount - 1) throw; Thread.Sleep(100 * (i + 1)); } } return 0; } public int DeleteWithTransaction(string tableName, Dictionary<string, object> conditions) { using var conn = GetOpenConnection(); using var transaction = conn.BeginTransaction(); try { var whereClause = string.Join(" AND ", conditions.Keys.Select(k => $"{k} = ${k}")); var sql = $"DELETE FROM {tableName} WHERE {whereClause}"; using var cmd = new SqliteCommand(sql, (SqliteConnection?)conn, (SqliteTransaction?)transaction); foreach (var param in conditions) { cmd.Parameters.AddWithValue($"${param.Key}", param.Value); } int result = cmd.ExecuteNonQuery(); transaction.Commit(); return result; } catch (SqliteException ex) when (ex.SqliteErrorCode == 5) // SQLITE_BUSY { transaction.Rollback(); Thread.Sleep(100); return DeleteWithTransaction(tableName, conditions); // 递归重试 } } /// <summary> /// 查询所有记录 /// </summary> public IEnumerable<T> QueryAll<T>() where T : class { using var conn = GetOpenConnection(); return conn.Query<T>("SELECT * FROM " + typeof(T).Name); } /// <summary> /// 根据条件查询 /// </summary> public IEnumerable<T> Query<T>(string sql, object param = null) { using var conn = GetOpenConnection(); return conn.Query<T>(sql, param); } /// <summary> /// 分页查询 /// </summary> public IEnumerable<T> QueryPaged<T>(int page, int pageSize, out int totalCount) { var tableName = typeof(T).Name; var offset = (page - 1) * pageSize; using var conn = GetOpenConnection(); var items = conn.Query<T>($"SELECT * FROM {tableName} LIMIT @limit OFFSET @offset", new { limit = pageSize, offset = offset }); totalCount = conn.ExecuteScalar<int>($"SELECT COUNT(*) FROM {tableName}"); return items; } public List<SqliteFieldInfo> GenerateFieldInfo<T>() { var fieldList = new List<SqliteFieldInfo>(); foreach (var prop in typeof(T).GetProperties()) { var field = new SqliteFieldInfo { Name = prop.Name }; // 解析数据类型 field.DataType = prop.PropertyType switch { var t when t == typeof(int) => "INTEGER", var t when t == typeof(string) => "TEXT", var t when t == typeof(double) => "REAL", _ => "BLOB" }; // 处理注解 var sqliteAttr = prop.GetCustomAttribute<SqliteField>(); if (sqliteAttr != null && sqliteAttr.Ignore) { continue; } var maxLenAttr = prop.GetCustomAttribute<MaxLengthAttr>(); var pkAttr = prop.GetCustomAttribute<PrimaryKey>(); field.IsPrimaryKey = pkAttr != null;//|| (sqliteAttr?.IsPrimaryKey ?? false) field.IsAutoIncrement = sqliteAttr?.AutoIncrement ?? false; field.IsNotEmpty = sqliteAttr?.NotNull ?? false; field.Length = maxLenAttr?.Length ?? sqliteAttr?.Length ?? 0; fieldList.Add(field); } return fieldList; }} List<PlcPoint> list = 根据SqliteDBHelper 初始一组点 参数是readerid
最新发布
06-01
好的，我需要帮助用户根据SqliteDBHelper类通过readerid参数初始化一组PlcPoint对象。首先，我需要理解用户的需求。他们有一个SqliteDBHelper类，可能用于处理数据库连接和操作，现在想要使用这个类，根据传入的...
public class User { //[PrimaryKey] public int Id { get; set; } [MaxLength(50)] public string Name { get; set; } } #endregion public static void main() { SqliteDBHelper sqliteDBHelper = new SqliteDBHelper("./xkj_reader.db"); //sqliteDBHelper.CreateSqliteDatabase(); List<SqliteFieldInfo> fieldList = new List<SqliteFieldInfo>(); var props = typeof(PlcPoint).GetProperties(); var columns = string.Join(",", props.Select(p => p.Name)); var values = string.Join(",", props.Select(p => $"@{p.Name}")); foreach ( var prop in props) { SqliteFieldInfo field = new SqliteFieldInfo(); field.Name = prop.Name; field.DataType = prop.PropertyType.ToString(); } sqliteDBHelper.CreateTable("rfid_points"); } 优化代码根据注解填充数据类型,长度,IsNotEmpty,IsPrimaryKey,IsAutoIncrement
06-01
嗯，用户希望优化他们的SqliteDBHelper类，特别是根据属性注解自动生成SqliteFieldInfo，并修复Update和Delete方法的错误。首先，我需要理解他们的代码结构。他们有一个SqliteFieldInfo类，用来定义表的字段信息，但...
SQLite数据库DBHelper(C#)
06-29
我写的一个SQLite数据库DBHelper(C#)，操作数据库层的类
Sqlite 库\DbHelperSqlite
02-23
sqlite库操作部分,对动软生成的dll,dal做相应调整
SQLite.Helper:一个简单的工具，可帮助您使用C＃管理SQLite数据库
05-18
SQLite.Helper ####是否在C＃项目中使用SQLite？####如果是，那就不用担心，SQLite.Helper是旨在帮助您管理SQLite数据库的类。 ####如何使用？####首先，请确保在您的系统上。 将DLL添加到您的项目引用中并使用代码 using SQLite . Helper ; 然后调用该类以使用它 SQLiteHelper sqlite = new SQLiteHelper ( " C:\database.db " ); 其中C：\ database.db是数据库的路径。 然后只需调用所需的方法，例如获取字符串 string StrValue = Convert . ToString ( sqlite . Get ( " SELECT `something` FROM `thattable` WHERE `otherthing` = 10
SQLite帮助类SQLiteHelper
06-24
一些小型的应用程序需要使用到数据库，sqlite可以说是最好的选择。这里个人整理了一般简单通用的操作类SQLiteHelper
android SQLite DBHelper 单例模式 实现
01-18
android SQLite DBHelper 单例模式 实现 dbhelper=DBHelper.getInstance(this);
Sqlite操作类-DbHelperSQLite
weixin_30344131的博客
11-13 549
1: /// <summary> 2: /// 数据访问基础类(基于SQLite) 3: /// 可以用户可以修改满足自己项目的需要。 4: /// </summary> 5: public abstract class DbHelperSQLite 6: { 7: ...
.Net下SQLite的DBHelp
dddp75921的博客
07-31 128
怎样获取SqLite请参考初识SqlLite ---.net连接数据库,怎样在SQLite使用Linq请参考在C#中利用Nuget包使用SQLite数据库和Linq to SQLite using System; using System.Collections.Generic; using System.Data; using System.Data.SQLite; //...
SQLiteHelper
weixin_34143774的博客
02-08 163
本文定义了一个DBHelper类，是SQLite的。需要添加一个System.Data.SQLite的dll，可以到SQLite的官网下载。类的代码如下 1 using System; 2 using System.Data; 3 using System.Data.SQLite; 4 using System.Configuration; 5 using Sys...
java sqlite dbhelper,Android 之 SQLite 数据库 通讯录应用程序
weixin_31108679的博客
03-13 475
新建一个 SQLLiteApp Android 工程数据库文件 存放位置如图：下面我们一步一步实现1. 新建电话类 com.models.Telephonepackage com.models;public class Telephone {private int id;private String name;private String telephone;public Telephone(St...
sqlite之DbHelper&ObjectService
weixin_30908103的博客
12-10 115
1.DbHelper类，继承系统的SQLiteOpenHelper类，创建，更新，获取游标 1 package tool; 2 3 import android.content.Context; 4 import android.database.Cursor; 5 import android.database.sqlite.SQLiteDatabase; 6 import...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值