Error while passing sql parameters to stored procedure in WCF service

Question

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 am  providing  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
skrcmr

Answer 1

Greeting,

One suggestion, Create a DAL(Data Access Layer) and the put the code for database operation inside the DAL layer. Call the DAL from the WCF Service. Hope that your application have good software architecture.

Take Care

PL

---

Helping People To Solve Technical Problems

Answer 2

Hi,

Firstly,passinf sqlparameter as an operation parameter reduces your interoperability greatly. You might want pass a datacontract as the operation parameter and use a translator to convert that into your busines entiy and pass it on to the DAL layer. That said, please find below a fix to your problem. When using sqlparameter in an operation, the "Add service reference" will not work. Firstly you need to add the following to your service class

 [KnownType(typeof(SqlParameter))]
    [KnownType(typeof(SqlInt32))]
    public class Service1 : IService1
    {

Next use svcutil to generate your proxy and app.config and not "Add service reference". Assuming that your service url is as follows http://localhost:2632/Service1.svc?wsdl. Us ethe following command to generate your proxy and config file

G:\Program Files\Microsoft Visual Studio 9.0\VC>svcutil http://localhost:2632/Service1.svc?wsdl

Use the generated proxy and app.config to instantiate your client as follows. Note, svcutil generates its own SqlParameter class and you have to set the properties as below. I have uploaded a working sample

http://cid-05c2e50f2c5140c1.skydrive.live.com/self.aspx/.Public/TimeValidation.rar

Service1Client client = new Service1Client();

            SqlParameter[] sqlparams = new SqlParameter[1];

            sqlparams[0] = new SqlParameter();
            sqlparams[0].DbType = DbType.Int32;
            sqlparams[0].ParameterName = "@Id";
            sqlparams[0].Value = 1;
            sqlparams[0].Direction = ParameterDirection.Input;
            sqlparams[0].SourceVersion = DataRowVersion.Default;
            client.GetReturnValues("GetEmpDuty", sqlparams);

---

http://thoughtorientedarchitecture.blogspot.com/

Answer 3

Hi,
Thanks Hari, for the response(that too on Sunday).

I modified the service class as below

<span>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.
[KnownType(typeof(SqlParameter))]
[KnownType(typeof(SqlInt32))]
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(storedprocedure, sqlCon))
{
sqlCom.Parameters.Add(sqlparams);
sqlCom.ExecuteNonQuery();

}
}


return emp;
}
}
}
</span>

and the client as

<span>protected void Page_Load(object sender, EventArgs e)
{
Service1Client s1 = new Service1Client();
SqlParameter[] sqlparams = new SqlParameter[4];

sqlparams[0] = new SqlParameter();
sqlparams[0].DbType = DbType.Int32;
sqlparams[0].ParameterName = "@Id";
sqlparams[0].Value = 4;
sqlparams[0].Direction = ParameterDirection.Input;
sqlparams[0].SourceVersion = DataRowVersion.Default;
sqlparams[1] = new SqlParameter();
sqlparams[1].DbType = DbType.String;
sqlparams[1].ParameterName = "@name";
sqlparams[1].Value = "bb";
sqlparams[1].Direction = ParameterDirection.Input;
sqlparams[1].SourceVersion = DataRowVersion.Default;
sqlparams[2] = new SqlParameter();
sqlparams[2].DbType = DbType.DateTime;
sqlparams[2].ParameterName = "@joindate";
sqlparams[2].Value = DateTime.Now;
sqlparams[2].Direction = ParameterDirection.Input;
sqlparams[2].SourceVersion = DataRowVersion.Default;
sqlparams[3] = new SqlParameter();
sqlparams[3].DbType = DbType.Boolean;
sqlparams[3].ParameterName = "@onduty";
sqlparams[3].Value = true;
sqlparams[3].Direction = ParameterDirection.Input;
sqlparams[3].SourceVersion = DataRowVersion.Default;
s1.GetReturnValues("InsertEmpDuty", sqlparams);


string j = "test";


}</span>

I followed your svcutil regime and it works, but one minor hitch.
When I placed a breakpoint in the service I found all the values of parameters become NULL, and it throws error.

I changed the line
sqlCom.Parameters.Add(sqlparams);
to
sqlCom.Parameters.AddRange(sqlparams);

But then too all the values are passing as null. Any Idea why?

Answer 4

I have redone my whole wcf client side.

My service code looks like below

[ServiceContract]
    public interface IService1
    {
        [OperationContract]
        void GetReturnValues(string storedprocedure, Employee[] empname);
   }
  
    [DataContract]
    public class Employee
    {
        [DataMember]
        private string paraname;
        public string Paraname
        {
            get { return paraname; }
            set { paraname = value; }
        }
        private string paravalue;
        public string Paravalue
        {
            get { return paravalue; }
            set { paravalue = value; }
        }
        private string paratype;
        public string Paratype
        {
            get { return paratype; }
            set { paratype = value; }
        }
       
    }

     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();
                  
                }
            }
       
        }

and the client looks like below

            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);

Further I have added a class file in the client as

    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        void GetReturnValues(string storedprocedure, Employee[] empname);
    }

    [DataContract]
    public class Employee
    {
        [DataMember]
        private string paraname;
        public string Paraname
        {
            get { return paraname; }
            set { paraname = value; }
        }
        private string paravalue;
        public string Paravalue
        {
            get { return paravalue; }
            set { paravalue = value; }
        }
        private string paratype;
        public string Paratype
        {
            get { return paratype; }
            set { paratype = value; }
        }

Now after creating the wcf i DO NOT create a reference in the Client side.
The problem I am facing right now is, the values sqlparams which i pass as "returnvalues" consists of 4 parameters.
But when I place a break point in the service I find that 0 parameters are returned, the name of the stored procedure goes properly.

Can anyone please guide on this mysterious case of disappearance of values

Regards
cmrhema

Answer 5

Hi

You need to declare Paravalue,ParaType as [Datamembers] as well.

---

Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/

Answer 6

The issue is because you have declared your private members as [DataMember] and in your client you are using the public members to send the data. So your public members need to be decorated with [DataMember]

---

Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/

Answer 7

Thanks Hari, for your constant replies.
I modified that too, but no avail. Modified in service

[DataContract]
    public class Employee
    {
        [DataMember]
        public string Paraname;
        [DataMember]
        public string Paravalue;
        [DataMember]
        public string Paratype;
    }

Modified in client

 [DataContract]
    public class Employee
    {

       [DataMember]
        public string Paraname;
        [DataMember]
        public string Paravalue;
        [DataMember]
        public string Paratype;
       

    }

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

Answer 8

Hi,

I am uploading a sample. Can you please check that. I am hosting the service ina console app. So first right clickthe consolehost project and debug->start new instance and then start the asp.net web application which is the client.

http://cid-05c2e50f2c5140c1.skydrive.live.com/self.aspx/.Public/Test1.rar

Teh class that you added in the client needs to be in the same namespace as the ISERVICE1

---

Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/

Answer 9

Hi Please find below another sample. This is exactly like your scenario. I think the issue is fixed. the issue was with the namespace. http://cid-05c2e50f2c5140c1.skydrive.live.com/self.aspx/.Public/test2.rar

---

Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/

Answer 10

Bull's Eye.
Yes you were absolutely right.
Thnx a bunch Hari.

Regards
Hema

Answer 11

No problem :)

---

Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/