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

Direct SQL Query Execution in C# Using SqlCommand

SQLCMD.exe was previously employed to execute SQL queries in batch files. However, to achieve this directly within C#, the SqlCommand class is the appropriate solution. This class allows for the seamless execution of SQL queries from within C# code.

To begin, establish a connection to the desired SQL database. You can accomplish this by creating a SqlConnection object and providing it with the appropriate connection string. The connection string should include information such as the server address, database name, username, and password.

Next, create a SqlCommand object, specifying the SQL query you wish to execute as its first parameter and the SqlConnection object as its second parameter. If necessary, you can use the Parameters property of the SqlCommand to add parameters to the query, enhancing its security and flexibility.

To execute the query, use the ExecuteReader() method of the SqlCommand object. This method returns a SqlDataReader object, through which you can iterate to access the results of your query. Each row in the result set can be accessed using indexers or by column name.

Here is an example of how this might be implemented:

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

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection))
    {
        sqlCommand.Parameters.AddWithValue("@tPatSName", "YourName");
        sqlConnection.Open();
        using (SqlDataReader reader = sqlCommand.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine($"Patient ID: {reader["tPatCulIntPatIDPk"]} | Name: {reader["tPatSFirstname"]} {reader["tPatSName"]} | Date of Birth: {reader["tPatDBirthday"]}");
                }
            }
        }
    }
}

By leveraging the SqlCommand and SqlDataReader classes, you can execute SQL queries directly within your C# code, providing a versatile and effective means of interacting with databases.

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