0

I have a DB connection class which executes all of my DB connections

I want to capture the queries and run them on SQL server tuning advisor to find out missing indexes etc

The SQL server profiler tuning takes forever to process. So i want to compose my own file to analyze

The issue is i don't know how should i capture query to properly analyze

Here my SqlCommand function

SQL server 2014 , c# .net 4.5

My question is what parameters, values etc i should log to analyze them perfectly

        public static DataTable cmd_SelectQuery(string srCommandText, List<string> lstParameterNames, List<object> lstParameters)
    {
        DataTable dsCmdPara = new DataTable();
        try
        {
            using (SqlConnection connection = new SqlConnection(DbConnection.srConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(srCommandText, connection))
                {
                    cmd.CommandTimeout = PublicSettings.irCommandTimeOutSettings_Second;
                    cmd.CommandType = CommandType.Text;

                    object objParameter;

                    for (int i = 0; i < lstParameterNames.Count; i++)
                    {
                        objParameter = lstParameters[i];
                        if (objParameter == null)
                            objParameter = "n";

                        cmd.Parameters.AddWithValue(lstParameterNames[i], lstParameters[i].ToString());
                    }
                    using (SqlDataAdapter sqlDa = new SqlDataAdapter(cmd))
                    {
                        sqlDa.Fill(dsCmdPara);
                        return dsCmdPara;
                    }
                }
            }
        }
        catch (Exception E)
        {
            Interlocked.Increment(ref GlobalStats.long_GlobalSQLErrorCount);

            StringBuilder srParameters = new StringBuilder();
            if (PublicSettings.blLogSqlParametersOnErrors == true)
            {
                srParameters.AppendLine("");
                srParameters.AppendLine("error at : cmd_UpdateDeleteQuery");
                foreach (var vrItem in lstParameters)
                {
                    srParameters.AppendLine("per parameter: " + vrItem + " \r\n\r\n");
                }
            }

            if (PublicSettings.blLogSqlErrors)
                ErrorLogger.LogSQL_Error("Error at: cmd_SelectQuery " + srCommandText + " " + E.Message.ToString() + " \r\n" + srParameters.ToString());
        }
        return dsCmdPara;
    }

    public static bool cmd_UpdateDeleteQuery(string srCommandText, List<string> lstParameterNames, List<object> lstParameters)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(DbConnection.srConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(srCommandText, connection))
                {
                    cmd.CommandTimeout = PublicSettings.irCommandTimeOutSettings_Second;
                    cmd.CommandType = CommandType.Text;

                    object objParameter;

                    for (int i = 0; i < lstParameterNames.Count; i++)
                    {
                        objParameter = lstParameters[i];
                        if (objParameter == null)
                            objParameter = "n";

                        cmd.Parameters.AddWithValue(lstParameterNames[i], objParameter);
                    }
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception E)
        {
            Interlocked.Increment(ref GlobalStats.long_GlobalSQLErrorCount);

            StringBuilder srParameters = new StringBuilder();
            if (PublicSettings.blLogSqlParametersOnErrors == true)
            {
                srParameters.AppendLine("");
                srParameters.AppendLine("error at : cmd_UpdateDeleteQuery");
                foreach (var vrItem in lstParameters)
                {
                    srParameters.AppendLine("per parameter: " + vrItem + " \r\n\r\n");
                }
            }

            if (PublicSettings.blLogSqlErrors)
                ErrorLogger.LogSQL_Error("Error at: cmd_UpdateDeleteQuery " + srCommandText + " " + E.Message.ToString() + srParameters.ToString());
        }
        return false;
    }
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
  • The actual SQL that an ADO.NET driver sends to the server is often an internal/private variable. For the purposes of running the tuner, this probably won't be easy. – MatthewMartin Sep 15 '14 at 16:19
  • @MatthewMartin sql server profiler cant filter by textdata like when using sql cmd command do you know any solution for that ? it works for only tsql i suppose – Furkan Gözükara Sep 15 '14 at 16:58
  • Possible duplicate of [Get the generated SQL-Statement from a SqlCommand-Object?](http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object) – Taylor Buchanan Mar 04 '16 at 04:51

0 Answers0