SQL Queries

  • Thread starter Thread starter Dylan Parry
  • Start date Start date
D

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,

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
 
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);
Click to expand...

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! :)
 
If you use stored procs you can be far more granular in your approach
to permissions, and security.
 
Hi,

Richard Brown said:
If you use stored procs you can be far more granular in your approach
to permissions, and security.
Click to expand...

And the application more flexible to changes in the DB
 
Dylan Parry realised that microsoft.public.dotnet.languages.csharp was a
little too quiet...

[...]

Thanks, everyone, for your replies.
 
Richard said:
If you use stored procs you can be far more granular in your approach
to permissions, and security.
Click to expand...

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#)
------------------------------------------------------------------------
 
Back
Top