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;
}
}
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();
}
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);
}
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);
}
/// <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;
}
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);
}
public IDbDataParameter CreateDbParameter(IDbCommand command, IDbAdapter dbAdapter)
{
var result = command.CreateParameter();
result.ParameterName = dbAdapter.FormatParameterName(Name);
result.Value = dbAdapter.ConvertToDbType(Value);
return result;
}
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;
}
public static void AddParameter(IDbCommand cmd, string name, object value)
{
IDataParameter p = cmd.CreateParameter ();
p.ParameterName = name;
p.Value = value;
cmd.Parameters.Add (p);
}
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);
}
}
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();
}
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);
}
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);
}
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;
}
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;
}
private void dbCommandAddParameter(IDbCommand dbCommand,
string parameterName, object value)
{
IDbDataParameter dbDataParameter=dbCommand.CreateParameter();
dbDataParameter.ParameterName = parameterName;
dbDataParameter.Value=value;
dbCommand.Parameters.Add(dbDataParameter);
}
internal IDataParameter ToDataParameter(IDbCommand command)
{
var param = command.CreateParameter();
param.ParameterName = Name;
param.Value = Value;
return param;
}
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();
}
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;
}
/// <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);
}
/// <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);
}
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();
}
}
}
/// <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);
}
}
/// <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));
}
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);
}
// ----------------------------------------------------------------------------
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);
}
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);
}
public IList <IDbDataParameter> Prepare(IDbCommand adoCommand) => this.Prepare(() => adoCommand.CreateParameter());
// 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);
}
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);
}
/// <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);
}
}
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);
}
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);
}
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);
}
}
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);
}
});
/// <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);
}
/// <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());
}
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);
}
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);
}
}
}
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);
}
/// <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
}));
}
/// <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());
}
// 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)));
