如何在c#中添加参数来筛选存储过程XML
本文关键字：筛选 存储过程 XML 参数 添加 | 更新日期: 2023-09-27 18:15:52
我有一个发送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#类中，我将查询称为:
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比较，我调用方法:
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:
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)
，更详细地说，在这里:
CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103,k.BranchOfficeId = currentUser)
您正在做的是，将k.BranchOfficeId = currentUser作为CONVERT -函数的第四个参数添加。这是'，'附近的一个语法错误。
如果这个附加条件是您想要在任何情况下添加的，那么在您以这种方式设置ColumnWhere的地方添加它将是最简单的:
k.BranchOfficeId = currentUser AND (The other condition you use normally)
但这是你的下一个问题:看起来currentUser是你的应用程序中的一个变量。必须使用值，而不是名称
比如
string.Format(" k.BranchOfficeId = {0} AND ({1}) ",currentUser,TheConditionAsItWasBefore)
老实说:这看起来太复杂了，容易被SQL注入，你可以把它展示给Code Review上的人