/// <summary>
/// Execute the stored procedure represented by the provided parameter and return the count of records affected
/// </summary>
/// <param cref="Socratic.DataAccess.Abstractions.IStoredProcedure{T}" name="storedProc"></param>
/// <returns>Count of affected records</returns>
public async Task <int> ExecuteAsync(IStoredProcedure <int> storedProc) // ToDo: common response object
{
var procInfo = StoredProcedureConfig.Build(storedProc);
await BuildTransactionalConnectionAsync();
return(await context.Database.GetDbConnection().ExecuteAsync(procInfo.Name, procInfo.Parameters, context.Database.CurrentTransaction.GetDbTransaction(), commandType: CommandType.StoredProcedure));
}
/// <summary>
/// Execute the stored procedure represented by the provided parameter and return an IEnumerable of the expected type
/// </summary>
/// <param cref="Socratic.DataAccess.Abstractions.IStoredProcedure{T}" name="storedProc"></param>
/// <typeparam name="T"></typeparam>
/// <returns>A sequence of data of T</returns>
public async Task <IEnumerable <T> > QueryListAsync <T>(IStoredProcedure <T> storedProc)
{
var procInfo = StoredProcedureConfig.Build(storedProc);
await BuildTransactionalConnectionAsync();
return(await context.Database.GetDbConnection().QueryAsync <T>(procInfo.Name, procInfo.Parameters, context.Database.CurrentTransaction.GetDbTransaction(), commandType: CommandType.StoredProcedure));
}
private string GenerateAndExecuteQuery(TreeNode databaseNode)
{
StringBuilder result = new StringBuilder(String.Empty);
//var nodesCsv = TreeNodeCollectionToCsv(GetCheckedTablesAsTreeNodes(databaseNode.Nodes), "'");
var nodesCsv = TreeNodeCollectionToCsv(GetCheckedTablesAsTreeNodes(databaseNode.Nodes, false), "");
if (nodesCsv == "" || nodesCsv == "''")
{
return("");
}
Collection <vw_SPGenenerator> dicTables_dicColumns = GetTabels_Rows(databaseNode.Text, nodesCsv);
if (dicTables_dicColumns.Count > 0)
{
foreach (TreeNode schemaNode in databaseNode.Nodes)
{
foreach (TreeNode tblNode in schemaNode.Nodes)
{
StoredProcedureConfig storedProcedureConfig = new StoredProcedureConfig();
storedProcedureConfig.UseGo = chkUseGo.Checked;
storedProcedureConfig.UseDataBase = chkAddUseDB.Checked;
string schemaName = tblNode.Text.Split('.')[0];
string tableName = tblNode.Text.Split('.')[1];
storedProcedureConfig.Name = new StoredProcedureName()
{
Prefix = txtPrefix.Text.Trim(),
Postfix = txtPostfix.Text.Trim(),
SchemaName = schemaName,
TableName = tableName /*,
* Name = ""*/
};
if (tblNode.Checked)
{
var allColumnsForTable = GetColumnsCollectionForTable(dicTables_dicColumns, schemaName, tableName);
if (chkDeleteRow.Checked)
{
#region if (chkDeleteRow.Checked)
storedProcedureConfig.Name.Name = txtDeleteRow.Text.Trim();
string deleteRowSpQuery = DataBaseHelper.GenerateDeleteRowSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(deleteRowSpQuery);
//if(chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(deleteRowSpQuery);
#endregion
#region From Template
storedProcedureConfig.Name.Name = txtDeleteRow.Text.Trim();
string generatedFromTemplate = DataBaseHelper.GenerateQueryFromTemplate(databaseNode.Text, allColumnsForTable, storedProcedureConfig, this.deleteRowTemplate);
result.AppendLine(generatedFromTemplate);
//if(chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(generatedFromTemplate);
#endregion From Template
}
if (chkInsert.Checked)
{
#region if (chkInsert.Checked)
storedProcedureConfig.Name.Name = txtInsert.Text.Trim();
string insertSpQuery = DataBaseHelper.GenerateInsertSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(insertSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(insertSpQuery);
#endregion
#region From Template
storedProcedureConfig.Name.Name = txtInsert.Text.Trim();
string generatedFromTemplate = DataBaseHelper.GenerateQueryFromTemplate(databaseNode.Text, allColumnsForTable, storedProcedureConfig, this.insertTemplate);
result.AppendLine(generatedFromTemplate);
//if(chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(generatedFromTemplate);
#endregion From Template
}
if (chkSelectAll.Checked)
{
#region if (chkSelectAll.Checked)
storedProcedureConfig.Name.Name = txtSelectAll.Text.Trim();
string selectAllSpQuery = DataBaseHelper.GenerateSelectAllSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(selectAllSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(selectAllSpQuery);
#endregion
}
if (chkSelectRow.Checked)
{
#region if (chkSelectRow.Checked)
storedProcedureConfig.Name.Name = txtSelectRow.Text.Trim();
string selectRowSpQuery = DataBaseHelper.GenerateSelectRowSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(selectRowSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(selectRowSpQuery);
#endregion
}
if (chkUpdate.Checked)
{
#region if (chkUpdate.Checked)
storedProcedureConfig.Name.Name = txtUpdate.Text.Trim();
string updateSpQuery = DataBaseHelper.GenerateUpdateSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(updateSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(updateSpQuery);
#endregion
#region From Template
storedProcedureConfig.Name.Name = txtUpdate.Text.Trim();
string generatedFromTemplate = DataBaseHelper.GenerateQueryFromTemplate(databaseNode.Text, allColumnsForTable, storedProcedureConfig, this.updateTemplate);
result.AppendLine(generatedFromTemplate);
//if(chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(generatedFromTemplate);
#endregion From Template
}
if (chkOldSearch_PageSort.Checked)
{
#region if (chkOldSearch_PageSort.Checked)
storedProcedureConfig.Name.Name = txtOldSearch_PageSort.Text.Trim();
string oldSearchWithPageSortSpQuery = DataBaseHelper.GenerateOldSearchWithPageSortTotalCountSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(oldSearchWithPageSortSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(oldSearchWithPageSortSpQuery);
#endregion
}
if (chkSearch_PageSort.Checked)
{
#region if (chkSearch_PageSort.Checked)
storedProcedureConfig.Name.Name = txtSearch_PageSort.Text.Trim();
string searchWithPageSortSpQuery = DataBaseHelper.GenerateSearchWithPagingAndSortingTotalCountSp(databaseNode.Text, allColumnsForTable, storedProcedureConfig);
result.AppendLine(searchWithPageSortSpQuery);
//if (chkUseGo.Checked) result.AppendLine("GO");
ExecuteQueryIfNeeded(searchWithPageSortSpQuery);
#endregion
}
}
}
}
}
return(result.ToString());
}
