protected override void PrepareCommand(DbCommand cmd)
{
base.PrepareCommand(cmd);
foreach (DbParameter p in cmd.Parameters)
{
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue)
{
continue;
}
object value = p.Value;
if (value == DBNull.Value)
{
continue;
}
Type type = value.GetType();
MySqlParameter mySqlParam = (MySqlParameter)p;
if (mySqlParam.DbType != DbType.Guid && type == typeof(Guid))
{
mySqlParam.MySqlDbType = MySqlDbType.VarChar;
mySqlParam.Size = 36;
continue;
}
if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan))
{
mySqlParam.MySqlDbType = MySqlDbType.Double;
mySqlParam.Value = ((TimeSpan)value).TotalDays;
continue;
}
switch (p.DbType)
{
case DbType.Binary:
if (((byte[])value).Length > 2000)
{
mySqlParam.MySqlDbType = MySqlDbType.LongBlob;
}
break;
case DbType.Time:
mySqlParam.MySqlDbType = MySqlDbType.DateTime;
break;
case DbType.DateTime:
mySqlParam.MySqlDbType = MySqlDbType.DateTime;
break;
case DbType.AnsiString:
if (value.ToString().Length > 65535)
{
mySqlParam.MySqlDbType = MySqlDbType.LongText;
}
break;
case DbType.String:
if (value.ToString().Length > 65535)
{
mySqlParam.MySqlDbType = MySqlDbType.LongText;
}
break;
case DbType.Object:
mySqlParam.MySqlDbType = MySqlDbType.LongText;
p.Value = SerializationManager.Instance.Serialize(value);
break;
}
}
//replace mysql specific function names in cmd.CommandText
cmd.CommandText = cmd.CommandText
.Replace("LEN(", "LENGTH(")
.Replace("GETDATE()", "NOW()")
.Replace("GETUTCDATE()", "UTC_TIMESTAMP()")
.Replace("DATEPART(Year,", "YEAR(")
.Replace("DATEPART(Month,", "MONTH(")
.Replace("DATEPART(Day,", "DAY(");
//replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX()
int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length);
string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty);
startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex);
}
}
protected override void PrepareCommand(DbCommand cmd)
{
base.PrepareCommand(cmd);
foreach (DbParameter p in cmd.Parameters)
{
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue)
{
if (p.DbType == DbType.Object)
{
OracleParameter op = (OracleParameter)p;
op.OracleType = OracleType.Cursor;
}
continue;
}
object value = p.Value;
if (value == DBNull.Value)
{
continue;
}
Type type = value.GetType();
OracleParameter oracleParam = (OracleParameter)p;
if (oracleParam.DbType != DbType.Guid && type == typeof(Guid))
{
oracleParam.OracleType = OracleType.Char;
oracleParam.Size = 36;
continue;
}
if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan))
{
oracleParam.OracleType = OracleType.Double;
oracleParam.Value = ((TimeSpan)value).TotalDays;
continue;
}
switch (p.DbType)
{
case DbType.Binary:
if (((byte[])value).Length > 2000)
{
oracleParam.OracleType = OracleType.Blob;
}
break;
case DbType.Time:
oracleParam.OracleType = OracleType.DateTime;
break;
case DbType.DateTime:
oracleParam.OracleType = OracleType.DateTime;
break;
case DbType.AnsiString:
if (value.ToString().Length > 4000)
{
oracleParam.OracleType = OracleType.Clob;
}
break;
case DbType.String:
if (value.ToString().Length > 2000)
{
oracleParam.OracleType = OracleType.NClob;
}
break;
case DbType.Object:
oracleParam.OracleType = OracleType.NClob;
p.Value = SerializationManager.Instance.Serialize(value);
break;
}
}
//replace oracle specific function names in cmd.CommandText
cmd.CommandText = cmd.CommandText
.Replace("LEN(", "LENGTH(")
.Replace("SUBSTRING(", "SUBSTR(")
.Replace("GETDATE()", "TO_CHAR(CURRENT_DATE,'DD-MON-YYYY HH:MI:SS')")
.Replace("GETUTCDATE()", "TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS')");
//replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX()
int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length);
string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty);
startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex);
}
//replace DATEPART with TO_CHAR(CURRENT_DATE,'XXXX')
startIndexOfCharIndex = cmd.CommandText.IndexOf("DATEPART(");
if (startIndexOfCharIndex > 0)
{
cmd.CommandText = cmd.CommandText
.Replace("DATEPART(Year", "TO_CHAR('YYYY'")
.Replace("DATEPART(Month", "TO_CHAR('MM'")
.Replace("DATEPART(Day", "TO_CHAR('DD'");
startIndexOfCharIndex = cmd.CommandText.IndexOf("TO_CHAR(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "TO_CHAR(".Length);
string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "TO_CHAR(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "TO_CHAR(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "TO_CHAR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty);
startIndexOfCharIndex = cmd.CommandText.IndexOf("TO_CHAR(", endIndexOfCharIndex);
}
}
}
protected override void PrepareCommand(DbCommand cmd)
{
base.PrepareCommand(cmd);
foreach (DbParameter p in cmd.Parameters)
{
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue)
{
continue;
}
object value = p.Value;
if (value == DBNull.Value)
{
continue;
}
Type type = value.GetType();
OleDbParameter oleDbParam = (OleDbParameter)p;
if (oleDbParam.DbType != DbType.Guid && type == typeof(Guid))
{
oleDbParam.OleDbType = OleDbType.Char;
oleDbParam.Size = 36;
continue;
}
if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan))
{
oleDbParam.OleDbType = OleDbType.Double;
oleDbParam.Value = ((TimeSpan)value).TotalDays;
continue;
}
switch (p.DbType)
{
case DbType.Binary:
if (((byte[])value).Length > 2000)
{
oleDbParam.OleDbType = OleDbType.LongVarBinary;
}
break;
case DbType.Time:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = value.ToString();
break;
case DbType.DateTime:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = value.ToString();
break;
case DbType.AnsiString:
if (value.ToString().Length > 4000)
{
oleDbParam.OleDbType = OleDbType.LongVarChar;
}
break;
case DbType.String:
if (value.ToString().Length > 2000)
{
oleDbParam.OleDbType = OleDbType.LongVarWChar;
}
break;
case DbType.Object:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = SerializationManager.Instance.Serialize(value);
break;
}
}
//replace msaccess specific function names in cmd.CommandText
cmd.CommandText = cmd.CommandText
.Replace("] [", "] AS [")
.Replace("UPPER(", "UCASE(")
.Replace("LOWER(", "LCASE(")
.Replace("SUBSTRING(", "MID(")
.Replace("GETDATE()", "DATE() + TIME()")
.Replace("GETUTCDATE()", "DATE() + TIME()")
.Replace("DATEPART(Year", "DATEPART('yyyy'")
.Replace("DATEPART(Month", "DATEPART('m'")
.Replace("DATEPART(Day", "DATEPART('d'")
.Replace("TO_NUMBER(", "CDBL(")
.Replace("CAST('", "CDATE('").Replace("' AS datetime", "')");
//replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX()
int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length);
string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty);
startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex);
}
}
protected override void PrepareCommand(DbCommand cmd)
{
base.PrepareCommand(cmd);
foreach (DbParameter p in cmd.Parameters)
{
if (cmd.CommandType == CommandType.StoredProcedure)
{
p.ParameterName = string.Empty;
}
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue)
{
continue;
}
object value = p.Value;
if (value == DBNull.Value)
{
continue;
}
Type type = value.GetType();
NpgsqlParameter npgParam = (NpgsqlParameter)p;
if (npgParam.DbType != DbType.Guid && type == typeof(Guid))
{
npgParam.DbType = DbType.String;
npgParam.Size = 36;
continue;
}
if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan))
{
npgParam.DbType = DbType.String;
npgParam.Value = "'" + ((TimeSpan)value).TotalDays + " days'";
continue;
}
switch (p.DbType)
{
case DbType.String:
npgParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text;
p.Value = value.ToString();
break;
case DbType.Object:
npgParam.DbType = DbType.String;
p.Value = SerializationManager.Instance.Serialize(value);
break;
}
}
//replace postgresql specific function names in cmd.CommandText
cmd.CommandText = cmd.CommandText
.Replace("SUBSTRING(", "substr(")
.Replace("LEN(", "length(")
.Replace("GETDATE()", "current_timestamp")
.Replace("GETUTCDATE()", "LOCALTIMESTAMP")
.Replace("DATEPART(Year", "date_part('year'")
.Replace("DATEPART(Month", "date_part('month'")
.Replace("DATEPART(Day", "date_part('day'");
//replace CHARINDEX with strpos and reverse seqeunce of param items in CHARINDEX()
int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length);
string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "strpos(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty);
startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex);
}
}
