/// <summary>
/// Create select command for multiple result set
/// </summary>
/// <param name="mapping">
/// Model data map for attribute mapping
/// </param>
/// <param name="fieldNames">
/// Name of the field names for where clause
/// </param>
/// <param name="fieldValues">
/// values of the field for where clause
/// </param>
/// <returns>
/// Command object with all parameters
/// </returns>
private IDbCommand CreateSelectCommandForMultipleResults(
ModelDataMap mapping,
string[] fieldNames,
object[] fieldValues)
{
SqlCommand cmd = new SqlCommand();
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb = this.GenerateColumnList(mapping, sb, true);
sb.Append(" from ");
sb.Append(mapping.ViewName);
sb.Append(" ");
if (fieldNames != null)
{
sb.Append(" where ");
for (int i = 0; i < fieldNames.Length; i++)
{
ModelColumnMap columnMap = (ModelColumnMap)mapping.Columns[fieldNames[i]];
sb.Append(columnMap.ColumnName);
sb.Append(" = @");
sb.Append(fieldNames[i]);
if (fieldNames.Length > 1 && i != fieldNames.Length - 1)
{
sb.Append(" AND ");
}
SqlParameter p = new SqlParameter();
if (!columnMap.IsDatabaseTypeNull)
{
p.DbType = columnMap.DatabaseType;
}
// p.SourceColumn = columnMap.FieldName;
if (columnMap.Field != null)
{
p.ParameterName = "@" + columnMap.FieldName;
}
else if (columnMap.Property != null)
{
p.ParameterName = "@" + columnMap.PropertyName;
}
object val = fieldValues[i];
p.Value = val;
cmd.Parameters.Add(p);
}
}
cmd.CommandText = sb.ToString();
return(cmd);
}
/// <summary>
/// Delete records based on delete conditions specified
/// </summary>
/// <param name="fieldNames">
/// Filter field names
/// </param>
/// <param name="fieldValues">
/// Filter values.
/// </param>
public void DeleteWithCondition(string[] fieldNames, string[] fieldValues)
{
SqlCommand command = (SqlCommand)this.connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.Append(this.commandBuilder.GetDeleteSqlWithoutWhere());
if (fieldNames != null)
{
sb.Append(" Where ");
for (int i = 0; i < fieldNames.Length; i++)
{
ModelColumnMap columnMap = (ModelColumnMap)this.modelDataMap.Columns[fieldNames[i]]; // mapping
sb.Append(columnMap.ColumnName);
sb.Append(" = @");
sb.Append(fieldNames[i]);
if (fieldNames.Length > 1 && i != fieldNames.Length - 1)
{
sb.Append(" AND ");
}
SqlParameter p = new SqlParameter();
if (!columnMap.IsDatabaseTypeNull)
{
p.DbType = columnMap.DatabaseType;
}
if (columnMap.Field != null)
{
p.ParameterName = "@" + columnMap.FieldName;
}
else if (columnMap.Property != null)
{
p.ParameterName = "@" + columnMap.PropertyName;
}
object val = fieldValues[i];
p.Value = val;
command.Parameters.Add(p);
}
}
command.CommandText = sb.ToString();
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
command.ExecuteNonQuery();
}
/// <summary>
/// This method is used to set the property of the model using ModelColumnMap class.
/// </summary>
/// <param name="model">
/// Model whose property to be set.
/// </param>
/// <param name="column">
/// Object of ModelColumnMap class
/// </param>
/// <param name="val">
/// Value to be set.
/// </param>
public void SetFieldValue(object model, ModelColumnMap column, object val)
{
// set value to null if it is null or DBNull
if (val == null || val == DBNull.Value)
{
val = null;
}
else if (column.Field.FieldType.IsEnum)
{
val = Enum.ToObject(column.FieldType, val);
}
else
{
// get the value from the field
Type valType = val.GetType();
if (valType != column.FieldType)
{
Type[] typeArray = new Type[1];
object[] valueArray = new object[1];
typeArray[0] = valType;
ConstructorInfo ci;
ci = column.FieldType.GetConstructor(typeArray);
if (ci != null)
{
valueArray[0] = val;
val = ci.Invoke(valueArray);
}
else
{
try
{
val = Convert.ChangeType(val, column.FieldType);
}
catch (Exception ex)
{
string message = ex.Message;
}
}
}
}
// set the value to the model's field
column.Field.SetValue(model, val);
}
/// <summary>
/// Creates stored procedure parameter
/// </summary>
/// <param name="name">
/// Parameter name
/// </param>
/// <param name="col">
/// Model column map
/// </param>
/// <param name="model">
/// I model object
/// </param>
/// <returns>
/// Asa parameter
/// </returns>
private IDataParameter CreateParameter(string name, ModelColumnMap col, IModel model)
{
IDataParameter p = new SqlParameter();
p.ParameterName = name;
if (col.Field != null)
{
p.SourceColumn = col.FieldName;
}
else if (col.Property != null)
{
p.SourceColumn = col.PropertyName;
}
if (!col.IsDatabaseTypeNull)
{
p.DbType = col.DatabaseType;
}
object val = null;
if (col.Field != null)
{
val = col.Field.GetValue(model);
}
else if (col.Property != null)
{
val = col.Property.GetValue(model, null);
}
if (val == null)
{
val = DBNull.Value;
}
p.Value = val;
return(p);
}
/// <summary>
/// This method is used to set the property of the model field.
/// </summary>
/// <param name="model">
/// Model whose property to be set.
/// </param>
/// <param name="field">
/// Property to be set.
/// </param>
/// <param name="val">
/// Value of the property
/// </param>
public void SetFieldValue(object model, FieldInfo field, object val)
{
ModelColumnMap column = (ModelColumnMap)this.mapping.Columns[field.Name];
this.SetFieldValue(model, column, val);
}
/// <summary>
/// Insert a new record to database using given imodel object.
/// </summary>
/// <param name="model">
/// A IModel object to be saved.
/// </param>
/// <returns>
/// A IModel object
/// </returns>
public IModel Insert(IModel model)
{
SqlDataReader reader = null;
IModel newModel = null;
ModelColumnMap c = null;
DataReaderConverter converter;
converter = new DataReaderConverter(this.modelDataMap);
// set the createdDate if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["createdDate"];
if (c != null)
{
converter.SetFieldValue(model, c, DateTime.Now);
}
// set the createdBy if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["createdBy"];
if (c != null)
{
string userName = Thread.CurrentPrincipal.Identity.Name;
converter.SetFieldValue(model, c, userName);
}
SqlCommand command = (SqlCommand)this.connection.CreateCommand();
command.CommandText = this.commandBuilder.GetInsertSql();
foreach (ModelColumnMap col in this.modelDataMap.Columns.Values)
{
if (!col.IsViewOnly)
{
SqlParameter p = new SqlParameter();
p.ParameterName = "@" + col.ColumnName;
if (col.Field != null)
{
p.SourceColumn = col.FieldName;
}
else if (col.Property != null)
{
p.SourceColumn = col.PropertyName;
}
if (!col.IsDatabaseTypeNull)
{
p.DbType = col.DatabaseType;
}
object val = null;
if (col.Field != null)
{
val = col.Field.GetValue(model);
}
else if (col.Property != null)
{
val = col.Property.GetValue(model, null);
}
if (val == null)
{
val = DBNull.Value;
}
p.Value = val;
command.Parameters.Add(p);
}
}
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
FieldInfo keyField = this.modelDataMap.KeyField;
if (keyField != null)
{
object id = reader[0];
converter.SetFieldValue(model, keyField, id);
newModel = model;
}
else if (this.modelDataMap.KeyProperty != null)
{
object id = reader[0];
converter.SetFieldValue(model, this.modelDataMap.KeyProperty, id);
newModel = model;
}
else
{
newModel = model;
}
}
}
finally
{
reader.Close();
}
return(newModel);
}
/// <summary>
/// Update a database record using given imodel object,
/// </summary>
/// <param name="model">
/// A CarrierRate.GlobalTranz.Framework.Common.Models.IModel object to be updated
/// </param>
/// <returns>
/// A CarrierRate.GlobalTranz.Framework.Common.Models.IModel object
/// </returns>
public IModel Update(IModel model)
{
ModelColumnMap c = null;
DataReaderConverter converter;
converter = new DataReaderConverter(this.modelDataMap);
// set the createdDate if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["modifiedDate"];
if (c != null)
{
converter.SetFieldValue(model, c, DateTime.Now);
}
// set the createdBy if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["modifiedBy"];
if (c != null)
{
string userName = Thread.CurrentPrincipal.Identity.Name;
converter.SetFieldValue(model, c, userName);
}
SqlCommand command = (SqlCommand)this.connection.CreateCommand();
command.CommandText = this.commandBuilder.GetUpdateSql();
foreach (ModelColumnMap col in this.modelDataMap.Columns.Values)
{
if (!col.IsViewOnly)
{
string paramName = "@" + col.ColumnName;
command.Parameters.Add(this.CreateParameter(paramName, col, model));
}
}
SqlParameter param = new SqlParameter();
param.ParameterName = "@key";
string keyName = null;
if (this.modelDataMap.KeyField != null)
{
keyName = this.modelDataMap.KeyField.Name;
}
else if (this.modelDataMap.KeyProperty != null)
{
keyName = this.modelDataMap.KeyProperty.Name;
}
ModelColumnMap keyCol;
keyCol = (ModelColumnMap)this.modelDataMap.Columns[keyName];
command.Parameters.Add(this.CreateParameter("@key", keyCol, model));
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(model);
}
return(null);
}
