Parameterized queries with SqlCommand

[Guest]

For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)

Parameters were idenfitied with a colon...
Where ModDate > :MyDate

But, now that I am stwitching to .NET, the parameters are identified with @
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?

Thanks,
Jon

PS: Creating stored procedures on my customers' databases is not an option.

 

[Kevin Spencer]

You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:

private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);

try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[Guest]

Thank you for your response. I know how to use parameters with SqlCommand,
but I also need to use local variables within the text of the query.
I easily used complex queries such as the following in my old programming
environment in the ADO control. It was simple because the parameter indicator
was a colon, and thus different from the local variable character, the at
sign.
In .NET I need to be able to use BOTH parameters AND local variables inside
the same query text (note, adding stored procedures to the databases of my
customers is not an option)...

Declare @D datetime, @D1 datetime, @D2 datetime

Select @D=Modified From ##Track Where TableName = 'DivisionMaster'
if @D is null
Select @D=Modified From ##Track Where TableName = 'ETPR_LastRun'
Select @D1=Modified From ##Track Where TableName = 'CustomerMaster'
if @D1 is null
Select @D1=Modified From ##Track Where TableName = 'ETPR_LastRun'
Select @D2=Modified From ##Track Where TableName = 'CustomerBillingProfile'
if @D2 is null
Select @D2=Modified From ##Track Where TableName = 'ETPR_LastRun'

Select <lots of fields>
From BigTable bg
<lots of joins>
Where bg.Something=:MyParameter
And (dm.Modified>@D or cm.Modified>@D1 or oa.Modified>@D2)

 

[Kevin Spencer]

Hi Jon,

I understand you now. I couldn't find any specific documentation about this,
but I employed a good guess and tested it:

SqlCommand command;
SqlConnection connection;
SqlDataReader reader;
connection = new SqlConnection(Globals.WebContentConnectionString);
connection.Open();
command = connection.CreateCommand();
string s = @"DECLARE @username nvarchar(20)
SELECT @username = 'JoeBlow'
-- Insert statements for procedure here
SELECT Password, PasswordAnswer, IsLockedOut FROM Members
WHERE MemberName = @username";
command.CommandText = s;
reader = command.ExecuteReader();
if (reader.Read()) TextBox1.Text = reader.GetString(0);
reader.Close();
connection.Dispose();
command.Dispose();

Worked perfectly.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:

private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);

try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)

Parameters were idenfitied with a colon...
Where ModDate > :MyDate

But, now that I am stwitching to .NET, the parameters are identified with
@
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?

Thanks,
Jon

PS: Creating stored procedures on my customers' databases is not an
option.

 

[Guest]

Thank you very much.
Jon

Hi Jon,

I understand you now. I couldn't find any specific documentation about this,
but I employed a good guess and tested it:

SqlCommand command;
SqlConnection connection;
SqlDataReader reader;
connection = new SqlConnection(Globals.WebContentConnectionString);
connection.Open();
command = connection.CreateCommand();
string s = @"DECLARE @UserName nvarchar(20)
SELECT @UserName = 'JoeBlow'
-- Insert statements for procedure here
SELECT Password, PasswordAnswer, IsLockedOut FROM Members
WHERE MemberName = @UserName";
command.CommandText = s;
reader = command.ExecuteReader();
if (reader.Read()) TextBox1.Text = reader.GetString(0);
reader.Close();
connection.Dispose();
command.Dispose();

Worked perfectly.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:

private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);

try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)

Parameters were idenfitied with a colon...
Where ModDate > :MyDate

But, now that I am stwitching to .NET, the parameters are identified with
@
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?

Thanks,
Jon

PS: Creating stored procedures on my customers' databases is not an
option.