private static ConvertedQuery ConvertToMSSqlSyntax(ISqlFunctionInfo queryInfo, IDictionary <string, object> parameterNames)
{
ConvertedQuery convertedQuery = new ConvertedQuery();
StringBuilder builder = new StringBuilder();
foreach (string name in parameterNames.Keys)
{
builder.Append(name);
builder.Append("|");
}
builder.Remove(builder.Length - 1, 1);
string regExp = builder.ToString();
MatchCollection collection = new Regex(regExp).Matches(queryInfo.Command);
IDictionary <string, object> oleDbParams = new Dictionary <string, object>();
int i = 1;
foreach (Match match in collection)
{
oleDbParams.Add("@sp" + i, parameterNames[match.Value.Trim()]);
i++;
}
convertedQuery.Parameters = oleDbParams;
convertedQuery.Query = queryInfo.Command;
foreach (string name in parameterNames.Keys)
{
convertedQuery.Query = convertedQuery.Query.Replace(name, "?");
}
return(convertedQuery);
}
private string RunOleDbSequel(string connectionString, IDictionary <string, object> parameters)
{
string query = _functionInfo.Command;
IDictionary <string, object> queryParameters = parameters;
if (ShouldConvert(_functionInfo, parameters))
{
ConvertedQuery convertedQuery = ConvertToMSSqlSyntax(_functionInfo, parameters);
query = convertedQuery.Query;
queryParameters = convertedQuery.Parameters;
}
DataSet dataSet = null;
string result = string.Empty;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand command = new OleDbCommand(query, connection))
{
if (_functionInfo.IsStoredProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
foreach (KeyValuePair <string, object> parm in queryParameters)
{
command.Parameters.Add(new OleDbParameter(parm.Key, parm.Value));
}
connection.Open();
if (_functionInfo.IsQuery)
{
dataSet = new DataSet();
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(dataSet);
if (dataSet != null && !dataSet.HasChanges())
{
StringWriter writer = new StringWriter();
dataSet.WriteXml(writer);
result = writer.ToString();
}
}
}
else
{
command.ExecuteNonQuery();
}
}
}
return(result);
}
private static ConvertedQuery ConvertToMSSqlSyntax(ISqlFunctionInfo queryInfo, IDictionary<string, object> parameterNames)
{
ConvertedQuery convertedQuery = new ConvertedQuery();
StringBuilder builder = new StringBuilder();
foreach (string name in parameterNames.Keys)
{
builder.Append(name);
builder.Append("|");
}
builder.Remove(builder.Length - 1, 1);
string regExp = builder.ToString();
MatchCollection collection = new Regex(regExp).Matches(queryInfo.Command);
IDictionary<string, object> oleDbParams = new Dictionary<string, object>();
int i = 1;
foreach (Match match in collection)
{
oleDbParams.Add("@sp" + i, parameterNames[match.Value.Trim()]);
i++;
}
convertedQuery.Parameters = oleDbParams;
convertedQuery.Query = queryInfo.Command;
foreach (string name in parameterNames.Keys)
{
convertedQuery.Query = convertedQuery.Query.Replace(name, "?");
}
return convertedQuery;
}
