如何从aspx页面获取数据到存储过程？-Java 学习之路

我必须在asp.net中创建一个RDLC报告 . 我正在使用不同的标量函数编写存储过程 . 我有一个GUI，我必须根据该标准选择一个标准并收集数据 . GUI在这里：
enter image description here
我已经实现了所有情况，如"All Staff"，"All services"等，但问题来自我必须从列表框中选择特定记录并根据它来提供数据 . 例如，我从列表中选择特定的员工记录，我的报告应显示仅具有所选记录的记录 . 我怎么处理这个？我的意思是当我从列表框中选择一些记录时，如何将这些记录带到我的存储过程的where子句以及如何在那里使用它？

我正在使用SQL SERVER 2008.我的存储过程是：

USE [PC_Rewrite]

GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo] . [spGetClients]（@ orderBy varchar（50））

AS BEGIN - 添加了SET NOCOUNT ON以防止额外的结果集 - 干扰SELECT语句 . SET NOCOUNT ON;

-- Insert statements for procedure here
IF(@orderBy = 'Consumer Name')
BEGIN
    SELECT c.Id, c.LastName, c.FirstName, c.MiddleInit, 
    c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, cs.Status, ca.Address, co.Phone, 
    dbo.GetEthnicity(c.Id) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id) AS Disabilities, 
    dbo.GetClientStaffContacts(c.Id) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
    dbo.GetClientInsuranceProviders(c.Id) AS InsuranceProvider FROM Client c 
    LEFT OUTER JOIN ClientStatus cs ON cs.Id = c.StatusId  
    LEFT OUTER JOIN(        
        SELECT ca.ParentEntityId, ca.Address
        FROM ContactAddress ca
        INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
    ) ca ON c.Id = ca.ParentEntityId 
    LEFT OUTER JOIN(        
        SELECT co.ParentEntityId, co.ContactData Phone
        FROM ContactOther co
        INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
    ) co ON c.Id = co.ParentEntityId 
    ORDER BY c.LastName, c.FirstName, c.MiddleInit
END

ELSE IF(@orderBy = 'Consumer Address')
BEGIN
    SELECT c.Id, c.LastName, c.FirstName, c.MiddleInit, 
    c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, cs.Status, ca.Address, co.Phone, 
    dbo.GetEthnicity(c.Id) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id) AS Disabilities, 
    dbo.GetClientStaffContacts(c.Id) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
    dbo.GetClientInsuranceProviders(c.Id) AS InsuranceProvider FROM Client c 
    LEFT OUTER JOIN ClientStatus cs ON cs.Id = c.StatusId  
    LEFT OUTER JOIN(        
        SELECT ca.ParentEntityId, ca.Address
        FROM ContactAddress ca
        INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
    ) ca on c.Id = ca.ParentEntityId 
    LEFT OUTER JOIN(        
        SELECT co.ParentEntityId, co.ContactData Phone
        FROM ContactOther co
        INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
    ) co ON c.Id = co.ParentEntityId 
    ORDER BY ca.Address
END

结束

任何帮助，将不胜感激 .

4 回答

1
如果您的存储过程看起来像这样：
```
Create PROCEDURE [dbo].[sp]
@ID int
AS
BEGIN
    Select ID, Display, GroupID           
    From SystemTypes
    Where ID = @ID
END
```
然后你可以像这样设置ID参数：
```
SqlCommand command = new SqlCommand("sp",connection);
command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID, SqlDbType.Int).Value = **VALUE**;
conn.open();

SqlDataReader reader = command.ExecuteReader();
```
如果你想使用像逗号分隔值字符串的东西..我发现此链接：

http://www.outsightinteractive.com/blog/comma_delimited_list_as_stored_procedure_parameter

http://www.4guysfromrolla.com/webtech/031004-1.shtml

Passing List<> to SQL Stored Procedure
回复于 2024-04-29T00:34:24+08:00
0

您的存储过程是否已存在？如果是这样，你必须使用参数吗？您可以根据需要随意更改存储过程吗？

如何调用存储过程有很多选项 . 这里有两个：您可以创建一个SqlCommand对象并将其设置为使用存储过程，然后为where子句中的各个子句创建SqlParameters . 例如，显示屏幕上每个选项的参数 . 对于列表框，您可以传入逗号分隔字符串，但这取决于您的数据库架构 .

另一种方法是使用Linq To Sql并将sproc拖放到设计器上并让它为您创建一个方法，然后使用生成的方法参数调用它 .

很抱歉不是更具体，但它受存储过程的工作方式的影响 .

回复于 2024-04-29T00:34:24+08:00
0

我想您想将搜索条件传递给您的商店程序吗？使用这样的东西..

//在代码后面

CommandType.StoredProcedure;

//为您的条件传递每个参数，如下面的Command.Parameters.Add（“@ ID，SqlDbType.string）.Value = staff;
Command.Parameters.Add（“@ ID，SqlDbType.string）.Value = service;

//现在在store过程中声明sql为varchar（200）set sql =“select * from tablename”

//检查参数是空白的还是有值if（@ paraname1！=“”）set sql = sql“where ... parameter1”;万一

if（@ paraname2！=“”）设置sql = sql“where ... parameter2”;万一

这是你在找什么？

回复于 2024-04-29T00:34:24+08:00

set @query ='select Resume.ResumeID，Resume.Name，Resume.DepartmentID，Department.DepartmentName，Resume.CurrentCompany，Resume.CurrentLocation，Resume.Phone，Resume.Email，Resume.KeySkills，Resume.TotalExpYears，Resume.TotalExpMonths， Resume.CandidateTextResume，Resume.ResumeStatus，Resume.IsActive来自[Resume]内部连接[Department] on [Resume] .DepartmentID = [Department] .DepartmentID其中[Resume] .IsActive = 1'

if(@DepartmentName != '')
begin 
        select  @query = @query + ' And [Department].DepartmentName like ''%'  + @DepartmentName + '%'''
end  

if(@TotalExpYears != '')
begin 
        select  @query = @query + ' And TotalExpYears like ''%'  + @TotalExpYears + '%'''
end  

if(@TotalExpMonths != '')
begin 
        select  @query = @query + ' And TotalExpMonths like ''%'  + @TotalExpMonths + '%'''
end

if(@KeySkills != '')
begin 
        select  @query = @query + ' And KeySkills like ''%'  + @KeySkills + '%'''
end

if(@CurrentLocation != '')
begin 
        select  @query = @query + ' And CurrentLocation like ''%'  + @CurrentLocation + '%'''
end

if(@CandidateTextResume != '')
begin 
        select  @query = @query + ' And CandidateTextResume like ''%'  + @CandidateTextResume + '%'''
end

if(@CurrentCompany != '')
begin 
        select  @query = @query + ' And CurrentCompany like ''%'  + @CurrentCompany + '%'''
end
if(@ResumeStatus != '')
begin
    select  @query = @query + ' And ResumeStatus like ''%'  + @ResumeStatus + '%'''
end

============== @strId as varchar（100）// strId是逗号分隔的ids'select * FROM tblSchool where intSchoolId IN（'@strId'）'

=================现在好吗？

回复于 2024-04-29T00:34:24+08:00

如何从aspx页面获取数据到存储过程？

4 回答

相关问题