/// <summary>
/// 填充 UI 字段 txtSql
/// 如果选中“原始查询”:
/// 使用相应的查询文本填充字段 txtSql.Text，并添加参数
/// 否则:
/// 使用 sql 填充文本字段 txtSql.Text
/// </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 static 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();
object paramValue = pInfo.Value;
if (paramValue == null)
{
param.Value = null;
}
else
{
param.Value = pInfo.Value.ToString();
}
result[i].Params[j] = param;
}
}
return(result);
}
/// <summary>
/// 使用原始 sql 文本填充 sql1 和 sql2
/// </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>
/// Формирование SqlCommand путем создания параметров из строк в qt.Params
/// и текста в qt.Text
/// </summary>
/// <param name="qt">Входные данные SqlQueryText</param>
/// <param name="conn">Подключение SqlConnection, связываемое с 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;
object val = GetObject(param.Value, param.SqlType);
sqlParam.Value = val;
cmd.Parameters.Add(sqlParam);
}
return(cmd);
}
/// <summary>
/// Преобразование текста SQL-запроса (в котором содержатся имена параметров)
/// и сведений о параметрах (SqlType и значения, возвращенные методом .ToString())
/// в одну строку SQL (в которой содержатся текстовые представления значений).
/// Выполнение этой строки не должно отличаться от выполнения исходного запроса, за исключением
/// предельных вариантов (например, строка содержит имя параметра или для десятичного числа указана слишком большая точность)
/// </summary>
/// <param name="qt">текст запроса и сведения о параметрах</param>
/// <returns>строка SQL для выполнения</returns>
internal static string GetQueryTextWithParams(SqlQueryText qt)
{
string s = qt.Text;
for (int i = qt.Params.Length - 1; i >= 0; i--)
{
ParameterText param = qt.Params[i];
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 ArgumentException("Boolean value other than True or False");
}
break;
case "Time":
TimeSpan ts = TimeSpan.Parse(param.Value);
val = ts.Ticks.ToString(CultureInfo.CurrentUICulture);
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="connectionString">The connectionString string</param>
internal void SetTexts(string expression, SqlQueryText[] infos, string connectionString)
{
this.txtExpression.Text = expression;
this.queryInfos = infos;
this.connection = connectionString;
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 static 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();
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;
for (int i=qt.Params.Length-1; i >= 0; i--){
ParameterText param = qt.Params[i];
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 ArgumentException("Boolean value other than True or False");
}
break;
case "Time":
TimeSpan ts = TimeSpan.Parse(param.Value);
val = ts.Ticks.ToString(CultureInfo.CurrentUICulture);
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;
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);
}
}
}