最佳实践 - 数据访问 [英] Best Practice - Data Access
问题描述
你好，
寻找一个相当简单的任务的意见，ASP.net（C＃）的新手，并希望
以确保我这样做尽可能高效。
我有一个基于Web的表单，我需要在提交之前运行一些SQL，
确定发送表单内容的确切位置。 />
收据表可能包含3,500名受助人的地区
但更可能包含多达1,000名受助人。表结构：
**********************************
[ID] [int] IDENTITY（1,1）
[收件人] [nvarchar]（30）
[收件人ID] [nvarchar] （20）
[面积] [nvarchar]（5）
[已暂停] [nvarchar]（3）
[RecipDay] [int ]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************
我的查询onSubmit将只返回一行，这将始终是
行，其中一个区域已经匹配并且修改了最长的数量
时间前（按UsedLastDate desc排序）。一旦找到FIRST
行，我希望它将列Recipient中的值返回到myLabel.Text（
ASP：我的ASPX表单上的标签）并停止它的搜索。
我已经将这一切都工作了，但不确定这是否足够。这里
是我的代码：
************************* *********
#region SQL查询 - 搜索支持运算符
SqlDataReader rdr = null;
SqlConnection conn =新的
SqlConnection（ConfigurationSettings.AppSettings [" ConnectionString"]）;
SqlCommand cmd = new SqlCommand（" SELECT * FROM Queue
暂停<''是''和RecipDay 0和RecipMon 0和Area =''[" + myArea +"]''
按使用时间说明排序"，conn）;
尝试
{
conn.Open（）;
rdr = cmd.ExecuteReader（CommandBehavior.SingleRow）;
while（rdr.Read（））
{
myLabel.Text ="你的支持运营商是
" + Convert.ToString（RDR ["收件人"]）+ QUOT ;.在您提出此支持票之前，请同意条款和
条件。;
}
}
终于
{
if（rdr！= null）
{
rdr.Close（）;
}
if（conn！= null）
{
conn.Close（）;
}
}
#endregion
****************** ****************
这似乎是从一个单元格中返回一个值的大量代码。
任何关于使这个更加简化的adivce或提示将是
赞赏。这是我的第一个.NET应用程序 - 努力争取我的头脑
围绕经典ASP的所有变化。
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.
推荐答案
你的SQL应该是
SELECT top 1收件人FROM Queue ....
然后使用
string recip =（string）cmd.ExecuteScalar（）;
recip将包含你的值。当你只用
返回一行中的一个值时，就会使用ExecuteScalar。
" G" < g@nospam.com写信息
新闻：35 ****************************** **** @ microsof t.com ...
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" <g@nospam.comwrote in message
news:35**********************************@microsof t.com...
你好，
在一个相当简单的任务上寻找意见，ASP.net（C＃）的新手，想要
，以确保我尽可能高效地完成这项工作。
我有一个基于网络的表格，我需要运行提交前的一些SQL，其中
确切地确定了发送表单内容的位置。
收件人表格可能包含3,500名受助人的地区
但更可能包含多达1,000名受助人。表结构：
**********************************
[ID] [int] IDENTITY（1,1）
[收件人] [nvarchar]（30）
[收件人ID] [nvarchar] （20）
[面积] [nvarchar]（5）
[已暂停] [nvarchar]（3）
[RecipDay] [int ]
[RecipMon] [int]
[RecipOverride] [int]
[UsedLastTime] [timestamp]
[UsedLastDate] [datetime]
**********************************
我的查询onSubmit将只返回一行，这将始终是
区域已匹配且被修改为最长的行
前一段时间（按UsedLastDate desc排序）。一旦找到
FIRST行，我希望它将列Recipient中的值返回到myLabel.Text
（ASP：我的ASPX表单上的标签）并停止它的搜索。
我已经将这一切都工作了，但不确定这是否足够。这里
是我的代码：
************************* *********
#region SQL查询 - 搜索支持运算符
SqlDataReader rdr = null;
SqlConnection conn =新的
SqlConnection（ConfigurationSettings.AppSettings [" ConnectionString"]）;
SqlCommand cmd = new SqlCommand（" SELECT * FROM Queue
暂停<''是''和RecipDay 0和RecipMon 0和Area =
''[" + myArea +"]''按By UsedLastTime Desc"，conn）;
尝试
{
conn.Open（）;
rdr = cmd.ExecuteReader（CommandBehavior.SingleRow）;
while（rdr.Read（））
{
myLabel.Text ="你的支持运营商是
" + Convert.ToString（RDR ["收件人"]）+ QUOT ;.在您提出此支持票之前，请同意条款和
条件。;
}
}
终于
{
if（rdr！= null）
{
rdr.Close（）;
}
if（conn！= null）
{
conn.Close（）;
}
}
#endregion
****************** ****************
这似乎是一个非常多的代码从一个返回一个值
细胞。
任何关于使这个更加精简的任何adivce或提示将是赞赏的。
赞赏。这是我的第一个.NET应用程序 - 努力争取我的头脑。来自经典ASP的所有变化。
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.
" Aidy" < ai ** @ noemail.xxxa.com写信息
新闻：5s ************************** **** @ bt.com ...
"Aidy" <ai**@noemail.xxxa.comwrote in message
news:5s******************************@bt.com...
你的SQL应该是
SELECT top 1收件人来自队列....
然后使用
string recip =（string）cmd.ExecuteScalar（）;
recip将包含您的价值。当你只有
返回一行中的一个值时，使用ExecuteScalar。
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.
谢谢 - 我现在正在阅读选择顶部和ExecuteScaler - 完成后我会用我的代码回复
。
问候，
G.
Thank you - I am now reading up on "Select Top" and "ExecuteScaler" - I will
reply with my code when done.
Regards,
G.
" Aidy" < ai ** @ noemail.xxxa.com写信息
新闻：5s ************************** **** @ bt.com ...
"Aidy" <ai**@noemail.xxxa.comwrote in message
news:5s******************************@bt.com...
你的SQL应该是
SELECT top 1收件人来自队列....
然后使用
string recip =（string）cmd.ExecuteScalar（）;
recip将包含您的价值。当你只有
返回一行中的一个值时，使用ExecuteScalar。
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.
谢谢，
我有：
**** ********************************** *
#region搜索收件人
SqlDataReader rdr = null;
SqlConnection conn = new
SqlConnection（ConfigurationSettings.AppSettings [" ConnectionString"]）;
SqlCommand cmd = new SqlCommand（" SELECT Top 1 Recipient，ID FROM
Queue in Paused<''Yes''和RecipDay 0 and RecipMon 0 and Area =
''[" + myArea +"]''按二手时间顺序排序"，conn）;
尝试
{
conn .Open（）;
string Recipient =（string）cmd.ExecuteScalar（）;
conn.Close（）;
}
终于
{
myLabel.Text =收件人;
}
#endregion
**************************************
引入if Null，Response.Redirect（" null.aspx"）是多么容易？ ？
亲切的问候，
G.
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.
这篇关于最佳实践 - 数据访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
