public void TestInsertDbOperation_002_InsertWithNulls() { // Initializes the Insert Statement InsertDbOperation insert = new InsertDbOperation(new Table("testtable", "dbo", "0")) { Assignments = new AssignExpression[] { new AssignExpression(new Field("field1"), "value1"), new AssignExpression(new Field("field2"), null), new AssignExpression(new Field("field3"), 200), new AssignExpression(new Field("field4"), new DateTime(2020, 1, 1)), } }; string statement = myDialect.Insert(insert); string expectedStatement = "INSERT INTO [dbo].[testtable]\r\n" + " (\r\n" + " [field1],\r\n" + " [field2],\r\n" + " [field3],\r\n" + " [field4]\r\n" + " )\r\n" + " VALUES (\r\n" + " 'value1',\r\n" + " NULL,\r\n" + " 200,\r\n" + " '2020-01-01 00:00:00'\r\n" + " )\r\n"; // Assertion Assert.AreEqual <string>(expectedStatement, statement); }
/// <summary> /// Inserts data into the database using the Microsoft SQL Server T-SQL Dialect. /// </summary> /// <param name="insert">The <see cref="InsertDbOperation"/></param> /// <returns>A string containing the T-SQL</returns> public override string Insert(InsertDbOperation insert) { // String Builders StringBuilder sbInsert = new StringBuilder(); // Validate Table if (insert.Table == null) { throw new Exception("Insert requires a table. Table is null."); } if (String.IsNullOrEmpty(insert.Table.Name)) { throw new Exception("Insert requires a table. Table is blank or empty."); } // Validate Assignments if (insert.Assignments == null) { throw new Exception("No assignments defined for the Insert Operation"); } if (insert.Assignments.Length == 0) { throw new Exception("Zero assignments defined for the Insert Operation"); } // Create arrays for insert data string[] insertFields = new string[insert.Assignments.Length]; string[] insertValues = new string[insert.Assignments.Length]; // Now format the sintax for (int f = 0; f < insert.Assignments.Length; f++) { insertFields[f] = FormatExpressionField(insert.Assignments[f].Field1); insertValues[f] = FormatExpressionField(insert.Assignments[f].Field2); } // Format for output sbInsert.Append("INSERT INTO "); sbInsert.AppendLine(FormatTable(insert.Table)); sbInsert.AppendLine(" ("); sbInsert.Append(" "); sbInsert.AppendLine(String.Join(",\r\n ", insertFields)); sbInsert.AppendLine(" )"); sbInsert.AppendLine(" VALUES ("); sbInsert.Append(" "); sbInsert.AppendLine(String.Join(",\r\n ", insertValues)); sbInsert.AppendLine(" )"); return(sbInsert.ToString()); }
public void TestInsertDbOperation_003_EscapedString() { // Initializes the Insert Statement InsertDbOperation insert = new InsertDbOperation(new Table("testtable", "dbo", "0")) { Assignments = new AssignExpression[] { new AssignExpression(new Field("field1"), "val'ue1"), } }; string statement = myDialect.Insert(insert); string expectedStatement = "INSERT INTO [dbo].[testtable]\r\n" + " (\r\n" + " [field1]\r\n" + " )\r\n" + " VALUES (\r\n" + " 'val''ue1'\r\n" + " )\r\n"; // Assertion Assert.AreEqual <string>(expectedStatement, statement); }
/// <summary> /// Creates a new instance of the <typeparamref name="TModel"/> into the database /// </summary> /// <param name="entity">The data to be created</param> /// <param name="dbConnection">Reference to an existing database connection</param> /// <param name="dbTransaction">Reference to an existing database transaction</param> /// <returns>An instance of the recently created object</returns> /// <exception cref="PrimaryKeyViolationException">Thrown if the record already exists</exception> public virtual TModel Create(TCreateDTO entity, IDbConnection dbConnection, IDbTransaction dbTransaction) { // Ensure Model is Valid if (!IsModelValid) { throw new Exception("Model is invalid"); } // Initializes a transaction, if applicable IDbConnection connection = dbConnection; IDbTransaction transaction = dbTransaction; try { // Creates a connection if no connection has been passed if (connection == null) { connection = DbContext.GetConnection(); } // Initializes a transaction if no transaction has been passed if (transaction == null) { transaction = connection.BeginTransaction(); } // Map Dto to Model var model = entity.MapToModel(); // Calls the "OnBeforeCreate" method if (!OnBeforeCreate(model, entity, connection, transaction)) { throw new Exception("Create operation cancelled"); } // Verify Primary Key Violation var models = Get(GetPrimaryKeyFilters(model), connection, transaction); if (models.Count > 0) { throw new PrimaryKeyViolationException($"Duplicated Key for '{typeof(TModel).Name}'"); } // Calls Model Format function FormatModel(model); // Define AssignExpressions var assignExpressions = new List <AssignExpression>(); foreach (var fieldInfo in Fields) { // Define the Assign Expression Object AssignExpression assignExpression; // For String type, shrink character count (when size is specified) if ((fieldInfo.Attribute.Type == typeof(string)) && (fieldInfo.Attribute.Size > 0)) { // Shrink Field Contents, if applicable var fieldContents = fieldInfo.Property.GetValue(model)?.ToString().PadRight(fieldInfo.Attribute.Size, ' ').Substring(0, fieldInfo.Attribute.Size).Trim(); // Apply New Contents to the Model //if (fieldContents == null) fieldContents = ""; fieldInfo.Property.SetValue(model, fieldContents); // Apply to the AssignExpression assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), fieldContents); } else { // Check for Enum types if (fieldInfo.Property.PropertyType.IsEnum) { // Enumerations have a special treatment as its value need to be parsed from the string that represents it var enumValue = fieldInfo.Property.GetValue(model); object finalValue = null; // If value is null, force it to zero if (enumValue == null) { finalValue = 0; } else { finalValue = Convert.ChangeType(enumValue, fieldInfo.Property.PropertyType.GetEnumUnderlyingType()); } assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), finalValue); } else { assignExpression = new AssignExpression(new Field(fieldInfo.Attribute.Name), fieldInfo.Property.GetValue(model)); } } assignExpressions.Add(assignExpression); } // Create the Insert Operation with the previously setup Assign Expressions var insert = new InsertDbOperation(DatabaseTableAttribute.Name) { Assignments = assignExpressions.ToArray() }; // Runs the Insert Statement DbContext.ExecuteNonQuery(insert, connection, transaction); // Calls the "OnAfterCreate" method OnAfterCreate(model, entity, connection, transaction); if (dbTransaction == null) { transaction.Commit(); } // Returns the Entity Created return(model); } catch { // Check if this action is joining a transaction if (dbTransaction == null) { transaction.Rollback(); } throw; } finally { // Closes the connection created on this method if (dbConnection == null) { if (connection?.State == ConnectionState.Open) { connection.Close(); } } } }
/// <summary> /// Converts an Insert Into Database Operation into a valid T-SQL Statement /// </summary> /// <param name="insert">The <see cref="InsertDbOperation"/></param> /// <returns>A string containing the T-SQL</returns> public abstract string Insert(InsertDbOperation insert);