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