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?

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?

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?

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

error-checking on INSERT

I'm writing a simple stored procedure in which I use INSERT/VALUES.
What is the best way to do error-checking to ensure that my rows got
written to the table? Is checking @.@.rowcount the best way?Check @.@.ERROR = 0 directly after the INSERT.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1ee281022fb6137798993b@.msnews.microsoft.com...
> I'm writing a simple stored procedure in which I use INSERT/VALUES.
> What is the best way to do error-checking to ensure that my rows got
> written to the table? Is checking @.@.rowcount the best way?|||http://www.sommarskog.se/error-handling-I.html
Madhivanan

error_number() won't return error code

hi all,

i have sql statement in ExecuteSQLTask, connecting to AdventureWorks

BEGIN TRY
insert into person.contacts(contactid) values (1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

i purposely put a table that does not exist in the database to catch
the error. however, the error number that is 208 won't appear, instead
the whole error message will be displayed.

if i change the query in try block to
insert into person.contact(contactid) values (1);

it'll return the error number 544, which is correct.

what did i do wrong?
thanks!

You did nothing wrong.

Try/Catch doesn't catch all errors, specifically compile time errors (there are a few others). Try running the following, "more obvious" compile time error. This compile time error won't be caught either, since the execution of the statements never happens.

BEGIN TRY

PRINT 'Inside Try-Block'

'This is not sql'

END TRY

BEGIN CATCH

SELECT error_number() as 'Error In Try Block'

END CATCH

|||

thanks jaegd for your reply.. it didn't say in BOL that try/catch doesn't catch all errors or maybe i left that out.

anyhow, my problem is not solved. is there an alternative where i can catch any errors that occur?

|||See the following BOL reference for the constructs which Try/Catch does not trap.
http://msdn2.microsoft.com/en-us/library/ms175976.aspx

However, To catch almost every error (including compile time and deferred name resolution errors), you can use dynamic SQL in combination with sp_executesql.

BEGIN TRY
DECLARE @.not_sql NVARCHAR(100)
SET @.not_sql = 'This is not sql'
EXEC sp_executesql @.not_sql

END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH

Note that almost every error is caught with this method, because broken client connections are not

trapped, hopefully for relatively obvious reasons (you have no

connection).|||thanks a lot jaegd