Lelle. You're using so called "dynamic sql" which you construct right from
your code. Although it works just fine for small projects, this approach has
several issues, from security to performance to manageability. By saying
"parameters" we mean using stored procedures or parameterized statements. In
short, the process is fairly simple:
1. Create a stored procedure that would select whatever data your page
needs; most definitely it would have some parameters (say, if you select
some orders for specific dates, then parameters would be those dates and
customerID); check out SqlServer's Help to learn all about them, if you need
to (in Query Analyzer click Help / Transact-SQL Help option);
2. You use DataAdapter to fill a dataset; Microsoft has released a good
collection of classes called "Application Blocks"; it's free, just search
msdn.microsoft.com to find and download it; it includes documentation, too,
I believe;
3. Or use your own class; it's also not difficult to create one; the best
thing would be to compile such classes as a separate project, so you can
reuse it across multiple applications. Below is the sample of data access
method of such class (sorry, I use C#
):
public static DataSet ExecSPDataSet(SqlConnection conn,int
commandTimeout,string procName,params object[] arrayOfParams)
{
try
{
SqlCommand comm = new SqlCommand(procName,conn); // create new Command
object
comm.CommandType = CommandType.StoredProcedure; // tell the code that you
gonna use stored procedure
SqlCommandBuilder.DeriveParameters(comm); //go to the Sql Server to get
names and data types of sproc parameters
comm.CommandTimeout = commandTimeout; // set timeout, useful if you have a
large chuncks of data to be returned
for( int i=1; i < comm.Parameters.Count && i < arrayOfParams.Length+1; i++ )
comm.Parameters
.Value = arrayOfParams[i-1]; // "transform" your array of
parameters into Command.Parameters object
SqlDataAdapter da = new SqlDataAdapter(comm); // create a new instance of
SqlDataAdapter object
DataSet ds = new DataSet(); // ...and new dataset which will contain your
data, too
da.Fill(ds); // physically go to get your data and fill dataset
comm.Parameters.Clear(); // just in case if you'll reuse this Command again
if(conn.State == ConnectionState.Open)
conn.Close(); // no matter what books say about pooling and garbage
collection, always close your connection
return ds; // enjoy your data
}
catch(SqlException ex)
{
if(ex.Number == 1205) //check for deadlocks, it would be MUCH better to
start a timer for the random number of seconds, so other requests would not
cause an infinitive loop
return ExecSPDataSet(conn,commandTimeout,procName,arrayOfParams); // call
this method again if deadlock happened
else throw; // something else happened - let the rest of the code know about
it
}
}
As you can see, this method calls stored procedure and expects an array of
objects, which are your parameters (key word "params" has nothing to do with
sql, it tells C# that those methods are not required).
Hope this helps a bit
Kikoz.