本文是小编为大家收集整理的关于从C#运行SYBASE SQL脚本的问题 - 太多参数的处理方法，想解了从C#运行SYBASE SQL脚本的问题 - 太多参数的问题怎么解决？从C#运行SYBASE SQL脚本的问题 - 太多参数问题的解决办法？从C#运行SYBASE SQL脚本的问题 - 太多参数问题的解决方案？那么可以参考本文帮助大家快速定位并解决问题，译文如有不准确的地方，大家可以切到English参考源文内容。
问题描述
我正在从Sybase API中的ASECOMMAND对象运行SYBASE SQL脚本问题.这是我的脚本的副本，该副本以sql字符串参数
传递到c#中SQL脚本:
DECLARE @dt DATETIME
DECLARE @mx INT
SELECT @dt = getDate()
EXEC DATABASE_NAME..StoredProcedure1
@id_own_grd = 200,
@id_dom_grd = 'TEST VALUE',
@value = @mx OUTPUT
EXEC DATABASE_NAME..StoredProcedure2
@id_level = @mx, @id_level_pt=NULL, @id_obj1 = 58464819,
@id_typ_lvl='TEST', @am_test=20130916, @id_obj1_sub = 12949,
@dt_start_lvl = '9/16/2013', @dt_end_lvl = '9/16/2013',@ct_blah=0,
@id_abs=" ", @id_obj1_trans=0,@am_obj1_vol=NULL, @am_obj3=-0.311095,
@id_obj_test = NULL, @id_obj5_test = NULL, @id_is_valid = '0',
@nm_db_origin='ORIGIN_DB', @id_owner=200
c#代码在SQL脚本上方调用:
public Dictionary<int, Dictionary<string, object>> ExecuteSqlQuery(string sql)
{
lock (this)
{
try
{
using (AseCommand command = new AseCommand(sql, Connection))
using (AseDataReader reader = command.ExecuteReader())
{
Log.Info("Executing Query: [" + sql + "] on " + DbSettings);
command.CommandTimeout = 120;
var results = new Dictionary<int, Dictionary<string, object>>();
var columns = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
int row = 0;
while (reader.Read())
{
Dictionary<string, object> rowResults = new Dictionary<string, object>();
results.Add(row, rowResults);
foreach (string item in columns)
{
rowResults.Add(item, reader[item]);
}
row++;
}
Log.Info("Finished Executing Query: [" + sql + "] on " + DbSettings + " in " + timer.TimeTaken());
return results;
}
}
catch (Exception e)
{
Log.Error("An error occured executing query SQL: " + e.Message, e);
throw;
}
}
}
每当我尝试运行上面的脚本时，就会抛出并捕获一个sybase.data.aseclient.aseexception，上面写着"无效的参数量".此C#代码适用于任何其他不调用存储过程的代码.我知道Sybase具有调用存储过程的特定步骤，尤其是在此链接中详细介绍的输出参数:
中:问题描述
I'm having issues running a Sybase SQL Script from an AseCommand object in the Sybase API. Here are copies of my script, which is passed into the C# as the sql string parameter
SQL Script:
DECLARE @dt DATETIME
DECLARE @mx INT
SELECT @dt = getDate()
EXEC DATABASE_NAME..StoredProcedure1
@id_own_grd = 200,
@id_dom_grd = 'TEST VALUE',
@value = @mx OUTPUT
EXEC DATABASE_NAME..StoredProcedure2
@id_level = @mx, @id_level_pt=NULL, @id_obj1 = 58464819,
@id_typ_lvl='TEST', @am_test=20130916, @id_obj1_sub = 12949,
@dt_start_lvl = '9/16/2013', @dt_end_lvl = '9/16/2013',@ct_blah=0,
@id_abs=" ", @id_obj1_trans=0,@am_obj1_vol=NULL, @am_obj3=-0.311095,
@id_obj_test = NULL, @id_obj5_test = NULL, @id_is_valid = '0',
@nm_db_origin='ORIGIN_DB', @id_owner=200
C# code that calls above SQL Script:
public Dictionary<int, Dictionary<string, object>> ExecuteSqlQuery(string sql)
{
lock (this)
{
try
{
using (AseCommand command = new AseCommand(sql, Connection))
using (AseDataReader reader = command.ExecuteReader())
{
Log.Info("Executing Query: [" + sql + "] on " + DbSettings);
command.CommandTimeout = 120;
var results = new Dictionary<int, Dictionary<string, object>>();
var columns = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
int row = 0;
while (reader.Read())
{
Dictionary<string, object> rowResults = new Dictionary<string, object>();
results.Add(row, rowResults);
foreach (string item in columns)
{
rowResults.Add(item, reader[item]);
}
row++;
}
Log.Info("Finished Executing Query: [" + sql + "] on " + DbSettings + " in " + timer.TimeTaken());
return results;
}
}
catch (Exception e)
{
Log.Error("An error occured executing query SQL: " + e.Message, e);
throw;
}
}
}
Whenever I try running the above script, an Sybase.Data.AseClient.AseException is thrown and caught which says "Invalid amount of parameters". This C# code works for any other code that does not call stored procedures. I am aware that Sybase has specific steps for calling a stored procedure, especially with output parameters, detailed in this link:
However, my users want to be able to call several stored procedures in one script - something I can't figure out how to do yet. Anyone got any ideas?
Thanks,
推荐答案
A colleague and I found the answer. You have to change a flag in the Sybase connection string. Add this to the connection string
;Named Parameters=false
This tells the Ase Client to not check each parameter passed to the stored procedure. If it blows up, its entirely on the caller. You also need to set NamedParameters = false for anytime you are using OUTPUT parameters as well.
更多相关问答