How to Execute SQL Queries Directly in C# Using SqlCommand?

Executing SQL Queries Directly in C

In a recent project, you mentioned the need to replace an outdated batch file that utilizes SQLCMD.exe. As you delve into C# development, you may encounter challenges in executing SQL queries directly from within your code. This article will guide you through the steps to achieve this using the SqlCommand class.

Understanding SqlCommand

SqlCommand is a crucial class within the System.Data.SqlClient namespace that enables you to execute SQL commands against a relational database. It provides a flexible and efficient way to perform database operations from within your C# code.

Implementing SQL Execution

To execute an SQL query directly in C# using SqlCommand, follow these essential steps:

1. Establish a Connection: Connect to your database using a SqlConnection object, providing the appropriate connection string.
2. Create a Command: Instantiate a SqlCommand object with the SQL query string as the first argument and the SqlConnection object as the second argument.
3. Add Parameters (Optional): If your query involves any parameters, add them to the SqlCommand using the Parameters.AddWithValue() method.
4. Open the Connection: Open the SqlConnection to establish communication with the database.
5. Execute the Query: Invoke the ExecuteReader() method on the SqlCommand object to execute the query and retrieve the results as a SqlDataReader object.
6. Read the Results: Use a while loop to iterate through the SqlDataReader and extract the desired values.
7. Close Resources: Always remember to close the SqlDataReader and SqlConnection objects once you're done with them.

Sample Code

Here's an example C# code snippet that demonstrates how to use SqlCommand for executing a parameterized SQL query:

string queryString = "SELECT tPatCulIntPatIDPk, tPatSFirstname, tPatSName, tPatDBirthday FROM [dbo].[TPatientRaw] WHERE tPatSName = @tPatSName";
string connectionString = "Server=.\PDATA_SQLEXPRESS;Database=;User Id=sa;Password=2BeChanged!;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
            reader["tPatCulIntPatIDPk"], reader["tPatSFirstname"])); // etc
        }
    }
    finally
    {
        reader.Close();
    }
}

By utilizing the SqlCommand class, you can now seamlessly execute SQL queries and retrieve results directly from within your C# applications.

The above is the detailed content of How to Execute SQL Queries Directly in C# Using SqlCommand?. For more information, please follow other related articles on the PHP Chinese website!