public void PrepareInsertVersionCommand(IDbCommand command, DatabaseVersion version, 
            string failedUpgradeFromVersion, string failedUpgradeToVersion, string failedUpgradeError)
        {
            command.CommandText = "INSERT INTO \"Version\" (\"Product\", \"Version\", " +
                "\"FailedUpgradeFromVersion\", \"FailedUpgradeToVersion\", \"FailedUpgradeError\") " +
                "VALUES (:product, :version, :failedupgradefromversion, " +
                ":failedupgradetoversion, :failedupgradeerror)";

            IDbDataParameter productParameter = command.CreateParameter();
            IDbDataParameter versionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeFromVersionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeToVersionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeErrorParameter = command.CreateParameter();

            command.Parameters.Add(productParameter);
            command.Parameters.Add(versionParameter);
            command.Parameters.Add(failedUpgradeFromVersionParameter);
            command.Parameters.Add(failedUpgradeToVersionParameter);
            command.Parameters.Add(failedUpgradeErrorParameter);

            productParameter.ParameterName = "product";
            productParameter.DbType = DbType.String;
            productParameter.Size = 250;

            versionParameter.ParameterName = "version";
            versionParameter.DbType = DbType.String;
            versionParameter.Size = 250;

            failedUpgradeFromVersionParameter.ParameterName = "failedupgradefromversion";
            failedUpgradeFromVersionParameter.DbType = DbType.String;
            failedUpgradeFromVersionParameter.Size = 50;

            failedUpgradeToVersionParameter.ParameterName = "failedupgradetoversion";
            failedUpgradeToVersionParameter.DbType = DbType.String;
            failedUpgradeToVersionParameter.Size = 50;

            failedUpgradeErrorParameter.ParameterName = "failedupgradeerror";
            failedUpgradeErrorParameter.DbType = DbType.String;
            failedUpgradeErrorParameter.Size = 4000;

            productParameter.Value = version.Product;
            versionParameter.Value = version.Version;

            if (failedUpgradeFromVersion != null)
            {
                failedUpgradeFromVersionParameter.Value = failedUpgradeFromVersion;
                failedUpgradeToVersionParameter.Value = failedUpgradeToVersion;
                failedUpgradeErrorParameter.Value = failedUpgradeError;
            }
            else
            {
                failedUpgradeFromVersionParameter.Value = DBNull.Value;
                failedUpgradeToVersionParameter.Value = DBNull.Value;
                failedUpgradeErrorParameter.Value = DBNull.Value;
            }
        }
        public void PrepareInsertVersionCommand(IDbCommand command, DatabaseVersion version, 
            string failedUpgradeFromVersion, string failedUpgradeToVersion, string failedUpgradeError)
        {
            command.CommandText = "INSERT INTO Version (Product, Version, " +
                "FailedUpgradeFromVersion, FailedUpgradeToVersion, FailedUpgradeError) " +
                "VALUES (@Product, @Version, @FailedUpgradeFromVersion, " +
                "@FailedUpgradeToVersion, @FailedUpgradeError)";

            IDbDataParameter productParameter = command.CreateParameter();
            IDbDataParameter versionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeFromVersionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeToVersionParameter = command.CreateParameter();
            IDbDataParameter failedUpgradeErrorParameter = command.CreateParameter();

            command.Parameters.Add(productParameter);
            command.Parameters.Add(versionParameter);
            command.Parameters.Add(failedUpgradeFromVersionParameter);
            command.Parameters.Add(failedUpgradeToVersionParameter);
            command.Parameters.Add(failedUpgradeErrorParameter);

            productParameter.ParameterName = "@Product";
            productParameter.DbType = DbType.String;
            productParameter.Size = 250;

            productParameter.ParameterName = "@Version";
            versionParameter.DbType = DbType.String;
            versionParameter.Size = 250;

            failedUpgradeFromVersionParameter.ParameterName = "@FailedUpgradeFromVersion";
            failedUpgradeFromVersionParameter.DbType = DbType.String;
            failedUpgradeFromVersionParameter.Size = 50;

            failedUpgradeToVersionParameter.ParameterName = "@FailedUpgradeToVersion";
            failedUpgradeToVersionParameter.DbType = DbType.String;
            failedUpgradeToVersionParameter.Size = 50;

            failedUpgradeErrorParameter.ParameterName = "@FailedUpgradeError";
            failedUpgradeErrorParameter.DbType = DbType.String;
            failedUpgradeErrorParameter.Size = 4000;

            productParameter.Value = version.Product;
            versionParameter.Value = version.Version;

            if (failedUpgradeFromVersion != null)
            {
                failedUpgradeFromVersionParameter.Value = failedUpgradeFromVersion;
                failedUpgradeToVersionParameter.Value = failedUpgradeToVersion;
                failedUpgradeErrorParameter.Value = failedUpgradeError;
            }
            else
            {
                failedUpgradeFromVersionParameter.Value = DBNull.Value;
                failedUpgradeToVersionParameter.Value = DBNull.Value;
                failedUpgradeErrorParameter.Value = DBNull.Value;
            }
        }
示例#3
0
文件: DbEntry.cs 项目: dfr0/moon
		protected void AddParameter (IDbCommand cmd, string name, object v)
		{
			IDataParameter param = cmd.CreateParameter ();
			param.ParameterName = name;
			param.Value = v;
			cmd.Parameters.Add (param);
		}
 private void dbcommandparameter(IDbCommand dbcommand, string parameternombre)
 {
     IDbDataParameter dbDataParameter=dbcommand.CreateParameter();
     dbDataParameter.ParameterName ="nombre";
     dbDataParameter.Value=Value;
     dbcommand.ExecuteNonQuery();
 }
示例#5
0
 public static void AddParameter(IDbCommand dbCommand, string nombre, object value)
 {
     IDbDataParameter dbDataParameter=dbCommand.CreateParameter ();
     dbDataParameter.ParameterName = nombre;
     dbDataParameter.Value = value;
     dbCommand.Parameters.Add (dbDataParameter);
 }
 /// <summary>
 /// Adds the parameter the specifed command
 /// </summary>
 /// <param name="command">The command.</param>
 /// <param name="name">The name.</param>
 /// <param name="val">The val.</param>
 protected void AddParameter(IDbCommand command, string name, object val)
 {
     IDbDataParameter parameter = command.CreateParameter();
     parameter.ParameterName = paramPrefix + name;
     parameter.Value = val ?? DBNull.Value;
     command.Parameters.Add(parameter);
 }
示例#7
0
 private static void addParameter(IDbCommand dbcommand,string name, object value)
 {
     IDbDataParameter dbdataparameter = dbcommand.CreateParameter ();
     dbdataparameter.ParameterName = name;
     dbdataparameter.Value = value;
     dbcommand.Parameters.Add (dbdataparameter);
 }
 private static IDataParameter BuildIdParameter(IDbCommand command, int id)
 {
     var parameter = command.CreateParameter();
     parameter.ParameterName = FirstParameter;
     parameter.Value = id;
     return parameter;
 }
 protected virtual void AddParam(IDbCommand command, string name, object value)
 {
     var param = command.CreateParameter();
     param.ParameterName = name;
     param.Value = value;
     command.Parameters.Add(param);
 }
示例#10
0
		/// <summary>
		/// Preenche as propriedades e os parâmetros de um comando do ADO.NET, baseado
		/// nos metadados contidos na DataTable, para construir um comando SQL DELETE.
		/// </summary>
		/// <param name="dt">A DataTable que contém os metadados</param>
		/// <param name="paramReplacement">Substituidor do nome do parâmetro (usualmente <c>?</c>)</param>
		/// <param name="cmd">O IDbCommand</param>
		/// <returns>O mesmo IDbCommand recebido</returns>
		public static IDbCommand FillDeleteCommand(DataTable dt, string paramReplacement, IDbCommand cmd) 
		{
			if (dt.PrimaryKey.Length == 0)
				throw new InvalidOperationException();

			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("DELETE FROM {0} WHERE ", dt.TableName);

			foreach (DataColumn col in dt.PrimaryKey) 
			{
				IDbDataParameter p = cmd.CreateParameter();
				p.SourceColumn = col.ColumnName;
				p.DbType = DbType.String;
				p.Size = 255;

				sb.AppendFormat("[{0}] = @{0} AND ", col.ColumnName);
				cmd.Parameters.Add(p);
			}

			sb.Length -= (" AND ").Length;

			Debug.WriteLine(sb.ToString());
			cmd.CommandText = sb.ToString();

			return ExpandParameters(cmd, paramReplacement);
		}
 private static IDataParameter BuildNameParameter(IDbCommand command, string name)
 {
     var parameter = command.CreateParameter();
     parameter.ParameterName = SecondParameter;
     parameter.Value = name;
     return parameter;
 }
示例#12
0
        private static void AddParameter(IDbCommand cmd, object param)
        {
            IDbDataParameter p;
            p = param as IDbDataParameter;

            if (p != null)
            {
                cmd.Parameters.Add(p);
                return;
            }

            p = cmd.CreateParameter();

            p.ParameterName = cmd.Parameters.Count.ToString();
            if (param == null)
            {
                p.Value = DBNull.Value;
            }
            else
            {
                p.Value = param;

                // make strings a consistent size, helps with query plan caching.
                if (param.GetType() == typeof(string) && p.Size < 4000)
                {
                    p.Size = 4000;
                }
            }

            cmd.Parameters.Add(p);
        }
示例#13
0
 public IDbDataParameter CreateDbParameter(IDbCommand command, IDbAdapter dbAdapter)
 {
     var result = command.CreateParameter();
     result.ParameterName = dbAdapter.FormatParameterName(Name);
     result.Value = dbAdapter.ConvertToDbType(Value);
     return result;
 }
示例#14
0
        public static void BuildCommand(IDbCommand cmd, string sql, params object[] parameters)
        {
            if (cmd == null) throw new ArgumentNullException("cmd");
            if (String.IsNullOrEmpty(sql)) throw new ArgumentNullException("sql");

            if (parameters == null)
            {
                parameters = new object[0];
            }

            cmd.CommandText = new Regex(" *[\r\n]+ *").Replace(sql, " ");

            var parameterNames = FindUniqueParameters(sql);
            if (parameterNames.Count != parameters.Length)
            {
                throw new MicrOrmException(String.Format("Parameter count mismatch.  {0} in the SQL string, {1} supplied", parameterNames.Count, parameters.Length));
            }

            for (var i = 0; i < parameters.Length; i++)
            {
                if (!parameterNames.ContainsKey(i))
                {
                    throw new MicrOrmException(String.Format("Parameter ordinal mismatch.  Parameter with ordinal {0} is missing in the SQL string", i));
                }
                var dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = parameterNames[i];
                dbParameter.Value = parameters[i];
                cmd.Parameters.Add(dbParameter);
            }
        }
 private static void AddParameter(IDbCommand command, string name, object value)
 {
     var parameter = command.CreateParameter();
     parameter.ParameterName = name;
     parameter.Value = value;
     command.Parameters.Add(parameter);
 }
 void SqlMapper.ICustomQueryParameter.AddParameter(IDbCommand command, string name)
 {
     var param = command.CreateParameter();
     param.ParameterName = name;
     Set(param, table, typeName);
     command.Parameters.Add(param);
 }
 public override void Initialize(IDatabaseEngine engine, IDbCommand command)
 {
     string innerParameterName = engine.GetParameterName(ParameterName);
     sqlSnippet = sqlSnippet.Replace(REPLACEMENT_VALUE, innerParameterName);
     _innerParameter = command.CreateParameter();
     _innerParameter.ParameterName = innerParameterName;
 }
示例#18
0
 public static void AddParameter(IDbCommand cmd, string name, object value)
 {
     IDataParameter p = cmd.CreateParameter ();
     p.ParameterName = name;
     p.Value = value;
     cmd.Parameters.Add (p);
 }
示例#19
0
文件: AdoUtil.cs 项目: kisflying/kion
        public void AddCommandParameter(IDbCommand cmd, int parameterIndex, string paramName, object paramValue,
                                           Enum dataType)
        {
            IDbDataParameter param = cmd.CreateParameter();
            if (dataType != null)
            {
                SetDataTypeToCommandParameter(param, dataType);
            }
            param.ParameterName = dbProvider.Metadata.GetParameterName(paramName);
            if (paramValue != null)
            {
                param.Value = paramValue;
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            if (dbProvider.Metadata.ParameterNamePrefix != "@")
            {
                // we need to replace
                cmd.CommandText =
                    cmd.CommandText.Replace("@" + paramName, dbProvider.Metadata.ParameterNamePrefix + paramName);
            }
        }
示例#20
0
        private string PrepareInsertCommand(string sql, IDbCommand command, IEnumerable<Column> columns)
        {
            var columnLookup = columns.ToDictionary(c => c.QuotedName, c => c);
            if (columnLookup.Count == 0) return PrepareCommand(sql, command);

            int openParenIndex = sql.IndexOf('(');
            int closeParenLength = sql.IndexOf(')') - openParenIndex;
            var columnNameList = sql.Substring(openParenIndex, closeParenLength).Trim('(', ')').Split(',');
            int index = 0;
            var sqlBuilder = new StringBuilder();
            foreach (var c in sql)
            {
                if (c == '?')
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = _schemaProvider.NameParameter("p" + index);
                    var column = columnLookup[columnNameList[index]];
                    parameter.DbType = column.DbType;
                    parameter.Size = column.MaxLength;
                    command.Parameters.Add(parameter);
                    
                    sqlBuilder.Append(parameter.ParameterName);
                    index++;
                }
                else
                {
                    sqlBuilder.Append(c);
                }
            }
            return sqlBuilder.ToString();
        }
示例#21
0
        internal FileStore(FileInfo storeFile)
        {
            StoreFile = storeFile;
              connection = Sqlite.GetDatabaseConnection(storeFile);

              using (var transaction = connection.BeginTransaction()) {
            using (var pragma = connection.CreateCommand()) {
              pragma.CommandText = "PRAGMA journal_mode = MEMORY";
              pragma.ExecuteNonQuery();
              pragma.CommandText = "PRAGMA temp_store = MEMORY";
              pragma.ExecuteNonQuery();
              pragma.CommandText = "PRAGMA synchonous = OFF";
              pragma.ExecuteNonQuery();
            }
            using (var create = connection.CreateCommand()) {
              create.CommandText = "CREATE TABLE IF NOT EXISTS store (key TEXT PRIMARY KEY ON CONFLICT REPLACE, size INT, time INT, data BINARY, cover BINARY)";
              create.ExecuteNonQuery();
            }
            transaction.Commit();
              }

              select = connection.CreateCommand();
              select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?";
              select.Parameters.Add(selectKey = select.CreateParameter());
              selectKey.DbType = DbType.String;
              select.Parameters.Add(selectSize = select.CreateParameter());
              selectSize.DbType = DbType.Int64;
              select.Parameters.Add(selectTime = select.CreateParameter());
              selectTime.DbType = DbType.Int64;

              selectCover = connection.CreateCommand();
              selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?";
              selectCover.Parameters.Add(selectCoverKey = select.CreateParameter());
              selectCoverKey.DbType = DbType.String;
              selectCover.Parameters.Add(selectCoverSize = select.CreateParameter());
              selectCoverSize.DbType = DbType.Int64;
              selectCover.Parameters.Add(selectCoverTime = select.CreateParameter());
              selectCoverTime.DbType = DbType.Int64;

              insert = connection.CreateCommand();
              insert.CommandText = "INSERT OR REPLACE INTO store VALUES(?,?,?,?,?)";
              insert.Parameters.Add(insertKey = select.CreateParameter());
              insertKey.DbType = DbType.String;
              insert.Parameters.Add(insertSize = select.CreateParameter());
              insertSize.DbType = DbType.Int64;
              insert.Parameters.Add(insertTime = select.CreateParameter());
              insertTime.DbType = DbType.Int64;
              insert.Parameters.Add(insertData = select.CreateParameter());
              insertData.DbType = DbType.Binary;
              insert.Parameters.Add(insertCover = select.CreateParameter());
              insertCover.DbType = DbType.Binary;

              InfoFormat("FileStore at {0} is ready", storeFile.FullName);

              vacuumer.Add(connection);
        }
示例#22
0
 protected void AddInput(IDbCommand dbCommand, string name, object value)
 {
     IDbDataParameter dbParameter = dbCommand.CreateParameter();
     dbParameter.Direction = ParameterDirection.Input;
     dbParameter.ParameterName = string.Concat(ParameterPrefix, name);
     dbParameter.Value = (value ?? DBNull.Value);
     dbCommand.Parameters.Add(dbParameter);
 }
示例#23
0
 public IDbDataParameter CreateDbCommandParameter(IDbCommand command, string parameterName, DbType type, object value)
 {
     IDbDataParameter param = command.CreateParameter();
     param.ParameterName = parameterName;
     param.DbType = type;
     param.Value = value;
     return param;
 }
示例#24
0
文件: base.cs 项目: mono/monomeeting
 static IDbDataParameter CreateParameter(IDbCommand cmd, string name, string val)
 {
     IDbDataParameter par = cmd.CreateParameter ();
     par.ParameterName = name;
     par.Value = val;
     par.DbType = DbType.String;
     return par;
 }
示例#25
0
        private void dbCommandAddParameter(IDbCommand dbCommand, 
		                                   string parameterName, object value)
        {
            IDbDataParameter dbDataParameter=dbCommand.CreateParameter();
            dbDataParameter.ParameterName = parameterName;
            dbDataParameter.Value=value;
            dbCommand.Parameters.Add(dbDataParameter);
        }
示例#26
0
        internal IDataParameter ToDataParameter(IDbCommand command)
        {
            var param = command.CreateParameter();
            param.ParameterName = Name;
            param.Value = Value;

            return param;
        }
示例#27
0
 public static void AddParameter(IDbCommand cmd, string name, byte[] value)
 {
     IDbDataParameter prm = cmd.CreateParameter();
     prm.ParameterName = name;
     prm.Value = value;
     prm.DbType = DbType.Binary;
     cmd.Parameters.Add(prm);
 }
		internal DataParameterCollection(IDbCommand command)
		{
			_command = command;
			_parameters = command.Parameters;	
			_parameters.Clear();
			_parameterDbType = command.CreateParameter().GetType();	
			
		}
示例#29
0
 static IDataParameter AddParameter(IDbCommand cmd, string name, object val)
 {
     IDataParameter p = cmd.CreateParameter();
     p.ParameterName = name;
     p.Value = val;
     cmd.Parameters.Add(p);
     return p;
 }
 private static IDbDataParameter CreateIdentityOutputParameter(IDbCommand command)
 {
     var newParameter = command.CreateParameter();
     newParameter.ParameterName = _newIdentityParameterName;
     newParameter.DbType = DbType.Int64;
     newParameter.Direction = ParameterDirection.Output;
     return newParameter;
 }
示例#31
0
        /// <summary>
        /// Get the schedule details for a given schedule id from the database
        /// </summary>
        /// <parameter name="ScheduleId"></parameter>
        /// <returns>Returns the schedule information<Schedule></returns>
        public Model.Entities.AirTravel.Schedule GetSchedule(long scheduleId)
        {
            Schedule      schedule = null;
            IDbConnection conn     = null;

            try
            {
                conn = this.GetConnection();
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "getScheduleDetails_BasedID";
                cmd.CommandType = CommandType.StoredProcedure;

                IDataParameter p1 = cmd.CreateParameter();
                p1.ParameterName = "@scheduleid";
                p1.Value         = scheduleId;
                cmd.Parameters.Add(p1);

                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        schedule = new Schedule();

                        schedule.ID = long.Parse(reader["ScheduleId"].ToString());

                        Airline airline = new Airline();
                        airline.Id   = int.Parse(reader["AirlineId"].ToString());
                        airline.Name = reader["AirlineName"].ToString();

                        Flight flight = new Flight();
                        flight.ID = long.Parse(reader["FlightId"].ToString());
                        flight.AirlineForFlight = airline;
                        flight.Name             = reader["FlightName"].ToString();

                        schedule.FlightInfo = flight;

                        City fromcity = new City();
                        fromcity.CityId = long.Parse(reader["FromId"].ToString());
                        fromcity.Name   = reader["FromCity"].ToString();

                        City tocity = new City();
                        tocity.CityId = long.Parse(reader["ToId"].ToString());
                        tocity.Name   = reader["ToCity"].ToString();

                        Route route = new Route();
                        route.ID            = long.Parse(reader["RouteId"].ToString());
                        route.FromCity      = fromcity;
                        route.ToCity        = tocity;
                        route.DistanceInKms = long.Parse(reader["DistanceInKms"].ToString());
                        schedule.RouteInfo  = route;

                        DateTime dtime = Convert.ToDateTime(reader["DepartureTime"]);
                        TimeSpan sp1   = new TimeSpan(dtime.Hour, dtime.Minute, dtime.Second);

                        DateTime atime = Convert.ToDateTime(reader["ArrivalTime"]);
                        TimeSpan sp2   = new TimeSpan(atime.Hour, atime.Minute, atime.Second);

                        schedule.DepartureTime = sp1;
                        schedule.ArrivalTime   = sp2;

                        schedule.DurationInMins = int.Parse(reader["DurationInMins"].ToString());
                        schedule.IsActive       = bool.Parse(reader["Status"].ToString());

                        //Method call to get flight costs
                        List <FlightCost> flightCosts = GetFlightCostsForSchedule(schedule.ID);

                        foreach (FlightCost cost in flightCosts)
                        {
                            schedule.AddFlightCost(cost);
                        }
                    }
                }
            }
            catch (Common.ConnectToDatabaseException)
            {
                throw new ScheduleDAOException("Unable to get the schedule details");
            }
            catch (ScheduleDAOException ex)
            {
                throw ex;
            }
            catch (Exception)
            {
                throw new ScheduleDAOException("Unable to get the schedule details");
            }
            return(schedule);
        }
示例#32
0
        /// <summary>
        /// locates an Book record by its primary id
        /// </summary>
        /// <param name="BookID">the primary key to the record to return</param>
        /// <returns>an Book record</returns>
        public Book BookFindByID(int BookID)
        {
            Book rv = null;

            // a default return value
            try
            {
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // configure the command object
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "BookFindByID";
                    IDbDataParameter p = command.CreateParameter();
                    p.ParameterName = "@BookID";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = BookID;
                    command.Parameters.Add(p);

                    // load the data from the database
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        // the mapper is constructed, and this is where the shape
                        // is validated to insure it is correct
                        // if the database structure changes,
                        // an exception will be thrown
                        // the less efficient Get Ordinal methods are only
                        // invoked one time per command
                        // this less efficient (but required) code
                        // is outside the loop
                        BookMapper mapper = new BookMapper(reader);
                        int        count  = 0;
                        while (reader.Read())
                        {
                            rv = mapper.ToBook(reader);
                            // the mapper uses the much more efficient getXXX
                            // methods internally to avoid boxing and
                            // string manipulation.  this more efficient code is
                            // inside the loop
                            count++;
                        }
                        // this method is expecting a single record to be returned
                        // check to see if more than one record was returned
                        // this probably means that a where clause is incorrect in the SQL layer
                        if (count > 1)
                        {
                            throw new Exception("Multiple reccords found with id: {BookID}");
                        }
                    }
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
                // ALL exceptions are logged by the when clause
                // and then the exception is tossed to the next layer up.
                // the catch block is NEVER invoked because the
                // when clause never evaluates to true since the
                // Log method returns false
            }
            return(rv);
        }
示例#33
0
        public override ClaveMes ReadEntityBy2Params(int mes, int anno)
        {
            using (IDbConnection connection = GetConnection())
            {
                IDbCommand command = connection.CreateCommand();
                command.Connection  = connection;
                command.CommandText = this.CommandText;
                command.CommandType = this.CommandType;

                foreach (IDataParameter param in this.GetParameters(command))
                {
                    command.Parameters.Add(param);
                }

                // Creando Parametro para filtrar por Mes y Anno
                IDataParameter param1 = command.CreateParameter();
                param1.ParameterName = Constants.FilterOneParam;
                param1.DbType        = DbType.Int32;
                param1.Value         = mes;
                command.Parameters.Add(param1);

                param1 = command.CreateParameter();
                param1.ParameterName = Constants.FilterTwoParam;
                param1.DbType        = DbType.Int32;
                param1.Value         = anno;

                command.Parameters.Add(param1);

                ClaveMes ent = null;

                try
                {
                    connection.Open();

                    using (IDataReader reader = command.ExecuteReader())
                    {
                        try
                        {
                            MapperBase <ClaveMes> mapper = GetMapper();
                            ent = mapper.MapEntity(reader);
                            return(ent);
                        }
                        catch
                        {
                            throw;
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
示例#34
0
        /// <summary>Saves an object.</summary>
        /// <param name="obj">Object.</param>
        /// <param name="ent">Entity.</param>
        /// <param name="hasMaterialBase">Determines if the base class table is material.</param>
        /// <param name="isBase">Determines if the the object is the base class table.</param>
        private static void _Save(object obj, __Entity ent, bool hasMaterialBase, bool isBase)
        {
            if (Cache != null)
            {
                if (!Cache.HasChanged(obj))
                {
                    return;
                }
            }

            IDbCommand cmd    = Connection.CreateCommand();
            string     update = "";
            string     insert = "";

            cmd.CommandText = ("INSERT INTO " + ent.TableName + " (");
            if (hasMaterialBase)
            {
                cmd.CommandText += ent.ChildKey + ", ";
                update           = "ON CONFLICT (" + ent.ChildKey + ") DO UPDATE SET ";
                insert           = (":ck, ");

                IDataParameter k = cmd.CreateParameter();
                k.ParameterName = ":ck";
                k.Value         = ent.PrimaryKey.GetValue(obj);
                cmd.Parameters.Add(k);
            }
            else
            {
                update = "ON CONFLICT (" + ent.PrimaryKey.ColumnName + ") DO UPDATE SET ";
            }


            IDataParameter p;
            bool           first = true;

            for (int i = 0; i < ent.LocalInternals.Length; i++)
            {
                if (i > 0)
                {
                    cmd.CommandText += ", "; insert += ", ";
                }
                cmd.CommandText += ent.LocalInternals[i].ColumnName;

                insert += (":v" + i.ToString());

                p = cmd.CreateParameter();
                p.ParameterName = (":v" + i.ToString());
                p.Value         = ent.LocalInternals[i].ToColumnType(ent.LocalInternals[i].GetValue(obj));
                cmd.Parameters.Add(p);

                if (!ent.LocalInternals[i].IsPrimaryKey)
                {
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        update += ", ";
                    }
                    update += (ent.LocalInternals[i].ColumnName + " = " + (":w" + i.ToString()));

                    p = cmd.CreateParameter();
                    p.ParameterName = (":w" + i.ToString());
                    p.Value         = ent.LocalInternals[i].ToColumnType(ent.LocalInternals[i].GetValue(obj));
                    cmd.Parameters.Add(p);
                }
            }
            cmd.CommandText += (") VALUES (" + insert + ") " + update);

            cmd.ExecuteNonQuery();
            cmd.Dispose();

            if (!isBase)
            {
                foreach (__Field i in ent.Externals)
                {
                    i.UpdateReferences(obj);
                }
            }

            if (Cache != null)
            {
                Cache.Put(obj);
            }
        }
示例#35
0
 /// <summary>
 /// Creates and adds a new SQL parameter to the command.
 /// </summary>
 /// <typeparam name="T">The type of the parameter.</typeparam>
 /// <param name="command">The command to use to create the parameter.</param>
 /// <param name="direction">The direction of the parameter.</param>
 /// <param name="parameterName">The name of the parameter.</param>
 /// <param name="value">The value of the parameter.</param>
 /// <param name="size">The size of the parameter value.</param>
 /// <param name="dbType">the <see cref="DbType"/> of the parameter.</param>
 /// <returns>A parameter created using the given arguments.</returns>
 public static void AddParameter <T>(this IDbCommand command, string parameterName, T value, ParameterDirection direction = ParameterDirection.Input, int?size = null, DbType?dbType = null)
 {
     command.Parameters.Add(command.CreateParameter(direction, parameterName, value, size));
 }
示例#36
0
        public static bool SaveSBtoADPropertyMapping(Dictionary <string, string> i_SBtoADPropertyMapping)
        {
            bool bSuccess = false;

            try
            {
                using (IDbConnection sqlConn = GetDbConnection())
                {
                    sqlConn.Open();


                    // Because of the uniqueness constraint put on the columns in tblSBtoADPropertyMapping we have to delete all the entries before we can "update" them.

                    using (IDbCommand sqlCmd = sqlConn.CreateCommand())
                    {
                        sqlCmd.CommandText = "DELETE FROM tblSBtoADPropertyMapping";

                        sqlCmd.ExecuteNonQuery();
                    }

                    using (IDbCommand sqlCmd = sqlConn.CreateCommand())
                    {
                        sqlCmd.CommandText = "INSERT INTO tblSBtoADPropertyMapping (iSBPropertyId, iADPropertyId) VALUES ((SELECT iSBPropertyId FROM tblSBDirProperties WHERE sSBPropertyName = @SBProperty), (SELECT iADPropertyId FROM tblADDirProperties WHERE sADPropertyName = @ADProperty))";

                        IDbDataParameter sqlParamSBProperty;
                        IDbDataParameter sqlParamADProperty;

                        sqlParamSBProperty               = sqlCmd.CreateParameter();
                        sqlParamSBProperty.DbType        = DbType.String;
                        sqlParamSBProperty.ParameterName = "@SBProperty";
                        sqlCmd.Parameters.Add(sqlParamSBProperty);

                        sqlParamADProperty               = sqlCmd.CreateParameter();
                        sqlParamADProperty.DbType        = DbType.String;
                        sqlParamADProperty.ParameterName = "@ADProperty";
                        sqlCmd.Parameters.Add(sqlParamADProperty);

                        foreach (string sSBProperty in i_SBtoADPropertyMapping.Keys)
                        {
                            string sADProperty = i_SBtoADPropertyMapping[sSBProperty];

                            sqlParamSBProperty.Value = sSBProperty;
                            sqlParamADProperty.Value = sADProperty;

                            sqlCmd.ExecuteNonQuery();
                        }
                    }

                    sqlConn.Close();

                    bSuccess = true;
                }
            }
            catch (Exception exc)
            {
                bSuccess = false;
                Console.Error.WriteLine("{0} SBConfigStore.LdapDAL.SaveSBtoADPropertyMapping exception: {1}", DateTime.Now, exc.ToString());
            }

            return(bSuccess);
        }
示例#37
0
        // ----------------------------------------------------------------------------

        private void CreateSP_Getcustomerorderheadinfo()
        {
            IDbDataParameter p;
            OracleParameter  oP;

            sp_Getcustomerorderheadinfo             = connectionProvider.GetConnection().CreateCommand();
            sp_Getcustomerorderheadinfo.CommandText = "WEB_SERVICES_QUERY.GETCUSTOMERORDERHEADINFO";
            sp_Getcustomerorderheadinfo.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_Getcustomerorderheadinfo).BindByName = true;
#endif

            p = sp_Getcustomerorderheadinfo.CreateParameter();
            p.ParameterName = "CLIENTIDENTITY_I";
            p.DbType        = DbType.String;
            p.Size          = 17;
            p.Direction     = ParameterDirection.Input;
            sp_Getcustomerorderheadinfo.Parameters.Add(p);

            p = sp_Getcustomerorderheadinfo.CreateParameter();
            p.ParameterName = "ORDERNUMBER_I";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Input;
            sp_Getcustomerorderheadinfo.Parameters.Add(p);

            p = sp_Getcustomerorderheadinfo.CreateParameter();
            p.ParameterName = "ORDERSEQUENCE_I";
            p.DbType        = DbType.Decimal;
            p.Precision     = 3;
            p.Scale         = 0;
            p.Direction     = ParameterDirection.Input;
            sp_Getcustomerorderheadinfo.Parameters.Add(p);

            p = sp_Getcustomerorderheadinfo.CreateParameter();
            p.ParameterName = "LANGUAGEID_I";
            p.DbType        = DbType.String;
            p.Size          = 3;
            p.Direction     = ParameterDirection.Input;
            sp_Getcustomerorderheadinfo.Parameters.Add(p);

            oP = sp_Getcustomerorderheadinfo.CreateParameter() as OracleParameter;
            oP.ParameterName = "CUSTOMERORDER_CUR_O";
#if ODP_NET
            oP.OracleDbType = OracleDbType.RefCursor;
#else
            oP.OracleType = OracleType.Cursor;
#endif
            oP.Direction = ParameterDirection.Output;
            sp_Getcustomerorderheadinfo.Parameters.Add(oP);

            oP = sp_Getcustomerorderheadinfo.CreateParameter() as OracleParameter;
            oP.ParameterName = "CUSTOMERWORKORDER_CUR_O";
#if ODP_NET
            oP.OracleDbType = OracleDbType.RefCursor;
#else
            oP.OracleType = OracleType.Cursor;
#endif
            oP.Direction = ParameterDirection.Output;
            sp_Getcustomerorderheadinfo.Parameters.Add(oP);

            oP = sp_Getcustomerorderheadinfo.CreateParameter() as OracleParameter;
            oP.ParameterName = "DEPARTURE_CUR_O";
#if ODP_NET
            oP.OracleDbType = OracleDbType.RefCursor;
#else
            oP.OracleType = OracleType.Cursor;
#endif
            oP.Direction = ParameterDirection.Output;
            sp_Getcustomerorderheadinfo.Parameters.Add(oP);

            oP = sp_Getcustomerorderheadinfo.CreateParameter() as OracleParameter;
            oP.ParameterName = "ORDERQUANTITIES_CUR_O";
#if ODP_NET
            oP.OracleDbType = OracleDbType.RefCursor;
#else
            oP.OracleType = OracleType.Cursor;
#endif
            oP.Direction = ParameterDirection.Output;
            sp_Getcustomerorderheadinfo.Parameters.Add(oP);
        }
示例#38
0
        internal static IDbCommand SetParameters(this IDbCommand dbCmd, Type type, object anonType, bool excludeDefaults)
        {
            if (anonType == null)
            {
                return(dbCmd);
            }

            dbCmd.Parameters.Clear();
            lastQueryType = null;

            var modelDef        = type.GetModelDefinition();
            var dialectProvider = dbCmd.GetDialectProvider();
            var fieldMap        = type.IsUserType() //Ensure T != Scalar<int>()
                ? dialectProvider.GetFieldDefinitionMap(modelDef)
                : null;

            anonType.ToObjectDictionary().ForEachParam(modelDef, excludeDefaults, (propName, columnName, value) =>
            {
                var propType = value != null ? value.GetType() : typeof(object);
                var inValues = value as SqlInValues;
                if (inValues != null)
                {
                    var i = 0;
                    foreach (var item in inValues.GetValues())
                    {
                        var p           = dbCmd.CreateParameter();
                        p.ParameterName = "v" + i++;
                        p.Direction     = ParameterDirection.Input;
                        dialectProvider.InitDbParam(p, item.GetType());

                        var pValue    = dialectProvider.GetFieldValue(item.GetType(), item);
                        var valueType = pValue != null ? pValue.GetType() : null;
                        if (valueType != null && valueType != propType)
                        {
                            dialectProvider.InitDbParam(p, valueType);
                        }

                        p.Value = pValue == null ?
                                  DBNull.Value
                          : p.DbType == DbType.String ?
                                  pValue.ToString() :
                                  pValue;

                        dbCmd.Parameters.Add(p);
                    }
                }
                else
                {
                    var p           = dbCmd.CreateParameter();
                    p.ParameterName = propName;
                    p.Direction     = ParameterDirection.Input;
                    dialectProvider.InitDbParam(p, propType);

                    FieldDefinition fieldDef;
                    if (fieldMap != null && fieldMap.TryGetValue(columnName, out fieldDef))
                    {
                        value         = dialectProvider.GetFieldValue(fieldDef, value);
                        var valueType = value != null ? value.GetType() : null;
                        if (valueType != null && valueType != propType)
                        {
                            dialectProvider.InitDbParam(p, valueType);
                        }
                    }
                    else
                    {
                        value         = dialectProvider.GetFieldValue(propType, value);
                        var valueType = value != null ? value.GetType() : null;
                        if (valueType != null && valueType != propType)
                        {
                            dialectProvider.InitDbParam(p, valueType);
                        }
                    }

                    p.Value = value == null ?
                              DBNull.Value
                      : p.DbType == DbType.String ?
                              value.ToString() :
                              value;

                    dbCmd.Parameters.Add(p);
                }
            });

            return(dbCmd);
        }
示例#39
0
 public IList <IDbDataParameter> Prepare(IDbCommand adoCommand)
 => this.Prepare(() => adoCommand.CreateParameter());
示例#40
0
        // BookGetSortedAndFiltered is used by advanced ajax portion of the
        // datatables example.  It is used to get the 'filtered' data for displaying
        // on the datatables form
        public List <Book> BookGetSortedAndFiltered(string sortColumn, string sortColumnDir, string searchValue, int skip, int take)
        {
            List <Book> rv = new List <Book>();

            try
            {
                string direction;
                string orderby;
                string where;
                bool wherethere = false;
                // check the sort column to verify it is a valid column
                // this is to avoid SQL injection
                switch (sortColumn.ToLower())
                {
                case ("bookid"): orderby = "bookid"; break;

                case ("isbn"):    orderby = "ISBN"; break;

                case ("bookname"): orderby = "bookname"; break;

                case ("pages"):     orderby = "pages"; break;

                case ("daysoverdue"): orderby = "daysoverdue"; break;

                case ("genrename"): orderby = "genrename"; break;

                default: orderby = "bookname"; break;
                }

                if (string.IsNullOrWhiteSpace(searchValue))
                {
                    where      = "";
                    wherethere = false;
                }
                else
                {
                    // where clause is not subject to SQL injection because the
                    // columns are compared to an @variable
                    // which will be loaded if wherethere is true
                    // wheredata is loaded from searchvalue
                    where      = $" where ISBN like @wheredata or Bookname like @wheredata or GenreName like @wheredata ";
                    wherethere = true;
                }
                // check direction to make sure it is valid
                // this is to avoid sql injection
                switch (sortColumnDir.ToLower())
                {
                case ("asc"): direction = " ASC"; break;

                default: direction = " DESC"; break;
                }
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // use text to create dynamic query
                    command.CommandType = CommandType.Text;
                    command.CommandText = "select " + "bookid,isbn,bookname,pages,price,books.genreid," +
                                          // from Books
                                          "genrename," +
                                          // from genres
                                          "checkouts, daysoverdue, avgrating from books " +
                                          //from inner query
                                          //first left outer join bringing over the genrename or NULL if no match
                                          "left outer join genres " +
                                          "on books.genreid = genres.genreid " +
                                          //-- second left outer join bringing over the checkout data etc.

                                          " left outer join " +
//-- inner query follows
                                          "(select bookid as bkid, " +
                                          " count(*) as checkouts," +
                                          "  sum(DATEDIFF(day, DueDate, GetDate())) as daysoverdue, " +
                                          "  avg(Rating) AvgRating " +

                                          "  from TypeDRatedBorrowings " +
                                          " where ReturnedDate is null " +
                                          "  group by bookid) as x " +
                                          //--end of inner query(still need the on clause for the previous left outer join)
                                          "  on bkid = bookid " +
                                          //  -- the first two joins are now joined to the third one also on the bookid
                                          // this is the where clause, it may not be present
                                          // and then the parameter will not be added either
                                          where +
                                          " order by " + orderby + direction +
                                          " offset @Skip rows fetch next @Take rows only";

                    IDbDataParameter p = command.CreateParameter();
                    p.ParameterName = "@Skip";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = skip;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@Take";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = take;
                    command.Parameters.Add(p);
                    if (wherethere)
                    {
                        p = command.CreateParameter();
                        p.ParameterName = "@wheredata";
                        p.DbType        = DbType.String;
                        p.Direction     = ParameterDirection.Input;
                        p.Value         = searchValue;
                        command.Parameters.Add(p);
                    }
                    // load the data from the database
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        // the mapper is constructed, and this is where the shape
                        // is validated to insure it is correct
                        // if the database structure changes,
                        // an exception will be thrown
                        // the less efficient Get Ordinal methods are only
                        // invoked one time per command
                        // this less efficient (but required) code
                        // is outside the loop
                        BookMapper mapper = new BookMapper(reader);

                        while (reader.Read())
                        {
                            Book a = mapper.ToBook(reader);
                            // the mapper uses the much more efficient getXXX
                            // methods internally to avoid boxing and
                            // string manipulation.  this more efficient code is
                            // inside the loop
                            if (a != null)
                            // if the mapper returns null for some reason it will
                            // be ignored
                            {
                                rv.Add(a);
                            }
                        }
                        // when the flow gets here, all the records have been processed
                        // this time there is not any post processing that needs to happen
                    }
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
            }
            return(rv);
        }
示例#41
0
        public IDbDataParameter CreateDataParameter(IDbCommand cmd, DbParam param)
        {
            DbType jsonDbType = (DbType)100;

            IDbDataParameter parameter = cmd.CreateParameter();

            parameter.ParameterName = param.Name;

            if (param.Value == null || param.Value == DBNull.Value)
            {
                parameter.Value = DBNull.Value;
            }
            else
            {
                parameter.Value = param.Value;
            }

            if (parameter is NpgsqlParameter)
            {
                NpgsqlParameter pgsqlParameter = (NpgsqlParameter)parameter;

                if (param.DbType == jsonDbType)
                {
                    parameter.DbType            = DbType.String;
                    pgsqlParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
                }
                else if (param.DbType != null)
                {
                    parameter.DbType = param.DbType.Value;
                }
            }
            else if (param.DbType != null)
            {
                parameter.DbType = param.DbType.Value;
            }

            if (param.Precision != null)
            {
                parameter.Precision = param.Precision.Value;
            }

            if (param.Scale != null)
            {
                parameter.Scale = param.Scale.Value;
            }

            if (param.Size != null)
            {
                parameter.Size = param.Size.Value;
            }



            const int defaultSizeOfStringOutputParameter = 4000;/* 当一个 string 类型输出参数未显示指定 Size 时使用的默认大小。如果有需要更大或者该值不足以满足需求,需显示指定 DbParam.Size 值 */

            if (param.Direction == ParamDirection.Input)
            {
                parameter.Direction = ParameterDirection.Input;
            }
            else if (param.Direction == ParamDirection.Output)
            {
                parameter.Direction = ParameterDirection.Output;
                param.Value         = null;
                if (param.Size == null && param.Type == typeof(string))
                {
                    parameter.Size = defaultSizeOfStringOutputParameter;
                }
            }
            else if (param.Direction == ParamDirection.InputOutput)
            {
                parameter.Direction = ParameterDirection.InputOutput;
                if (param.Size == null && param.Type == typeof(string))
                {
                    parameter.Size = defaultSizeOfStringOutputParameter;
                }
            }
            else
            {
                throw new NotSupportedException(string.Format("ParamDirection '{0}' is not supported.", param.Direction));
            }

            return(parameter);
        }
示例#42
0
        /// <summary>
        /// returns a list of Books, optionally skipping several for paging
        /// purposes and only returning the amount in the page
        /// </summary>
        /// <param name="Skip">the number of records to ignore</param>
        /// <param name="Take">the number of records to return</param>
        /// <returns></returns>
        public List <Book> BooksGetAll(int Skip = 0, int Take = 0)
        {
            List <Book> rv = new List <Book>();

            // a default return value is an empty list
            try
            {
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // configure the command object
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "BooksGetAll";

                    IDbDataParameter p = command.CreateParameter();
                    p.ParameterName = "@Skip";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = Skip;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@Take";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = Take;
                    command.Parameters.Add(p);
                    // load the data from the database
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        // the mapper is constructed, and this is where the shape
                        // is validated to insure it is correct
                        // if the database structure changes,
                        // an exception will be thrown
                        // the less efficient Get Ordinal methods are only
                        // invoked one time per command
                        // this less efficient (but required) code
                        // is outside the loop
                        BookMapper mapper = new BookMapper(reader);

                        while (reader.Read())
                        {
                            Book a = mapper.ToBook(reader);
                            // the mapper uses the much more efficient getXXX
                            // methods internally to avoid boxing and
                            // string manipulation.  this more efficient code is
                            // inside the loop
                            if (a != null)
                            // if the mapper returns null for some reason it will
                            // be ignored
                            {
                                rv.Add(a);
                            }
                        }
                        // when the flow gets here, all the records have been processed
                        // this time there is not any post processing that needs to happen
                    }
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
                // ALL exceptions are logged by the when clause
                // and then the exception is tossed to the next layer up.
                // the catch block is NEVER invoked because the
                // when clause never evaluates to true since the
                // Log method returns false
            }
            return(rv);
        }
        /// <summary>
        /// Add all the parameters needed to the command just before it executes
        /// </summary>
        /// <param name="command">The raw command prior to execution</param>
        /// <param name="identity">Information about the query</param>
        /// <param name="isForever"></param>
        protected void AddParameters(IDbCommand command, SqlMapper.Identity identity, bool isForever)
        {
            var literals = SqlMapper.GetLiteralTokens(identity.sql);

            if (templates != null)
            {
                foreach (var template in templates)
                {
                    var newIdent = identity.ForDynamicParameters(template.GetType());
                    Action <IDbCommand, object> appender;

                    lock (paramReaderCache)
                    {
                        if (!paramReaderCache.TryGetValue(newIdent, out appender))
                        {
                            appender = SqlMapper.CreateParamInfoGenerator(newIdent, true, RemoveUnused, literals);
                            paramReaderCache[newIdent] = appender;
                        }
                    }

                    appender(command, template);
                }

                // The parameters were added to the command, but not the
                // DynamicParameters until now.
                foreach (IDbDataParameter param in command.Parameters)
                {
                    // If someone makes a DynamicParameters with a template,
                    // then explicitly adds a parameter of a matching name,
                    // it will already exist in 'parameters'.
                    if (!parameters.ContainsKey(param.ParameterName))
                    {
                        parameters.Add(param.ParameterName, new ParamInfo
                        {
                            AttachedParam      = param,
                            CameFromTemplate   = true,
                            DbType             = param.DbType,
                            Name               = param.ParameterName,
                            ParameterDirection = param.Direction,
                            Size               = param.Size,
                            Value              = param.Value,
                            IsForever          = isForever
                        });
                    }
                }

                // Now that the parameters are added to the command, let's place our output callbacks
                var tmp = outputCallbacks;
                if (tmp != null)
                {
                    foreach (var generator in tmp)
                    {
                        generator();
                    }
                }
            }

            foreach (var param in parameters.Values)
            {
                if (param.CameFromTemplate)
                {
                    continue;
                }

                var    dbType = param.DbType;
                var    val    = param.Value;
                string name   = Clean(param.Name);
                var    isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter;

                SqlMapper.ITypeHandler handler = null;
                if (dbType == null && val != null && !isCustomQueryParameter)
                {
#pragma warning disable 618
                    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);
#pragma warning disable 618
                }
                if (isCustomQueryParameter)
                {
                    ((SqlMapper.ICustomQueryParameter)val).AddParameter(command, name);
                }
                else if (dbType == EnumerableMultiParameter)
                {
#pragma warning disable 612, 618
                    SqlMapper.PackListParameters(command, name, val);
#pragma warning restore 612, 618
                }
                else
                {
                    bool             add = !command.Parameters.Contains(name);
                    IDbDataParameter p;
                    if (add)
                    {
                        p = command.CreateParameter();
                        p.ParameterName = name;
                    }
                    else
                    {
                        p = (IDbDataParameter)command.Parameters[name];
                    }

                    p.Direction = param.ParameterDirection;
                    if (handler == null)
                    {
#pragma warning disable 0618
                        p.Value = SqlMapper.SanitizeParameterValue(val);
#pragma warning restore 0618
                        if (dbType != null && p.DbType != dbType)
                        {
                            p.DbType = dbType.Value;
                        }
                        var s = val as string;
                        if (s?.Length <= DbString.DefaultLength)
                        {
                            p.Size = DbString.DefaultLength;
                        }
                        if (param.Size != null)
                        {
                            p.Size = param.Size.Value;
                        }
                        if (param.Precision != null)
                        {
                            p.Precision = param.Precision.Value;
                        }
                        if (param.Scale != null)
                        {
                            p.Scale = param.Scale.Value;
                        }
                    }
                    else
                    {
                        if (dbType != null)
                        {
                            p.DbType = dbType.Value;
                        }
                        if (param.Size != null)
                        {
                            p.Size = param.Size.Value;
                        }
                        if (param.Precision != null)
                        {
                            p.Precision = param.Precision.Value;
                        }
                        if (param.Scale != null)
                        {
                            p.Scale = param.Scale.Value;
                        }
                        handler.SetValue(p, val ?? DBNull.Value);
                    }

                    if (add)
                    {
                        command.Parameters.Add(p);
                    }
                    param.AttachedParam = p;
                }
            }

            // note: most non-priveleged implementations would use: this.ReplaceLiterals(command);
            if (literals.Count != 0)
            {
                SqlMapper.ReplaceLiterals(this, command, literals);
            }
        }
示例#44
0
        internal static IDbCommand SetParameters(this IDbCommand dbCmd, Dictionary <string, object> dict, bool excludeDefaults, ref string sql)
        {
            if (dict == null)
            {
                return(dbCmd);
            }

            dbCmd.Parameters.Clear();
            var dialectProvider = dbCmd.GetDialectProvider();

            var paramIndex = 0;
            var sqlCopy    = sql; //C# doesn't allow changing ref params in lambda's

            foreach (var kvp in dict)
            {
                var value    = kvp.Value;
                var propName = kvp.Key;
                if (excludeDefaults && value == null)
                {
                    continue;
                }

                var inValues = sql != null?GetMultiValues(value) : null;

                if (inValues != null)
                {
                    var propType = value?.GetType() ?? typeof(object);
                    var sb       = StringBuilderCache.Allocate();
                    foreach (var item in inValues)
                    {
                        var p = dbCmd.CreateParameter();
                        p.ParameterName = "v" + paramIndex++;

                        if (sb.Length > 0)
                        {
                            sb.Append(',');
                        }
                        sb.Append(dialectProvider.ParamString + p.ParameterName);

                        p.Direction = ParameterDirection.Input;
                        dialectProvider.InitDbParam(p, item.GetType());

                        dialectProvider.SetParamValue(p, item, item.GetType());

                        dbCmd.Parameters.Add(p);
                    }

                    var sqlIn = StringBuilderCache.ReturnAndFree(sb);
                    if (string.IsNullOrEmpty(sqlIn))
                    {
                        sqlIn = "NULL";
                    }
                    sqlCopy = sqlCopy?.Replace(dialectProvider.ParamString + propName, sqlIn);
                    if (dialectProvider.ParamString != "@")
                    {
                        sqlCopy = sqlCopy?.Replace("@" + propName, sqlIn);
                    }
                }
                else
                {
                    var p = dbCmd.CreateParameter();
                    p.ParameterName = propName;

                    p.Direction = ParameterDirection.Input;
                    p.Value     = value ?? DBNull.Value;
                    if (value != null)
                    {
                        dialectProvider.InitDbParam(p, value.GetType());
                    }

                    dbCmd.Parameters.Add(p);
                }
            }

            sql = sqlCopy;

            return(dbCmd);
        }
示例#45
0
        private Dictionary <string, Persist.FieldBehaviour> _buildParamaters(object targetObject, IDbCommand cmd, out PropertyInfo pIdentity, Persist.FieldBehaviour filter = Persist.FieldBehaviour.common)
        {
            IDbDataParameter param;
            Dictionary <string, Persist.FieldBehaviour> parameters = new Dictionary <string, Persist.FieldBehaviour>();

            pIdentity = null;

            foreach (PropertyInfo p in targetObject.GetType().GetProperties())
            {
                System.Attribute[] attrs = System.Attribute.GetCustomAttributes(p);
                foreach (System.Attribute att in attrs)
                {
                    if (att is Persist)
                    {
                        Persist prop = (Persist)att;
                        if (filter != Persist.FieldBehaviour.common && filter != (filter & prop.Behaviour))
                        {
                            continue;
                        }
                        //Recuperer la valeur de la propriete
                        string name  = string.Empty == prop.Field ? p.Name : prop.Field;
                        object value = null;
                        value = p.GetGetMethod().Invoke(targetObject, null);
                        if (SqlDbType.UniqueIdentifier == prop.DbType && (Guid)value == Guid.Empty)
                        {
                            value = DBNull.Value;
                        }
                        else
                        {
                            //Tester si enumeration et cast via le sqldbtype

                            if (null != value && value.GetType().IsEnum)
                            {
                                switch (prop.DbType)
                                {
                                case SqlDbType.Bit:
                                case SqlDbType.Int:
                                case SqlDbType.TinyInt:
                                case SqlDbType.Timestamp:
                                case SqlDbType.SmallInt:
                                case SqlDbType.BigInt:
                                case SqlDbType.Binary:
                                    break;

                                case SqlDbType.Char:
                                case SqlDbType.NChar:
                                    value = (char)((int)value);
                                    break;

                                case SqlDbType.VarBinary:
                                case SqlDbType.Decimal:
                                case SqlDbType.Float:
                                case SqlDbType.Money:
                                case SqlDbType.Real:
                                case SqlDbType.SmallMoney:
                                case SqlDbType.Date:
                                case SqlDbType.SmallDateTime:
                                case SqlDbType.DateTime:
                                case SqlDbType.DateTime2:
                                case SqlDbType.DateTimeOffset:
                                case SqlDbType.NText:
                                case SqlDbType.NVarChar:
                                case SqlDbType.VarChar:
                                case SqlDbType.Text:
                                case SqlDbType.Time:
                                case SqlDbType.UniqueIdentifier:
                                default:
                                    throw new Exception("Can't convert enum value to database value");
                                }
                            }
                        }
                        //Recuperer la valeur de la propriete
                        parameters.Add(name, prop.Behaviour);
                        param = cmd.CreateParameter();
                        param.ParameterName = "@" + name;
                        param.Value         = null == value ? DBNull.Value : value;
                        //Patch for non generic  sql type
                        if (SqlDbType.SmallDateTime == prop.DbType)
                        {
                            param.DbType = DbType.String;
                        }
                        //param.DbType = (DbType)prop.DbType;
                        cmd.Parameters.Add(param);

                        if (Persist.FieldBehaviour.autoincrement != (prop.Behaviour & Persist.FieldBehaviour.autoincrement))
                        {
                        }
                        else
                        {
                            pIdentity = p;
                        }
                    }
                }
            }
            return(parameters);
        }
示例#46
0
 private void SetCommandParameters(IDbCommand command, List <AspNetCore.ReportingServices.ReportIntermediateFormat.ParameterValue> queryParams, object[] paramValues)
 {
     if (queryParams != null)
     {
         int            num = 0;
         IDataParameter dataParameter;
         while (true)
         {
             if (num < paramValues.Length)
             {
                 if (!this.m_odpContext.IsSharedDataSetExecutionOnly || !((DataSetParameterValue)queryParams[num]).OmitFromQuery)
                 {
                     try
                     {
                         dataParameter = command.CreateParameter();
                     }
                     catch (Exception innerException)
                     {
                         throw new ReportProcessingException(ErrorCode.rsErrorCreatingQueryParameter, innerException, this.m_dataSet.Name.MarkAsPrivate());
                     }
                     dataParameter.ParameterName = queryParams[num].Name;
                     bool   flag = dataParameter is IDataMultiValueParameter && paramValues[num] is ICollection;
                     object obj  = paramValues[num];
                     if (obj == null)
                     {
                         obj = DBNull.Value;
                     }
                     if (!(dataParameter is IDataMultiValueParameter) && paramValues[num] is ICollection)
                     {
                         break;
                     }
                     if (flag)
                     {
                         int count = ((ICollection)obj).Count;
                         if (1 == count)
                         {
                             try
                             {
                                 Global.Tracer.Assert(obj is object[], "(paramValue is object[])");
                                 dataParameter.Value = (obj as object[])[0];
                             }
                             catch (Exception innerException2)
                             {
                                 throw new ReportProcessingException(ErrorCode.rsErrorAddingQueryParameter, innerException2, this.m_dataSource.Name.MarkAsModelInfo());
                             }
                         }
                         else
                         {
                             object[] array = new object[count];
                             ((ICollection)obj).CopyTo(array, 0);
                             ((IDataMultiValueParameter)dataParameter).Values = array;
                         }
                     }
                     else
                     {
                         try
                         {
                             dataParameter.Value = obj;
                         }
                         catch (Exception innerException3)
                         {
                             throw new ReportProcessingException(ErrorCode.rsErrorAddingQueryParameter, innerException3, this.m_dataSource.Name.MarkAsModelInfo());
                         }
                     }
                     try
                     {
                         command.Parameters.Add(dataParameter);
                     }
                     catch (Exception innerException4)
                     {
                         throw new ReportProcessingException(ErrorCode.rsErrorAddingQueryParameter, innerException4, this.m_dataSource.Name.MarkAsModelInfo());
                     }
                     if (this.m_odpContext.UseVerboseExecutionLogging)
                     {
                         this.m_executionMetrics.SetQueryParameters(command.Parameters);
                     }
                 }
                 num++;
                 continue;
             }
             return;
         }
         throw new ReportProcessingException(ErrorCode.rsErrorAddingMultiValueQueryParameter, null, this.m_dataSet.Name.MarkAsPrivate(), dataParameter.ParameterName);
     }
 }
示例#47
0
        internal static IDbCommand SetParameters(this IDbCommand dbCmd, Type type, object anonType, bool excludeDefaults, ref string sql)
        {
            if (anonType == null)
            {
                return(dbCmd);
            }

            dbCmd.Parameters.Clear();

            var modelDef        = type.GetModelDefinition();
            var dialectProvider = dbCmd.GetDialectProvider();
            var fieldMap        = type.IsUserType() //Ensure T != Scalar<int>()
                ? dialectProvider.GetFieldDefinitionMap(modelDef)
                : null;

            var sqlCopy = sql; //C# doesn't allow changing ref params in lambda's
            Dictionary <string, PropertyAccessor> anonTypeProps = null;

            var paramIndex = 0;

            anonType.ToObjectDictionary().ForEachParam(modelDef, excludeDefaults, (propName, columnName, value) =>
            {
                var propType = value?.GetType() ?? ((anonTypeProps ??= TypeProperties.Get(anonType.GetType()).PropertyMap)
                                                    .TryGetValue(propName, out var pType)
                        ? pType.PropertyInfo.PropertyType
                        : typeof(object));
                var inValues = GetMultiValues(value);
                if (inValues != null)
                {
                    var sb = StringBuilderCache.Allocate();
                    foreach (var item in inValues)
                    {
                        var p           = dbCmd.CreateParameter();
                        p.ParameterName = "v" + paramIndex++;

                        if (sb.Length > 0)
                        {
                            sb.Append(',');
                        }
                        sb.Append(dialectProvider.ParamString + p.ParameterName);

                        p.Direction = ParameterDirection.Input;
                        dialectProvider.InitDbParam(p, item.GetType());

                        dialectProvider.SetParamValue(p, item, item.GetType());

                        dbCmd.Parameters.Add(p);
                    }

                    var sqlIn = StringBuilderCache.ReturnAndFree(sb);
                    if (string.IsNullOrEmpty(sqlIn))
                    {
                        sqlIn = "NULL";
                    }
                    sqlCopy = sqlCopy?.Replace(dialectProvider.ParamString + propName, sqlIn);
                    if (dialectProvider.ParamString != "@")
                    {
                        sqlCopy = sqlCopy?.Replace("@" + propName, sqlIn);
                    }
                }
                else
                {
                    var p           = dbCmd.CreateParameter();
                    p.ParameterName = propName;
                    p.Direction     = ParameterDirection.Input;
                    dialectProvider.InitDbParam(p, propType);

                    FieldDefinition fieldDef = null;
                    fieldMap?.TryGetValue(columnName, out fieldDef);

                    dialectProvider.SetParamValue(p, value, propType, fieldDef);

                    dbCmd.Parameters.Add(p);
                }
            });
示例#48
0
        /// <summary>
        /// uses optimistic concurrency to update a record.  If two sessions
        /// are editing the same record at the same time
        /// the last writer will not overwrite any changes made by the first writer
        /// instead the return value of 0 indicates that a conflict occured
        /// the return value of 1 indicates that no conflict occured
        /// </summary>
        /// <param name="BookID"></param>
        /// <param name="BookName"></param>
        /// <param name="BookDOB"></param>
        /// <param name="BookLocation"></param>
        /// <returns>the number of records updated (should be 1)</returns>
        public int BookUpdateSafe(int BookID,
                                  string oldISBN,
                                  string oldBookName,
                                  int oldPages, decimal oldPrice, int oldGenreID,
                                  string newISBN,
                                  string newBookName,
                                  int newPages, decimal newPrice, int newGenreID)
        {
            int rv = 0;

            // a reasonable default value to return
            try
            {
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // configure the command object
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "BookUpdateSafe";

                    IDbDataParameter p = command.CreateParameter();
                    p.ParameterName = "@BookID";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = BookID;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@oldISBN";
                    p.DbType        = DbType.String;
                    p.Direction     = ParameterDirection.Input;
                    if (string.IsNullOrWhiteSpace(oldISBN))
                    {
                        p.Value = DBNull.Value;
                    }
                    else
                    {
                        p.Value = oldISBN;
                    }

                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@oldBookName";
                    p.DbType        = DbType.String;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = oldBookName;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@oldPages";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = oldPages;

                    command.Parameters.Add(p);


                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@oldPrice";
                    p.DbType        = DbType.Decimal;
                    p.Precision     = 18;
                    p.Scale         = 2;
                    p.Direction     = ParameterDirection.Input;

                    p.Value = oldPrice;

                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@oldGenreID";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = oldGenreID;
                    command.Parameters.Add(p);


                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@newISBN";
                    p.DbType        = DbType.String;
                    p.Direction     = ParameterDirection.Input;
                    if (string.IsNullOrWhiteSpace(newISBN))
                    {
                        p.Value = DBNull.Value;
                    }
                    else
                    {
                        p.Value = newISBN;
                    }
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@newBookName";
                    p.DbType        = DbType.String;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = newBookName;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@newPages";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = newPages;

                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@NewPrice";
                    p.DbType        = DbType.Decimal;
                    p.Precision     = 18;
                    p.Scale         = 2;
                    p.Direction     = ParameterDirection.Input;

                    p.Value = newPrice;

                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@newGenreID";
                    p.DbType        = DbType.String;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = newGenreID;
                    command.Parameters.Add(p);

                    // since this procedure returns a single row with
                    // a single value in column 1
                    // we can retrieve it much more efficiently using
                    // execute scalar, avoiding all the reader overhead
                    rv = Convert.ToInt32(command.ExecuteScalar());
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
                // ALL exceptions are logged by the when clause
                // and then the exception is tossed to the next layer up.
                // the catch block is NEVER invoked because the
                // when clause never evaluates to true since the
                // Log method returns false
            }
            return(rv);
        }
示例#49
0
        /// <summary>
        /// Generate a command using the current columns, target and parameters
        /// </summary>
        /// <param name="dbCommandType"></param>
        /// <returns></returns>
        public virtual void UpdateCommand(IDbCommand cmd)
        {
            Validate();

            // compile all required field paths; this will ensure every table we need to hit will be present
            List <IFieldPath> paths = new List <IFieldPath>();

            paths.Add(new FieldPath(_target.IdField));
            paths.AddRange(_columns);
            paths.AddRange(_orderBy.ConvertAll <IFieldPath>(s => s.Path));
            paths.AddRange(_groupBy);

            SqlString where = null;
            if (_where != null)
            {
                where = _where.ToSqlString().Flatten();
                paths.AddRange(where.Parts.FindAll(o => o is IFieldPath).ConvertAll <IFieldPath>(o => (IFieldPath)o));
            }

            // add all field paths and calculate unique joins for all of them
            var aliases = UniqueFieldPaths.FromPaths(paths);

            AssignAliases(aliases);

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT {0} FROM {1}", GetColumnDefinitions(aliases), GetFromClause(aliases));
            cmd.Parameters.Clear();
            if (where != null)
            {
                // loop over parts of Where clause
                // if field, lookup alias and concat
                // if string, just concat
                // if parameter, add placeholder text, then add paramater to Command
                var whereStr = new StringBuilder();
                int paramIdx = 0;
                foreach (var part in where.Parts)
                {
                    if (part is IFieldPath)
                    {
                        whereStr.AppendFormat("{0}.[{1}]", aliases[(IFieldPath)part].Alias, ((IFieldPath)part).Last.SourceName);
                    }
                    else if (part is SqlStringParameter)
                    {
                        var p    = (SqlStringParameter)part;
                        var cmdp = cmd.CreateParameter();
                        cmdp.ParameterName = String.Concat("@", paramIdx++);
                        cmdp.Value         = p.Value;
                        cmd.Parameters.Add(cmdp);
                        whereStr.Append(cmdp.ParameterName);
                    }
                    else
                    {
                        whereStr.Append(part);
                    }
                }

                if (whereStr.Length > 0)
                {
                    sb.Append(" WHERE ");
                    sb.Append(whereStr);
                }
            }

            sb.Append(GetOrderByClause(aliases));
            sb.Append(GetGroupByClause(aliases));
            cmd.CommandText = sb.ToString();
        }
 public IDbDataParameter CreateParameter()
 {
     return(_inner.CreateParameter());
 }
示例#51
0
        public virtual IDbDataParameter CreateDataParameter(IDbCommand cmd, DbParam param)
        {
            IDbDataParameter parameter = cmd.CreateParameter();

            parameter.ParameterName = param.Name;

            Type parameterType = null;

            if (param.Value == null || param.Value == DBNull.Value)
            {
                parameter.Value = DBNull.Value;
                parameterType   = param.Type ?? typeof(object);
            }
            else
            {
                parameterType = param.Value.GetType();
                if (parameterType.IsEnum)
                {
                    parameterType   = Enum.GetUnderlyingType(parameterType);
                    parameter.Value = Convert.ChangeType(param.Value, parameterType);
                }
                else
                {
                    parameter.Value = param.Value;
                }
            }

            if (param.Precision != null)
            {
                parameter.Precision = param.Precision.Value;
            }

            if (param.Scale != null)
            {
                parameter.Scale = param.Scale.Value;
            }

            if (param.Size != null)
            {
                parameter.Size = param.Size.Value;
            }

            if (param.DbType != null)
            {
                parameter.DbType = param.DbType.Value;
            }
            else
            {
                parameter.DbType = this.DbType;
            }

            const int defaultSizeOfStringOutputParameter = 4000;/* 当一个 string 类型输出参数未显示指定 Size 时使用的默认大小。如果有需要更大或者该值不足以满足需求,需显示指定 DbParam.Size 值 */

            if (param.Direction == ParamDirection.Input)
            {
                parameter.Direction = ParameterDirection.Input;
            }
            else if (param.Direction == ParamDirection.Output)
            {
                parameter.Direction = ParameterDirection.Output;
                param.Value         = null;
                if (param.Size == null && param.Type == UtilConstants.TypeOfString)
                {
                    parameter.Size = defaultSizeOfStringOutputParameter;
                }
            }
            else if (param.Direction == ParamDirection.InputOutput)
            {
                parameter.Direction = ParameterDirection.InputOutput;
                if (param.Size == null && param.Type == UtilConstants.TypeOfString)
                {
                    parameter.Size = defaultSizeOfStringOutputParameter;
                }
            }
            else
            {
                throw new NotSupportedException(string.Format("ParamDirection '{0}' is not supported.", param.Direction));
            }

            return(parameter);
        }
示例#52
0
        public void testGetNumber()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create or replace table testGetNumber(cola number)";
                int count = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, count);

                int   numInt   = 10000;
                long  numLong  = 1000000L;
                short numShort = 10;

                string insertCommand = "insert into testgetnumber values (?),(?),(?)";
                cmd.CommandText = insertCommand;

                var p1 = cmd.CreateParameter();
                p1.ParameterName = "1";
                p1.Value         = numInt;
                p1.DbType        = DbType.Int32;
                cmd.Parameters.Add(p1);

                var p2 = cmd.CreateParameter();
                p2.ParameterName = "2";
                p2.Value         = numLong;
                p2.DbType        = DbType.Int32;
                cmd.Parameters.Add(p2);

                var p3 = cmd.CreateParameter();
                p3.ParameterName = "3";
                p3.Value         = numShort;
                p3.DbType        = DbType.Int16;
                cmd.Parameters.Add(p3);

                count = cmd.ExecuteNonQuery();
                Assert.AreEqual(3, count);

                cmd.CommandText = "select * from testgetnumber";
                IDataReader reader = cmd.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numInt, reader.GetInt32(0));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numLong, reader.GetInt64(0));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numShort, reader.GetInt16(0));

                Assert.IsFalse(reader.Read());
                reader.Close();

                cmd.CommandText = "drop table if exists testgetnumber";
                count           = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, count);

                conn.Close();
            }
        }
        /// <summary>
        /// Handles the specified command.
        /// </summary>
        /// <param name="commandSend">The command.</param>
        /// <returns></returns>
        /// <exception cref="DotNetWorkQueueException">Failed to insert record - the ID of the new record returned by SQLite was 0</exception>
        public long Handle(SendMessageCommand commandSend)
        {
            if (!_databaseExists.Exists(_configurationSend.ConnectionInfo.ConnectionString))
            {
                return(0);
            }

            if (!_messageExpirationEnabled.HasValue)
            {
                _messageExpirationEnabled = _options.Value.EnableMessageExpiration;
            }

            using (var connection = _dbFactory.CreateConnection(_configurationSend.ConnectionInfo.ConnectionString, false))
            {
                connection.Open();

                var expiration = TimeSpan.Zero;
                if (_messageExpirationEnabled.Value)
                {
                    expiration = MessageExpiration.GetExpiration(commandSend, data => data.GetExpiration());
                }

                var jobName       = _jobSchedulerMetaData.GetJobName(commandSend.MessageData);
                var scheduledTime = DateTimeOffset.MinValue;
                var eventTime     = DateTimeOffset.MinValue;
                if (!string.IsNullOrWhiteSpace(jobName))
                {
                    scheduledTime = _jobSchedulerMetaData.GetScheduledTime(commandSend.MessageData);
                    eventTime     = _jobSchedulerMetaData.GetEventTime(commandSend.MessageData);
                }

                IDbCommand commandStatus = null;
                using (var command = SendMessage.GetMainCommand(commandSend, connection, _commandCache, _headers, _serializer))
                {
                    long id;
                    using (var commandMeta = SendMessage.CreateMetaDataRecord(commandSend.MessageData.GetDelay(), expiration,
                                                                              connection, commandSend.MessageToSend, commandSend.MessageData, _tableNameHelper, _headers,
                                                                              _options.Value, _getTime))
                    {
                        if (_options.Value.EnableStatusTable)
                        {
                            commandStatus = CreateStatusRecord(connection, commandSend.MessageToSend,
                                                               commandSend.MessageData);
                        }

                        using (var trans = _dbFactory.CreateTransaction(connection).BeginTransaction())
                        {
                            try
                            {
                                if (string.IsNullOrWhiteSpace(jobName) || _jobExistsHandler.Handle(new DoesJobExistQuery <IDbConnection, IDbTransaction>(jobName, scheduledTime, connection, trans)) ==
                                    QueueStatuses.NotQueued)
                                {
                                    command.Transaction = trans;
                                    command.ExecuteNonQuery();
                                    var commandId = connection.CreateCommand();
                                    commandId.Transaction = trans;
                                    commandId.CommandText = "SELECT last_insert_rowid();";
                                    id = Convert.ToInt64(commandId.ExecuteScalar());
                                    if (id > 0)
                                    {
                                        commandMeta.Transaction = trans;

                                        var param = commandMeta.CreateParameter();
                                        param.ParameterName = "@QueueID";
                                        param.DbType        = DbType.Int64;
                                        param.Value         = id;
                                        commandMeta.Parameters.Add(param);
                                        commandMeta.ExecuteNonQuery();
                                        if (commandStatus != null)
                                        {
                                            commandStatus.Transaction = trans;

                                            param = commandStatus.CreateParameter();
                                            param.ParameterName = "@QueueID";
                                            param.DbType        = DbType.Int64;
                                            param.Value         = id;
                                            commandStatus.Parameters.Add(param);
                                            commandStatus.ExecuteNonQuery();
                                        }

                                        if (!string.IsNullOrWhiteSpace(jobName))
                                        {
                                            _sendJobStatus.Handle(new SetJobLastKnownEventCommand <IDbConnection, IDbTransaction>(jobName, eventTime,
                                                                                                                                  scheduledTime, connection, trans));
                                        }
                                        trans.Commit();
                                    }
                                    else
                                    {
                                        throw new DotNetWorkQueueException(
                                                  "Failed to insert record - the ID of the new record returned by SQLite was 0");
                                    }
                                }
                                else
                                {
                                    throw new DotNetWorkQueueException(
                                              "Failed to insert record - the job has already been queued or processed");
                                }
                            }
                            finally
                            {
                                commandStatus?.Dispose();
                            }
                        }
                    }
                    return(id);
                }
            }
        }
示例#54
0
        protected DbParameter[] CreateParemeters(T entity, IDbCommand command, string sql)
        {
            var pattern = $"{Comodin}([0-9a-zA-Z_]+)";

            List <string> listParameters = new List <string>();

            DbParameter[] parameters = new DbParameter[0];

            MatchCollection matches = Regex.Matches(sql, pattern);

            foreach (Match param in matches)
            {
                var index = listParameters.IndexOf(param.Value);

                if (index == -1)
                {
                    foreach (var property in typeof(T).GetProperties())
                    {
                        if ($"{Comodin}{property.Name}".ToUpper().Equals(param.Value.ToUpper()))
                        {
                            object value = property.GetValue(entity, null);

                            if (property.PropertyType == typeof(DateTime))
                            {
                                if (!((DateTime?)value).HasValue || (DateTime?)value == default(DateTime))
                                {
                                    value = null;
                                }
                            }

                            Array.Resize(ref parameters, parameters.Length + 1);

                            /* 08-Ene-2020  SCC  Para control de cadenas largas se usa el CLOB de Oracle */
                            if (property.PropertyType.Name.ToUpper() == "OBJECT")
                            {
                                //parameters[parameters.Length - 1].DbType = DbType.Binary;
                                //parameters[parameters.Length - 1].OracleDataType = OracleDbType.Raw;
                                //char[] writeBuffer = value.ToString().ToCharArray();
                                OracleClob cValue = new OracleClob((OracleConnection)command.Connection);
                                if (value != null)
                                {
                                    byte[] writeBuffer = System.Text.Encoding.Unicode.GetBytes(value.ToString());
                                    cValue.Write(writeBuffer, 0, writeBuffer.Length);
                                }
                                parameters[parameters.Length - 1] = (DbParameter)command.CreateParameter(param.Value, cValue ?? (object)DBNull.Value);
                            }
                            else
                            {
                                if (property.PropertyType.Name.ToUpper().Contains("LIST"))
                                {
                                    parameters[parameters.Length - 1].DbType = DbType.Binary;
                                    //parameters[parameters.Length - 1].OracleDataType = OracleDbType.RefCursor;
                                }
                                else
                                {
                                    parameters[parameters.Length - 1] = (DbParameter)command.CreateParameter(param.Value, value ?? DBNull.Value);
                                }
                            }



                            break;
                        }
                    }
                }
                listParameters.Add(param.Value);
            }
            return(parameters);
        }
示例#55
0
        /// <summary>
        /// Update the existing schedule for the database
        /// </summary>
        /// <parameter name="ScheduleInfo"></parameter>
        /// <returns>int</returns>
        public bool UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
        {
            IDbConnection conn      = null;
            bool          isUpdated = false;

            try
            {
                int RouteId = GetRouteID(scheduleInfo);

                conn = this.GetConnection();
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UpdateSchedule";
                cmd.CommandType = CommandType.StoredProcedure;

                IDataParameter p1 = cmd.CreateParameter();
                p1.ParameterName = "@scheduleid";
                p1.Value         = scheduleInfo.ID;
                cmd.Parameters.Add(p1);

                IDataParameter p2 = cmd.CreateParameter();
                p2.ParameterName = "@flightid";
                p2.Value         = scheduleInfo.FlightInfo.ID;
                cmd.Parameters.Add(p2);

                IDataParameter p3 = cmd.CreateParameter();
                p3.ParameterName = "@routeid";
                p3.Value         = RouteId;
                cmd.Parameters.Add(p3);

                IDataParameter p7 = cmd.CreateParameter();
                p7.ParameterName = "@departuretime";
                p7.Value         = scheduleInfo.DepartureTime;
                cmd.Parameters.Add(p7);

                IDataParameter p4 = cmd.CreateParameter();
                p4.ParameterName = "@arrivaltime";
                p4.Value         = scheduleInfo.ArrivalTime;
                cmd.Parameters.Add(p4);

                IDataParameter p5 = cmd.CreateParameter();
                p5.ParameterName = "@durationinmins";
                p5.Value         = scheduleInfo.DurationInMins;
                cmd.Parameters.Add(p5);

                IDataParameter p6 = cmd.CreateParameter();
                p6.ParameterName = "@isactive";
                p6.Value         = scheduleInfo.IsActive;
                cmd.Parameters.Add(p6);

                cmd.ExecuteNonQuery();

                isUpdated = true;
            }
            catch (Common.ConnectToDatabaseException)
            {
                throw new ScheduleDAOException("Unable to update schedule");
            }
            catch (Exception ex)
            {
                throw new ScheduleDAOException("Unable to update schedule");
            }
            finally
            {
                conn.Close();
            }

            return(isUpdated);
        }
        /// <summary>
        /// Execute an insert statement. Fill the parameter object with
        /// the ouput parameters if any, also could return the insert generated key
        /// </summary>
        /// <param name="session">The session</param>
        /// <param name="parameterObject">The parameter object used to fill the statement.</param>
        /// <returns>Can return the insert generated key.</returns>
        public virtual object ExecuteInsert(ISession session, object parameterObject)
        {
            return(Execute(PreInsertEventKey, PostInsertEventKey, session, parameterObject,
                           (r, p) =>
            {
                #region RunInsert

                object generatedKey = null;
                SelectKey selectKeyStatement = null;

                if (statement is Insert)
                {
                    selectKeyStatement = ((Insert)statement).SelectKey;
                }

                if (selectKeyStatement != null && !selectKeyStatement.isAfter)
                {
                    IMappedStatement mappedStatement = modelStore.GetMappedStatement(selectKeyStatement.Id);
                    generatedKey = mappedStatement.ExecuteQueryForObject(session, p, null);

                    ObjectProbe.SetMemberValue(p, selectKeyStatement.PropertyName, generatedKey,
                                               r.DataExchangeFactory.ObjectFactory,
                                               r.DataExchangeFactory.AccessorFactory);
                }

                preparedCommand.Create(r, session, Statement, p);

                using (IDbCommand command = r.IDbCommand)
                {
                    if (statement is Insert)
                    {
                        command.ExecuteNonQuery();
                    }
                    // Retrieve output parameter if the result class is specified
                    else if (statement is Procedure && (statement.ResultClass != null) &&
                             modelStore.DataExchangeFactory.TypeHandlerFactory.IsSimpleType(statement.ResultClass))
                    {
                        IDataParameter returnValueParameter = command.CreateParameter();
                        returnValueParameter.Direction = ParameterDirection.ReturnValue;
                        command.Parameters.Add(returnValueParameter);

                        command.ExecuteNonQuery();
                        generatedKey = returnValueParameter.Value;

                        ITypeHandler typeHandler = modelStore.DataExchangeFactory.TypeHandlerFactory.GetTypeHandler(statement.ResultClass);
                        generatedKey = typeHandler.GetDataBaseValue(generatedKey, statement.ResultClass);
                    }
                    else
                    {
                        generatedKey = command.ExecuteScalar();
                        if ((statement.ResultClass != null) &&
                            modelStore.DataExchangeFactory.TypeHandlerFactory.IsSimpleType(statement.ResultClass))
                        {
                            ITypeHandler typeHandler = modelStore.DataExchangeFactory.TypeHandlerFactory.GetTypeHandler(statement.ResultClass);
                            generatedKey = typeHandler.GetDataBaseValue(generatedKey, statement.ResultClass);
                        }
                    }

                    if (selectKeyStatement != null && selectKeyStatement.isAfter)
                    {
                        IMappedStatement mappedStatement = modelStore.GetMappedStatement(selectKeyStatement.Id);
                        generatedKey = mappedStatement.ExecuteQueryForObject(session, p, null);

                        ObjectProbe.SetMemberValue(p, selectKeyStatement.PropertyName, generatedKey,
                                                   r.DataExchangeFactory.ObjectFactory,
                                                   r.DataExchangeFactory.AccessorFactory);
                    }

                    RetrieveOutputParameters(r, session, command, p);
                }

                // ???
                return generatedKey;

                #endregion
            }));
        }
示例#57
0
 /// <summary>
 /// Creates a new instance of an <see cref="System.Data.IDbDataParameter"></see> object.
 /// </summary>
 /// <returns>An IDbDataParameter object.</returns>
 IDbDataParameter IDbCommand.CreateParameter()
 {
     return(_innerDbCommand.CreateParameter());
 }
示例#58
0
        // BookGetSortedAndFilteredcount is used by advanced ajax portion of the
        // datatables example.  It is used to get the 'filtered' count for displaying
        // on the datatables form
        public int BookGetSortedAndFilteredCount(string sortColumn, string sortColumnDir, string searchValue)
        {
            try
            {
                string direction;
                string orderby;
                string where;
                bool wherethere = false;
                // check the sort column to verify it is a valid column
                // this is to avoid SQL injection
                switch (sortColumn.ToLower())
                {
                case ("bookid"): orderby = "bookid"; break;

                case ("isbn"): orderby = "ISBN"; break;

                case ("bookname"): orderby = "bookname"; break;

                case ("pages"): orderby = "pages"; break;

                case ("daysoverdue"): orderby = "daysoverdue"; break;

                case ("genrename"): orderby = "genrename"; break;

                default: orderby = "bookname"; break;
                }

                if (string.IsNullOrWhiteSpace(searchValue))
                {
                    where      = "";
                    wherethere = false;
                }
                else
                {
                    // where clause is not subject to SQL injection because the
                    // columns are compared to an @variable
                    // which will be loaded if wherethere is true
                    // wheredata is loaded from searchvalue
                    where      = $" where ISBN like @wheredata or Bookname like @wheredata or GenreName like @wheredata ";
                    wherethere = true;
                }
                // check direction to make sure it is valid
                // this is to avoid sql injection
                switch (sortColumnDir.ToLower())
                {
                case ("asc"): direction = " ASC"; break;

                default: direction = " DESC"; break;
                }
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // use text to create dynamic query
                    command.CommandType = CommandType.Text;
                    command.CommandText = "select count(*) from books " +
                                          //from inner query
                                          //first left outer join bringing over the genrename or NULL if no match
                                          "left outer join genres " +
                                          "on books.genreid = genres.genreid " +
                                          //-- second left outer join bringing over the checkout data etc.

                                          " left outer join " +
//-- inner query follows
                                          "(select bookid as bkid, " +
                                          " count(*) as checkouts," +
                                          "  sum(DATEDIFF(day, DueDate, GetDate())) as daysoverdue, " +
                                          "  avg(Rating) AvgRating " +

                                          "  from TypeDRatedBorrowings " +
                                          " where ReturnedDate is null " +
                                          "  group by bookid) as x " +
                                          //--end of inner query(still need the on clause for the previous left outer join)
                                          "  on bkid = bookid " +
                                          //  -- the first two joins are now joined to the third one also on the bookid
                                          // this is the where clause, it may not be present
                                          // and then the parameter will not be added either
                                          where;

                    if (wherethere)
                    {
                        IDbDataParameter p = command.CreateParameter();
                        p.ParameterName = "@wheredata";
                        p.DbType        = DbType.String;
                        p.Direction     = ParameterDirection.Input;
                        p.Value         = searchValue;
                        command.Parameters.Add(p);
                    }
                    // load the data from the database
                    object rv = command.ExecuteScalar();
                    return(Convert.ToInt32(rv));
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
            }
            return(0);
        }
 public IDbDataParameter CreateParameter()
 {
     return(_command.CreateParameter());
 }
 public static IDbCommand AddParameter(this IDbCommand command, string name, object?value)
 => command.Chain(x => x.Parameters.Add(command.CreateParameter(name, value)));