public void ShouldHandleParameterlessStoredProcedure()
{
var query = "my_sp";
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement(query, CommandType.StoredProcedure, null);
Assert.That(executableSql, Is.EqualTo("EXEC my_sp "));
}
public void ShouldHandleParameterlessQuery()
{
var query = "select * from my_table";
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement(query, CommandType.Text, null);
Assert.That(executableSql, Is.EqualTo(query));
}
public void ShouldCreateExecutableQuery()
{
DateTime dt = new DateTime(2010, 12, 31, 23, 59, 59);
float? nullable = null;
decimal decimalValue = 123.456m;
string sql =
"Select * FROM [dbo].[TableX] Where Column1 > @value1 and Column2 = @value2 and Column3 = @value3 and Column4 = @value4 and Column5 = @value5 and Column6 = @value6 and Column7 = @value7";
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(0.CreateSqlParameter("value1"));
parameters.Add("hello world".CreateSqlParameter("value2"));
parameters.Add(nullable.CreateSqlParameter("value3"));
parameters.Add(decimalValue.CreateSqlParameter("value4"));
parameters.Add(true.CreateSqlParameter("value5"));
var sqlParameter = 321.12m.CreateSqlParameter("value6");
sqlParameter.Size = 10;
sqlParameter.Precision = 2;
parameters.Add(sqlParameter);
parameters.Add(dt.CreateSqlParameter("value7", SqlDbType.DateTime));
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement(sql, parameters);
Trace.WriteLine(executableSql);
}
public int TimeOut(AttendanceModel model)
{
try
{
using (var SqlConnection = new SqlConnection(ConnectionString))
{
SqlConnection.Open();
using (var sqlTransaction = SqlConnection.BeginTransaction())
{
var sql = $" INSERT INTO {TableName}(EmployeeId,DateTimeOut,RegularHoursRendered,OTHoursRendered)" +
$"VALUES(@EmployeeId,@DateTimeOut,@RegularHoursRendered,@OTHoursRendered){SelectScopeIdentitySql()}";
var insertSql = SqlDebugHelper.CreateExecutableSqlStatement(sql, CommandType.Text,
new List <SqlParameter> {
new SqlParameter("EmployeeId", model.EmployeeId),
new SqlParameter("DateTimeOut", model.DateTimeOut),
new SqlParameter("RegularHoursRendered", model.RegularHoursRendered),
new SqlParameter("OTHoursRendered", model.OTHoursRendered),
});
var id = DatabaseHelper.ExecuteScalar <int>(sqlTransaction, CommandType.Text, insertSql);
sqlTransaction.Commit();
return(id);
}
}
}
catch (Exception e)
{
// failed transaction
return(-1);
}
}
private DbDataReader RetryOnDeadlock()
{
var query = Comando.CommandText;
SqlDataReader reader = null;
try
{
var pars = Comando.Parameters.Cast <SqlParameter>().ToList();
var rs = SqlDebugHelper.CreateExecutableSqlStatement(Comando.CommandText, Comando.CommandType, pars).Replace("@@", "@");
query = rs;
bool retry = false;
do
{
try
{
var sqlConnection = new SqlConnection(Comando.Connection.ConnectionString);
var sqlCommand = new SqlCommand(query, sqlConnection);
reader = sqlCommand.ExecuteReader();
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
retry = true;
}
}
} while (retry);
}
catch (Exception ex)
{
// ignored
}
return(reader);
}
public void ShouldThrowExceptionIfSqlIsNull()
{
Assert.That(() => SqlDebugHelper.CreateExecutableSqlStatement(null, null),
Throws.Exception.TypeOf <ArgumentNullException>());
Assert.That(() => SqlDebugHelper.CreateExecutableSqlStatement(null, CommandType.StoredProcedure, null),
Throws.Exception.TypeOf <ArgumentNullException>());
}
public void ShouldIgnoreNullValuedSqlParameters()
{
var list = new List <SqlParameter>()
{
null, null, null
};
string sp = SqlDebugHelper.CreateExecutableSqlStatement("sp", list);
Assert.That(sp, Is.EqualTo("EXEC sp "));
}
public void ShouldNotUseNPrefixForVarchar()
{
var parameter = "test".CreateSqlParameter("param");
parameter.SqlDbType = SqlDbType.VarChar;
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement("my_sp", parameter);
Trace.WriteLine(executableSql);
Assert.That(executableSql, Is.EqualTo("EXEC my_sp @param = 'test'"));
}
public void ShouldFormatDatesInIso8601Format()
{
DateTime dt = new DateTime(2015, 12, 31, 23, 59, 10).AddMilliseconds(12345);
var parameter = dt.CreateSqlParameter("param", SqlDbType.DateTime);
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement("my_sp", parameter);
Trace.WriteLine(executableSql);
Assert.That(executableSql,
Is.EqualTo("EXEC my_sp @param = convert(datetime,'2015-12-31 23:59:22:345', 121)"));
}
public void ShouldCreateCustomImplementation()
{
SqlDebugHelper.SqlGenerator = new CustomerGenerator();
var parameter = "test".CreateSqlParameter("param");
parameter.SqlDbType = SqlDbType.VarChar;
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement("my_sp", parameter);
Trace.WriteLine(executableSql);
Assert.That(executableSql, Is.EqualTo("EXEC my_sp @param = 'ConstantValue'"));
}
public void ShouldHandleGeometry()
{
var geom = SqlGeometry.Point(52.123, 5.321, 4326).CreateSqlParameter("geom");
var geof = SqlGeography.Point(52.123, 5.321, 4326).CreateSqlParameter("geof");
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(geom);
parameters.Add(geof);
var executableSqlStatement = SqlDebugHelper.CreateExecutableSqlStatement("insert into geo VALUES (@geom, @geof)", parameters);
Trace.WriteLine(executableSqlStatement);
}
//private readonly Tracer _tracer;
//private readonly ILogger<HintCommandInterceptor> _logger;
//public HintCommandInterceptor(Tracer tracer, ILogger<HintCommandInterceptor> logger)
//{
// _tracer = tracer;
// _logger = logger;
//}
public override InterceptionResult <DbDataReader> ReaderExecuting(
System.Data.Common.DbCommand command,
CommandEventData eventData,
InterceptionResult <DbDataReader> result)
{
// 在此处操作命令文本等.
//command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
try {
var options = IoC.Resolve <IOptions <MonitorOptions> >()?.Value;
if (options != null && options.UseMonitor)
{
var _tracer = IoC.Resolve <Tracer>();
if (_tracer != null)
{
var traceId = _tracer.CurrentSpan.Context.TraceId.ToHexString();
if (traceId != "00000000000000000000000000000000")
{
var p = new List <SqlParameter>();
foreach (var commandParameter in command.Parameters)
{
p.Add((SqlParameter)commandParameter);
}
var sql = SqlDebugHelper.CreateExecutableSqlStatement(command.CommandText, p.ToArray());
using (_tracer.StartActiveSpan("sql-get-" + CommonHelper.NewSequentialGuid(), out var span))
{
span.SetAttribute(MonitorKeys.request_type,
RequestTypeEnum.Sql.ToString().ToLowerInvariant());
span.SetAttribute(MonitorKeys.sql_raw, command.CommandText);
span.SetAttribute(MonitorKeys.sql_body, sql);
span.End();
}
}
}
}
}
catch (Exception ex) {
var _logger = IoC.Resolve <ILogger <HintCommandInterceptor> >();
_logger.LogError("HintCommandInterceptor error:" + ex.Message);
}
return(base.ReaderExecuting(command, eventData, result));
}
public void ShouldCreateExecutableStoredProcedure()
{
float? nullable = null;
decimal decimalValue = 123.456m;
string sql = "sp_test";
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(0.CreateSqlParameter("value1"));
parameters.Add("hello world".CreateSqlParameter("value2"));
parameters.Add(nullable.CreateSqlParameter("value3"));
parameters.Add(decimalValue.CreateSqlParameter("value4"));
string executableSql = SqlDebugHelper.CreateExecutableSqlStatement(sql, parameters);
Trace.WriteLine(executableSql);
Assert.That(executableSql,
Is.EqualTo("EXEC sp_test @value1 = 0, @value2 = N'hello world', @value3 = null, @value4 = 123.456"));
}
public int Create(EmployeeModel model)
{
try
{
using (var sqlConnection = new SqlConnection(ConnectionString))
{
// we need to open the connection
sqlConnection.Open();
using (var sqlTransaction = sqlConnection.BeginTransaction())
{
// sql insert template
//"$" yung sign na yan parang "++" lang tulad sa java
//"@"=variables para sa fieldname
var sql = $"INSERT INTO {TableName}(LastName, FirstName, MiddleName, Birthdate, Sex, Barangay, Street, City, Citizenship, Religion, CivilStatus)" +
$"VALUES (@lastName, @firstName, @middleName, @bday, @sex, @barangay, @street, @city, @citizenship, @religion, @civilStatus) {SelectScopeIdentitySql()}"; // this adds a select scope_identity() sql is to the query to return the new ID
// generates an insert sql statement with the parameters
var insertSql = SqlDebugHelper.CreateExecutableSqlStatement(sql, CommandType.Text,
new List <SqlParameter> {
new SqlParameter("lastName", model.LastName),
new SqlParameter("firstName", model.FirstName),
new SqlParameter("middleName", model.MiddleName),
new SqlParameter("bday", model.Birthdate),
new SqlParameter("sex", model.Sex),
new SqlParameter("barangay", model.Barangay),
new SqlParameter("street", model.Street),
new SqlParameter("city", model.City),
new SqlParameter("citizenship", model.Citizenship),
new SqlParameter("religion", model.Religion),
new SqlParameter("civilStatus", model.CivilStatus),
});
// returns the new ID
var id = DatabaseHelper.ExecuteScalar <int>(sqlTransaction, CommandType.Text, insertSql);
// INSERT TO ED ATTAINMENT
// sql insert template
sql = "INSERT INTO EducationalAttainments(EmployeeId, SchoolAttended, YearGraduated, Qualification)" +
$"VALUES (@empId, @schoolAttended, @yearGrad, @qualification) {SelectScopeIdentitySql()}";
foreach (var item in model.EducationalAttainments)
{
// generates an insert sql statement with the parameters
insertSql = SqlDebugHelper.CreateExecutableSqlStatement(sql, CommandType.Text,
new List <SqlParameter> {
new SqlParameter("empId", id),
new SqlParameter("schoolAttended", item.SchoolAttended),
new SqlParameter("yearGrad", item.YearGraduated),
new SqlParameter("qualification", item.Qualification)
});
DatabaseHelper.ExecuteScalar <int>(sqlTransaction, CommandType.Text, insertSql);
}
sqlTransaction.Commit();
return(id);
}
}
}
catch (Exception e)
{
// failed transaction
return(-1);
}
}
