如何将多个sql select语句中的数据填充到一个ridview中 [英] How to populate data from multiple sql select statements into one ridview

问题描述

您好，我有一个问题，即使用多个查询从数据库中获取数据，并将其返回到asp.net c＃代码，并将生成的数据集填充到单个gridview中。



我将展示数据库表的外观。

我的表是： -



1）tbl_user [userid，用户名，电子邮件，电话]

2）tbl_skills [skillid，skillname]

3）tbl_qualification [qualification_id，qualification_name]

4）tbl_user_skill [ userid，skill_id]

5）tbl_user_qualifiaction [userid，qualification_id]



我要求，基于userid，我需要创建一个逗号分开的技能列表，逗号分隔的资格列表，技能数，资格数，用户ID，用户名，电子邮件和电话。



为此，我创建了以下内容存储过程： -

Hello, I have a question about taking data from database using multiple queries, and returning it to the asp.net c# code, and populate the resulting dataset to a single gridview.

I will show how the database tables look like.
My tables are :-

1) tbl_user [userid, username, email, phone]
2) tbl_skills [skillid, skillname]
3) tbl_qualification [qualification_id, qualification_name]
4) tbl_user_skill [userid, skill_id]
5) tbl_user_qualifiaction [userid, qualification_id]

I require that, based on userid, I need to create a comma separated list of skills, a comma separated list of qualifications, count of skills, count of qualifications, userid, username, email and phone.

For that I created the following stored procedure:-

ALTER PROCEDURE [dbo].[sp_loaduserdetailsingridview]
@flag int=0,
@userid int=0,
@skillnames varchar(max)=null,
@skillcount int=0, 
@qualnames varchar(max)=null, 
@qualcount int=0,
@qualid int=0,
@skillid int=0
as
if(@flag=0)
 begin
 select @skillnames=coalesce(@skillnames + ', ','') + skill_name from tbl_skill inner join 
 tbl_user_skill on tbl_skill.skill_id=tbl_user_skill.skill_id where tbl_user_skill.userid=@userid

select @qualnames=coalesce(@qualnames + ', ','') + qualification_name from tbl_qualification inner join
 tbl_user_qualification on tbl_qualification.qualification_id=tbl_user_qualification.qualification_id
 where tbl_user_qualification.userid=@userid

select @skillcount=count(skill_id) from tbl_user_skill where userid=@userid

select @qualcount=count(qualification_id) from tbl_user_qualification where userid=@userid

select @skillnames as skillnames, @skillcount as skillcount, @qualnames as qualnames, @qualcount as qualcount

select username, email, hone from tbl_user where userid=@userid
end

我需要将最后两个选择查询返回到asp.net c＃代码，并将其填入单个gridview。

gridview应该看起来像以下格式： -

I require the last two select queries o be returned to the asp.net c# code, and populate them in a single gridview.
The gridview should look like the following format:-

[username, skillnames, skillcount, qualnames, qualcount, email, phone]

我尝试使用数据集并使用单个数据集追加数据，但失败了上班。当我使用多个数据集时，gridview中的某些字段将被填充，其他字段将被存储，并且某些字段将被复制。



如何解决此问题。

I tried using a dataset and append the data using a single dataset, but failed to work. When I use multiple dataset, some fields in the gridview will be populated, others will be bank, and some fields are duplicated.

How to solve this.

推荐答案

你好朋友可以看一下这个修改过的商店程序......

hello friend can you please check it out this modified store procedure...

ALTER PROCEDURE [dbo].[sp_loaduserdetailsingridview]
	@flag INT=0,
	@userid INT=0,
	@skillnames VARCHAR(MAX)=NULL,
	@skillcount INT=0, 
	@qualnames VARCHAR(MAX)=NULL, 
	@qualcount INT=0,
	@qualid INT=0,
	@skillid INT=0
AS
IF(@flag=0)
BEGIN
	SELECT @skillnames=COALESCE(@skillnames + ', ','') + skill_name FROM tbl_skill 
		INNER JOIN  tbl_user_skill ON tbl_skill.skill_id=tbl_user_skill.skill_id 
	WHERE tbl_user_skill.userid=@userid
 
	SELECT @qualnames=COALESCE(@qualnames + ', ','') + qualification_name FROM tbl_qualification 
		INNER JOIN tbl_user_qualification ON tbl_qualification.qualification_id=tbl_user_qualification.qualification_id
	WHERE tbl_user_qualification.userid=@userid
 
	SELECT @skillcount=COUNT(skill_id) FROM tbl_user_skill 
	WHERE userid=@userid
 
	SELECT @qualcount=COUNT(qualification_id) FROM tbl_user_qualification 
	WHERE userid=@userid
	
	SELECT username
		, @skillnames AS skillnames
		, @skillcount AS skillcount
		, @qualnames AS qualnames
		, @qualcount AS qualcount
		, email
		, hone FROM tbl_user WHERE userid=@userid
		
END

你好专业



这是真的，你有朦胧的数据库，你需要窥视



但在IT中总是有异常的解决方案..





尝试加入和子查询

和您喜欢的数据格式[用户名，技能名称，技能，资格，资格，电子邮件，电话]



你需要使用Coalesce（）in构建SQL的功能



更多

在sqlserver中使用Coalesce（） [< a href =http://www.codeproject.com/Tips/605339/Using-Coalesce-in-sqlservertarget =_ blanktitle =New Window> ^ ]



http://msdn.microsoft.com/en-us/ library / ms190349.aspx [ ^ ]



很乐意提供帮助!!!

hello profession

its true that you have hazy database that you need to marge as peek

but in IT there is always solution for Exception..


try good joining and Sub Queries
and for data format as you like [username, skillnames, skillcount, qualnames, qualcount, email, phone]

you need to use Coalesce() in build function of SQL

for more
Using Coalesce() in sqlserver[^]

http://msdn.microsoft.com/en-us/library/ms190349.aspx[^]

HAppy to help!!!

这篇关于如何将多个sql select语句中的数据填充到一个ridview中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！