How Can I Execute SQL Queries Directly in C# Without Using SQLCMD.exe?

Executing SQL Queries Directly in C#

Problem:

Current systems prohibit the execution of batch files that leverage SQLCMD.exe, requiring an alternative appraoch in C#.

Solution:

Utilizing the SqlCommand Class:

To execute SQL queries directly from C#, you can employ the SqlCommand class. This class enables you to construct and execute parameterized SQL commands, thereby avoiding injection attacks.

Example Code:

The following sample code demonstrates how to use SqlCommand with parameterized SQL:

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

This code connects to the specified SQL Server instance and opens a connection. It then creates a SqlCommand object using the parameterized SQL query and adds the relevant parameter to the command. The connection is opened, the query is executed, and the results are retrieved using a SqlDataReader object.

Please note that error handling and connection closing should be implemented in practical applications.

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