private void OnCopyQuery()
{
Model.QueriesRow NewRow = ActiveModel.Queries.NewQueriesRow();
foreach (DataColumn col in ActiveModel.Queries.Columns)
{
if (col.ColumnName != "PK")
{
NewRow[col.ColumnName] = SelectedQuery[col.ColumnName];
}
}
ActiveModel.Queries.AddQueriesRow(NewRow);
foreach (Model.QueryTagMappingsRow Row in SelectedQuery.GetQueryTagMappingsRows())
{
Model.QueryTagMappingsRow NewTagMappingRow = ActiveModel.QueryTagMappings.NewQueryTagMappingsRow();
foreach (DataColumn col in ActiveModel.QueryTagMappings.Columns)
{
if (col.ColumnName == "Query")
{
NewTagMappingRow[col.ColumnName] = NewRow.PK;
}
else if (col.ColumnName != "PK")
{
NewTagMappingRow[col.ColumnName] = Row[col.ColumnName];
}
}
ActiveModel.QueryTagMappings.AddQueryTagMappingsRow(NewTagMappingRow);
}
ActiveModel.Queries.AcceptChanges();
ActiveModel.QueryTagMappings.AcceptChanges();
}
private void RefreshColumnsInsert(string QueryText)
{
List <Model.QueryTagMappingsRow> Columns = new List <Model.QueryTagMappingsRow>();
SqlConnection Connection = new SqlConnection(CurrentQuery.DatabasesRow.ConnectionString);
String StrippedQueryString = QueryText;
foreach (Match match in Regex.Matches(QueryText, "(@[_a-zA-Z]+)"))
{
StrippedQueryString = StrippedQueryString.Replace(match.Value, "''");
}
SqlCommand QueryCommand = new SqlCommand(StrippedQueryString, Connection);
Connection.Open();
SqlDataReader sqlReader = QueryCommand.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable Results = sqlReader.GetSchemaTable();
foreach (DataRow row in Results.Rows)
{
if (!(bool)row["IsReadOnly"])
{
Model.QueryTagMappingsRow NewRow = ActiveModel.QueryTagMappings.NewQueryTagMappingsRow();
NewRow.ColumnName = row["ColumnName"].ToString();
NewRow.Query = CurrentQuery.PK;
NewRow.Tag = 0;
NewRow.OutputToController = false;
Columns.Add(NewRow);
}
}
foreach (Match match in Regex.Matches(QueryText, "(@[_a-zA-Z]+)"))
{
Model.QueryTagMappingsRow NewRow = ActiveModel.QueryTagMappings.NewQueryTagMappingsRow();
NewRow.ColumnName = match.Value;
NewRow.Query = CurrentQuery.PK;
NewRow.Tag = 0;
NewRow.OutputToController = false;
Columns.Add(NewRow);
}
TagMappings = Columns.ToArray();
}
private void ProcessSelectQuery()
{
Log.TraceEnterFunction(this);
SqlConnection sqlConnection = new SqlConnection(ActiveQuery.DatabasesRow.ConnectionString);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(ActiveQuery.QueryText, sqlConnection);
DataTable dataResults = new DataTable();
Log.Info(this, "Performing query: " + ActiveQuery.QueryText);
sqlDataAdapter.Fill(dataResults);
Log.Debug(this, "Query completed");
if (dataResults.Rows.Count > 100)
{
Log.Warn("Warning! Query returned many results (" + dataResults.Rows.Count + " rows)!");
}
else if (dataResults.Rows.Count == 0)
{
Log.Warn("Warning! Query returned zero results!");
}
foreach (DataRow row in dataResults.Rows)
{
foreach (DataColumn col in dataResults.Columns)
{
Model.QueryTagMappingsRow TagMapping = ActiveModel.QueryTagMappings.FirstOrDefault(p => p.Query == ActiveQuery.PK && p.ColumnName == col.ColumnName);
if (TagMapping != null && !TagMapping.IsColumnNameNull() && TagMapping.TagsRow != null && TagMapping.QueriesRow != null)
{
Log.Debug(this, "Mapping result to tag: " + TagMapping.ColumnName + " to tag " + TagMapping.TagsRow);
Model.TagsRow TagToWrite = TagMapping.TagsRow;
ControllerBase ControllerToWrite = TagToWrite.ControllersRow.Controller;
ControllerToWrite.Write(TagToWrite, row[col]);
Log.Info(this, "Wrote column '" + TagMapping.ColumnName + "', value '" + row[col] + "' to tag " + TagToWrite);
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + col.ColumnName + "'");
}
}
}
Log.TraceExitFunction(this);
}
private void RefreshStoredProcColumns(string QueryText)
{
List <Model.QueryTagMappingsRow> Columns = new List <Model.QueryTagMappingsRow>();
SqlConnection conn = new SqlConnection(CurrentQuery.DatabasesRow.ConnectionString);
SqlCommand cmd = new SqlCommand(QueryText, conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in cmd.Parameters)
{
Model.QueryTagMappingsRow NewRow = ActiveModel.QueryTagMappings.NewQueryTagMappingsRow();
NewRow.ColumnName = p.ParameterName;
NewRow.Query = CurrentQuery.PK;
NewRow.Tag = 0;
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue || p.Direction == ParameterDirection.InputOutput)
{
NewRow.OutputToController = true;
}
Columns.Add(NewRow);
}
TagMappings = Columns.ToArray();
}
private void ProcessStoredProcedure()
{
Log.TraceEnterFunction(this);
Log.Info(this, "Beginning STORED PROCEDURE: " + ActiveQuery.QueryText);
SqlConnection sqlConnection = new SqlConnection(ActiveQuery.DatabasesRow.ConnectionString);
SqlCommand sqlCommand = new SqlCommand(ActiveQuery.QueryText, sqlConnection);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlCommand.CommandType = CommandType.StoredProcedure;
foreach (Model.QueryTagMappingsRow tagMapping in ActiveQuery.GetQueryTagMappingsRows())
{
if (tagMapping != null && !tagMapping.IsColumnNameNull() && tagMapping.TagsRow != null && tagMapping.QueriesRow != null)
{
Log.Debug(this, "Setting up tag mapping for column '" + tagMapping.ColumnName + "'");
SqlParameter Parameter = new SqlParameter(tagMapping.ColumnName, null);
if (tagMapping.ColumnName == "@RETURN_VALUE")
{
Parameter.Direction = ParameterDirection.ReturnValue;
}
else if (tagMapping.OutputToController)
{
Parameter.Direction = ParameterDirection.InputOutput;
Parameter.Value = tagMapping.TagsRow.ControllersRow.Controller.Read(tagMapping.TagsRow).ToSqlString();
Log.Info(this, "Read InOut parameter '" + Parameter.ParameterName + "' from tag " + tagMapping.TagsRow + ". Value read=" + Parameter.Value);
}
else
{
Parameter.Direction = ParameterDirection.Input;
Parameter.Value = tagMapping.TagsRow.ControllersRow.Controller.Read(tagMapping.TagsRow).ToSqlString();
Log.Info(this, "Read input parameter '" + Parameter.ParameterName + "' from tag " + tagMapping.TagsRow + ". Value read=" + Parameter.Value);
}
Log.Debug(this, "Parameterization complete for for column '" + tagMapping.ColumnName + "', type=" + Parameter.Direction.ToString());
sqlCommand.Parameters.Add(Parameter);
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + tagMapping.ColumnName + "'");
}
}
sqlConnection.Open();
DataTable dataResults = new DataTable();
sqlDataAdapter.Fill(dataResults);
Log.Debug(this, "Stored procedure execution completed");
foreach (SqlParameter Parameter in sqlCommand.Parameters)
{
if (Parameter.Direction != ParameterDirection.Input)
{
Log.Debug(this, "Mapping output parameter '" + Parameter.ParameterName + "'");
Model.QueryTagMappingsRow TagMapping = ActiveModel.QueryTagMappings.FirstOrDefault(p => p.Query == ActiveQuery.PK && p.ColumnName == Parameter.ParameterName);
if (TagMapping != null && !TagMapping.IsColumnNameNull() && TagMapping.TagsRow != null && TagMapping.QueriesRow != null)
{
Model.TagsRow TagToWrite = TagMapping.TagsRow;
ControllerBase ControllerToWrite = TagToWrite.ControllersRow.Controller;
ControllerToWrite.Write(TagToWrite, Parameter.Value);
Log.Info(this, "Wrote output parameter '" + Parameter.ParameterName + "', value '" + Parameter.Value.ToString() + "' to tag '" + TagToWrite + "'");
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + Parameter.ParameterName + "'");
}
}
}
Log.TraceExitFunction(this);
}
