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,

 

[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!

 

[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,

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

 

[Guest]

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

http://msdn.microsoft.com/library/d...ag2/html/entlibjan2006_dataaccessappblock.asp

 

[Dylan Parry]

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

[...]

Thanks, everyone, for your replies.

 

[Frans Bouma [C# MVP]]

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