我将直接在数据库上执行SQL查询。 我使用以下命令定义了到数据库的连接:
System.Data.Entity.DbContext rawDbContext = new DbContext(connectionString);
我不想直接在查询字符串中插入参数以避免SQL注入,因此我想通过以下方式为我的SQL查询设置参数化值:
string sqlCommandString =
"IF EXISTS(select* from @MappingTableName where " + Environment.NewLine +
"BranchID= @PrimaryKeyID and " + Environment.NewLine +
"BranchNo = @BranchNo and " + Environment.NewLine +
"TableName = @TableName and " + Environment.NewLine +
"BranchSchema = @SchemaNameInBranch and " + Environment.NewLine +
"TableID = @TableID) " + Environment.NewLine +
" select 1" + Environment.NewLine +
"ELSE " + Environment.NewLine +
"select 0 " + Environment.NewLine;
SqlParameter parameterMappingTableName = new SqlParameter("@MappingTableName",vipMappingTableName);
SqlParameter parameterSchemaNameInBranch = new SqlParameter("@SchemaNameInBranch",schemaName);
SqlParameter parameterPrimaryKeyInBranch = new SqlParameter("@PrimaryKeyID",primaryNodeId);
SqlParameter parameterBranchNo = new SqlParameter("@BranchNo",branchNo);
SqlParameter parameterTableId = new SqlParameter("@TableID",tableId);
SqlParameter parameterTableName = new SqlParameter("@TableName",tableName);
DbRawSqlQuery<int> result = rawDbContext.Database.SqlQuery<int>(sqlCommandString,new[] {
parameterMappingTableName,parameterSchemaNameInBranch,parameterPrimaryKeyInBranch,parameterBranchNo,parameterTableId,parameterTableName
});
int finalResult = result.Single();
运行此查询会导致"Must declare the table variable \"@MappingTableName\"."
引起惊叹
我该如何解决?