如何为返回单个记录的SQL Server存储过程编写WCF [英] How to write WCF for a SQL Server stored procedure that returns a single record

问题描述

我是WCF的新手，需要创建一个IIS托管的WCF服务，该服务将运行一个接受记录ID的存储过程，并返回一个包含25个字段的记录。考虑到从存储过程返回的字段数，最简单的方法是什么。具有最小复杂性的WCF信息令人沮丧地难以找到。 :(想从Web服务转移到WCF ...

PS - 使用VS2012和VB，但如果需要可以将C＃转换为VB。



在等待答案的同时进行进一步的研究，似乎我可以使用sql中的数据集，然后需要将其转换为xml以从服务返回值？

  ALTER   PROCEDURE  [dbo]。[procImpoundRec_Get] 
  @ ID  intASBEGIN 
   -   添加SET NOCOUNT ON以防止额外的结果集 
   -   干扰SELECT语句。 
  SET   NOCOUNT   ON ; 
  SELECT  * 
  FROM  tblImpoundRecs 
  WHERE  ID =  @ ID  
 
  END  

这是接口在AADM_Service_Interface：

< servicecontract（）> 
 公共 接口 AADM_Service_Interface 
< operationcontract（）> 
 功能 Impound_Get（ ByVal  ID  As  整数）作为 字符串 
 结束 接口 

这是'实施：

 公共 类 AADM_Service 
 实现 AADM_Service_Interface 
 
 公共 功能 Impound_GetRecord（ID 作为 整数 ）作为 字符串 实现 AADM_Service_Interface.Impound_Get 
  Dim  objConnect  As  新 SqlConnection（ConnectionStrings.AADM）
  Dim  myCommand  As  SqlCommand = 新 SqlCommand 
  Dim  strReturnRecord  As   String  =   
 使用 myCommand .Connection = objConnect .CommandText =   procImpoundRec_Get 
 .CommandType = CommandType.StoredProcedure .Parameters.Add（新 SqlParameter（  @ ID，ID））
 结束 使用 
 
 昏暗 obj DataAdapter  As  新 SqlDataAdapter（myCommand）
  Dim  ds 作为 新 DataSet 
 
 尝试 
 objDataAdapter.Fill（ds）
 strReturnRecord = ds.GetXml 
 
  Catch  ex  As 例外
 
 结束 尝试 
 
 返回 strReturnRecord 
 
 结束 功能 
 
 结束 班级 

置于评论[/编辑]

解决方案

创建所有SQL组件。这是存储过程：

  ALTER   PROCEDURE  [dbo]。[procImpoundRec_Get] 
  @ ID  intASBEGIN 
 
   -   添加SET NOCOUNT ON以防止额外的结果集 
   -   干扰SELECT语句。  
 
  SET   NOCOUNT   ON ; 
 
  SELECT  *  FROM  tblImpoundRecs  WHERE  ID =  @ IDEND  

这里'' AADM_Service_Interface中的接口：

< servicecontract（）> 
 公共 接口 AADM_Service_Interface< operationcontract（）> 
 功能 Impound_Get（ ByVal  ID  As  整数）作为 字符串 
 结束 接口 

这里''实施：

 公共 类 AADM_Service  Implements  AADM_Service_Interface 
 公共 功能 Impound_GetRecord（ID  As  整数） As  字符串 
 实现 AADM_Service_Interface.Impound_Get 
  Dim  obj Connect  As  新 SqlConnection（ConnectionStrings.AADM）
  Dim  myCommand  As  SqlCommand = 新 SqlCommand 
  Dim  strReturnRecord  As   String  =   
 使用 myCommand 
 .Connection = objConnect 
 .CommandText =   procImpoundRec_Get 
 .CommandType = CommandType.StoredProcedure 
 .Parameters.Add（新 SqlParameter（  @ID ，ID））
 结束 使用 
  Dim  objDataAdapter  As  新 SqlDataAdapter（myCommand）
  Dim  ds  As  新 DataSet 
 尝试 objDataAdapter.Fill（ds）
 strReturnRecord = ds.GetXml 
  Catch  ex  As 异常
 结束 尝试 
 
 返回 strReturnRecord 
 结束 功能 
 结束 类 

I''m new to WCF and need to create an IIS hosted WCF Service that will run a stored procedure that accepts a record ID and returns a single record that has 25 fields. What is the easiest way to accomplish this given the number of fields being returned from the stored procedure. WCF information with the least bit of complexity is frustratingly difficult to find. :( Would like to move from web service to WCF...
PS - Using VS2012 and VB but can convert C# to VB if necessary.

Upon further research while waiting for an answer, it appears I can use the dataset from the sql and then need to convert it to xml to return the value from the service?

[EDIT]

ALTER PROCEDURE [dbo].[procImpoundRec_Get]
    @ID intASBEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT *
FROM tblImpoundRecs
WHERE ID = @ID

END

Here''s the Interface in AADM_Service_Interface:

<servicecontract()>
Public Interface AADM_Service_Interface
<operationcontract()>
Function Impound_Get(ByVal ID As Integer) As String
End Interface

Here''s the implementation:

Public Class AADM_Service
    Implements AADM_Service_Interface

Public Function Impound_GetRecord(ID As Integer) As String Implements AADM_Service_Interface.Impound_Get
Dim objConnect As New SqlConnection(ConnectionStrings.AADM)
Dim myCommand As SqlCommand = New SqlCommand
Dim strReturnRecord As String = ""
With myCommand .Connection = objConnect .CommandText = "procImpoundRec_Get"
    .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@ID", ID))
End With

Dim objDataAdapter As New SqlDataAdapter(myCommand)
Dim ds As New DataSet

Try
    objDataAdapter.Fill(ds)
    strReturnRecord = ds.GetXml

Catch ex As Exception

End Try

Return strReturnRecord

End Function

End Class

Moved from comment[/EDIT]

解决方案

Created all SQL components. Here''s the stored procedure:

ALTER PROCEDURE [dbo].[procImpoundRec_Get] 
@ID intASBEGIN 

-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 

SET NOCOUNT ON; 

SELECT * FROM tblImpoundRecs WHERE ID = @IDEND 

Here''s the Interface in AADM_Service_Interface:

<servicecontract()> 
Public Interface AADM_Service_Interface <operationcontract()> 
  Function Impound_Get(ByVal ID As Integer) As String 
End Interface 

Here''s the implementation:

Public Class AADM_Service Implements AADM_Service_Interface 
 Public Function Impound_GetRecord(ID As Integer) As String 
 Implements AADM_Service_Interface.Impound_Get 
 Dim objConnect As New SqlConnection(ConnectionStrings.AADM) 
 Dim myCommand As SqlCommand = New SqlCommand 
 Dim strReturnRecord As String = "" 
 With myCommand 
  .Connection = objConnect 
  .CommandText = "procImpoundRec_Get" 
  .CommandType = CommandType.StoredProcedure 
  .Parameters.Add(New SqlParameter("@ID", ID)) 
 End With 
 Dim objDataAdapter As New SqlDataAdapter(myCommand) 
 Dim ds As New DataSet 
 Try objDataAdapter.Fill(ds) 
 strReturnRecord = ds.GetXml 
 Catch ex As Exception 
 End Try 

Return strReturnRecord 
End Function 
End Class

这篇关于如何为返回单个记录的SQL Server存储过程编写WCF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！