I've got a page in my application that will allow users to run stored procedures against the database so that they can manage their data or make changes without using SSMS. Using the SqlCommandBuilder.DeriveParameters method, I was able to successfully fetch and display all stored procedures in a select tag as well as display their input & output parameters.
Here is the contract class:
public class DatabaseUpdateModel
{
public int ClientId { get; set; }
public string StoredProcedureName { get; set; }
public List<SelectListItem> StoredProcedureNames { get; set; }
public Dictionary<string, SqlParamCollection> StoredProcedures { get; set; }
public Dictionary<int, string> ParameterInputs { get; set; }
public DatabaseUpdateModel()
{
StoredProcedureNames = new List<SelectListItem>();
StoredProcedures = new Dictionary<string, SqlParamCollection>();
ParameterInputs = new Dictionary<int, string>();
}
}
I do this in two steps - step 1 happens on the controller's index loading to fetch the stored procedure names:
Service methods:
private DatabaseUpdateModel GetDatabaseUpdateStoredProcedureNames(string connectionString)
{
_logger.LogTrace("Begin GetDatabaseUpdateModel service method");
SqlConnection connection = new SqlConnection(connectionString);
DBConnectionSetter.SetDBConnection(_SqlHelper, connectionString);
DatabaseUpdateModel model = new DatabaseUpdateModel();
IDataReader reader = _SqlHelper.GetDataReader("SELECT name FROM sys.objects WHERE type = 'p' ORDER BY name ASC", CommandType.Text, _appSettings.CommandTimeoutInSeconds, out connection, null);
while (reader.Read())
{
SelectListItem storedProcedure = new SelectListItem
{
Value = ((string)reader["name"]).Trim(' '),
Text = (string)reader["name"]
};
model.StoredProcedureNames.Add(storedProcedure);
}
reader.Close();
return model;
}
Step 2 happens via Ajax when a stored procedure is selected
public DatabaseUpdateModel GetDatabaseUpdateProcedureParameters(string connectionString, string storedProcedureName)
{
_logger.LogTrace("Begin GetDatabaseUpdateProcedureParameters service method");
SqlConnection connection = new SqlConnection(connectionString);
DBConnectionSetter.SetDBConnection(_SqlHelper, connectionString);
DatabaseUpdateModel model = GetDatabaseUpdateStoredProcedureNames(connectionString);
connection.Open();
Contract.Shared.SqlParamCollection collection = new Contract.Shared.SqlParamCollection();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);
foreach (SqlParameter param in command.Parameters)
{
if (param.Direction == ParameterDirection.Input)
{
collection.InputParameters.Add($"{param.ParameterName} - {param.SqlDbType}");
collection.SqlParameters.Add(param);
}
else
{
collection.OutputParameters.Add($"{param.ParameterName} - {param.SqlDbType}");
}
}
model.StoredProcedures.Add(storedProcedureName, collection);
connection.Close();
return model;
}
What this leaves me with is this:
Now I've been following various articles and resources to get to this point but now I'm having issues correctly binding these inputs to a model to be submitted either by an action or via ajax. Here's my current code in the view (the commented out line is one of my latest attempts to get this working).
@model DatabaseUpdateModel
<form>
<table id="summaryTable" class="table">
<thead>
<tr>
<td>Input Parameters</td>
<td>Output Parameters</td>
</tr>
</thead>
<tbody>
<tr>
<td>
<ul>
@if (!string.IsNullOrEmpty(Model.StoredProcedureName))
{
for (int i = 0; i < Model.StoredProcedures[Model.StoredProcedureName].InputParameters.Count(); i++)
{
<li>
@Model.StoredProcedures[Model.StoredProcedureName].InputParameters[i].ToString()<br />
@*<input id="databaseUpdateParam[@i]" name="parameter[@i]" asp-for="@Model.ParameterInputs[@i]" type="text" />*@
<input id="databaseUpdateParam[@i]"type="text" />
</li>
}
}
</ul>
</td>
<td>
<ul>
@if (!string.IsNullOrEmpty(Model.StoredProcedureName))
{
foreach (var param in Model.StoredProcedures[Model.StoredProcedureName].OutputParameters)
{
<li>
@param
</li>
}
}
</ul>
</td>
</tr>
</tbody>
</table>
</form>
@if (Model.StoredProcedures[Model.StoredProcedureName].InputParameters.Count() > 0)
{
<button type="submit" asp-action="Foo">Submit</button>
}
Ajax function that runs when a value is selected:
function selectDatabaseUpdateStoredProcedure(clientId, storedProcedureName) {
var selected = $("#clientStoredProcedurePicker option:selected").val();
console.log(selected); // Selected Stored Procedure Name
$.ajax({
url: "/Process/GetDatabaseUpdateProcedureParameters?ClientId=" + clientId + "&StoredProcedureName=" + storedProcedureName,
success: function (data) {
console.log(data);
$("#summaryTableDiv").html(data);
}
});
}
My assumption, and everything that I read up to this point has said that I should be able to simply be able to use the name attribute to denote the dictionary's key and the value in the tag would correspond to the value entered upon form submission. However all of my attempts to achieve this are met with errors (for example that the index is not present in the dictionary).
How can this be done either through a controller action or ajax?
