将sql参数传递给WCF服务中的存储过程时出错 [英] Error while passing sql parameter to stored procedures in WCF service
问题描述
我使用的是Visual Studio 2010，.net framework 4.0，asp.net，c＃和sql server 2008.
我应该传递存储过程从客户端到wcf服务。
WCF服务应该执行存储过程并返回结果。
当我传递没有任何参数的存储过程时，它可以工作，但是当我传递任何参数时，它会抛出错误，如下所示
请参阅InnerException了解更多详情。
我提供WCF服务的快照，以下是IService
请帮我将对象[]传递给wcf服务
Iservice1.cs
[ServiceContract]
公共接口IService1
{
[OperationContract]
bool SaveRecord（字符串SQLQuery，object []参数）;
}
Service1.cs
公共类服务1：IService1
{
public bool SaveRecord（string SQLQuery，object [] parameters）
{
bool call = DBConnectionProvider.saveRecord（SQLQuery，parameters）;
回电;
}
}
}
我在Class1.cs文件中创建了一个函数
公共类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）
{
抛出e;
}
返回dbCon;
}
public static bool saveRecord（string SQLQuery，object []参数）
{
// SqlTransaction事务= sqlConnection.BeginTransaction（）;
尝试
{
SqlCommand command = new SqlCommand（SQLQuery，DBConnectionProvider.acquireConnection（））;
foreach（参数中的string []参数）
{
command.Param eters.AddWithValue（安培; QUOT; @&安培; QUOT; + param [0]，param [1]）;
}
int result = command.ExecuteNonQuery（）;
command.Dispose（）;
//transaction.Commit（）;
if（result& gt; 0）
{
返回true;
}
else
{
返回false ;
}
//command.Dispose（）;
}
catch（SqlException sqlError）
{
//transaction.Rollback（）;
抛出新的异常（sqlError.Message.ToString（），sqlError）;
}
}
}
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);
}
}
}
推荐答案
您正在传递单维数组作为para米，并且在检索时你将它视为一个二维数组。
You are passing a single dimension array as parameters, and while retrieving you are treating it as a two-dimension array.
foreach (string[] param in parameters)
你需要传递一个List< dictionary>< string object =paramvalue =>> （虽然这会在wsdl中更改为ArrayList）。这将为您提供更好的类型安全性，但您仍需要识别paramValue的数据类型。所以你还需要提供一个数据类型作为自定义枚举。所以它改变如下。
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.
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>
但是，您应该在存储过程和每个特定函数中具有特定查询。其他用户可以传递任意数量的参数和任何可能导致运行时SQL错误（对于不坏用户）或整个数据库被删除（对于无情用户）的查询。
虽然这段代码可行，但不是一个好方法。
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.
这篇关于将sql参数传递给WCF服务中的存储过程时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
