} // execRoutine private static SqlCommand CreateSqlCommand(SqlConnection con, int commandTimeOutInSeconds, CachedRoutine cachedRoutine, Controllers.ExecController.ExecType type) { var cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandTimeout = commandTimeOutInSeconds; var isTVF = cachedRoutine.Type == "TVF"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = string.Format("[{0}].[{1}]", cachedRoutine.Schema, cachedRoutine.Routine); if (isTVF) { string parmCsvList = string.Join(",", cachedRoutine.Parameters.Where(p => !p.IsResult).Select(p => p.Name).ToArray()); cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format("select * from [{0}].[{1}]({2})", cachedRoutine.Schema, cachedRoutine.Routine, parmCsvList); } else if (type == Controllers.ExecController.ExecType.Scalar) { string parmCsvList = string.Join(",", cachedRoutine.Parameters.Where(p => !p.IsResult).Select(p => p.Name).ToArray()); if (cachedRoutine.Type.Equals(string.Intern("PROCEDURE"), StringComparison.CurrentCultureIgnoreCase)) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = string.Format("[{0}].[{1}]", cachedRoutine.Schema, cachedRoutine.Routine); } else if (cachedRoutine.Type.Equals(string.Intern("FUNCTION"), StringComparison.OrdinalIgnoreCase)) { cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format("select [{0}].[{1}]({2})", cachedRoutine.Schema, cachedRoutine.Routine, parmCsvList); } } return(cmd); }
// public static ExecutionResult ExecRoutineQuery( // Controllers.ExecController.ExecType type, // string schemaName, // string routineName, // Endpoint endpoint, // Dictionary<string, string> inputParameters, // Dictionary<string, string> requestHeaders, // string remoteIpAddress, // List<ExecutionPlugin> plugins, // int commandTimeOutInSeconds, // out Dictionary<string, dynamic> outputParameterDictionary, // ExecutionBase execRoutineQueryMetric, // ref Dictionary<string, string> responseHeaders, // out int rowsAffected // ) public static async Task <ExecutionResult> ExecRoutineQueryAsync( CancellationToken cancellationToken, Controllers.ExecController.ExecType type, string schemaName, string routineName, Endpoint endpoint, Dictionary <string, string> inputParameters, Dictionary <string, string> requestHeaders, string remoteIpAddress, List <ExecutionPlugin> plugins, int commandTimeOutInSeconds, ExecutionBase execRoutineQueryMetric, Dictionary <string, string> responseHeaders ) { SqlConnection con = null; SqlCommand cmd = null; int rowsAffected = 0; try { var s1 = execRoutineQueryMetric.BeginChildStage(string.Intern("Lookup cached routine")); var cachedRoutine = endpoint.CachedRoutines.FirstOrDefault(r => r.Equals(schemaName, routineName)); if (cachedRoutine == null) { // TODO: Return 404 rather? throw new Exception($"The routine [{schemaName}].[{routineName}] was not found."); } s1.End(); // // jsDAL METADATA // { var s2 = execRoutineQueryMetric.BeginChildStage("Process metadata"); string metaResp = null; try { metaResp = ProcessMetadata(requestHeaders, ref responseHeaders, cachedRoutine); } catch (Exception) { /*ignore metadata failures*/ } if (metaResp != null) { return(new ExecutionResult() { userError = metaResp }); } s2.End(); } var s3 = execRoutineQueryMetric.BeginChildStage("Open connection"); var cs = endpoint.GetSqlConnection(); if (cs == null) { throw new Exception($"Execution connection not found on endpoint '{endpoint.Pedigree}'({endpoint.Id})."); } var csb = new SqlConnectionStringBuilder(cs.ConnectionStringDecrypted); // {schemaName}.{routineName} -- including schema.routine will create too many unique connection pools csb.ApplicationName = $"jsdal-server EXEC {endpoint.Pedigree}".Left(128); con = new SqlConnection(csb.ToString()); if (endpoint.CaptureConnectionStats) { con.StatisticsEnabled = true; con.ResetStatistics(); } else { con.StatisticsEnabled = false; } await con.OpenAsync(); s3.End(); // // PLUGINS // var s4 = execRoutineQueryMetric.BeginChildStage("Process plugins"); ProcessPlugins(plugins, con); s4.End(); var prepareCmdMetric = execRoutineQueryMetric.BeginChildStage("Prepare command"); // // CREATE SQL COMMAND // cmd = CreateSqlCommand(con, commandTimeOutInSeconds, cachedRoutine, type); // // PARAMETERS // SetupSqlCommandParameters(cmd, cachedRoutine, inputParameters, plugins, remoteIpAddress); prepareCmdMetric.End(); Dictionary <string /*Table0..N*/, ReaderResult> readerResults = null; //DataSet ds = null; object scalarVal = null; if (type == Controllers.ExecController.ExecType.Query) { var execStage = execRoutineQueryMetric.BeginChildStage("Execute Query"); readerResults = await ProcessExecQueryAsync(cancellationToken, cmd, inputParameters); // { Old Dataset-based code // var da = new SqlDataAdapter(cmd); // ds = new DataSet(); // var firstTableRowsAffected = da.Fill(ds); // Fill only returns rows affected on first Table // if (ds.Tables.Count > 0) // { // foreach (DataTable t in ds.Tables) // { // rowsAffected += t.Rows.Count; // } // } // if (inputParameters.ContainsKey("$select") && ds.Tables.Count > 0) // { // string limitToFieldsCsv = inputParameters["$select"]; // if (!string.IsNullOrEmpty(limitToFieldsCsv)) // { // var listPerTable = limitToFieldsCsv.Split(new char[] { ';' }/*, StringSplitOptions.RemoveEmptyEntries*/); // for (int tableIx = 0; tableIx < listPerTable.Length; tableIx++) // { // var fieldsToKeep = listPerTable[tableIx].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToLookup(s => s.Trim()); // if (fieldsToKeep.Count > 0) // { // var table = ds.Tables[tableIx]; // for (int i = 0; i < table.Columns.Count; i++) // { // var match = fieldsToKeep.FirstOrDefault((k) => k.Key.Equals(table.Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase)); // if (match == null) // { // table.Columns.Remove(table.Columns[i]); // i--; // } // } // } // } // } // } // $select // } execStage.End(); } else if (type == Controllers.ExecController.ExecType.NonQuery) { var execStage = execRoutineQueryMetric.BeginChildStage("Execute NonQuery"); rowsAffected = await cmd.ExecuteNonQueryAsync(cancellationToken); execStage.End(); } else if (type == Controllers.ExecController.ExecType.Scalar) { var execStage = execRoutineQueryMetric.BeginChildStage("Execute Scalar"); scalarVal = await cmd.ExecuteScalarAsync(cancellationToken); execStage.End(); } else { throw new NotSupportedException($"ExecType \"{type.ToString()}\" not supported"); } long?bytesReceived = null; long?networkServerTimeMS = null; if (endpoint.CaptureConnectionStats) { RetrieveConnectionStatistics(con, out bytesReceived, out networkServerTimeMS); } var outputParameterDictionary = RetrieveOutputParameterValues(cachedRoutine, cmd); //!executionTrackingEndFunction(); return(new ExecutionResult() { ReaderResults = readerResults, DataSet = null, // deprecated ScalarValue = scalarVal, NetworkServerTimeInMS = networkServerTimeMS, BytesReceived = bytesReceived, RowsAffected = rowsAffected, OutputParameterDictionary = outputParameterDictionary, ResponseHeaders = responseHeaders }); } finally { cmd?.Dispose(); con?.Close(); con?.Dispose(); } } // execRoutine