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