/// <summary> /// Insert a new row to database using stored procedure /// </summary> /// <param name="model"> /// An Imodel object with data to be saved /// </param> /// <returns> /// Returns I model object with new key /// </returns> public IModel InsertProcedure(IModel model) { SqlDataReader reader = null; IModel newModel = null; DataReaderConverter converter; converter = new DataReaderConverter(this.modelDataMap); // creates new command object with stored procedure parameters SqlCommand command = this.commandBuilder.GetInsertCommand(model); command.Connection = (SqlConnection)this.connection; command.Transaction = (SqlTransaction)this.transaction; // Execute stored procedure reader = command.ExecuteReader(); try { while (reader.Read()) { // set newly generated primary key to model 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> /// 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); }