/// <summary>
/// Fills the UI field txtSql
/// If "original Query" is checked:
/// Fill the field txtSql.Text with the appropriate query text and add parameters
/// Else:
/// Fill the text field txtSql.Text with sql
/// </summary>
private void FillSqlText()
{
if (this.chkUseOriginal.Checked)
{
SqlQueryText qt = this.queryInfos[0];
if (this.radioQuery2.Checked)
{
qt = this.queryInfos[1];
}
String s = qt.Text + "\r\n";
s += "-------------------------------";
for (int i = 0; i < qt.Params.Length; i++)
{
ParameterText param = qt.Params[i];
if (param.SqlType == "String")
{
s += "\r\n" + param.Name + " [" + param.SqlType + "]: " + Utils.QuoteString(param.Value);
}
else
{
s += "\r\n" + param.Name + " [" + param.SqlType + "]: " + param.Value;
}
}
this.txtSql.Text = s;
}
else
{
this.txtSql.Text = this.currentSql;
}
}
private SqlQueryText[] GetFullQueryInfo(DataContext dataContext, IQueryable query)
{
System.Data.Common.DbCommand dbCommand = dataContext.GetCommand(query);
SqlQueryText[] result = new SqlQueryText[1];
for (int i = 0, n = 1; i < n; i++)
{
result[i].Text = dbCommand.CommandText;
int nParams = dbCommand.Parameters.Count;
result[i].Params = new ParameterText[nParams];
for (int j = 0; j < nParams; j++)
{
ParameterText param = new ParameterText();
System.Data.Common.DbParameter pInfo = dbCommand.Parameters[j];
param.Name = pInfo.ParameterName;
param.SqlType = pInfo.DbType.ToString();
Console.WriteLine("Before SqlType= " + param.SqlType);
object paramValue = pInfo.Value;
if (paramValue == null)
{
param.Value = null;
}
else
{
param.Value = pInfo.Value.ToString();
}
result[i].Params[j] = param;
}
}
return(result);
}
/// <summary>
/// fill sql1 and sql2 with the original sql texts
/// </summary>
private void InitSqlTexts()
{
SqlQueryText qt1 = this.queryInfos[0];
this.sql1 = Utils.GetQueryTextWithParams(qt1);
if (twoQueries)
{
SqlQueryText qt2 = this.queryInfos[1];
this.sql2 = Utils.GetQueryTextWithParams(qt2);
}
}
/// <summary>
/// Create a SqlCommand by creating parameters from the strings in qt.Params
/// and using the text in qt.Text
/// </summary>
/// <param name="qt">SqlQueryText input</param>
/// <param name="conn">SqlConnection to associate with the SqlCommand</param>
/// <returns>SqlCommand</returns>
private static SqlCommand GetSqlCommand(SqlQueryText qt, SqlConnection conn)
{
SqlCommand cmd = new SqlCommand(qt.Text, conn);
foreach (ParameterText param in qt.Params)
{
System.Data.SqlClient.SqlParameter sqlParam = cmd.CreateParameter();
sqlParam.ParameterName = param.Name;
Console.WriteLine("Param.name:" + param.Name + "Param.SqlType:" + param.SqlType);
object val = GetObject(param.Value, param.SqlType);
sqlParam.Value = val;
cmd.Parameters.Add(sqlParam);
}
return(cmd);
}
/// <summary>
/// Translates the sql query text (which contains parameter names)
/// and the information about the parameters (SqlType and value as given by .ToString())
/// into one sql string (that contains text representations of the values).
/// Executing this string should be the same as executing the original query except in
/// corner cases (e.g. a string contains a parameter name, a decimal is given with too high precision)
/// </summary>
/// <param name="qt">query text and information about the parameters</param>
/// <returns>the sql string to execute</returns>
internal static string GetQueryTextWithParams(SqlQueryText qt)
{
string s = qt.Text;
foreach (ParameterText param in qt.Params)
{
string val;
switch (param.SqlType.ToString())
{
case "String":
case "Guid":
case "DateTime":
val = QuoteString(param.Value);
break;
case "Boolean":
if (param.Value == "True")
{
val = "1";
}
else if (param.Value == "False")
{
val = "0";
}
else
{
throw new Exception("Boolean value other than True or False");
}
break;
case "Time":
TimeSpan ts = TimeSpan.Parse(param.Value);
val = ts.Ticks.ToString();
break;
default:
val = param.Value;
break;
}
s = s.Replace(param.Name, val);
}
return(s);
}
/// <summary>
/// Sets up this form with the data from the query
/// </summary>
/// <param name="expression">The query as expression text</param>
/// <param name="infos">The Sql text and parameter descriptions for the query</param>
/// <param name="connection">The connection string</param>
internal void SetTexts(string expression, SqlQueryText[] infos, string connection)
{
this.txtExpression.Text = expression;
this.queryInfos = infos;
this.connection = connection;
this.radioQuery1.Checked = true;
if (this.queryInfos.Length > 1) {
this.twoQueries = true;
this.radioQuery1.Visible = true;
this.radioQuery2.Visible = true;
} else {
this.twoQueries = false;
}
this.chkUseOriginal.Checked = false;
this.btnQuery.Visible = true;
this.InitSqlTexts();
this.FillSqlText();
this.uiInitialized = true;
}
public SqlQueryInfo(SqlQueryText[] queries)
{
this.queries = queries;
}
private SqlQueryText[] GetFullQueryInfo(DataContext dataContext, IQueryable query)
{
System.Data.Common.DbCommand dbCommand = dataContext.GetCommand(query);
SqlQueryText[] result = new SqlQueryText[1];
for (int i = 0, n = 1; i < n; i++) {
result[i].Text = dbCommand.CommandText;
int nParams = dbCommand.Parameters.Count ;
result[i].Params = new ParameterText[nParams];
for (int j = 0; j < nParams; j++) {
ParameterText param = new ParameterText();
System.Data.Common.DbParameter pInfo = dbCommand.Parameters[j];
param.Name = pInfo.ParameterName;
param.SqlType = pInfo.DbType.ToString();
Console.WriteLine("Before SqlType= " + param.SqlType);
object paramValue = pInfo.Value;
if (paramValue == null) {
param.Value = null;
} else {
param.Value = pInfo.Value.ToString();
}
result[i].Params[j] = param;
}
}
return result;
}
/// <summary>
/// Translates the sql query text (which contains parameter names)
/// and the information about the parameters (SqlType and value as given by .ToString())
/// into one sql string (that contains text representations of the values).
/// Executing this string should be the same as executing the original query except in
/// corner cases (e.g. a string contains a parameter name, a decimal is given with too high precision)
/// </summary>
/// <param name="qt">query text and information about the parameters</param>
/// <returns>the sql string to execute</returns>
internal static string GetQueryTextWithParams(SqlQueryText qt)
{
string s = qt.Text;
foreach (ParameterText param in qt.Params) {
string val;
switch (param.SqlType.ToString()) {
case "String":
case "Guid":
case "DateTime":
val = QuoteString(param.Value);
break;
case "Boolean":
if (param.Value == "True") {
val = "1";
} else if (param.Value == "False") {
val = "0";
} else {
throw new Exception("Boolean value other than True or False");
}
break;
case "Time":
TimeSpan ts = TimeSpan.Parse(param.Value);
val = ts.Ticks.ToString();
break;
default:
val = param.Value;
break;
}
s = s.Replace(param.Name, val);
}
return s;
}
/// <summary>
/// Create a SqlCommand by creating parameters from the strings in qt.Params
/// and using the text in qt.Text
/// </summary>
/// <param name="qt">SqlQueryText input</param>
/// <param name="conn">SqlConnection to associate with the SqlCommand</param>
/// <returns>SqlCommand</returns>
private static SqlCommand GetSqlCommand(SqlQueryText qt, SqlConnection conn)
{
SqlCommand cmd = new SqlCommand(qt.Text, conn);
foreach (ParameterText param in qt.Params) {
System.Data.SqlClient.SqlParameter sqlParam = cmd.CreateParameter();
sqlParam.ParameterName = param.Name;
Console.WriteLine("Param.name:" + param.Name + "Param.SqlType:" + param.SqlType);
object val = GetObject(param.Value, param.SqlType);
sqlParam.Value = val;
cmd.Parameters.Add(sqlParam);
}
return cmd;
}
// Execute queries using the original information about the query
// This method constructs the query and parameters as in Linq to SQL
// (not using the parameter values as part of the Sql text)
internal static void ExecuteOriginalQueries(DataSet ds1, DataSet ds2, SqlQueryText[] infos, string connectionString)
{
SqlConnection conn = new SqlConnection(connectionString);
// retrieve data
using (conn) {
conn.Open();
SqlCommand cmd1 = GetSqlCommand(infos[0], conn);
SqlDataAdapter adapter1 = new SqlDataAdapter(cmd1);
adapter1.Fill(ds1);
if (infos.Length > 1) {
SqlCommand cmd2 = GetSqlCommand(infos[1], conn);
SqlDataAdapter adapter2 = new SqlDataAdapter(cmd2);
adapter2.Fill(ds2);
}
}
}