如何在C#中向过滤存储过程XML添加参数? [英] How can I add parameter to Filter stored procedure XML in C#?
问题描述
我有一个发送XML的存储过程:
I have a stored procedure that send XML:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ChartEnergyKPIS]
@columns varchar(max),
@groupBy varchar(max),
@filters varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query varchar(max)
SET @query = 'declare @xml XML
SET @xml = (SELECT '+ @columns + '
FROM [dbo].[KPIS] k (NOLOCK)
INNER JOIN [dbo].[KPISEnergy] ke (NOLOCK) ON k.Id = ke.IdKPI
INNER JOIN [dbo].[Meter] m (NOLOCK) ON ke.IdMeter = m.Id
' + @filters + ' ' +
@groupBy + ' FOR XML RAW) SELECT @xml';
PRINT @query
EXEC(@query)
END
我想做的是通过k.BranchOfficeId过滤此XML，在C#类中，我将查询称为:
That I want to do is filter this XML by k.BranchOfficeId, in C# class I call query something like:
public static string GetChartEnergy(string initDate, string endDate, string type)
{
string result = string.Empty;
var structure = new List<QueryStructure>();
try
{
structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103)", null, false));
structure.Add(type.CreateQueryStructure(string.Empty, false, "CASE WHEN m.Type = 1 THEN 'Agua' ELSE CASE WHEN m.Type = 2 THEN 'Luz' ELSE 'Gas' END END AS Type", " m.type", "m.Type", false));
//agrega una columna para sacar el total de registros por filtro
structure.Add(new QueryStructure
{
ColumnSelect = "SUM(ke.Month) AS Total",
Operator = Operator.Nothing,
ColumnWhere = string.Empty
});
result = Chars.GetChartInfo(structure, "ChartEnergyKPIS");
}
catch (Exception)
{
throw;
}
return result;
}
所以我的尝试很简单，就是将过滤器添加到创建查询中，例如，并与我在方法中调用的currentUser进行比较.
So my try to do it is simple to add filter into create query like and compare with currentUser I call in method:
public static string GetChartEnergy(string initDate, string endDate, string type, int currentUser)
{
string result = string.Empty;
var structure = new List<QueryStructure>();
try
{
structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103,k.BranchOfficeId = currentUser)", null, false));
最后在一个类中，我执行一个存储过程并根据应用的过滤器获取Json:
and finally in a class I execute a stored procedure and get Json depending of applied filters:
public static string GetChartInfo(List<QueryStructure> queryStructure, string procedureName)
{
string result = string.Empty;
string xml = string.Empty;
try
{
var queryWhere = queryStructure.GetWhere();
var columnsQuery = queryStructure.GetSelectGroupBy(true);
var groupByQuery = queryStructure.GetSelectGroupBy(false);
using (var oContext = new EF.SSMA())
{
SqlParameter param1 = new SqlParameter("@columns", columnsQuery);
SqlParameter param2 = new SqlParameter("@groupBy", groupByQuery);
SqlParameter param3 = new SqlParameter("@filters", queryWhere);
xml = oContext.Database.SqlQuery<string>(string.Format("dbo.{0} @columns, @groupBy, @filters", procedureName), param1, param2, param3).First();
}
if (!string.IsNullOrEmpty(xml))
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(string.Format("<root>{0}</root>", xml));
doc.LoadXml("<root>" + xml + "</root>");
result = Newtonsoft.Json.JsonConvert.SerializeXmlNode(doc);
result = result.Replace("{\"root\":{\"row\":", "");
result = result.Replace("}}", "");
result = result.Replace("\"@", "\"");
if (result.Substring(0, 1) == "{")
{
result = string.Format("[{0}]", result);
}
}
else
{
result = "[]";
}
}
catch (Exception)
{
throw;
}
return result;
}
创建查询结构类:
public static QueryStructure CreateQueryStructure(this String value, string endDate, bool isDate,
string columnName, string whereName, string groupByName, bool isNullField)
{
QueryStructure structure = new QueryStructure();
if (!string.IsNullOrEmpty(value))
{
if (value != ",")
{
if (isDate)
{
//obtiene la estructura para un filtro entre fechas
structure.ColumnSelect = columnName;
structure.ColumnGroupBy = groupByName;
structure.ColumnWhere = string.Format("({0} BETWEEN convert(datetime,\'{1}\', 103) and convert(datetime,\'{2}\', 103))", whereName, value.Remove(value.Length - 1), endDate.Remove(value.Length - 1));
structure.Values = null;
structure.Operator = Operator.Nothing;
}
else
{
if (isNullField)
{
//obtiene la estructura de un filtro por un campo que es null o no
if (value.Remove(value.Length - 1) != "-1")
{
structure.ColumnWhere = string.Format("{0} IS{1} NULL", whereName,
value.Remove(value.Length - 1) == "0"
? " NOT" :
string.Empty);
structure.Values = null;
structure.Operator = Operator.And;
}
}
else
{
//obtiene la estructura de un campo aplicando la regla IN seleccionando
//el campo a mostrar y el campo en groupBy
structure.ColumnSelect = columnName;
structure.ColumnGroupBy = groupByName;
structure.ColumnWhere = whereName;
structure.Values = value.Remove(value.Length - 1);
structure.Operator = Operator.And;
}
}
}
}
return structure;
}
推荐答案
您的错误发生在这里:
structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103,k.BranchOfficeId = currentUser)", null, false)
或更详细地说，在这里:
or, more in detail, here:
CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103,k.BranchOfficeId = currentUser)
您正在做的是将k.BranchOfficeId = currentUser作为第四个参数添加到CONVERT函数.这是确定的'，'附近的语法错误.
What you are doing is, to add your k.BranchOfficeId = currentUser as a fourth parameter to the CONVERT-function. This is a sure syntax error near ','.
如果您想在任何情况下添加此附加条件，最简单的方法是将其添加到以这种方式设置ColumnWhere的位置:
If this additional condition is something you want to add in any case it would be easiest to add this in the place where you set your ColumnWhere in this way:
k.BranchOfficeId = currentUser AND (The other condition you use normally)
但这是您的下一个问题:看来currentUser是应用程序中的变量.您必须使用值，而不是名称
But here is your next issue: It looks like currentUser is a variable in your application. You must use the value, rather than the name
类似
string.Format(" k.BranchOfficeId = {0} AND ({1}) ",currentUser,TheConditionAsItWasBefore)
希望这对您有帮助...
Hope this helps...
并且说实话:这看起来很复杂，容易进行SQL注入，您可以在代码审查" 上向人们展示这一点
And to be honest: This looks far to complicated, prone to SQL injection and you might show this to the folks on Code Review
这篇关于如何在C#中向过滤存储过程XML添加参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
