苏飞论坛

标题: 求sqlite 操作类 [打印本页]

作者: hoo_eagle 时间: 2012-12-11 21:59
标题: 求sqlite 操作类
求sqlite 操作类，谢谢

作者: 站长苏飞 时间: 2012-12-11 22:04
呵呵，我这里好像没有这个类，主要是做什么的，没听说过，也许我这里有类似的

作者: hoo_eagle 时间: 2012-12-11 22:42
是一种数据库呀。类似access,mssql

作者: wubpy 时间: 2012-12-11 22:55
其实自己下载个 SQL server 的数据库操作类然后比对着改改就OK 了。

作者: 站长苏飞 时间: 2012-12-12 10:35
呵呵，我没用过这种数据库，抱歉，我这儿没，楼上说的是，修改一下，或者查查其它地方看看吧

作者: 水中草 时间: 2013-1-7 12:03
我这里有一个，看看能不能用。不能上传附件，分段给你传吧。

[code=csharp]using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SQLite;

namespace SQLiteHelper
{
public class SQLiteHelper
{
      //数据库连接字符串(web.config来配置)，可以动态更改SQLString支持多数据库.
  System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["SQLString"]);
      public static string connectionString = @"Data Source=F:\MyCode\mydb.db";


      public SQLiteHelper() { }

      #region 公用方法

      public static int GetMaxID(string FieldName, string TableName)
      {
         string strsql = "select max(" + FieldName + ")+1 from " + TableName;
         object obj = GetSingle(strsql);
         if (obj == null)
         {
            return 1;
         }
         else
         {
            return int.Parse(obj.ToString());
         }
      }

      public static bool Exists(string strSql)
      {
         object obj = GetSingle(strSql);
         int cmdresult;
         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
         {
            cmdresult = 0;
         }
         else
         {
            cmdresult = int.Parse(obj.ToString());
         }
         if (cmdresult == 0)
         {
            return false;
         }
         else
         {
            return true;
         }
      }

      public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
      {
         object obj = GetSingle(strSql, cmdParms);
         int cmdresult;
         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
         {
            cmdresult = 0;
         }
         else
         {
            cmdresult = int.Parse(obj.ToString());
         }
         if (cmdresult == 0)
         {
            return false;
         }
         else
         {
            return true;
         }
      }

      #endregion

      #region  执行简单SQL语句

      /// <summary>
      /// 执行SQL语句，返回影响的记录数
      /// </summary>
      /// <param name="SQLString">SQL语句</param>
      /// <returns>影响的记录数</returns>
      public static int ExecuteSql(string SQLString)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                  try
                  {
                     connection.Open();
                     int rows = cmd.ExecuteNonQuery();
                     return rows;
                  }
                  catch (System.Data.SQLite.SQLiteException E)
                  {
                     connection.Close();
                     throw new Exception(E.Message);
                  }
            }
         }
      }

      /// <summary>
      /// 执行SQL语句，设置命令的执行等待时间
      /// </summary>
      /// <param name="SQLString"></param>
      /// <param name="Times"></param>
      /// <returns></returns>
      public static int ExecuteSqlByTime(string SQLString, int Times)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                  try
                  {
                     connection.Open();
                     cmd.CommandTimeout = Times;
                     int rows = cmd.ExecuteNonQuery();
                     return rows;
                  }
                  catch (System.Data.SQLite.SQLiteException E)
                  {
                     connection.Close();
                     throw new Exception(E.Message);
                  }
            }
         }
      }

      /// <summary>
      /// 执行多条SQL语句，实现数据库事务。
      /// </summary>
      /// <param name="SQLStringList">多条SQL语句</param>
      public static void ExecuteSqlTran(ArrayList SQLStringList)
      {
         using (SQLiteConnection conn = new SQLiteConnection(connectionString))
         {
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = conn;
            SQLiteTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                  for (int n = 0; n < SQLStringList.Count; n++)
                  {
                     string strsql = SQLStringList[n].ToString();
                     if (strsql.Trim().Length > 1)
                     {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                     }
                  }
                  tx.Commit();
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                  tx.Rollback();
                  throw new Exception(E.Message);
            }
         }
      }

      /// <summary>
      /// 执行带一个存储过程参数的的SQL语句。
      /// </summary>
      /// <param name="SQLString">SQL语句</param>
      /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
      /// <returns>影响的记录数</returns>
      public static int ExecuteSql(string SQLString, string content)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                  connection.Open();
                  int rows = cmd.ExecuteNonQuery();
                  return rows;
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                  throw new Exception(E.Message);
            }
            finally
            {
                  cmd.Dispose();
                  connection.Close();
            }
         }
      }

      /// <summary>
      /// 执行带一个存储过程参数的的SQL语句。
      /// </summary>
      /// <param name="SQLString">SQL语句</param>
      /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
      /// <returns>影响的记录数</returns>
      public static object ExecuteSqlGet(string SQLString, string content)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                  connection.Open();
                  object obj = cmd.ExecuteScalar();
                  if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                  {
                     return null;
                  }
                  else
                  {
                     return obj;
                  }
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                  throw new Exception(E.Message);
            }
            finally
            {
                  cmd.Dispose();
                  connection.Close();
            }
         }
      }[/code]

作者: 水中草 时间: 2013-1-7 12:05
[code=csharp]       /// <summary>
      /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
      /// </summary>
      /// <param name="strSQL">SQL语句</param>
      /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
      /// <returns>影响的记录数</returns>
      public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
            myParameter.Value = fs;
            cmd.Parameters.Add(myParameter);
            try
            {
                  connection.Open();
                  int rows = cmd.ExecuteNonQuery();
                  return rows;
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                  throw new Exception(E.Message);
            }
            finally
            {
                  cmd.Dispose();
                  connection.Close();
            }
         }
      }

      /// <summary>
      /// 执行一条计算查询结果语句，返回查询结果（object）。
      /// </summary>
      /// <param name="SQLString">计算查询结果语句</param>
      /// <returns>查询结果（object）</returns>
      public static object GetSingle(string SQLString)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                  try
                  {
                     connection.Open();
                     object obj = cmd.ExecuteScalar();
                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                     {
                        return null;
                     }
                     else
                     {
                        return obj;
                     }
                  }
                  catch (System.Data.SQLite.SQLiteException e)
                  {
                     connection.Close();
                     throw new Exception(e.Message);
                  }
            }
         }
      }

      /// <summary>
      /// 执行查询语句，返回SQLiteDataReader(使用该方法切记要手工关闭SQLiteDataReader和连接)
      /// </summary>
      /// <param name="strSQL">查询语句</param>
      /// <returns>SQLiteDataReader</returns>
      public static SQLiteDataReader ExecuteReader(string strSQL)
      {
         SQLiteConnection connection = new SQLiteConnection(connectionString);
         SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
         try
         {
            connection.Open();
            SQLiteDataReader myReader = cmd.ExecuteReader();
            return myReader;
         }
         catch (System.Data.SQLite.SQLiteException e)
         {
            throw new Exception(e.Message);
         }
         //finally //不能在此关闭，否则，返回的对象将无法使用
         //{
         // cmd.Dispose();
         // connection.Close();
         //}
      }

      /// <summary>
      /// 执行查询语句，返回DataSet
      /// </summary>
      /// <param name="SQLString">查询语句</param>
      /// <returns>DataSet</returns>
      public static DataSet Query(string SQLString)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            DataSet ds = new DataSet();
            try
            {
                  connection.Open();
                  SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                  command.Fill(ds, "ds");
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                  throw new Exception(ex.Message);
            }
            return ds;
         }
      }

      public static DataSet Query(string SQLString, string TableName)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            DataSet ds = new DataSet();
            try
            {
                  connection.Open();
                  SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                  command.Fill(ds, TableName);
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                  throw new Exception(ex.Message);
            }
            return ds;
         }
      }

      /// <summary>
      /// 执行查询语句，返回DataSet,设置命令的执行等待时间
      /// </summary>
      /// <param name="SQLString"></param>
      /// <param name="Times"></param>
      /// <returns></returns>
      public static DataSet Query(string SQLString, int Times)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            DataSet ds = new DataSet();
            try
            {
                  connection.Open();
                  SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                  command.SelectCommand.CommandTimeout = Times;
                  command.Fill(ds, "ds");
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                  throw new Exception(ex.Message);
            }
            return ds;
         }
      }

      #endregion

      #region 执行带参数的SQL语句

      /// <summary>
      /// 执行SQL语句，返回影响的记录数
      /// </summary>
      /// <param name="SQLString">SQL语句</param>
      /// <returns>影响的记录数</returns>
      public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                  try
                  {
                     PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                     int rows = cmd.ExecuteNonQuery();
                     cmd.Parameters.Clear();
                     return rows;
                  }
                  catch (System.Data.SQLite.SQLiteException E)
                  {
                     throw new Exception(E.Message);
                  }
            }
         }
      }

      /// <summary>
      /// 执行多条SQL语句，实现数据库事务。
      /// </summary>
      /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
      public static void ExecuteSqlTran(Hashtable SQLStringList)
      {
         using (SQLiteConnection conn = new SQLiteConnection(connectionString))
         {
            conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                  SQLiteCommand cmd = new SQLiteCommand();
                  try
                  {
                     //循环
                     foreach (DictionaryEntry myDE in SQLStringList)
                     {
                        string cmdText = myDE.Key.ToString();
                        SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();

                        trans.Commit();
                     }
                  }
                  catch
                  {
                     trans.Rollback();
                     throw;
                  }
            }
         }
      }[/code]

作者: 水中草 时间: 2013-1-7 12:06
[code=csharp]       /// <summary>
      /// 执行一条计算查询结果语句，返回查询结果（object）。
      /// </summary>
      /// <param name="SQLString">计算查询结果语句</param>
      /// <returns>查询结果（object）</returns>
      public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                  try
                  {
                     PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                     object obj = cmd.ExecuteScalar();
                     cmd.Parameters.Clear();
                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                     {
                        return null;
                     }
                     else
                     {
                        return obj;
                     }
                  }
                  catch (System.Data.SQLite.SQLiteException e)
                  {
                     throw new Exception(e.Message);
                  }
            }
         }
      }

      /// <summary>
      /// 执行查询语句，返回SQLiteDataReader (使用该方法切记要手工关闭SQLiteDataReader和连接)
      /// </summary>
      /// <param name="strSQL">查询语句</param>
      /// <returns>SQLiteDataReader</returns>
      public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
      {
         SQLiteConnection connection = new SQLiteConnection(connectionString);
         SQLiteCommand cmd = new SQLiteCommand();
         try
         {
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return myReader;
         }
         catch (System.Data.SQLite.SQLiteException e)
         {
            throw new Exception(e.Message);
         }
         //finally //不能在此关闭，否则，返回的对象将无法使用
         //{
         // cmd.Dispose();
         // connection.Close();
         //}

      }

      /// <summary>
      /// 执行查询语句，返回DataSet
      /// </summary>
      /// <param name="SQLString">查询语句</param>
      /// <returns>DataSet</returns>
      public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
      {
         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
         {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
            {
                  DataSet ds = new DataSet();
                  try
                  {
                     da.Fill(ds, "ds");
                     cmd.Parameters.Clear();
                  }
                  catch (System.Data.SQLite.SQLiteException ex)
                  {
                     throw new Exception(ex.Message);
                  }
                  return ds;
            }
         }
      }

      public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
      {
         if (conn.State != ConnectionState.Open)
            conn.Open();
         cmd.Connection = conn;
         cmd.CommandText = cmdText;
         if (trans != null)
            cmd.Transaction = trans;
         cmd.CommandType = CommandType.Text;//cmdType;
         if (cmdParms != null)
         {

            foreach (SQLiteParameter parameter in cmdParms)
            {
                  if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                     (parameter.Value == null))
                  {
                     parameter.Value = DBNull.Value;
                  }
                  cmd.Parameters.Add(parameter);
            }
         }
      }

      #endregion

      #region 参数转换
      /// <summary>
      /// 放回一个SQLiteParameter
      /// </summary>
      /// <param name="name">参数名字</param>
      /// <param name="type">参数类型</param>
      /// <param name="size">参数大小</param>
      /// <param name="value">参数值</param>
      /// <returns>SQLiteParameter的值</returns>
      public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, int size, object value)
      {
         SQLiteParameter parm = new SQLiteParameter(name, type, size);
         parm.Value = value;
         return parm;
      }

      public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)
      {
         SQLiteParameter parm = new SQLiteParameter(name, type);
         parm.Value = value;
         return parm;
      }

      #endregion
}
}
[/code]

作者: 站长苏飞 时间: 2013-1-7 12:35

水中草发表于 2013-1-7 12:06

你现在应该可以上传了

作者: 水中草 时间: 2013-1-7 13:08
谢谢，重新上传了附件。

作者: yisa 时间: 2013-1-7 13:50
sqlite 类重要的是多线程操作呵呵 {:soso_e141:}

作者: myde520 时间: 2013-1-25 10:53
我之前写过有用到SQLlite数据库的小工具，不知道我放在哪里了

作者: mxat 时间: 2013-3-6 10:45
其实你把sqlserver的类改成就可以了

作者: goldowl 时间: 2016-1-16 22:16
受教了学习中……

欢迎光临苏飞论坛 (http://www.sufeinet.com/)