Zil Posts - Answer Overflow

Zil

•Created by Zil on 7/27/2023 in #help

Need help with dapper to prevent sql injection on database parameter!

Hello, this is my current endpoint:

c++
        [HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            using var connection = new SqlConnection(_config.GetConnectionString("Default"));

            var sql = "SELECT [Key],[Code] " +
                $"FROM [{databaseName}].[dbo].[workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

c++
        [HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            using var connection = new SqlConnection(_config.GetConnectionString("Default"));

            var sql = "SELECT [Key],[Code] " +
                $"FROM [{databaseName}].[dbo].[workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

Now reading the documentation of dapper they implement a endpoint like this:

c++
var parameters = new { UserName = username, Password = password };
var sql = "SELECT * from users where username = @UserName and password = @Password";
var result = connection.Query(sql, parameters);

c++
var parameters = new { UserName = username, Password = password };
var sql = "SELECT * from users where username = @UserName and password = @Password";
var result = connection.Query(sql, parameters);

How can I implement this on the database parameter from my code? Thanks in advance!

21 replies

CC#

•Created by Zil on 7/7/2023 in #help

✅ Handling changing Keys after an insert

Hello folks, I have a function that inserts new data into a sql database. the data is workflows and conaints a hierarchy with ParentKeys and a sequence. Example data that is being pushed to the database:

  [
    {
      key: 4,
      parentKey: 0,
      sequence: 1,
      parameters: [
        {
          ...
        },
        {
          ...
        },
        ...
      ]
    },
    {
      key: 5,
      parentKey: 0,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 6,
      parentKey: 5,
      sequence: 1,
      parameters: [
        ...
      ]
    },
    {
      key: 7,
      parentKey: 5,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 8,
      parentKey: 0,
      sequence: 3,
      parameters: [
        ...
      ]
    }
  ]

  [
    {
      key: 4,
      parentKey: 0,
      sequence: 1,
      parameters: [
        {
          ...
        },
        {
          ...
        },
        ...
      ]
    },
    {
      key: 5,
      parentKey: 0,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 6,
      parentKey: 5,
      sequence: 1,
      parameters: [
        ...
      ]
    },
    {
      key: 7,
      parentKey: 5,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 8,
      parentKey: 0,
      sequence: 3,
      parameters: [
        ...
      ]
    }
  ]

36 replies

CC#

•Created by Zil on 7/6/2023 in #help

Returning a value after a insert statement

7 replies

CC#

•Created by Zil on 7/6/2023 in #help

DotNet and Dapper. Incorrect syntax near ','.

Hello folks, Im trying to execute a sql query:

c++
[HttpPost]
        public async Task<ActionResult<List<Step>>> PostNewStepInfo(string databaseName, List<Step> steps)
        {
            try
            {
                using var connection = new SqlConnection(_config.GetConnectionString("Default"));

                foreach (var step in steps)
                {
                    var test = await connection.ExecuteAsync($@"
                    INSERT INTO[{databaseName}].[dbo].[StepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                    OUTPUT INSERTED.[Key] 
                    VALUES('{step.Type}', {step.ParentKey}, {step.SubWorkflowCode}, {step.Sequence}, {step.WorkflowKey});"
                        );

                    foreach (var parameter in step.Parameters)
                    {
                        await connection.ExecuteAsync($@"
                        INSERT INTO [{databaseName}].[dbo].[ParameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                        VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {test}, 'NULL', 0);"
                            );
                    }
                }
                return Ok(steps);
            }
            catch (SqlException ex) {
                Console.WriteLine($"SQL Exception: {ex.Message}");
                throw;
            }
        }

c++
[HttpPost]
        public async Task<ActionResult<List<Step>>> PostNewStepInfo(string databaseName, List<Step> steps)
        {
            try
            {
                using var connection = new SqlConnection(_config.GetConnectionString("Default"));

                foreach (var step in steps)
                {
                    var test = await connection.ExecuteAsync($@"
                    INSERT INTO[{databaseName}].[dbo].[StepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                    OUTPUT INSERTED.[Key] 
                    VALUES('{step.Type}', {step.ParentKey}, {step.SubWorkflowCode}, {step.Sequence}, {step.WorkflowKey});"
                        );

                    foreach (var parameter in step.Parameters)
                    {
                        await connection.ExecuteAsync($@"
                        INSERT INTO [{databaseName}].[dbo].[ParameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                        VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {test}, 'NULL', 0);"
                            );
                    }
                }
                return Ok(steps);
            }
            catch (SqlException ex) {
                Console.WriteLine($"SQL Exception: {ex.Message}");
                throw;
            }
        }

This is the data im trying to insert

[
  {
      "type": "a test",
      "parentKey": null,
      "subWorkflowCode": null,
      "sequence": 4,
      "workflowKey": 1,
      "parameters": [
          {
              "name": "od1",
              "source": "od1",
              "value": "od1"
          },
          {
              "name": "od2",
              "source": "od2",
              "value": "od2"
          }
      ]
  }
]

[
  {
      "type": "a test",
      "parentKey": null,
      "subWorkflowCode": null,
      "sequence": 4,
      "workflowKey": 1,
      "parameters": [
          {
              "name": "od1",
              "source": "od1",
              "value": "od1"
          },
          {
              "name": "od2",
              "source": "od2",
              "value": "od2"
          }
      ]
  }
]

Error:

(0x80131904): Incorrect syntax near ','.

(0x80131904): Incorrect syntax near ','.

Thanks in advance!

7 replies

CC#

•Created by Zil on 7/5/2023 in #help

Looping a query bad practice? Good alternative?

This is my end-point atm:

c++
[HttpPost]
public async Task<ActionResult<List<Step>>> PostNewStepInfo(List<Step> steps)
{
    using var connection = new SqlConnection(_config.GetConnectionString("Default"));

    // loop?

    return Ok(steps);
}

c++
[HttpPost]
public async Task<ActionResult<List<Step>>> PostNewStepInfo(List<Step> steps)
{
    using var connection = new SqlConnection(_config.GetConnectionString("Default"));

    // loop?

    return Ok(steps);
}

The data it receives: (In this case there is only 2, but this can be big, can also reach like 100 objects with each multiple parameters.)

[
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  },
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  }
]

[
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  },
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  }
]

Now ofcourse I could just loop over each object and then also loop over the parameters and execute a query for each item to the table i want. This feels like bad practice and would love to hear your opinion on it and maybe a good alternative. Thanks in advance!

4 replies

CC#

•Created by Zil on 7/5/2023 in #help

Need some help with my end-point

So i have this end point:

c++
{
    [Route("[controller]")]
    [ApiController]
    public class StepController : ControllerBase
    {
        private readonly DataContext _context;

        public StepController(DataContext context)
        {
            _context = context;
        }


        [HttpGet]
        public async Task<ActionResult<List<Step>>> Get(string databaseName, int workflowKey)
        {
            string query = "SQLQUERY";

            // some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?
            var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

            return Ok(steps);

        }
    }

c++
{
    [Route("[controller]")]
    [ApiController]
    public class StepController : ControllerBase
    {
        private readonly DataContext _context;

        public StepController(DataContext context)
        {
            _context = context;
        }


        [HttpGet]
        public async Task<ActionResult<List<Step>>> Get(string databaseName, int workflowKey)
        {
            string query = "SQLQUERY";

            // some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?
            var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

            return Ok(steps);

        }
    }

My model:

c++
    public class Step
    {
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int ParentKey { get; set; }
        public string SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

c++
    public class Step
    {
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int ParentKey { get; set; }
        public string SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

The query works fine in mssql, some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?

c++
var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

c++
var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

This is the error im getting:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

This is my first Dotnet project so all tips and hints are welcome!

39 replies

CC#

•Created by Zil on 7/3/2023 in #help

❔ SQL query for multiple data objects

Hello folks, Imagine i have a json array of json objects that i send to an API wich uses a SQL query to INSERT the data into the database. Each json object in the array contains data that should be pushed into table 1. every json object also contains a property called parameters that is an Array of objects. each object of this parameters array needs to be inserted into table 2. Is it normal practice to for example loop over all parameters and execute the SQL query for each item? It feels wrong to do that somehow but then again i dont know what other option there is. Thanks in advance!

9 replies

CC#

•Created by Zil on 6/29/2023 in #help

Accidently closed last post. Need help with data structure(Noob).

Hello this is my function

c++
public async Task<IEnumerable<StepModel>> GetSteps(int workflowKey, string databaseName)
    {
        var steps = await _db.LoadData<dynamic, dynamic>(
            "SQL QUERY"
            , new { });

            var mergedSteps = steps
            .GroupBy(s => s.Key)
            .Select(g => new StepModel
            {
                // Not relevant
                Parameters = g.Select(p => new ParametersModel
                {
                    Name = p.Name,
                    Source = p.Source,
                    Value = p.Value
                }).ToList()
            });

        return mergedSteps;
    }

}

c++
public async Task<IEnumerable<StepModel>> GetSteps(int workflowKey, string databaseName)
    {
        var steps = await _db.LoadData<dynamic, dynamic>(
            "SQL QUERY"
            , new { });

            var mergedSteps = steps
            .GroupBy(s => s.Key)
            .Select(g => new StepModel
            {
                // Not relevant
                Parameters = g.Select(p => new ParametersModel
                {
                    Name = p.Name,
                    Source = p.Source,
                    Value = p.Value
                }).ToList()
            });

        return mergedSteps;
    }

}

Model:

c++
{
    public class StepModel
    {
        // non relevant stuff
        public List<ParametersModel> Parameters { get; set; }
        
    }

    public class ParametersModel
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }
}

c++
{
    public class StepModel
    {
        // non relevant stuff
        public List<ParametersModel> Parameters { get; set; }
        
    }

    public class ParametersModel
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }
}

The focus here is on the parameters of a stepmodel. Current return value of merged steps is something like this(example):

"parameters": [
      {
        "name": "namevalue1",
        "source": "sourcevalue1",
        "value": "valuevalue1"
      },
      {
        "name": "namevalue2",
        "source": "sourcevalue2",
        "value": "valuevalue2"
      },
      {
        "name": "namevalue3",
        "source": "sourcevalue3",
        "value": "valuevalue3"
      },

"parameters": [
      {
        "name": "namevalue1",
        "source": "sourcevalue1",
        "value": "valuevalue1"
      },
      {
        "name": "namevalue2",
        "source": "sourcevalue2",
        "value": "valuevalue2"
      },
      {
        "name": "namevalue3",
        "source": "sourcevalue3",
        "value": "valuevalue3"
      },

What i want is:
"parameters": {
  "namevalue1": {
    "source": "sourcevalue1",
    "value": "valuevalue1"
  },
  "namevalue2": {
    "source": "sourcevalue2",
    "value": "valuevalue2"
  },
  "namevalue3:" {
    "source": "sourcevalue3",
    "value": "valuevalue3"
  },
}

What i want is:
"parameters": {
  "namevalue1": {
    "source": "sourcevalue1",
    "value": "valuevalue1"
  },
  "namevalue2": {
    "source": "sourcevalue2",
    "value": "valuevalue2"
  },
  "namevalue3:" {
    "source": "sourcevalue3",
    "value": "valuevalue3"
  },
}

Previous help consists of changing my Model to a Record. Unfortunately i cant do this because of constraints from PO. Thanks in advance!

53 replies

Gaming

Programming