Best Practice - Data Access

  • Thread starter Thread starter G
  • Start date Start date
G

G

Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and want
to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit, which
determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500 recipients
but is more likely to contain up to 1,000. Table structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always be the
row where an Area has been matched and that was modified the longest amount
of time ago (order by UsedLastDate desc). As soon as it finds the FIRST
row, I want it return the value in column Recipient to myLabel.Text (an
ASP:Label on my ASPX form) and discontinue its search.

I have this all working already, but not sure if this is sufficient. Here
is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area = '["+myArea+"]'
Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my head
round all the changes from classic ASP.

G.
 
Your SQL should be

SELECT top 1 Recipient FROM Queue where ....

Then use

string recip = (string) cmd.ExecuteScalar();

recip will contain your value. ExecuteScalar is used when you only ever
return one value in one row.
 
Aidy said:
Your SQL should be

SELECT top 1 Recipient FROM Queue where ....

Then use

string recip = (string) cmd.ExecuteScalar();

recip will contain your value. ExecuteScalar is used when you only ever
return one value in one row.
Click to expand...


Thank you - I am now reading up on "Select Top" and "ExecuteScaler" - I will
reply with my code when done.

Regards,

G.
 
Aidy said:
Your SQL should be

SELECT top 1 Recipient FROM Queue where ....

Then use

string recip = (string) cmd.ExecuteScalar();

recip will contain your value. ExecuteScalar is used when you only ever
return one value in one row.
Click to expand...


Thank you,

I have:

**************************************
#region search for recipient
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT Top 1 Recipient, ID FROM
Queue where Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
string Recipient= (string)cmd.ExecuteScalar();
conn.Close();
}
finally
{
myLabel.Text = Recipient;
}
#endregion
**************************************

How easy is it to introduce a "if Null, Response.Redirect("null.aspx")" ?

Kind regards,

G.
 
Correction to my last reply:

My code is:

**************************************
#region search for recipient
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT Top 1 Recipient, ID FROM
Queue where Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
string Recipient= (string)cmd.ExecuteScalar();
myLabel.Text = Recipient;
conn.Close();
}
finally
{
}
#endregion
**************************************
 
How easy is it to introduce a "if Null, Response.Redirect("null.aspx")" ?

Can't you use

if (Recipient == null)
Response.Redirect ("null.aspx");
 
Aidy has you on the right track..

but imma hack your application with code like Area = '[" + myArea + "]'

just use a parameterized query please...you don't need sprocs to take
advantage of them.

".... Area = @Area..."

cmd.Parameters.Add("@Area", SqlDbType.VarChar, 5).Value = myArea;


Karl
 
Aidy has you on the right track..
but imma hack your application with code like Area = '[" + myArea + "]'
just use a parameterized query please...you don't need sprocs to take
advantage of them.
".... Area = @Area..."
cmd.Parameters.Add("@Area", SqlDbType.VarChar, 5).Value = myArea;
Karl
Click to expand...


Further up in my code, I create myArea from a value entered in a textbox

string myArea = City.Text;

I then use this entered value to drill down to a specific record in the
table. Will this work with your suggestion?

Thanks for your help,

G.
 
If..

Area = '[" + SOME_VARIABLE +"]' works in your code.


Area = @Area

Command.Parameters.Add("@Area", SqlDbType.VarChar, 5).Value = SOME_VARIABLE

will work :)

Of course, it's pretty easy to try out one way or the other..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/


G said:
Aidy has you on the right track..
but imma hack your application with code like Area = '[" + myArea + "]'
just use a parameterized query please...you don't need sprocs to take
advantage of them.
".... Area = @Area..."
cmd.Parameters.Add("@Area", SqlDbType.VarChar, 5).Value = myArea;
Karl
Click to expand...


Further up in my code, I create myArea from a value entered in a textbox

string myArea = City.Text;

I then use this entered value to drill down to a specific record in the
table. Will this work with your suggestion?

Thanks for your help,

G.
Click to expand...
 
hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
LINQ is a great new technology that brings your SQL and database code write
into the domain of your c# code. i.e. compiler type checking on your
queries etc. people may slate me for recommending a beta product to a .net
newcomer, but if i was in your shoes, i would not want to learn how to use
the current ADO.NET techniques because they are on the way out shortly. if
you learn LINQ, you'll never want to go back to what i call 'the dark days
of ADO'. i would say anyone who has used it would vouch the same. i have
found that applications are developed much faster with LINQ, and much more
effortlessly because sql bugs are more readily spotted by the compiler. it
may take slightly longer to get to grips with LINQ (for me at least from a
set-in-my-ways ADO background) but then again, ADO is a heck of a learning
curve too, what with all the SqlDataAdapters, SqlCommands, SqlDataReaders,
SqlConnections etc.

LINQ has a tool called SqlMetal which will read the structure of your
database (and sprocs) and generate all the database code you'll need for
your application. this has taken 50% of the code (and complexity) out of
several web applications that i have upgraded to LINQ.

for example, here is how you would achieve your request in LINQ. notice
that the code is using 'z' like a c# object, (not an sql string which has no
inherent link to the database), and also that all the conditions are c#
expressions. this means if your query is incorrect, the compiler will
complain.

MyDataBase db = new MyDataBase(); // this object is generated by SQL
Metal, it represents your database
Queue q = (from z in db.Queue where z.Paused != "Yes" && z.RecipDay > 0 &&
z.RecipMon > 0 && z.Area == myArea order by z.UsedLastTime descending select
z).Single(); // take one record
if(q == null)
Response.Redirect(whatever);
else
myLabel.Text = "Your support operator is " + q.Recipient + ". Please
agree ...";

also, note that you can use q.Recipient instead of
Convert.ToString(rdr["Recipient"])
i can't recommend LINQ enough, to me it is the biggest change to
web/database application development since .Net itself. the only caveat at
the moment is that because it is still in beta, there are one or two
compromises when you install LINQ into visual studio 2005. intellisense is
not supported fully yet for LINQ. this doesn't really bother me though.

let me know if you have any questions. before you try and use this in your
app, it is well worth it in the long run to take at least a day and muck
about with the code samples provided with the LINQ May CTP download.

i hope this helps.
tim




----- Original Message -----
From: "G" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.aspnet
Sent: Wednesday, February 07, 2007 10:25 AM
Subject: Best Practice - Data Access

Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and want
to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit, which
determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500 recipients
but is more likely to contain up to 1,000. Table structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always be
the row where an Area has been matched and that was modified the longest
amount of time ago (order by UsedLastDate desc). As soon as it finds the
FIRST row, I want it return the value in column Recipient to myLabel.Text
(an ASP:Label on my ASPX form) and discontinue its search.

I have this all working already, but not sure if this is sufficient. Here
is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one
cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my head
round all the changes from classic ASP.

G.
Click to expand...
 
I will slate you for suggesting a pre-beta product to a newcomer.

Also, ADO.NET isn't on it's way out...LINQ is an abstraction on top of
ADO.NET. I don't think you can be a good programmer without understanding
what happens below the abstraction..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/


Tim Mackey said:
hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
LINQ is a great new technology that brings your SQL and database code
write into the domain of your c# code. i.e. compiler type checking on
your queries etc. people may slate me for recommending a beta product to
a .net newcomer, but if i was in your shoes, i would not want to learn how
to use the current ADO.NET techniques because they are on the way out
shortly. if you learn LINQ, you'll never want to go back to what i call
'the dark days of ADO'. i would say anyone who has used it would vouch
the same. i have found that applications are developed much faster with
LINQ, and much more effortlessly because sql bugs are more readily spotted
by the compiler. it may take slightly longer to get to grips with LINQ
(for me at least from a set-in-my-ways ADO background) but then again, ADO
is a heck of a learning curve too, what with all the SqlDataAdapters,
SqlCommands, SqlDataReaders, SqlConnections etc.

LINQ has a tool called SqlMetal which will read the structure of your
database (and sprocs) and generate all the database code you'll need for
your application. this has taken 50% of the code (and complexity) out of
several web applications that i have upgraded to LINQ.

for example, here is how you would achieve your request in LINQ. notice
that the code is using 'z' like a c# object, (not an sql string which has
no inherent link to the database), and also that all the conditions are c#
expressions. this means if your query is incorrect, the compiler will
complain.

MyDataBase db = new MyDataBase(); // this object is generated by SQL
Metal, it represents your database
Queue q = (from z in db.Queue where z.Paused != "Yes" && z.RecipDay > 0 &&
z.RecipMon > 0 && z.Area == myArea order by z.UsedLastTime descending
select z).Single(); // take one record
if(q == null)
Response.Redirect(whatever);
else
myLabel.Text = "Your support operator is " + q.Recipient + ". Please
agree ...";

also, note that you can use q.Recipient instead of
Convert.ToString(rdr["Recipient"])
i can't recommend LINQ enough, to me it is the biggest change to
web/database application development since .Net itself. the only caveat
at the moment is that because it is still in beta, there are one or two
compromises when you install LINQ into visual studio 2005. intellisense
is not supported fully yet for LINQ. this doesn't really bother me though.

let me know if you have any questions. before you try and use this in
your app, it is well worth it in the long run to take at least a day and
muck about with the code samples provided with the LINQ May CTP download.

i hope this helps.
tim




----- Original Message -----
From: "G" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.aspnet
Sent: Wednesday, February 07, 2007 10:25 AM
Subject: Best Practice - Data Access

Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and
want to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit, which
determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500
recipients but is more likely to contain up to 1,000. Table structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always be
the row where an Area has been matched and that was modified the longest
amount of time ago (order by UsedLastDate desc). As soon as it finds the
FIRST row, I want it return the value in column Recipient to myLabel.Text
(an ASP:Label on my ASPX form) and discontinue its search.

I have this all working already, but not sure if this is sufficient.
Here is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one
cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my head
round all the changes from classic ASP.

G.
Click to expand...
Click to expand...
 
point taken, although most of us don't have a clue about assembly language
any more. this is an exageration for sure, but i'll never want to see an
SqlCommand object again, simply because you can achieve the same result with
less code, type-safe code, and easier-to-read code, using LINQ.

my reference to ADO is as the 'classic' ADO paradigm where the sql (or
related db logic) has no type fidelity to the database. this mismatch in
developing db-applications is definitely on the way out, and i have yet to
come across one person who doesn't think LINQ is the best thing to happen
..Net in a long time.

somewhere along the line, we leave behind the old and just use the best
tools available to perform the job at hand. in general i agree with your
idea that deep understanding is better than the black-box view where the
coder has no idea of what is happening underneath. however, the original
poster is in a situation where s/he needs to choose a database layer
technology, and current ADO.NET is not easy to learn. in my opinion, it is
at least worth one person suggesting that s/he look into LINQ. it is quite
possible that a newcomer would take very naturally to LINQ, because it is
closer to SQL than anything else, whereas they might flounder with the
complexity of the ADO objects. surely you would at least agree with that?
in my experience, beginners nearly faint when they are introduced to ADO for
the first time in training course environments.

tim


Karl Seguin said:
I will slate you for suggesting a pre-beta product to a newcomer.

Also, ADO.NET isn't on it's way out...LINQ is an abstraction on top of
ADO.NET. I don't think you can be a good programmer without understanding
what happens below the abstraction..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/


Tim Mackey said:
hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
LINQ is a great new technology that brings your SQL and database code
write into the domain of your c# code. i.e. compiler type checking on
your queries etc. people may slate me for recommending a beta product to
a .net newcomer, but if i was in your shoes, i would not want to learn
how to use the current ADO.NET techniques because they are on the way out
shortly. if you learn LINQ, you'll never want to go back to what i call
'the dark days of ADO'. i would say anyone who has used it would vouch
the same. i have found that applications are developed much faster with
LINQ, and much more effortlessly because sql bugs are more readily
spotted by the compiler. it may take slightly longer to get to grips
with LINQ (for me at least from a set-in-my-ways ADO background) but then
again, ADO is a heck of a learning curve too, what with all the
SqlDataAdapters, SqlCommands, SqlDataReaders, SqlConnections etc.

LINQ has a tool called SqlMetal which will read the structure of your
database (and sprocs) and generate all the database code you'll need for
your application. this has taken 50% of the code (and complexity) out of
several web applications that i have upgraded to LINQ.

for example, here is how you would achieve your request in LINQ. notice
that the code is using 'z' like a c# object, (not an sql string which has
no inherent link to the database), and also that all the conditions are
c# expressions. this means if your query is incorrect, the compiler will
complain.

MyDataBase db = new MyDataBase(); // this object is generated by SQL
Metal, it represents your database
Queue q = (from z in db.Queue where z.Paused != "Yes" && z.RecipDay > 0
&& z.RecipMon > 0 && z.Area == myArea order by z.UsedLastTime descending
select z).Single(); // take one record
if(q == null)
Response.Redirect(whatever);
else
myLabel.Text = "Your support operator is " + q.Recipient + ". Please
agree ...";

also, note that you can use q.Recipient instead of
Convert.ToString(rdr["Recipient"])
i can't recommend LINQ enough, to me it is the biggest change to
web/database application development since .Net itself. the only caveat
at the moment is that because it is still in beta, there are one or two
compromises when you install LINQ into visual studio 2005. intellisense
is not supported fully yet for LINQ. this doesn't really bother me
though.

let me know if you have any questions. before you try and use this in
your app, it is well worth it in the long run to take at least a day and
muck about with the code samples provided with the LINQ May CTP download.

i hope this helps.
tim




----- Original Message -----
From: "G" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.aspnet
Sent: Wednesday, February 07, 2007 10:25 AM
Subject: Best Practice - Data Access

Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and
want to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit, which
determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500
recipients but is more likely to contain up to 1,000. Table structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always be
the row where an Area has been matched and that was modified the longest
amount of time ago (order by UsedLastDate desc). As soon as it finds
the FIRST row, I want it return the value in column Recipient to
myLabel.Text (an ASP:Label on my ASPX form) and discontinue its search.

I have this all working already, but not sure if this is sufficient.
Here is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one
cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my head
round all the changes from classic ASP.

G.
Click to expand...
Click to expand...
Click to expand...
 
Thanks both for your advice.

I will certainly look into both, and I think being the anal bugger that I
am - I would like to learn and understand both anyway.

I will keep on my current path with ADO for this project and play aroujnd
with this other product you speak of. Once I understand how to use it I
could I guess easily adapt it into this project?

Regards,

Gary.


Tim Mackey said:
point taken, although most of us don't have a clue about assembly language
any more. this is an exageration for sure, but i'll never want to see an
SqlCommand object again, simply because you can achieve the same result
with less code, type-safe code, and easier-to-read code, using LINQ.

my reference to ADO is as the 'classic' ADO paradigm where the sql (or
related db logic) has no type fidelity to the database. this mismatch in
developing db-applications is definitely on the way out, and i have yet to
come across one person who doesn't think LINQ is the best thing to happen
.Net in a long time.

somewhere along the line, we leave behind the old and just use the best
tools available to perform the job at hand. in general i agree with your
idea that deep understanding is better than the black-box view where the
coder has no idea of what is happening underneath. however, the original
poster is in a situation where s/he needs to choose a database layer
technology, and current ADO.NET is not easy to learn. in my opinion, it
is at least worth one person suggesting that s/he look into LINQ. it is
quite possible that a newcomer would take very naturally to LINQ, because
it is closer to SQL than anything else, whereas they might flounder with
the complexity of the ADO objects. surely you would at least agree with
that? in my experience, beginners nearly faint when they are introduced to
ADO for the first time in training course environments.

tim


"Karl Seguin [MVP]" <[email protected]>
wrote in message
I will slate you for suggesting a pre-beta product to a newcomer.

Also, ADO.NET isn't on it's way out...LINQ is an abstraction on top of
ADO.NET. I don't think you can be a good programmer without understanding
what happens below the abstraction..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/


Tim Mackey said:
hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
LINQ is a great new technology that brings your SQL and database code
write into the domain of your c# code. i.e. compiler type checking on
your queries etc. people may slate me for recommending a beta product
to a .net newcomer, but if i was in your shoes, i would not want to
learn how to use the current ADO.NET techniques because they are on the
way out shortly. if you learn LINQ, you'll never want to go back to
what i call 'the dark days of ADO'. i would say anyone who has used it
would vouch the same. i have found that applications are developed much
faster with LINQ, and much more effortlessly because sql bugs are more
readily spotted by the compiler. it may take slightly longer to get to
grips with LINQ (for me at least from a set-in-my-ways ADO background)
but then again, ADO is a heck of a learning curve too, what with all the
SqlDataAdapters, SqlCommands, SqlDataReaders, SqlConnections etc.

LINQ has a tool called SqlMetal which will read the structure of your
database (and sprocs) and generate all the database code you'll need for
your application. this has taken 50% of the code (and complexity) out
of several web applications that i have upgraded to LINQ.

for example, here is how you would achieve your request in LINQ. notice
that the code is using 'z' like a c# object, (not an sql string which
has no inherent link to the database), and also that all the conditions
are c# expressions. this means if your query is incorrect, the compiler
will complain.

MyDataBase db = new MyDataBase(); // this object is generated by SQL
Metal, it represents your database
Queue q = (from z in db.Queue where z.Paused != "Yes" && z.RecipDay > 0
&& z.RecipMon > 0 && z.Area == myArea order by z.UsedLastTime descending
select z).Single(); // take one record
if(q == null)
Response.Redirect(whatever);
else
myLabel.Text = "Your support operator is " + q.Recipient + ". Please
agree ...";

also, note that you can use q.Recipient instead of
Convert.ToString(rdr["Recipient"])
i can't recommend LINQ enough, to me it is the biggest change to
web/database application development since .Net itself. the only caveat
at the moment is that because it is still in beta, there are one or two
compromises when you install LINQ into visual studio 2005. intellisense
is not supported fully yet for LINQ. this doesn't really bother me
though.

let me know if you have any questions. before you try and use this in
your app, it is well worth it in the long run to take at least a day and
muck about with the code samples provided with the LINQ May CTP
download.

i hope this helps.
tim




----- Original Message -----
From: "G" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.aspnet
Sent: Wednesday, February 07, 2007 10:25 AM
Subject: Best Practice - Data Access


Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and
want to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit,
which determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500
recipients but is more likely to contain up to 1,000. Table structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always
be the row where an Area has been matched and that was modified the
longest amount of time ago (order by UsedLastDate desc). As soon as it
finds the FIRST row, I want it return the value in column Recipient to
myLabel.Text (an ASP:Label on my ASPX form) and discontinue its search.

I have this all working already, but not sure if this is sufficient.
Here is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one
cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my
head round all the changes from classic ASP.

G.
Click to expand...
Click to expand...
Click to expand...
 
hi Gary,
yes it should be quite straight forward. i've done several straight upgrade
projects from current ADO to LINQ, and it is easy enough. you can even do
it page by page without affecting the rest of the web site. you still keep
all your stored procedures etc., the best thing is the strange satisfaction
one gets (i do at least) of deleting large volumes of carefully written and
tested code, replacing it with something newer.

if you start into LINQ, might i recommend an online video which converted me
to the merits of LINQ, it's also very enjoyable, anders is quite funny and
holds the viewers attention well:
http://channel9.msdn.com/showpost.aspx?postid=114680

good luck
tim


G said:
Thanks both for your advice.

I will certainly look into both, and I think being the anal bugger that I
am - I would like to learn and understand both anyway.

I will keep on my current path with ADO for this project and play aroujnd
with this other product you speak of. Once I understand how to use it I
could I guess easily adapt it into this project?

Regards,

Gary.


Tim Mackey said:
point taken, although most of us don't have a clue about assembly
language any more. this is an exageration for sure, but i'll never want
to see an SqlCommand object again, simply because you can achieve the
same result with less code, type-safe code, and easier-to-read code,
using LINQ.

my reference to ADO is as the 'classic' ADO paradigm where the sql (or
related db logic) has no type fidelity to the database. this mismatch in
developing db-applications is definitely on the way out, and i have yet
to come across one person who doesn't think LINQ is the best thing to
happen .Net in a long time.

somewhere along the line, we leave behind the old and just use the best
tools available to perform the job at hand. in general i agree with your
idea that deep understanding is better than the black-box view where the
coder has no idea of what is happening underneath. however, the original
poster is in a situation where s/he needs to choose a database layer
technology, and current ADO.NET is not easy to learn. in my opinion, it
is at least worth one person suggesting that s/he look into LINQ. it is
quite possible that a newcomer would take very naturally to LINQ, because
it is closer to SQL than anything else, whereas they might flounder with
the complexity of the ADO objects. surely you would at least agree with
that? in my experience, beginners nearly faint when they are introduced
to ADO for the first time in training course environments.

tim


"Karl Seguin [MVP]" <[email protected]>
wrote in message
I will slate you for suggesting a pre-beta product to a newcomer.

Also, ADO.NET isn't on it's way out...LINQ is an abstraction on top of
ADO.NET. I don't think you can be a good programmer without
understanding what happens below the abstraction..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/


hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
LINQ is a great new technology that brings your SQL and database code
write into the domain of your c# code. i.e. compiler type checking on
your queries etc. people may slate me for recommending a beta product
to a .net newcomer, but if i was in your shoes, i would not want to
learn how to use the current ADO.NET techniques because they are on the
way out shortly. if you learn LINQ, you'll never want to go back to
what i call 'the dark days of ADO'. i would say anyone who has used it
would vouch the same. i have found that applications are developed
much faster with LINQ, and much more effortlessly because sql bugs are
more readily spotted by the compiler. it may take slightly longer to
get to grips with LINQ (for me at least from a set-in-my-ways ADO
background) but then again, ADO is a heck of a learning curve too, what
with all the SqlDataAdapters, SqlCommands, SqlDataReaders,
SqlConnections etc.

LINQ has a tool called SqlMetal which will read the structure of your
database (and sprocs) and generate all the database code you'll need
for your application. this has taken 50% of the code (and complexity)
out of several web applications that i have upgraded to LINQ.

for example, here is how you would achieve your request in LINQ.
notice that the code is using 'z' like a c# object, (not an sql string
which has no inherent link to the database), and also that all the
conditions are c# expressions. this means if your query is incorrect,
the compiler will complain.

MyDataBase db = new MyDataBase(); // this object is generated by SQL
Metal, it represents your database
Queue q = (from z in db.Queue where z.Paused != "Yes" && z.RecipDay > 0
&& z.RecipMon > 0 && z.Area == myArea order by z.UsedLastTime
descending select z).Single(); // take one record
if(q == null)
Response.Redirect(whatever);
else
myLabel.Text = "Your support operator is " + q.Recipient + ".
Please agree ...";

also, note that you can use q.Recipient instead of
Convert.ToString(rdr["Recipient"])
i can't recommend LINQ enough, to me it is the biggest change to
web/database application development since .Net itself. the only
caveat at the moment is that because it is still in beta, there are one
or two compromises when you install LINQ into visual studio 2005.
intellisense is not supported fully yet for LINQ. this doesn't really
bother me though.

let me know if you have any questions. before you try and use this in
your app, it is well worth it in the long run to take at least a day
and muck about with the code samples provided with the LINQ May CTP
download.

i hope this helps.
tim




----- Original Message -----
From: "G" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.aspnet
Sent: Wednesday, February 07, 2007 10:25 AM
Subject: Best Practice - Data Access


Hello,

Looking for opinions on a fairly simple task, new to ASP.net (C#) and
want to make sure I do this as efficiently as possible.
I have a web based form, and I need to run some SQL before submit,
which determines exactly where to send the form contents.

The table of "receipients" could contain in the region of 3,500
recipients but is more likely to contain up to 1,000. Table
structure:

**********************************
[ID] [int] IDENTITY(1,1)
[Recipient] [nvarchar](30)
[RecipientID] [nvarchar](20)
[Area] [nvarchar](5)
[Paused] [nvarchar](3)
[RecipDay] [int]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************

My query onSubmit will only ever return ONE row, and this will always
be the row where an Area has been matched and that was modified the
longest amount of time ago (order by UsedLastDate desc). As soon as
it finds the FIRST row, I want it return the value in column Recipient
to myLabel.Text (an ASP:Label on my ASPX form) and discontinue its
search.

I have this all working already, but not sure if this is sufficient.
Here is my code:

**********************************
#region SQL Query - search for support operator
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM Queue where
Paused <> 'Yes' and RecipDay > 0 and RecipMon > 0 and Area =
'["+myArea+"]' Order By UsedLastTime Desc", conn);
try
{
conn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (rdr.Read())
{
myLabel.Text = "Your support operator is
"+Convert.ToString(rdr["Recipient"])+". Please agree to the terms and
conditions before you raise this support ticket.";
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
#endregion
**********************************

This seems like an awful lot of code for returning one value from one
cell.

Any adivce or tips on making this little more streamlined would be
appreciated. This is my very first .NET app - struggling to get my
head round all the changes from classic ASP.

G.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top