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;
}