Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » WCF »
Error while passing sql parameters to stored procedure in WCF
Posted Date: 01 Nov 2009 Posted By: Hema Member Level: Bronze Points: 1
Responses:
4
|
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
|
Responses
|
| Author: Hari 01 Nov 2009 | Member Level: Gold | Rating:  Points: 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);
TimeValidation.rar | | Author: Hema 01 Nov 2009 | Member Level: Bronze | Rating: Points: 2 |
Hi,
Thanks Hari, for the response(that too on Sunday).
I modified the service class as below
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;
}
}
}
and the client as
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";
}
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?
| | Author: Hari 03 Nov 2009 | Member Level: Gold | Rating: Points: 6 |
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
| | Author: Hema 03 Nov 2009 | Member Level: Bronze | Rating: Points: 2 |
Thnx Hari. Solved it.
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|