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