Hi,
Kindly excuse if I am posting in the wrong place.
I am using Visual Studio 2008, .net framework 3.5, asp.net , c# and sql server 2005.
I am supposed to pass stored procedures from client to wcf service.
The WCF service should execute the stored procedure and return the result.
When I pass the stored procedure which does not have any parameter, it works, but the moment I pass any parameter, it throws error as below
There was an error while trying to serialize parameter http://tempuri.org/:sqlparams. The InnerException message was 'Type 'System.Data.Sq lTypes.SqlInt32 ' with data contract name 'int:http://www.w3.org/2001/XMLSchema' is not expected. Add any types not known statically to the list of known types - for example, by using the KnownTypeAttrib ute attribute or by adding them to the list of known types passed to DataContractSer ializer.'. Please see InnerException for more details.
I provide the snapshot of the WCF service, The below is IService - namespace wcfstoredprocedure
-
{
-
[ServiceContract]
-
public interface IService1
-
{
-
[OperationContract]
-
Employee GetReturnValues(string storedprocedure, SqlParameter[] sqlparams);
-
}
-
-
[DataContract]
-
public class Employee
-
{
-
[DataMember]
-
private int empId;
-
public int EmpId
-
{
-
get { return empId; }
-
set { empId = value; }
-
}
-
[DataMember]
-
private string empName;
-
public string EmpName
-
{
-
get { return empName; }
-
set { empName = value; }
-
}
-
[DataMember]
-
private DateTime empJoinDate;
-
public DateTime EmpJoinDate
-
{
-
get { return empJoinDate; }
-
set { empJoinDate = value; }
-
}
-
[DataMember]
-
private int empOnDuty;
-
public int EmpOnDuty
-
{
-
get { return empOnDuty; }
-
set { empOnDuty = value; }
-
}
-
}
-
}
-
The Below code is of Service1.cs - namespace wcfstoredprocedure
-
{
-
// NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in Web.config and in the associated .svc file.
-
public class Service1 : IService1
-
{
-
DataTable dt = new DataTable();
-
public Employee GetReturnValues(string storedprocedure, SqlParameter[] sqlparams)
-
{
-
Employee emp = new Employee();
-
using (SqlConnection sqlCon=new SqlConnection(@"server=ABC-415D0247602\SQLEXPRESS;integrated security=true;database=Employee"))
-
{
-
using (SqlCommand sqlCom = new SqlCommand("GetEmpDuty", sqlCon))
-
{
-
-
SqlDataAdapter da = new SqlDataAdapter(sqlCom);
-
da.Fill(dt);
-
}
-
}
-
emp.EmpId =Convert.ToInt32(dt.Rows[0]["Id"]);
-
emp.EmpJoinDate =Convert.ToDateTime(dt.Rows[0]["joindate"]);
-
emp.EmpName = dt.Rows[0]["Name"].ToString();
-
emp.EmpOnDuty = Convert.ToInt32(dt.Rows[0]["onduty"]);
-
-
return emp;
-
}
-
}
-
}
-
The below is the code for the Client -
ServiceReference1.Employee sq = new WebApplicationstoredprocedure.ServiceReference1.Employee();
-
ServiceReference1.Service1Client sc = new WebApplicationstoredprocedure.ServiceReference1.Service1Client();
-
SqlParameter[] sqlparams=new SqlParameter[1];
-
-
sqlparams[0] = new SqlParameter("@Id", SqlDbType.Int);
-
sqlparams[0].Value = 1;
-
sq = sc.GetReturnValues("GetEmpDuty", sqlparams);
-
Response.Write(sq.empId.ToString());
-
Response.Write("<br>");
-
Response.Write(sq.empJoinDate.ToString());
-
Response.Write("<br>");
-
Response.Write(sq.empName.ToString());
-
Response.Write("<br>");
-
Response.Write(sq.empOnDuty.ToString());
-
Please do let me know where am I going wrong.
I tried to add [Serializable], but did not work.
Changed sqlparameters to object, but did not work.
Any suggestions
Regards
cmrhema
3 5047 Plater 7,872
Recognized Expert Expert
Not knowing much about it, what if you took a single instance of an SqlCommand (which would have the procedure name and the parameters stored in itself) instead of a string and SqlParameter[] ?
Hi Plater ,
Thanks for the reply.
I have changed the whole service as below
My service looks as (Iservice.cs) - using System;
-
using System.Collections.Generic;
-
using System.Linq;
-
using System.Runtime.Serialization;
-
using System.ServiceModel;
-
using System.Text;
-
using System.Data.SqlClient;
-
-
namespace wcfstoredprocedure
-
{
-
[ServiceContract]
-
public interface IService1
-
{
-
[OperationContract]
-
void GetReturnValues(string storedprocedure, Employee[] empname);
-
}
-
-
[DataContract]
-
public class Employee
-
{
-
[DataMember]
-
public string Paraname;
-
[DataMember]
-
public string Paravalue;
-
[DataMember]
-
public string Paratype;
-
}
-
}
-
Service.svc.cs - using System;
-
using System.Collections.Generic;
-
using System.Linq;
-
using System.Runtime.Serialization;
-
using System.ServiceModel;
-
using System.Text;
-
using System.Data.SqlClient;
-
using System.Data;
-
using System.Data.SqlTypes;
-
-
namespace wcfstoredprocedure
-
{
-
// NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in Web.config and in the associated .svc file.
-
-
public class Service1 : IService1
-
{
-
-
public void GetReturnValues(string storedprocedure, Employee[] empname)
-
{
-
-
Employee emp = new Employee();
-
using (SqlConnection sqlCon = new SqlConnection(@"server=ABC-415D0247602\SQLEXPRESS;integrated security=true;database=Employee"))
-
{
-
using (SqlCommand sqlCom = new SqlCommand(storedprocedure, sqlCon))
-
{
-
sqlCon.Open();
-
for (int i = 0; i < empname.Length; i++)
-
{
-
sqlCom.Parameters.Add(empname[i].Paraname, empname[i].Paratype);
-
sqlCom.Parameters[i].Value = empname[i].Paravalue;
-
}
-
sqlCom.ExecuteNonQuery();
-
-
}
-
}
-
-
}
-
}
-
}
-
My Client has two files
one class file and one code behind file.
The class file - using System;
-
using System.Collections.Generic;
-
using System.Linq;
-
using System.Web;
-
using System.ServiceModel;
-
using System.Runtime.Serialization;
-
-
namespace WebApplicationstoredprocedure
-
{
-
[ServiceContract]
-
public interface IService1
-
{
-
[OperationContract]
-
void GetReturnValues(string storedprocedure, Employee[] empname);
-
}
-
-
[DataContract]
-
public class Employee
-
{
-
-
[DataMember]
-
public string Paraname;
-
[DataMember]
-
public string Paravalue;
-
[DataMember]
-
public string Paratype;
-
-
-
}
-
}
The code behind file -
using System;
-
using System.Collections.Generic;
-
using System.Linq;
-
using System.Web;
-
using System.Web.UI;
-
using System.Web.UI.WebControls;
-
using System.Data.SqlClient;
-
using System.Data;
-
using System.Net;
-
using System.ServiceModel;
-
-
namespace WebApplicationstoredprocedure
-
{
-
public partial class _Default : System.Web.UI.Page
-
{
-
protected void Page_Load(object sender, EventArgs e)
-
{
-
-
-
WSHttpBinding ws = new WSHttpBinding();
-
EndpointAddress ep = new EndpointAddress(@"http://localhost:1150/Service1.svc");
-
ChannelFactory<IService1> chFactory = new ChannelFactory<IService1>(ws, ep);
-
IService1 channel = chFactory.CreateChannel();
-
-
Employee[] sqlparams = new Employee[4];
-
-
sqlparams[0] = new Employee();
-
sqlparams[0].Paratype = "SqlDbType.Int";
-
sqlparams[0].Paraname = "@Id";
-
sqlparams[0].Paravalue = "4";
-
-
sqlparams[1] = new Employee();
-
sqlparams[1].Paratype = "SqlDbType.NVarChar";
-
sqlparams[1].Paraname = "@name";
-
sqlparams[1].Paravalue = "bb";
-
-
sqlparams[2] = new Employee();
-
sqlparams[2].Paratype = "SqlDbType.DateTime";
-
sqlparams[2].Paraname = "@joindate";
-
sqlparams[2].Paravalue = "10/10/2000";
-
-
sqlparams[3] = new Employee();
-
sqlparams[3].Paratype = "SqlDbType.Bit";
-
sqlparams[3].Paraname = "@onduty";
-
sqlparams[3].Paravalue = "true";
-
channel.GetReturnValues("InsertEmpDuty", sqlparams);
-
-
}
-
}
-
}
I am providing what exactly appears in the watch window in service
storedprocedure "InsertEmpD uty" string
empname {wcfstoredproce dure.Employee[0]} wcfstoredproced ure.Employee[]
Whereas I have passed 4 parameters to empname in client, this is the watch window of client
- sqlparams {WebApplication storedprocedure .Employee[4]} WebApplications toredprocedure. Employee[]
+ [0] {WebApplication storedprocedure .Employee} WebApplications toredprocedure. Employee
+ [1] {WebApplication storedprocedure .Employee} WebApplications toredprocedure. Employee
+ [2] {WebApplication storedprocedure .Employee} WebApplications toredprocedure. Employee
+ [3] {WebApplication storedprocedure .Employee} WebApplications toredprocedure. Employee
Its really driving me crazy.....
Regards
cmrhema
The error has been rectified
Both the service and the client were under different namespace, hence we had errors.
After sharing the same namespace, the service and client works properly
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bob |
last post by:
I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type. In the input
parameters data field of the...
|
by: TJS |
last post by:
trying to get a record count from a stored procedure using a supplied SQL
statement
Error msg:
===========
"The SqlParameterCollection only accepts non-null SqlParameter type
objects,not String objects."
SPROC:
========
|
by: Mike |
last post by:
Hi,
I am trying to insert parameters into a stored procedure using DAAB (see
code at the bottom of this post). I am getting the following error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details:...
|
by: Jeremy Ames |
last post by:
I have a datagrid that updates the table using a stored procedure. The stored procedure is confirmed to complete correctly, yet the sql data adapter is returning an error that my application is catching.
Everything within my stored procedure is contained within a transaction. When utilizing the sql profiler, we can see that the transaction successfully begins and commits. The data in the sql table is updated at this point.
My data...
|
by: Jimmy |
last post by:
I have a web page that displays data from a sql server db. I'm using a stored
procedure to return data from a field of type text as an OUTPUT parameter.
How can I use an OUTPUT parameter of datatype text being returned from a
stored procedure? I need to use it's value to display comments text on a
profile page. The comments will be more than 8000 characters so using varchar
is not an option.
Maybe I'm on the wrong track...if so, then...
| |
by: Magy |
last post by:
What would be the best way to execute a Oracle stored procedure that excepts
several input paramters, through a web method in vb.net. What would be a
good way to get to the web method, the Oracle stored procedure's definition;
the name of the proc. and all of the parameter information such as name,
value, type and value. I'm looking for the best method. Any ideas.
Magy
|
by: Ranginald |
last post by:
Hi,
I'm having trouble passing a parameter from my default.aspx page to my
default2.aspx page.
I have values from a query in a list box and the goal is to pass the
"catID" from default.aspx to a stored procedure on the details2.aspx
page.
I can successfully pass the values from the listbox control to a
|
by: Cirene |
last post by:
I have a stored procedure named insertcompany.
I keep getting the error:
Sys.webforms.pagerequestmanagerservererrorexception: Procedure or function
'InsertCompany' expects parameter '@ApptPhone1Required', which was not
supplied.
But, I DID supply it along with all others.... (here's a snippet)
MyCommand.Parameters.AddWithValue("@InstructionsOperator",
|
by: Soniad |
last post by:
Hello,
I have stored procedure in which i am passing parameters (varchar(8000)),
these parameters can have values or can be empty (''), i have written blocks of codes for each parameter , which executes only if parameter has values, it should not enter the block if parameter is empty,
how to detect whether the parameter is empty or not.
and also i am having output parameter (varchar(1)) that is returned by stored procedure, if all of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |