我在WebServices中写了如下函数来调用存储过程:
[WebMethod]
public void ExecuteProcedureReturnDataSetRef(string procName, SqlParameter[] coll, ref DataSet dst)
{
try
{
SqlDataAdapter dad = new SqlDataAdapter();
for (int i = 0; i < coll.Length; i++)
{
cmd.Parameters.Add(coll[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
dad.SelectCommand = cmd;
dad.Fill(dst);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
}
现在的问题是,我在C#中如何定义SqlParameter[] coll参数来进行传递?
我是如下写的,但是报错
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[1];
coll[0].SqlValue = departmentId;
dbdml.ExecuteProcedureReturnDataSetRef("PersonInfo", coll,ref dst);
其中departmentId是部门id号,是Varchar类型,长度是10
请问如何正确的调用,谢谢!!!
[WebMethod]
public void ExecuteProcedureReturnDataSetRef(string procName, SqlParameter[] coll, ref DataSet dst)
{
try
{
SqlDataAdapter dad = new SqlDataAdapter();
for (int i = 0; i < coll.Length; i++)
{
cmd.Parameters.Add(coll[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
dad.SelectCommand = cmd;
dad.Fill(dst);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
}
现在的问题是,我在C#中如何定义SqlParameter[] coll参数来进行传递?
我是如下写的,但是报错
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[1];
coll[0].SqlValue = departmentId;
dbdml.ExecuteProcedureReturnDataSetRef("PersonInfo", coll,ref dst);
其中departmentId是部门id号,是Varchar类型,长度是10
请问如何正确的调用,谢谢!!!
coll[1] = new SqlParameter("@??",??);
coll[0].SqlValue = departmentId;
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[1];
coll[0] = new SqlParameter();
coll[0].ParameterName = "@id_1";
coll[0].Value = departmentId;
coll[1] = ...
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[1];
coll[0].SqlValue = departmentId;with
SqlParameter[] coll = new SqlParameter[1];
col1[0] = new SqlParameter( "@departmentId", SqlType.Varchar, 10 );
col1[0].Value = departmentId;
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[n];
coll[0] = new SqlParameter("@par1");//每一个数组元素都需要实例化
coll[0].SqlValue = departmentId;coll[1] = new SqlParameter("@par2");//每一个数组元素都需要实例化
coll[1].SqlValue = .....;....
Knight94(愚翁) ( ) 信誉:110 change
System.Data.SqlClient.SqlParameter[] coll = new SqlParameter[1];
coll[0].SqlValue = departmentId;with
SqlParameter[] coll = new SqlParameter[1];
col1[0] = new SqlParameter( "@departmentId", SqlType.Varchar, 10 );
col1[0].Value = departmentId;
coll[0] = new SqlParameter("@departmentId", departmentId);
dbdml.ExecuteProcedureReturnDataSetRef("PersonInfo", coll,ref dst);
我是按照以上进行的调用,其他楼上的方法我也试了,都提示同一个错误:
参数"2":无法从"System.Data.SqlClient.SqlParameter[]"转换为"远华人力资源管理系统基础版.GoldenSoft.SqlParameter[]"
其中GoldenSoft是为添加Web Services时的引用名
请大家看看是什么错误,谢谢~!!!
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;/// <summary>
/// DBDML 的摘要说明
/// </summary>
[WebService(Namespace = "http://www.jnjr.com.cn/cuiyh/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class DBDML : System.Web.Services.WebService
{
#region 变量
private string connectionString = ConfigurationSettings.AppSettings["MappingConnectionString"]; private SqlConnection con = new SqlConnection();
private SqlCommand cmd = new SqlCommand();
#endregion public DBDML ()
{ //如果使用设计的组件,请取消注释以下行
//InitializeComponent(); }
//打开当前数据库
public void OpenConnection()
{
if (con.State == ConnectionState.Closed)
{
con.ConnectionString = connectionString;
cmd.Connection = con;
}
try
{
con.Open();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
} //关闭当前数据库
public void CloseConnection()
{
if (con.State == ConnectionState.Open)
{
cmd.Dispose();
con.Close();
con.Dispose();
}
} //执行sql语句,并返回影响的记录数,主要用于插入、删除、修改
[WebMethod]
public int ExecuteSql(string strSql)
{
int count = 0;
try
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
count = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
return count;
} //执行一条sql语句,同时进行事务处理
[WebMethod]
public void ExecuteSqlWithTransaction(string strSql)
{
SqlTransaction trans = null;
try
{
OpenConnection();
trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
} //执行多条sql语句,同时进行事务处理
[WebMethod]
public void ExecuteMulSqlWithTransaction(string[] strSql)
{
SqlTransaction trans=null;
try
{
OpenConnection();
trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
foreach (string sql in strSql)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
} //执行不带参数的存储过程,并返回影响的记录数
[WebMethod]
public int ExecuteProcedure(string procName)
{
int count = 0;
try
{
OpenConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
count = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
return count;
} //执行带多个参数的存储过程
[WebMethod]
public void ExecuteMulProcedure(string procName, SqlParameter[] coll)
{
try
{
OpenConnection();
cmd.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
cmd.Parameters.Add(coll[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
} ////执行带有多个参数的存储过程,并返回引用的DataSet
[WebMethod]
public void ExecuteProcedureReturnDataSetRef(string procName,System.Data.SqlClient.SqlParameter[] coll, ref DataSet dst)
{
try
{
OpenConnection();
cmd.Parameters.Clear();
SqlDataAdapter dad = new SqlDataAdapter();
for (int i = 0; i < coll.Length; i++)
{
//SqlParameter sp = new SqlParameter("@departmentId", SqlDbType.VarChar, 10);
//sp.SqlValue = coll[i];
cmd.Parameters.Add(coll[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
dad.SelectCommand = cmd;
dad.Fill(dst);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
} //执行一条sql语句并返回指定的DataSet
[WebMethod]
public DataSet Dataset(string strSql)
{
DataSet dst = new DataSet();
SqlDataAdapter dad = new SqlDataAdapter();
try
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
dad.SelectCommand = cmd;
dad.Fill(dst);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
return dst;
} //执行一条sql语句,并返回引用的DataSet
[WebMethod]
public void DatasetRef(string strSql, ref DataSet dst)
{
SqlDataAdapter dad = new SqlDataAdapter();
try
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
dad.SelectCommand = cmd;
dad.Fill(dst);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
}
}以上是我的全部Web Services,请看有什么问题
其次,sqlparameter类型不能序列化(记得以前回答过类似的问题),也就是不webmethod中不能出现sqlparameter类型的形参。
如果不能使用SqlParameter类型的形参,那该如何写
难道需要这样写吗:
[WebMethod]
public void ExecuteProcedureReturnDataSetRef(string procName, string[] para, strin[]value)
{
}
如果是这样的话,那如果参数的值类型不同,就要写多个相同的函数吗
谢谢回答!!!