I am trying to create a web service that I can use to return data from a table in my MSSQL server. The web service will be used to provide information for use in FLEX.
Thanks for responding. I have folowed the example. I am getting three errors.
I am getting an error on InitalizeComponent(); the error states: Method Must have a return type.
The next two errors are on the same line: DataSet ds = new DataSet(); the error reads: The type of namespace name 'DataSet' could not be found (are you missine a using directive or and assembly reference?) The error occurs on both words DataSet.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
namespace Muster_WebService
{
/// <summary>
/// Summary description for WebService1
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{
InitializeComponent();
[WebMethod]
public string CountWords(string Sentence)
{
string[] Words = Sentence.Split(' ');
return "Your sentence contains " + Words.Length + " word(s).";
}
[WebMethod]
public DataSet GetCustOrders(string IDMask)
{
IDMask = IDMask.Replace("'", "''");
//IDMask is the Customer ID that the client submits.
//Replace single quotation marks with two single quotation marks
//so that all single quotation marks in the CustomerID are parsed correctly.
//Modify this connection string to use your SQL Server and log on information.
SqlConnection con = new SqlConnection("server=<your server>;uid=<your user id>;pwd=<your Password>;database=northwind");
//Open the Customers table to serve as the parent table.
SqlDataAdapter daCust = new SqlDataAdapter("Select * From Customers Where CustomerID Like '%" + IDMask + "%'", con);
//Open the Orders table to serve as the child table.
SqlDataAdapter daOrders = new SqlDataAdapter("Select * From Orders Where CustomerID Like '%" + IDMask + "%'", con);
//Create a client-side DataSet to hold the Customers and Orders tables.
DataSet ds = new DataSet();
//Explicitly open the connection to allow explicit closing.
con.Open();
//Fill the DataSet with the Customers table and the Orders table.
daCust.Fill(ds, "Cust");
daOrders.Fill(ds, "Orders");
//Explicitly close the connection - do not wait for garbage collection.
con.Close();
//Relate Customers to Orders.
ds.Relations.Add("CustOrd", ds.Tables["Cust"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
//The relationship is Orders nested in Customers.
ds.Relations[0].Nested = true;
//Return the DataSet to the client.
return ds;
}
}
}
1,For the first question you can delete InitializeComponent() from web service, since the asp.net designer requires this call.
2,For the second question, you need to add System.Data.dll reference and using System.Data at the top of web service.
For second question I will advice not to return DataSet from the service as your client is a Flex client. Instead return and array or List of some class from the web service.
Thanks & Regards
Anup Das Gupta
Mark as Answer if you feel so. Visit My Blog
I keep getting the error below when I click on the operation in the .asmx file:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at WebService3.Referrals.GetDataSet(String strSQL)
at WebService3.Referrals.GetReferrals()
I have rewritten the code to look like this"
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Referrals
Inherits System.Web.Services.WebService
Private Function GetDataSet(ByVal strSQL As String) As DataSet
Dim myConnection As New SqlConnection("Data Source=DBServer;Initial Catalog=pophealth;User ID=userID;Password=myPassword;Trusted_Connection=true")
Dim myCommand As New SqlCommand(strSQL, myConnection)
myConnection.Open()
Dim myDataAdapter As New SqlDataAdapter()
myDataAdapter.SelectCommand = myCommand
Dim myDataSet As New DataSet()
myDataAdapter.Fill(myDataSet)
myConnection.Close()
Return myDataSet
End Function
<WebMethod()> Public Function GetReferrals() As DataSet
Return GetDataSet("SELECT * FROM tblReferral ORDER BY ReferralNum DESC")
End Function
End Class
This error has gotten me stumped. I have tried to write the web service 2 different ways and I continue to get this error:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Referral_WebService.WebService1.GetReferrals()
ckersey2
Member
94 Points
131 Posts
Web Service to return information from MSSQL
Jun 23, 2011 01:24 PM|LINK
Hi All
I am trying to create a web service that I can use to return data from a table in my MSSQL server. The web service will be used to provide information for use in FLEX.
Can anyone give me a hand?
Thanks in advance,
Chase K.
chase.kersey@med.navy.mil
Peter pi - M...
Star
12871 Points
1786 Posts
Re: Web Service to return information from MSSQL
Jun 27, 2011 09:41 AM|LINK
Hi ckersey2,
Regarding how to use a web service as a data source for a client application in visual C#.NET,you can refer to the following article.
http://support.microsoft.com/kb/308495
Best regards,
Peter
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
ckersey2
Member
94 Points
131 Posts
Re: Web Service to return information from MSSQL
Jun 27, 2011 02:04 PM|LINK
Hi Peter,
Thanks for responding. I have folowed the example. I am getting three errors.
I am getting an error on InitalizeComponent(); the error states: Method Must have a return type.
The next two errors are on the same line: DataSet ds = new DataSet(); the error reads: The type of namespace name 'DataSet' could not be found (are you missine a using directive or and assembly reference?) The error occurs on both words DataSet.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Services; using System.Data.SqlClient; namespace Muster_WebService { /// <summary> /// Summary description for WebService1 /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. // [System.Web.Script.Services.ScriptService] public class WebService1 : System.Web.Services.WebService { InitializeComponent(); [WebMethod] public string CountWords(string Sentence) { string[] Words = Sentence.Split(' '); return "Your sentence contains " + Words.Length + " word(s)."; } [WebMethod] public DataSet GetCustOrders(string IDMask) { IDMask = IDMask.Replace("'", "''"); //IDMask is the Customer ID that the client submits. //Replace single quotation marks with two single quotation marks //so that all single quotation marks in the CustomerID are parsed correctly. //Modify this connection string to use your SQL Server and log on information. SqlConnection con = new SqlConnection("server=<your server>;uid=<your user id>;pwd=<your Password>;database=northwind"); //Open the Customers table to serve as the parent table. SqlDataAdapter daCust = new SqlDataAdapter("Select * From Customers Where CustomerID Like '%" + IDMask + "%'", con); //Open the Orders table to serve as the child table. SqlDataAdapter daOrders = new SqlDataAdapter("Select * From Orders Where CustomerID Like '%" + IDMask + "%'", con); //Create a client-side DataSet to hold the Customers and Orders tables. DataSet ds = new DataSet(); //Explicitly open the connection to allow explicit closing. con.Open(); //Fill the DataSet with the Customers table and the Orders table. daCust.Fill(ds, "Cust"); daOrders.Fill(ds, "Orders"); //Explicitly close the connection - do not wait for garbage collection. con.Close(); //Relate Customers to Orders. ds.Relations.Add("CustOrd", ds.Tables["Cust"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]); //The relationship is Orders nested in Customers. ds.Relations[0].Nested = true; //Return the DataSet to the client. return ds; } } }chase.kersey@med.navy.mil
Peter pi - M...
Star
12871 Points
1786 Posts
Re: Web Service to return information from MSSQL
Jun 28, 2011 06:05 AM|LINK
Hi,
1,For the first question you can delete InitializeComponent() from web service, since the asp.net designer requires this call.
2,For the second question, you need to add System.Data.dll reference and using System.Data at the top of web service.
Best regards,
Peter
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
asteranup
All-Star
30184 Points
4906 Posts
Re: Web Service to return information from MSSQL
Jun 28, 2011 12:26 PM|LINK
Hi,
For second question I will advice not to return DataSet from the service as your client is a Flex client. Instead return and array or List of some class from the web service.
Anup Das Gupta
Mark as Answer if you feel so. Visit My Blog
ckersey2
Member
94 Points
131 Posts
Re: Web Service to return information from MSSQL
Jun 28, 2011 01:44 PM|LINK
Hi Peter,
I keep getting the error below when I click on the operation in the .asmx file:
Imports System.Web.Services Imports System.Web.Services.Protocols Imports System.ComponentModel Imports System.Data.SqlClient Imports System.Configuration Imports System.Data ' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. <System.Web.Script.Services.ScriptService()> _ <System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _ <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ <ToolboxItem(False)> _ Public Class Referrals Inherits System.Web.Services.WebService Private Function GetDataSet(ByVal strSQL As String) As DataSet Dim myConnection As New SqlConnection("Data Source=DBServer;Initial Catalog=pophealth;User ID=userID;Password=myPassword;Trusted_Connection=true") Dim myCommand As New SqlCommand(strSQL, myConnection) myConnection.Open() Dim myDataAdapter As New SqlDataAdapter() myDataAdapter.SelectCommand = myCommand Dim myDataSet As New DataSet() myDataAdapter.Fill(myDataSet) myConnection.Close() Return myDataSet End Function <WebMethod()> Public Function GetReferrals() As DataSet Return GetDataSet("SELECT * FROM tblReferral ORDER BY ReferralNum DESC") End Function End Classchase.kersey@med.navy.mil
ckersey2
Member
94 Points
131 Posts
Re: Web Service to return information from MSSQL
Jun 28, 2011 02:03 PM|LINK
Peter,
This error has gotten me stumped. I have tried to write the web service 2 different ways and I continue to get this error:
chase.kersey@med.navy.mil
Peter pi - M...
Star
12871 Points
1786 Posts
Re: Web Service to return information from MSSQL
Jun 29, 2011 02:33 AM|LINK
Hi ckersey2,
To modify the policy to allow full trust for all Intranet assemblies.Please refer to the following article to troubleshoot this issue.
http://social.msdn.microsoft.com/Forums/eu/sqlnetfx/thread/1505d7e6-59dc-4997-b707-9e9736a1f978
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49634
Best regards,
Peter
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework