Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do

Answer this question

Escape a SQL string programmatically?

VBwant2B

Use paramitrimized queries. Then you never have to worry about format's or SQL Injection.
It's olso better for the preformance, because you don't need to have to concatenate a string for example:

string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";

No escape characters needed, you doesn't have to think about using a " or not etc.

Parameters are like placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1 WHERE MyDate = @MyDate";
SqlParameter pMyDate = new SqlParameter("@MyDate", SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query, dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add( pMyDate );

// Execute the query and get results.
SqlDataReader reader = dbCommand.ExecuteReader();

  try
  {
   // Walkthrough results.
   while(reader.Read())
   {
    // TODO: Do something with the data.
   }
  }
  finally
  {
   // Close reader.
   reader.Close();
  }
}
}
finally
{
// Close connection.
dbConn.Close();
}

Escape a SQL string programmatically?

Answer this question