如何从数据库返回特定值 [英] How do I return specific value from database
问题描述
我的页面上有两个标签，需要根据登录的用户进行更改。
首先，我想从数据库中检索学科名称。在我的数据库中，我有一个名为ATHLETE_MEETING_DISCIPLINE_RESULT的表，它链接到ATHLETE和MEETING_EVENTS。
MEETING_EVENTS表是MEETING和DISCIPLINE的链接表。
电子邮件地址在USER表中，该表也链接到ATHLETE_MEETING_DISCIPLINE_RESULT表
我使用的是3层方法。
此方法位于数据访问层，我尝试使用存储过程返回学科名称。
public string GetAthleteDiscipline（字符串电子邮件）
{
字符串 disciplineName = null ;
string connString = ConfigurationManager.ConnectionStrings [ DBConnection的]的ConnectionString。
// SqlConnection con = new SqlConnection（connString）;
尝试
{
// con.Open （）;
使用（SqlConnection con = new SqlConnection（CS））
{
SqlCommand cmd = con.CreateCommand（）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spGetAthleteDiscipline;
disciplineName = cmd.ExecuteNonQuery（）。ToString（）;
}
}
catch （例外）
{
}
return disciplineName;
}
在我的业务逻辑层，我有
public string GetAthleteDiscipline（ string emailAddress）
{
return db.GetAthleteDiscipline（emailAddress）;
}
在我的演示层，我有根据我试图设置标签的地方到我的存储过程的结果
lblDisciplineID.Text = bll.GetAthleteDiscipline（email）
我尝试了什么：
我的存储过程如下：< br $>
SELECT D.Name，AMDR.FinishingResult，AMDR.Placement
来自DISCIPLINE作为D，[USER]作为U，MEETING_EVENTS作为我， ATHLETE_MEETING_DISCIPLINE_RESULT作为AMDR
WHERE U.EmailAddress = @emailAddress
AND AMDR.MeetingDisciplineID = ME.MeetingDisciplineID
AND ME.DisciplineID = D.DisciplineID 。
但我试图检索的dicipline值始终为null或空白
试试JOIN - 如果不知道表格模式和列关系，我不能具体，但如果你有两个表使用rs和地址
用户（ID，姓名）
地址（ID，用户ID，地址）
然后
SELECT u.Name，a.Address
FROM 用户u
JOIN 地址 ON a。 UserId = u.ID然后返回每个用户名及其适当的地址 - 这听起来就像你想用SP做的那样。
编辑：
好的 - 首先要注意的是你没有ATHLETE条目！
你有用户，但没有ATHLETE。
所以你尝试使用AthleteID的任何东西都会给出问题。
和Alvin Green在100米和铁饼比赛中竞争，而不是400米栏：
SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT a
JOIN DISCIPLINE d ON a.MeetingDisciplineID = d.DisciplineID
SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT
SELECT * < span class =code-keyword> FROM DISCIPLINE
Alvin是ID 18，这是纪律ID 1和20 。
所以试试这个：
SELECT u.FirstName，u.LastName，d.Name，AMDR.FinishingResult，AMDR.Placement FROM ATHLETE_MEETING_DISCIPLINE_RESULT AMDR
JOIN [ USER ] u ON AMDR.AthleteID = u.UserID
JOIN MEETING_EVENTS ME ON me.MeetingDisciplineID = AMDR.MeetingDisciplineID
JOIN DISCIPLINE d ON d.DisciplineID = AMDR.MeetingDisciplineID
WHERE U.EmailAddress = @ emailAddress
哪个给出
FirstName LastName名称FinishingResult放置
Alvin Green 100m 12.09 4
Alvin Green讨论投掷57.4 3
您是否尝试过将参数添加到查询中？
cmd.Parameters [@ emailAddress]。Value = email;
ExecuteNonQuery表示运行查询并忽略任何结果，因为您对它们不感兴趣。显然，这不是你想要的结果，因为你想要的结果。 ExecuteNonQuery用于INSERT，UPDATE和DELETE等不返回数据的东西，不用于SELECT查询。
使用ExecuteReader代替
SqlCommand.ExecuteReader Method（System。 Data.SqlClient） [ ^ ]
I have two labels on my page that needs to change according to which user is logged on.
First I want to retrieve the discipline name from the database. In my database I have a table called ATHLETE_MEETING_DISCIPLINE_RESULT which links to ATHLETE and MEETING_EVENTS.
The MEETING_EVENTS table is a linking table for MEETING and DISCIPLINE.
The email address is in the USER table which also links to ATHLETE_MEETING_DISCIPLINE_RESULT table
I am using the 3-tier method.
this method is in the data access layer where I tried to return the discipline name using a stored procedure.
public string GetAthleteDiscipline(string email)
{
string disciplineName = null;
string connString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
//SqlConnection con = new SqlConnection(connString);
try
{
//con.Open();
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spGetAthleteDiscipline";
disciplineName = cmd.ExecuteNonQuery().ToString();
}
}
catch (Exception)
{
}
return disciplineName;
}
In my business logic layer i have
public string GetAthleteDiscipline(string emailAddress)
{
return db.GetAthleteDiscipline(emailAddress);
}
On my presentation layer I have where I tried to set the label according to the result of my stored procedure
lblDisciplineID.Text = bll.GetAthleteDiscipline(email)
What I have tried:
my stored procedure is as follows:
SELECT D.Name,AMDR.FinishingResult,AMDR.Placement
FROM DISCIPLINE As D,[USER] As U, MEETING_EVENTS As ME, ATHLETE_MEETING_DISCIPLINE_RESULT As AMDR
WHERE U.EmailAddress = @emailAddress
AND AMDR.MeetingDisciplineID = ME.MeetingDisciplineID
AND ME.DisciplineID = D.DisciplineID.
but the dicipline value I am trying to retrieve is always null or blank
Try a JOIN - I can't be specific without knowing teh table schema and teh column relationships, but if you have two tables Users and Addresses
Users (ID, Name) Addresses (ID, UserID, Address)
Then
SELECT u.Name, a.Address FROM Users u JOIN Addresses a ON a.UserId = u.IDThen that returns each user name with it's appropriate address - which sound like what you are trying to do with your SP.
EDIT:
OK - the first thing to notice is you have no ATHLETE entries!
You have USERs, but no ATHLETEs.
So anything where you try to use AthleteID is going to give problems.
And Alvin Green competed in the 100m and the Discus, not the 400m hurdles:
SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT a JOIN DISCIPLINE d ON a.MeetingDisciplineID = d.DisciplineID SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT SELECT * FROM DISCIPLINE
Alvin is ID 18, which is discipline IDs 1 and 20.
So try this:
SELECT u.FirstName, u.LastName, d.Name, AMDR.FinishingResult, AMDR.Placement FROM ATHLETE_MEETING_DISCIPLINE_RESULT AMDR JOIN [USER] u ON AMDR.AthleteID = u.UserID JOIN MEETING_EVENTS ME ON me.MeetingDisciplineID = AMDR.MeetingDisciplineID JOIN DISCIPLINE d ON d.DisciplineID = AMDR.MeetingDisciplineID WHERE U.EmailAddress = @emailAddress
Which gives
FirstName LastName Name FinishingResult Placement Alvin Green 100m 12.09 4 Alvin Green Discuss Throw 57.4 3
Have you tried adding the parameter to the query?
cmd.Parameters["@emailAddress"].Value = email;
ExecuteNonQuery means run the query and ignore any results as you're not interested in them. Obviously that isn't what you want to do as you want the results back. ExecuteNonQuery is to be used for things like INSERT, UPDATE and DELETE that don't return data, not for SELECT queries.
Use ExecuteReader instead
SqlCommand.ExecuteReader Method (System.Data.SqlClient)[^]
这篇关于如何从数据库返回特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
