Error while passing sql parameters to stored procedure in WCF service

[cmrhema]

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

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

[Plater]

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[] ?

[cmrhema]

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 "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

[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