Array of Objects type parameters used in the parameterized SQL query

Question

I am doing a enhancement job to my code. There are arrays of parameter that are passing into my methods. And those arrays of parameters are being passed into SQL query to fetch data.

For preventing the possible SQL injection, I decide to use the SqlParameter way to handle with the those parameters. For passing the string[] type parameters, this post works well and solve my issue.

But I do have a special method that accept Xobject[] as the parameter. For example:

public void GetDetails(CarModel[] carModels)
{
    query = "Select [MAKE],[YEAR] IN [FAKEDB].[FAKETABLE] WHERE [MAKE] " +
        "IN (*carModels makes here *) AND [YEAR] IN (*carModels years here*)"
}

Assume both Make and Year are string type. Is it possible I can still use SQLparameter as the solution to this method?

Possible duplicate of [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) — Evan Trimboli, Jun 18 '19 at 21:31
Rather than trying to create a one-size-fits-all method, you might want to take a cue from how TableAdapters are built with various specialized methods. Typed parameters are generally going to be better and safer than object or strings. — Ňɏssa Pøngjǣrdenlarp, Jun 18 '19 at 21:49

score 0 · Accepted Answer · answered Jun 19 '19 at 06:17

      ///this code can help you  
        static void Main(string[] args)
          {
               string query = "Select[MAKE],[YEAR] IN[FAKEDB].[FAKETABLE] WHERE[MAKE] " +
    "IN (*Makes*) AND [YEAR] IN (*Year*)";

         CarModel[] carModels = new CarModel[2] { new CarModel() { Make = "number1", Year = "1988" },
      new CarModel() { Make = "number2", Year = "2017" }
       };
        var result = SetQueryParameter(query, carModels);
      }

       public static string SetQueryParameter(string query, CarModel[] CarModels)
      {
        var makesStr = "(";
        var yearStr = "(";
        int counter = 1;
        foreach (var item in CarModels)
        {
            makesStr += item.Make; 
            yearStr += item.Year;
            if (CarModels.Count() != counter++)
            {
                makesStr += ",";
                yearStr += ",";
            }


        }

        makesStr += ")";
        yearStr += ")";

        return query.Replace("(*Makes*)", makesStr).Replace("(*Year*)", yearStr);


    }

Thanks! this really helps me to find the solution! – leon365 Jun 19 '19 at 18:52 — leon365, Jun 19 '19 at 18:52

Array of Objects type parameters used in the parameterized SQL query

1 Answers1