Click here to Skip to main content
15,935,429 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I am using Visual Studio 2010, .net framework 4.0, asp.net , c# and sql server 2008.
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
Please see InnerException for more details.
I provide the snapshot of the WCF service, The below is IService

Please help me to pass object[] to wcf service

Iservice1.cs

[ServiceContract]
public interface IService1
{
[OperationContract]
bool SaveRecord(string SQLQuery, object[] parameters);
}

Service1.cs

public class Service1 : IService1
{
public bool SaveRecord(string SQLQuery, object[] parameters)
{
bool call = DBConnectionProvider.saveRecord(SQLQuery, parameters);
return call;
}

}
}


I have created a function in Class1.cs file

public class DBConnectionProvider
{
private static SqlConnection dbCon = null;
static string connstr = "Connection String";

public static SqlConnection acquireConnection()
{
try
{
if (dbCon == null || dbCon.State == ConnectionState.Closed)
{
dbCon = new SqlConnection(connstr);

dbCon.Open();
}
}
catch (SqlException e)
{
throw e;
}
return dbCon;
}




public static bool saveRecord(string SQLQuery, object[] parameters)
{

//SqlTransaction transaction = sqlConnection.BeginTransaction();
try
{
SqlCommand command = new SqlCommand(SQLQuery, DBConnectionProvider.acquireConnection());
foreach (string[] param in parameters)
{
command.Parameters.AddWithValue("@" + param[0], param[1]);
}

int result = command.ExecuteNonQuery();
command.Dispose();
//transaction.Commit();
if (result > 0)
{
return true;
}
else
{
return false;
}
//command.Dispose();

}
catch (SqlException sqlError)
{
//transaction.Rollback();

throw new Exception(sqlError.Message.ToString(), sqlError);
}

}
}
Posted
Comments
ZurdoDev 13-Sep-13 12:57pm    
What's the error?

1 solution

You are passing a single dimension array as parameters, and while retrieving you are treating it as a two-dimension array.
C#
foreach (string[] param in parameters)


Whereas you need to pass a List<dictionary><string object="" paramvalue="">> (this gets changed into ArrayList in wsdl although). This will offer you a bit better type safety, but you still need to identify the data type of paramValue. So you also need to provide a data type as custom Enum. so it gets changed as below.

C#
public enum DataType { Int, Char, String, Date .... }

public static bool saveRecord(string SQLQuery, List<tuple><string,>> param) {

foreach(item in param) {
   switch(item.Item2) { case DataType.Int: 

   command.AddWithValue(item.Item1, int.Parse(item.Item3.ToString());  break;
    // similar case for all other enum
   }
}
</tuple>


However, you should have specific queries in stored procedure and specific functions for each. Else user can pass any number of parameters and any query which can lead to runtime SQL errors (for not bad users) or your entire DB deleted (for ruthless users).

Although this code will work but its not a good way.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900