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.

"G" <(E-Mail Removed)> wrote in message
news:3578873A-2EEE-4734-95E4-(E-Mail Removed)...
> 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.

"Aidy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


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

Regards,

G.

"Aidy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


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

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


"G" <(E-Mail Removed)> wrote in message
news:C6F5951D-8B89-40E7-8FD1-(E-Mail Removed)...
> 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
> **************************************

> 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


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" <(E-Mail Removed)> wrote in message
news:FEBB919B-60DB-45D7-A2A8-(E-Mail Removed)...
>> 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
>
>
> 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.

hi G
seeing as you're new to asp.net, it may be worth looking into LINQ
http://msdn2.microsoft.com/en-us/net.../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" <(E-Mail Removed)>
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.