asp.net sql command quries or store procedure name

Question

my queries executing from an ASP.NET application and I don't want those SQL queries & also store procedure to be traced by SQL Server Profiler. How can I do it?

Satyaprakash Samantaray · Answer

To disable tracing, open the Web.config file for the application, and find the element within the section. Either set the enabled attribute to "false" (to disable tracing) or set the localOnly attribute to "true" (to enable tracing only on the server itself). Note that even with tracing disabled in this way, it is possible for individual pages to turn on page-level tracing either within the Page directive of the ASP.NET page, or programmatically through application code. Encrypting and Decrypting database objects for security and privacy >> https://tinyurl.com/bdz3zpw3

Asht T · Answer

Jignesh Kumar Thank u... but i don't want . ..to tranced query in sql profile for security purpose, means do not dispaly my query like exec GetData_P @Flag=N'ListofStudent' in sql profile I already write query in dot application but when we run sql profile then exec GetData_P @Flag=N'ListofStudent' this is shown in sql profile so how to hide this in sql profile............ . DO NOT TRACE In SQL PROFILE or other wise encrypt store procedure : i used WITH Encryption but this decrypted useing SQL Decryptor.... actually i dont want to show my store procedure logic to anyone.

Jignesh Kumar · Answer

Hello asht, Please find example for store procedure, Store procedure for Insert record to table, CREATE PROCEDURE InsertUser @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @Email NVARCHAR(100) AS BEGIN INSERT INTO Users (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email); END; Update record : CREATE PROCEDURE UpdateUser @Id INT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @Email NVARCHAR(100) AS BEGIN UPDATE Users SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE Id = @Id; END; Get records from table : CREATE PROCEDURE GetUsers AS BEGIN SELECT Id, FirstName, LastName, Email FROM Users; END; Delete records from table : CREATE PROCEDURE DeleteUser @Id INT AS BEGIN DELETE FROM Users WHERE Id = @Id; END; Back end code for C# : using System; using System.Data; using System.Data.SqlClient; class Program { static string connectionString = "your_connection_string_here"; static void Main() { InsertUser("John", "Doe", "john.doe@example.com"); DisplayUsers(); UpdateUser(1, "UpdatedJohn", "UpdatedDoe", "updated.john.doe@example.com"); DisplayUsers(); DeleteUser(1); DisplayUsers(); } static void InsertUser(string firstName, string lastName, string email) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("InsertUser", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@FirstName", firstName); command.Parameters.AddWithValue("@LastName", lastName); command.Parameters.AddWithValue("@Email", email); command.ExecuteNonQuery(); } } } static void DisplayUsers() { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("GetUsers", connection)) { command.CommandType = CommandType.StoredProcedure; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"Id: {reader["Id"]}, FirstName: {reader["FirstName"]}, LastName: {reader["LastName"]}, Email: {reader["Email"]}"); } } } } Console.WriteLine("---------------------"); } static void UpdateUser(int id, string firstName, string lastName, string email) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("UpdateUser", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Id", id); command.Parameters.AddWithValue("@FirstName", firstName); command.Parameters.AddWithValue("@LastName", lastName); command.Parameters.AddWithValue("@Email", email); command.ExecuteNonQuery(); } } } static void DeleteUser(int id) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("DeleteUser", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Id", id); command.ExecuteNonQuery(); } } } } Note : To trace store prcedure you need to run sql profiler. Please refer this thread which will help you out, https://www.mssqltips.com/sqlservertip/6441/using-sql-server-profiler-to-capture-stored-procedures-executions/

asp.net sql command quries or store procedure name

Answers (3)