My goal with this code is to calculate the processing time of the query string that's passed into the stored procedure. The query string is created by the user, a student taking a sql/database course. In a subsequent query, the string is compared against the key to determine if it produces the correct results for the assignment. As you'll see, it's VB.NET code (.NET 4.5 project) calling a C# method (.NET 3.5 project) that calls a stored procedure (SQL SERVER 2012).
My initial call from the main program project is here:
Dim executionTime As Integer = exercise.GetSqlTime(txtUserSQL, connectionString)
The method called is here:
public int GetSqlTime(string SqlString, string ConnectionString)//bookmark
{
int executionTime = 0;
SqlConnection con = new SqlConnection(ConnectionString);
using (SqlCommand cmd = new SqlCommand("QueryExecutionTime", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserSQLstring", SqlDbType.NVarChar).Value = SqlString;
cmd.Parameters.Add("@Duration", SqlDbType.Int);
cmd.Parameters["@Duration"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
executionTime = Convert.ToInt32(cmd.Parameters["@Duration"].Value);
}
return executionTime;
}
My stored procedure is here:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[QueryExecutionTime]
@UserSQLstring NvarChar(3000),
@Duration int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartTime datetime = GETDATE()
EXEC sp_executesql @UserSQLstring
DECLARE @EndTime datetime = GETDATE()
SET @Duration = DATEDIFF(MILLISECOND,@StartTime,@EndTime)
END
The result is 0 when the query string is correct. When the string is incorrect, it returns a value between 1.4 and 2.5 seconds
This is the first time I've used a stored procedure with an output parameter. I had no idea it would prove to be so challenging.
Am I overlooking a simple detail that would make this work correctly? I have already tried at least five or six examples here on Stack Overflow alone - along with several other websites. What you're seeing here is my latest iteration. If you need additional information, please let me know.
If you know of a better way of doing this, I'm open to any and all suggestions.
