Esempio n. 1
0
        /// <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, object[] paramList)
        {
            SQLiteConnection cn  = new SQLiteConnection(connectionString);
            SQLiteCommand    cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);

            if (null != parms)
            {
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            object result = cmd.ExecuteScalar();

            cmd.Dispose();
            cn.Close();

            return(result);
        }
Esempio n. 2
0
        /// <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, 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;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, paramList);
                foreach (SQLiteParameter parm in coll)
                {
                    cmd.Parameters.Add(parm);
                }
            }
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }
            int result = cmd.ExecuteNonQuery();

            cmd.Dispose();
            return(result);
        }
Esempio n. 3
0
        /// <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;
            SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, commandParameters);

            foreach (SQLiteParameter parm in coll)
            {
                cmd.Parameters.Add(parm);
            }
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }

            DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);

            return(ds);
        }
Esempio n. 4
0
        ///<overloads></overloads>
        /// <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)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            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);
        }
Esempio n. 5
0
        /// <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("Command must have live connection attached.", "cmd");
            }
            cmd.CommandText = commandText;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            return(rdr);
        }
Esempio n. 6
0
        public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            int result = cmd.ExecuteNonQuery();

            cmd.Dispose();
            cn.Close();

            return(result);
        }
Esempio n. 7
0
        /// <summary>
        /// Parses parameter names from SQL Statement, assigns values from object array , and returns fully populated ParameterCollection
        /// </summary>
        /// <param name="commandText">Sql Statement with SQL Server style "@param" style embedded parameters only [no OleDb style "?" placeholders]</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>SQLiteParameterCollection</returns>
        /// <remarks>Status 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>
        public static SQLiteParameterCollectionSub DeriveParameters(string commandText, object[] paramList)
        {
            if (paramList == null)
            {
                return(null);
            }
            //SQLiteParameterCollection coll = new SQLiteParameterCollection();
            SQLiteParameterCollectionSub coll = new SQLiteParameterCollectionSub();
            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;

            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("Invalid data type");


                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("Value is of unknown data type");
                } // end switch

                j++;
            }
            return(coll);
            //return null;
        }