/// <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); }