I have been using below code to execute my SQL Query, which looks something like this
SELECT abc.... FROM .... (many joins).. WHERE userid =" + userId + " AND UserState = " +userState ...; (Other parameters)
Below is how I am running the query and returning datatable
using (var context = new DbContext())
{
DataTable dt= new DataTable();
var conn = context.Database.Connection;
var connectionState = conn.State;
try
{
if (connectionState != ConnectionState.Open)
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = buildveryLongQuery(userId,userState);
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
dt.Load(reader);
}
}
}
above method is working fine but it provides SQL injection. how can I parametrized it?
I tried below method:
Changed query to use @userId
IDbDataParameter personParam = cmd.CreateParameter();
personParam.DbType = DbType.Int32;
personParam.ParameterName = "@userId";
personParam.Value = userId;
but I am getting below error
Must declare the scalar variable \"@userId\
buildveryLongQuery(userId,userState);.