public bool DeleteById(int id)
{
_errorCode = 0;
_rowsAffected = 0;
try
{
var sb = new StringBuilder();
sb.Append("DELETE FROM [dbo].[tState]");
sb.Append("WHERE");
sb.Append("[stateId]=@stateId;");
sb.Append("SELECT @intErrorCode = @@ERROR;");
var commanText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "the db connection can't be null");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand", "the db Delete command for entity [State] can't be null");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commanText;
_dataHandler.AddParameterToCommand(dbCommand, "@stateId", csType.Int, ParameterDirection.Input, id);
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", csType.Int, ParameterDirection.Output, null);
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Delete method for entity [state] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("StateRepository::Delete::Error occured.", ex);
}
//throw new NotImplementedException();
}
// The following function will insert data from the Create view
public bool Insert(OwnersEntity entity)
{
try
{
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Owners] can't be null. ");
}
// CODE BELOW INSERTS USER INFORMATION INTO MULTIPLE TABLES
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "Owner_Insert";
string hashedpassword = Crypto.HashPassword(entity.Password);
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@chnUsername", CsType.String, ParameterDirection.Input, entity.Username);
_dataHandler.AddParameterToCommand(dbCommand, "@chnFirstName", CsType.String, ParameterDirection.Input, entity.FirstName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnLastName", CsType.String, ParameterDirection.Input, entity.LastName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnEmail", CsType.String, ParameterDirection.Input, entity.Email);
_dataHandler.AddParameterToCommand(dbCommand, "@chnPassword", CsType.String, ParameterDirection.Input, hashedpassword);
_dataHandler.AddParameterToCommand(dbCommand, "@binIsActive", CsType.Boolean, ParameterDirection.Input, entity.IsActive);
_dataHandler.AddParameterToCommand(dbCommand, "@intAge", CsType.Int, ParameterDirection.Input, entity.Age);
_dataHandler.AddParameterToCommand(dbCommand, "@chnRole", CsType.String, ParameterDirection.Input, entity.Role);
_dataHandler.AddParameterToCommand(dbCommand, "@chnOwnerName", CsType.String, ParameterDirection.Input, entity.OwnerName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnPetName", CsType.String, ParameterDirection.Input, entity.PetName);
_dataHandler.AddParameterToCommand(dbCommand, "@intPetAge", CsType.Int, ParameterDirection.Input, entity.PetAge);
_dataHandler.AddParameterToCommand(dbCommand, "@chnContactPhone", CsType.String, ParameterDirection.Input, entity.ContactPhone);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Owners] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("OwnersRepository::Insert::Error occured.", ex);
}
}
// The following function will insert data from the Create view
public bool Insert(UsersEntity entity)
{
try
{
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Owners] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.StoredProcedure;
if (entity.Role == "Sitter")
{
dbCommand.CommandText = "Sitter_Insert";
}
else if (entity.Role == "Owner")
{
dbCommand.CommandText = "Owner_Insert";
}
string hashedpassword = Crypto.HashPassword(entity.Password);
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@chnUsername", CsType.String, ParameterDirection.Input, entity.Username);
_dataHandler.AddParameterToCommand(dbCommand, "@chnFirstName", CsType.String, ParameterDirection.Input, entity.FirstName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnLastName", CsType.String, ParameterDirection.Input, entity.LastName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnEmail", CsType.String, ParameterDirection.Input, entity.Email);
_dataHandler.AddParameterToCommand(dbCommand, "@chnPassword", CsType.String, ParameterDirection.Input, hashedpassword);
_dataHandler.AddParameterToCommand(dbCommand, "@intAge", CsType.Int, ParameterDirection.Input, entity.Age);
_dataHandler.AddParameterToCommand(dbCommand, "@binIsActive", CsType.Boolean, ParameterDirection.Input, entity.IsActive);
_dataHandler.AddParameterToCommand(dbCommand, "@chnRole", CsType.String, ParameterDirection.Input, entity.Role);
// Insert User record information based on role
if (entity.Role == "Sitter")
{
_dataHandler.AddParameterToCommand(dbCommand, "@chnName", CsType.String, ParameterDirection.Input, entity.Name);
_dataHandler.AddParameterToCommand(dbCommand, "@decFee", CsType.Decimal, ParameterDirection.Input, entity.Fee);
_dataHandler.AddParameterToCommand(dbCommand, "@chnBio", CsType.String, ParameterDirection.Input, entity.Bio);
_dataHandler.AddParameterToCommand(dbCommand, "@dtmHiringDate", CsType.DateTime, ParameterDirection.Input, entity.HiringDate);
_dataHandler.AddParameterToCommand(dbCommand, "@decGrossSalary", CsType.Decimal, ParameterDirection.Input, entity.GrossSalary);
}
else if (entity.Role == "Owner")
{
_dataHandler.AddParameterToCommand(dbCommand, "@chnOwnerName", CsType.String, ParameterDirection.Input, entity.OwnerName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnPetName", CsType.String, ParameterDirection.Input, entity.PetName);
_dataHandler.AddParameterToCommand(dbCommand, "@intPetAge", CsType.Int, ParameterDirection.Input, entity.PetAge);
_dataHandler.AddParameterToCommand(dbCommand, "@chnContactPhone", CsType.String, ParameterDirection.Input, entity.ContactPhone);
}
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Users] reported the Database ErrorCode: " + _errorCode);
}
}
}
#region Depricated code By Role
/* Depricated code
*
* if (entity.Role == "Owner")
* {
* var newuserid = 0;
* sb.Clear();
* sb.Append("SET DATEFORMAT DMY; ");
* sb.Append("SELECT UserID ");
* sb.Append("FROM [Users] ");
* sb.Append("WHERE Username = @chnUsername ");
* sb.Append("SELECT @intErrorCode=@@ERROR; ");
*
* commandText = sb.ToString();
* sb.Clear();
*
* using (var dbConnection = _dbProviderFactory.CreateConnection())
* {
* if (dbConnection == null)
* throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
*
* dbConnection.ConnectionString = _connectionString;
*
* using (var dbCommand = _dbProviderFactory.CreateCommand())
* {
* if (dbCommand == null)
* throw new ArgumentNullException("dbCommand" + " The db Get command for entity [Owners] can't be null. ");
*
* dbCommand.Connection = dbConnection;
* dbCommand.CommandText = commandText;
* //Input Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@chnUsername", CsType.String, ParameterDirection.Input, entity.Username);
*
* //Output Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
* //Open Connection
* if (dbConnection.State != ConnectionState.Open)
* dbConnection.Open();
*
* //Execute query.
* using (var reader = dbCommand.ExecuteReader())
* {
* if (reader.HasRows)
* {
* while (reader.Read())
* {
* newuserid = reader.GetInt32(0);
* }
* }
* }
* }
* }
* if (newuserid != 0)
* {
* sb.Clear();
* sb.Append("SET DATEFORMAT DMY; ");
* sb.Append("INSERT [Owners] ");
* sb.Append("( ");
* sb.Append("[OwnerName], ");
* sb.Append("[PetName], ");
* sb.Append("[PetAge], ");
* sb.Append("[ContactPhone], ");
* sb.Append("[ModifiedDate], ");
* sb.Append("[UserID] ");
* sb.Append(") ");
* sb.Append("VALUES (");
* sb.Append("@chnOwnerName, ");
* sb.Append("@chnPetName, ");
* sb.Append("@intPetAge, ");
* sb.Append("@chnContactPhone, ");
* sb.Append("ISNULL(@dtmModifiedDate, (getdate())), ");
* sb.Append("@intUserID ");
* sb.Append(") ");
* sb.Append("SELECT @intErrorCode=@@ERROR; ");
*
* commandText = sb.ToString();
* sb.Clear();
*
* using (var dbConnection = _dbProviderFactory.CreateConnection())
* {
* if (dbConnection == null)
* throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
*
* dbConnection.ConnectionString = _connectionString;
*
* using (var dbCommand = _dbProviderFactory.CreateCommand())
* {
* if (dbCommand == null)
* throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Owners] can't be null. ");
*
* dbCommand.Connection = dbConnection;
* dbCommand.CommandText = commandText;
*
* //Input Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@chnOwnerName", CsType.String, ParameterDirection.Input, entity.OwnerName);
* _dataHandler.AddParameterToCommand(dbCommand, "@chnPetName", CsType.String, ParameterDirection.Input, entity.PetName);
* _dataHandler.AddParameterToCommand(dbCommand, "@intPetAge", CsType.Int, ParameterDirection.Input, entity.PetAge);
* _dataHandler.AddParameterToCommand(dbCommand, "@chnContactPhone", CsType.String, ParameterDirection.Input, entity.ContactPhone);
* _dataHandler.AddParameterToCommand(dbCommand, "@intUserID", CsType.Int, ParameterDirection.Input, newuserid);
* _dataHandler.AddParameterToCommand(dbCommand, "@dtmModifiedDate", CsType.DateTime, ParameterDirection.Input, null);
*
*
* //Output Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
*
* //Open Connection
* if (dbConnection.State != ConnectionState.Open)
* dbConnection.Open();
*
* //Execute query.
* _rowsAffected = dbCommand.ExecuteNonQuery();
* _errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
*
* if (_errorCode != 0)
* {
* // Throw error.
* throw new Exception("The Insert method for entity [Owners] reported the Database ErrorCode: " + _errorCode);
* }
*
* }
* }
* }
* }
* if (entity.Role == "Sitter")
* {
* var newuserid = 0;
* sb.Clear();
* sb.Append("SET DATEFORMAT DMY; ");
* sb.Append("SELECT UserID ");
* sb.Append("FROM [Users] ");
* sb.Append("WHERE Username = @chnUsername ");
* sb.Append("SELECT @intErrorCode=@@ERROR; ");
*
* commandText = sb.ToString();
* sb.Clear();
*
* using (var dbConnection = _dbProviderFactory.CreateConnection())
* {
* if (dbConnection == null)
* throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
*
* dbConnection.ConnectionString = _connectionString;
*
* using (var dbCommand = _dbProviderFactory.CreateCommand())
* {
* if (dbCommand == null)
* throw new ArgumentNullException("dbCommand" + " The db Get command for entity [Owners] can't be null. ");
*
* dbCommand.Connection = dbConnection;
* dbCommand.CommandText = commandText;
* //Input Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@chnUsername", CsType.String, ParameterDirection.Input, entity.Username);
*
* //Output Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
* //Open Connection
* if (dbConnection.State != ConnectionState.Open)
* dbConnection.Open();
*
* //Execute query.
* using (var reader = dbCommand.ExecuteReader())
* {
* if (reader.HasRows)
* {
* while (reader.Read())
* {
* newuserid = reader.GetInt32(0);
* }
* }
* }
* }
* }
* if (newuserid != 0)
* {
* sb.Clear();
* sb.Append("SET DATEFORMAT DMY; ");
* sb.Append("INSERT [Sitters] ");
* sb.Append("( ");
* sb.Append("[Name], ");
* sb.Append("[Fee], ");
* sb.Append("[Bio], ");
* sb.Append("[Age], ");
* sb.Append("[HiringDate], ");
* sb.Append("[GrossSalary], ");
* sb.Append("[ModifiedDate], ");
* sb.Append("[UserID] ");
* sb.Append(") ");
* sb.Append("VALUES (");
* sb.Append("@chnName, ");
* sb.Append("@decFee, ");
* sb.Append("@chnBio, ");
* sb.Append("@intAge, ");
* sb.Append("@dtmHiringDate, ");
* sb.Append("@decGrossSalary, ");
* sb.Append("ISNULL(@dtmModifiedDate, (getdate())), ");
* sb.Append("@intUserID ");
* sb.Append(") ");
* sb.Append("SELECT @intErrorCode=@@ERROR; ");
*
* commandText = sb.ToString();
* sb.Clear();
*
* using (var dbConnection = _dbProviderFactory.CreateConnection())
* {
* if (dbConnection == null)
* throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
*
* dbConnection.ConnectionString = _connectionString;
*
* using (var dbCommand = _dbProviderFactory.CreateCommand())
* {
* if (dbCommand == null)
* throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Owners] can't be null. ");
*
* dbCommand.Connection = dbConnection;
* dbCommand.CommandText = commandText;
*
* //Input Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@chnName", CsType.String, ParameterDirection.Input, entity.Name);
* _dataHandler.AddParameterToCommand(dbCommand, "@decFee", CsType.Decimal, ParameterDirection.Input, entity.Fee);
* _dataHandler.AddParameterToCommand(dbCommand, "@chnBio", CsType.String, ParameterDirection.Input, entity.Bio);
* _dataHandler.AddParameterToCommand(dbCommand, "@intAge", CsType.Int, ParameterDirection.Input, entity.Age);
* _dataHandler.AddParameterToCommand(dbCommand, "@intUserID", CsType.Int, ParameterDirection.Input, newuserid);
* _dataHandler.AddParameterToCommand(dbCommand, "@dtmHiringDate", CsType.DateTime, ParameterDirection.Input, entity.HiringDate);
* _dataHandler.AddParameterToCommand(dbCommand, "@decGrossSalary", CsType.Decimal, ParameterDirection.Input, entity.GrossSalary);
* _dataHandler.AddParameterToCommand(dbCommand, "@dtmModifiedDate", CsType.DateTime, ParameterDirection.Input, null);
*
*
* //Output Parameters
* _dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
*
* //Open Connection
* if (dbConnection.State != ConnectionState.Open)
* dbConnection.Open();
*
* //Execute query.
* _rowsAffected = dbCommand.ExecuteNonQuery();
* _errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
*
* if (_errorCode != 0)
* {
* // Throw error.
* throw new Exception("The Insert method for entity [Owners] reported the Database ErrorCode: " + _errorCode);
* }
*
* }
* }
* }
* }
*/
#endregion
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("UsersRepository::Insert::Error occured.", ex);
}
}
public List <Ubigeo> SelectAll()
{
//_errorCode = 0;
//_rowsAffected = 0;
var returnedEntities = new List <Ubigeo>();
try
{
var sb = new StringBuilder();
sb.Append("SET DATEFORMAT DMY; ");
sb.Append("SELECT ");
sb.Append("[ubigeoId],[title],[code],[label],[search],[numberChildrens],[levelOrder],[fatherId]");
sb.Append(" FROM [GEN].[Ubigeo]");
sb.Append("WHERE [levelOrder]= 0 and [fatherId]= 0 ");
sb.Append("order by title asc ");
sb.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection(sConexion, DatabaseType.SQLServer))
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = sConexion;
using (var dbCommand = _dbProviderFactory.CreateCommand(commandText, DatabaseType.SQLServer, dbConnection))
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db command for entity [Ubigeo] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters - None
//Output Parameters
_dataHandler.AddParameterToCommand((SqlCommand)dbCommand, "@intErrorCode", csType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
using (var reader = dbCommand.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
var entity = new Ubigeo();
entity.ubigeoId = reader.GetInt32(0);
entity.title = reader.GetString(1);
entity.code = reader.GetString(2);
returnedEntities.Add(entity);
}
}
}
//_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The SelectAll method for entity [ServiceType] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(returnedEntities);
}
catch (Exception ex)
{
//Log exception error
//_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
FactoryFileLogger.FactoryLoggerMethod(ex.Message + "\n" + ex.StackTrace + "\n" + ex.Source, LogLevel.Critical, "DOUbigeo.SelectAll");
//Bubble error to caller and encapsulate Exception object
throw new Exception("EmployeesRepository::SelectAll::Error occured.", ex);
}
}
public bool Insert(EmployeesEntity entity)
{
try
{
var sb = new StringBuilder();
sb.Append("SET DATEFORMAT DMY; ");
sb.Append("INSERT [HR].[Employees] ");
sb.Append("( ");
sb.Append("[Id], ");
sb.Append("[Name], ");
sb.Append("[Age], ");
sb.Append("[HiringDate], ");
sb.Append("[GrossSalary], ");
sb.Append("[ModifiedDate] ");
sb.Append(") ");
sb.Append("VALUES ");
sb.Append("( ");
sb.Append(" @intId, ");
sb.Append(" @chnName, ");
sb.Append(" @intAge, ");
sb.Append(" @dtmHiringDate, ");
sb.Append(" @decGrossSalary, ");
sb.Append(" ISNULL(@dtmModifiedDate, (getdate())) ");
sb.Append(") ");
sb.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Employees] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intId", CsType.Int, ParameterDirection.Input, entity.Id);
_dataHandler.AddParameterToCommand(dbCommand, "@chnName", CsType.String, ParameterDirection.Input, entity.Name);
_dataHandler.AddParameterToCommand(dbCommand, "@intAge", CsType.Int, ParameterDirection.Input, entity.Age);
_dataHandler.AddParameterToCommand(dbCommand, "@dtmHiringDate", CsType.DateTime, ParameterDirection.Input, entity.HiringDate);
_dataHandler.AddParameterToCommand(dbCommand, "@decGrossSalary", CsType.Decimal, ParameterDirection.Input, entity.GrossSalary);
_dataHandler.AddParameterToCommand(dbCommand, "@dtmModifiedDate", CsType.DateTime, ParameterDirection.Input, null);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Employees] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("EmployeesRepository::Insert::Error occured.", ex);
}
}
public bool Insert(SuburbEntity entity)
{
try
{
var sb = new StringBuilder();
sb.Append("INSERT [dbo].[Suburb] ");
sb.Append("( ");
sb.Append("[SuburbName],");
sb.Append("[StateId]");
sb.Append(") ");
sb.Append("VALUES ");
sb.Append("( ");
sb.Append(" @SuburbName,");
sb.Append(" @StateId");
sb.Append(") ");
sb.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Suburb] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@SuburbName", CsType.String, ParameterDirection.Input, entity.SuburbName);
_dataHandler.AddParameterToCommand(dbCommand, "@StateId", CsType.String, ParameterDirection.Input, entity.StateId);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Suburb] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("SuburbRepository::Insert::Error occured.", ex);
}
}
public bool Insert(PersonsEntity entity)
{
try
{
var sb = new StringBuilder();
sb.Append("INSERT [TechTest].[Persons] ");
sb.Append("( ");
sb.Append("[PersonId], ");
sb.Append("[Firstname], ");
sb.Append("[Surname], ");
sb.Append("[Gender], ");
sb.Append("[EmailAddress], ");
sb.Append("[PhoneNumber] ");
sb.Append(") ");
sb.Append("VALUES ");
sb.Append("( ");
sb.Append(" @intId, ");
sb.Append(" @chnName, ");
sb.Append(" @intAge, ");
sb.Append(" @dtmHiringDate, ");
sb.Append(" @decGrossSalary, ");
sb.Append(" ISNULL(@dtmModifiedDate, (getdate())) ");
sb.Append(") ");
sb.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Persons] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@personId", CsType.Int, ParameterDirection.Input, entity.PersonId);
_dataHandler.AddParameterToCommand(dbCommand, "@firstName", CsType.Int, ParameterDirection.Input, entity.Firstname);
_dataHandler.AddParameterToCommand(dbCommand, "@surname", CsType.Int, ParameterDirection.Input, entity.Surname);
_dataHandler.AddParameterToCommand(dbCommand, "@gender", CsType.Int, ParameterDirection.Input, entity.Gender);
_dataHandler.AddParameterToCommand(dbCommand, "@emailAddress", CsType.Int, ParameterDirection.Input, entity.EmailAddress);
_dataHandler.AddParameterToCommand(dbCommand, "@phoneNumber", CsType.Int, ParameterDirection.Input, entity.PhoneNumber);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Persons] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("PersonsRepository::Insert::Error occured.", ex);
}
}
// Insert function will insert given parameters into record in SQL Server
public bool Insert(SessionsEntity entity)
{
try
{
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Sessions] can't be null. ");
}
// CODE BELOW USES A STORED PROCEDURE TO INSERT INTO SESSIONS TABLES
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "Session_Insert";
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intSitterID", CsType.Int, ParameterDirection.Input, entity.SitterID);
_dataHandler.AddParameterToCommand(dbCommand, "@intOwnerID", CsType.Int, ParameterDirection.Input, entity.OwnerID);
_dataHandler.AddParameterToCommand(dbCommand, "@chnStatus", CsType.String, ParameterDirection.Input, entity.Status);
_dataHandler.AddParameterToCommand(dbCommand, "@decFee", CsType.Decimal, ParameterDirection.Input, entity.Fee);
_dataHandler.AddParameterToCommand(dbCommand, "@dtmDate", CsType.DateTime, ParameterDirection.Input, entity.Date);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Sessions] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("SessionsRepository::Insert::Error occured.", ex);
}
}
