By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,263 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,263 IT Pros & Developers. It's quick & easy.

Error while passing sql parameters to stored procedure in WCF service

100+
P: 375
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.SqlTypes.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 KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'. Please see InnerException for more details.

I provide the snapshot of the WCF service, The below is IService
Expand|Select|Wrap|Line Numbers
  1. namespace wcfstoredprocedure
  2. {
  3.     [ServiceContract]
  4.     public interface IService1
  5.     {
  6.         [OperationContract]
  7.         Employee GetReturnValues(string storedprocedure, SqlParameter[] sqlparams);
  8.    }
  9.  
  10.     [DataContract]
  11.     public class Employee
  12.     {
  13.         [DataMember]
  14.         private int empId;
  15.         public int EmpId
  16.         {
  17.             get { return empId; }
  18.             set { empId = value; }
  19.         }
  20.         [DataMember]
  21.         private string empName;
  22.         public string EmpName
  23.         {
  24.             get { return empName; }
  25.             set { empName = value; }
  26.         }
  27.         [DataMember]
  28.         private DateTime empJoinDate;
  29.         public DateTime EmpJoinDate
  30.         {
  31.             get { return empJoinDate; }
  32.             set { empJoinDate = value; }
  33.         }
  34.         [DataMember]
  35.         private int empOnDuty;
  36.         public int EmpOnDuty
  37.         {
  38.             get { return empOnDuty; }
  39.             set { empOnDuty = value; }
  40.         }
  41.     }
  42. }
  43.  
The Below code is of Service1.cs

Expand|Select|Wrap|Line Numbers
  1. namespace wcfstoredprocedure
  2. {
  3.     // 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.
  4.     public class Service1 : IService1
  5.     {
  6.         DataTable dt = new DataTable();
  7.         public Employee GetReturnValues(string storedprocedure, SqlParameter[] sqlparams)
  8.         {
  9.             Employee emp = new Employee();
  10.             using (SqlConnection sqlCon=new SqlConnection(@"server=ABC-415D0247602\SQLEXPRESS;integrated security=true;database=Employee"))
  11.             {
  12.                 using (SqlCommand sqlCom = new SqlCommand("GetEmpDuty", sqlCon))
  13.                 {
  14.  
  15.                     SqlDataAdapter da = new SqlDataAdapter(sqlCom);
  16.                      da.Fill(dt);
  17.                 }
  18.             }
  19.             emp.EmpId =Convert.ToInt32(dt.Rows[0]["Id"]);
  20.             emp.EmpJoinDate =Convert.ToDateTime(dt.Rows[0]["joindate"]);
  21.             emp.EmpName = dt.Rows[0]["Name"].ToString();
  22.             emp.EmpOnDuty = Convert.ToInt32(dt.Rows[0]["onduty"]);
  23.  
  24.             return emp;
  25.         }
  26.     }
  27. }
  28.  


The below is the code for the Client

Expand|Select|Wrap|Line Numbers
  1. ServiceReference1.Employee sq = new WebApplicationstoredprocedure.ServiceReference1.Employee();
  2. ServiceReference1.Service1Client sc = new WebApplicationstoredprocedure.ServiceReference1.Service1Client();
  3. SqlParameter[] sqlparams=new SqlParameter[1];
  4.  
  5. sqlparams[0] = new SqlParameter("@Id", SqlDbType.Int);
  6. sqlparams[0].Value = 1;
  7. sq = sc.GetReturnValues("GetEmpDuty", sqlparams);
  8. Response.Write(sq.empId.ToString());
  9. Response.Write("<br>");
  10. Response.Write(sq.empJoinDate.ToString());
  11. Response.Write("<br>");
  12. Response.Write(sq.empName.ToString());
  13. Response.Write("<br>");
  14. Response.Write(sq.empOnDuty.ToString());
  15.  
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
Nov 1 '09 #1
Share this Question
Share on Google+
3 Replies


Plater
Expert 5K+
P: 7,872
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[] ?
Nov 3 '09 #2

100+
P: 375
Hi Plater ,
Thanks for the reply.
I have changed the whole service as below

My service looks as (Iservice.cs)

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.Text;
  7. using System.Data.SqlClient;
  8.  
  9. namespace wcfstoredprocedure
  10. {
  11.     [ServiceContract]
  12.     public interface IService1
  13.     {
  14.         [OperationContract]
  15.         void GetReturnValues(string storedprocedure, Employee[] empname);
  16.    }
  17.  
  18.     [DataContract]
  19.     public class Employee
  20.     {
  21.         [DataMember]
  22.         public string Paraname;
  23.         [DataMember]
  24.         public string Paravalue;
  25.         [DataMember]
  26.         public string Paratype;
  27.     }
  28. }
  29.  
Service.svc.cs
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.Text;
  7. using System.Data.SqlClient;
  8. using System.Data;
  9. using System.Data.SqlTypes;
  10.  
  11. namespace wcfstoredprocedure
  12. {
  13.     // 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.
  14.  
  15.     public class Service1 : IService1
  16.     {
  17.  
  18.         public void GetReturnValues(string storedprocedure, Employee[] empname)
  19.         {
  20.  
  21.             Employee emp = new Employee();
  22.             using (SqlConnection sqlCon = new SqlConnection(@"server=ABC-415D0247602\SQLEXPRESS;integrated security=true;database=Employee"))
  23.             {
  24.                 using (SqlCommand sqlCom = new SqlCommand(storedprocedure, sqlCon))
  25.                 {
  26.                     sqlCon.Open();
  27.                     for (int i = 0; i < empname.Length; i++)
  28.                     {
  29.                         sqlCom.Parameters.Add(empname[i].Paraname, empname[i].Paratype);
  30.                         sqlCom.Parameters[i].Value = empname[i].Paravalue;
  31.                     }
  32.                     sqlCom.ExecuteNonQuery();
  33.  
  34.                 }
  35.             }
  36.  
  37.         }
  38.     }
  39. }
  40.  

My Client has two files
one class file and one code behind file.

The class file
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.ServiceModel;
  6. using System.Runtime.Serialization;
  7.  
  8. namespace WebApplicationstoredprocedure
  9. {
  10.     [ServiceContract]
  11.     public interface IService1
  12.     {
  13.         [OperationContract]
  14.         void GetReturnValues(string storedprocedure, Employee[] empname);
  15.     }
  16.  
  17.     [DataContract]
  18.     public class Employee
  19.     {
  20.  
  21.        [DataMember]
  22.         public string Paraname;
  23.         [DataMember]
  24.         public string Paravalue;
  25.         [DataMember]
  26.         public string Paratype;
  27.  
  28.  
  29.     }
  30. }
The code behind file
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data.SqlClient;
  8. using System.Data;
  9. using System.Net;
  10. using System.ServiceModel;
  11.  
  12. namespace WebApplicationstoredprocedure
  13. {
  14.     public partial class _Default : System.Web.UI.Page
  15.     {
  16.         protected void Page_Load(object sender, EventArgs e)
  17.         {
  18.  
  19.  
  20.             WSHttpBinding ws = new WSHttpBinding();
  21.             EndpointAddress ep = new EndpointAddress(@"http://localhost:1150/Service1.svc");
  22.             ChannelFactory<IService1> chFactory = new ChannelFactory<IService1>(ws, ep);
  23.             IService1 channel = chFactory.CreateChannel();
  24.  
  25.             Employee[] sqlparams = new Employee[4];
  26.  
  27.             sqlparams[0] = new Employee();
  28.             sqlparams[0].Paratype = "SqlDbType.Int";
  29.             sqlparams[0].Paraname = "@Id";
  30.             sqlparams[0].Paravalue = "4";
  31.  
  32.             sqlparams[1] = new Employee();
  33.             sqlparams[1].Paratype = "SqlDbType.NVarChar";
  34.             sqlparams[1].Paraname = "@name";
  35.             sqlparams[1].Paravalue = "bb";
  36.  
  37.             sqlparams[2] = new Employee();
  38.             sqlparams[2].Paratype = "SqlDbType.DateTime";
  39.             sqlparams[2].Paraname = "@joindate";
  40.             sqlparams[2].Paravalue = "10/10/2000";
  41.  
  42.             sqlparams[3] = new Employee();
  43.             sqlparams[3].Paratype = "SqlDbType.Bit";
  44.             sqlparams[3].Paraname = "@onduty";
  45.             sqlparams[3].Paravalue = "true";
  46.             channel.GetReturnValues("InsertEmpDuty", sqlparams);
  47.  
  48.         }
  49.     }
  50. }
I am providing what exactly appears in the watch window in service
storedprocedure "InsertEmpDuty" string
empname {wcfstoredprocedure.Employee[0]} wcfstoredprocedure.Employee[]


Whereas I have passed 4 parameters to empname in client, this is the watch window of client
- sqlparams {WebApplicationstoredprocedure.Employee[4]} WebApplicationstoredprocedure.Employee[]
+ [0] {WebApplicationstoredprocedure.Employee} WebApplicationstoredprocedure.Employee
+ [1] {WebApplicationstoredprocedure.Employee} WebApplicationstoredprocedure.Employee
+ [2] {WebApplicationstoredprocedure.Employee} WebApplicationstoredprocedure.Employee
+ [3] {WebApplicationstoredprocedure.Employee} WebApplicationstoredprocedure.Employee

Its really driving me crazy.....

Regards
cmrhema
Nov 4 '09 #3

100+
P: 375
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
Nov 4 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.