alexmen

专注.net软件开发,项目管理体系PMBOK.

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
  /// <summary>
    /// 使用  System.Data.SQLite.dll  版本 1.0.102.0   官方例子名称:sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.102.0
    /// </summary>
    public class SqliteHelper
    {
        private readonly static string DbName = "XXX";
        private readonly static string dbPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
        private readonly static string connectionstring = String.Format("Data Source={0}\\{1}.db;Version=3;Pooling=False;Max Pool Size=100;", dbPath, DbName);

        public static DbConnection GetConn
        {
            get
            {
                SQLiteConnection conn = new SQLiteConnection(connectionstring);
                conn.Open();
                return conn;
            }
        }
        public static int ExecuteNonQuery(string commandText)
        {
            using (DbCommand cmd = GetConn.CreateCommand())
            {
                cmd.CommandText = commandText;
                return cmd.ExecuteNonQuery();

            }
        }

        public static int ExecuteNonQuery(string commandText, DbParameter[] parameters)
        {
            using (DbCommand cmd = GetConn.CreateCommand())
            {
                cmd.CommandText = commandText;
                cmd.Parameters.AddRange(parameters);
                int r = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return r;
            }
        }

        public static object ExecuteScalar(string commandText)
        {
            using (DbCommand cmd = GetConn.CreateCommand())
            {
                cmd.CommandText = commandText;
                return cmd.ExecuteScalar();

            }
        }

        public static DbDataReader ExecuteReader(string commandText)
        {
            DbCommand cmd = GetConn.CreateCommand();
            try
            {
                cmd.CommandText = commandText;
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;

            }
            catch (Exception ex)
            {
                GetConn.Close();
                cmd.Dispose();
                throw ex;
            }

        }

        public static DbDataReader ExecuteReader(string commandText, DbParameter[] paramers)
        {
            DbCommand cmd = GetConn.CreateCommand();
            try
            {
                cmd.CommandText = commandText;
                cmd.Parameters.AddRange(paramers);
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                GetConn.Close();
                cmd.Dispose();
                throw;
            }

        }

        public static DataSet ExecuteDataSet(string commandText, DbParameter[] paramers)
        {
            using (DbCommand cmd = GetConn.CreateCommand())
            {
                int dtbegin = Environment.TickCount;
                DataSet ds = new DataSet();
                cmd.CommandText = commandText;
                cmd.Parameters.AddRange(paramers);
                DbDataAdapter adp = new SQLiteDataAdapter(cmd as SQLiteCommand);
                adp.Fill(ds);
                int dtEnd = Environment.TickCount;
                int times = dtEnd - dtbegin;
                return ds;

            }
        }

        public static void ExecuteNonMany(List<string> list)
        {
            DbConnection conn = GetConn;
            using (DbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                try
                {
                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        list.ForEach((str) =>
                        {
                            cmd.CommandText = str;
                            cmd.ExecuteNonQuery();
                        });
                    }
                }
                catch (Exception)
                {

                    trans.Rollback();
                }

            }
        }


        public static void ExecuteNonMany(string sql, List<DbParameter[]> parameterList)
        {
            using (DbConnection conn = GetConn)
            {
                using (DbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        using (DbCommand cmd = conn.CreateCommand())
                        {
                            parameterList.ForEach((parameter) =>
                            {
                                cmd.CommandText = sql;
                                cmd.Parameters.AddRange(parameter);
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();

                            });

                            trans.Commit();
                        }
                    }
                    catch (Exception)
                    {

                        trans.Rollback();
                    }

                }
            }

        }


    }

 

posted on 2016-08-02 23:08  alexmen  阅读(209)  评论(0编辑  收藏  举报