SQL Queries

[Dylan Parry]

Hi,

I was wondering if anyone has some perspective as to what the best way
to query an MS SQL Server database is using C#?

Currently, I am using the SqlConnection, SqlCommand and SqlDataReader
classes by opening a connection, writing an query string and passing it
to the SqlCommand object along with the SqlConnection object.

eg.

SqlConnection connection = new SqlConnection(/connectionString/);
connection.Open();
string query = "SELECT ...";
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader = command.ExecuteReader();
[...]

(or something like that - I cut out most of the code)

Now this is okay, but I am finding it quite complicated to dynamically
generate queries using this technique, especially if some of the values
are /null/.

Is there an easier/better way of creating the query string, or am I
stuck with using IF statements and "query += ..." to build them?

Cheers,

--
Dylan Parry
http://webpageworkshop.co.uk -- FREE Web tutorials and references

NP: Genesis - Dance on a Volcano (Live)

[Tasos Vogiatzoglou]

Dylan,

You can use stored procedures. But you can also create your command
string as following :

string sqlString = "SELECT * FROM MyTable Where id=@id";

and then

SqlConnection con = <get your connection>;

SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlString;
cmd.Parameters.Add("id",SqlDbType.Int);

using (SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ) {
// do stuff
}

Regards,
Tasos

[Dylan Parry]

Pondering the eternal question of "Hobnobs or Rich Tea?", Tasos
Vogiatzoglou finally proclaimed:

string sqlString = "SELECT * FROM MyTable Where id=@id"; [...] SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlString;
cmd.Parameters.Add("id",SqlDbType.Int);

Thanks. That looks interesting, and while is probably involves writing
more code, will most likely make it easier for me understand what I'm
doing! :)

--
Dylan Parry
http://electricfreedom.org -- Where the Music Progressively Rocks!

[Richard Brown]

If you use stored procs you can be far more granular in your approach
to permissions, and security.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Richard Brown" <ri***************@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

If you use stored procs you can be far more granular in your approach
to permissions, and security.

And the application more flexible to changes in the DB
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Demetri]

Microsoft Data Application Blocks makes data access dramatically easier. You
should take a look.

http://msdn.microsoft.com/library/de...ssappblock.asp

--
-Demetri
"Dylan Parry" wrote:

Hi,

I was wondering if anyone has some perspective as to what the best way
to query an MS SQL Server database is using C#?

Currently, I am using the SqlConnection, SqlCommand and SqlDataReader
classes by opening a connection, writing an query string and passing it
to the SqlCommand object along with the SqlConnection object.

eg.

SqlConnection connection = new SqlConnection(/connectionString/);
connection.Open();
string query = "SELECT ...";
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader = command.ExecuteReader();
[...]

(or something like that - I cut out most of the code)

Now this is okay, but I am finding it quite complicated to dynamically
generate queries using this technique, especially if some of the values
are /null/.

Is there an easier/better way of creating the query string, or am I
stuck with using IF statements and "query += ..." to build them?

Cheers,

--
Dylan Parry
http://webpageworkshop.co.uk -- FREE Web tutorials and references

NP: Genesis - Dance on a Volcano (Live)

[Dylan Parry]

Dylan Parry realised that microsoft.public.dotnet.languages.csharp was a
little too quiet...

[...]

Thanks, everyone, for your replies.

--
Dylan Parry
http://webpageworkshop.co.uk -- FREE Web tutorials and references

[Frans Bouma [C# MVP]]

Richard Brown wrote:

If you use stored procs you can be far more granular in your approach
to permissions, and security.

That just took 3 posts... one asks a question about SQL queries and
someone steps in and starts a lecture about how great stored procedures
are.

Please avoid that discussion here.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------