469,342 Members | 5,647 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

Parameterized queries with SqlCommand

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.

May 24 '07 #1
4 12152
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

"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
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.

May 24 '07 #2
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)
May 24 '07 #3
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

"Kevin Spencer" <un**********@nothinks.comwrote in message
news:ub**************@TK2MSFTNGP04.phx.gbl...
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

"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
>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.


May 25 '07 #4
Thank you very much.
Jon

"Kevin Spencer" wrote:
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

"Kevin Spencer" <un**********@nothinks.comwrote in message
news:ub**************@TK2MSFTNGP04.phx.gbl...
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

"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
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.


May 25 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dirk Vervecken | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.