Complete Guide to Direct SQL Query Execution in C#: Migrating from Batch to ADO.NET

Migration Background from Batch to C#

In modern software development environments, traditional batch scripts are gradually being replaced by more secure and controllable applications. The original batch script in the question uses the sqlcmd.exe tool to execute SQL queries. While this approach is simple and direct, it has security and management limitations in enterprise environments. Replacing batch processing with C# applications enables better error handling, user interface integration, and security management.

Introduction to ADO.NET Core Components

ADO.NET is the core component library for data access in .NET Framework, providing a series of classes for connecting to and operating databases. Among them, SqlConnection is used to establish connections to SQL Server databases, SqlCommand is used to execute SQL commands, and SqlDataReader is used for efficient reading of query results.

Detailed Connection String Configuration

Connection strings are key configurations for establishing database connections, requiring parameters such as server address, database name, and authentication information. Based on the parameters in the original batch script, the corresponding C# connection string should be configured as:

string connectionString = "Server=.\PDATA_SQLEXPRESS;Database=PDATA_SQLEXPRESS;User Id=sa;Password=2BeChanged!;";

Where the Server parameter specifies the database server instance, Database parameter specifies the target database, and User Id and Password provide authentication credentials.

Parameterized SQL Query Implementation

To prevent SQL injection attacks, parameterized queries must be used instead of string concatenation. The query condition WHERE tPatSName = '%name%' in the original batch should be converted to:

string queryString = "SELECT tPatCulIntPatIDPk, tPatSFirstname, tPatSName, tPatDBirthday FROM [dbo].[TPatientRaw] WHERE tPatSName = @tPatSName";

The parameter @tPatSName is bound to the actual value through the SqlParameter object:

command.Parameters.AddWithValue("@tPatSName", "actual query value");

Complete Execution Process

The following complete C# code implementation demonstrates the full process from connection establishment to result reading:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.AddWithValue("@tPatSName", "target name");
    
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    
    try
    {
        while (reader.Read())
        {
            int patientID = (int)reader["tPatCulIntPatIDPk"];
            string firstName = reader["tPatSFirstname"].ToString();
            string lastName = reader["tPatSName"].ToString();
            DateTime birthday = (DateTime)reader["tPatDBirthday"];
            
            // Process query results
            Console.WriteLine($"ID: {patientID}, Name: {firstName} {lastName}, Birthday: {birthday}");
        }
    }
    finally
    {
        reader.Close();
    }
}

Resource Management and Exception Handling

Using the using statement ensures that SqlConnection is properly released after use, avoiding resource leaks. SqlDataReader is explicitly closed in the finally block, ensuring database connections are released even if exceptions occur.

LINQ to SQL Alternative Approach

In addition to direct ADO.NET operations, the ExecuteQuery method of LINQ to SQL can be used to execute raw SQL queries. This method directly maps query results to entity objects, providing stronger type safety:

var results = db.ExecuteQuery<Patient>(
    "SELECT tPatCulIntPatIDPk as PatientID, tPatSFirstname as FirstName, " +
    "tPatSName as LastName, tPatDBirthday as BirthDate " +
    "FROM [dbo].[TPatientRaw] WHERE tPatSName = {0}", "query condition");

This approach automatically handles parameterized queries and maps result columns to entity class properties, simplifying the data conversion process.

Security Best Practices

In practical applications, hardcoding connection strings and passwords in code should be avoided. It is recommended to use configuration files, environment variables, or secure credential storage mechanisms. For production environments, consider using Windows authentication instead of SQL Server authentication to enhance security.

Performance Optimization Recommendations

For frequently executed queries, connection pooling can be considered to optimize performance. By properly configuring the Pooling, Min Pool Size, and Max Pool Size parameters in the connection string, connection establishment overhead can be significantly reduced.

Error Handling and Logging

Comprehensive error handling mechanisms are crucial for production environments. It is recommended to add try-catch blocks around database operations, catch specific exceptions like SqlException, and log detailed error information for troubleshooting.