Sql parameters already declared

Question

So, I'm trying to use parameters in SQL, since someone in my database has the last name "O'Brien", as you can imagine, without using parameters, it caused me a lot of hell before you start yelling at me telling me I should have already used parameterized queries, I am very new to using SQL correctly, and I did not have time to learn how to do this correctly, but so far I just used string queries, my code looks like this:

//string constr = ConfigurationManager.ConnectionStrings["EBSLIVE"].ConnectionString;
string constr = ConfigurationManager.ConnectionStrings["EBSTEST"].ConnectionString;
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
    using(SqlCommand cmd = new SqlCommand())
    {
        foreach (string name in StringNames)
        {
            string[] StuName = name.Split('|');
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE";

            cmd.Parameters.AddWithValue("@PERSONCODE", StuName[0]);
            cmd.Parameters.AddWithValue("@FORENAME", StuName[1]);
            cmd.Parameters.AddWithValue("@SURNAME", StuName[2]);
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    DataTable ndt = new DataTable();
                    sda.Fill(ndt);
                    dt.Merge(ndt);
                }
            }
        }
    }              
}
Numbers.AddRange(dt.Rows.OfType<DataRow>().Select(dr => dr.Field<string>("Number")).ToList());

But for some reason this tells me that @PERSONCODE is already declared. I have no idea what is going on. Any help would be appreciated.

Answer 1

SqlCommand DataAdapters, , SqlCommand . , .

SqlCommand, DataAdapter.SelectCommand . AddWithValue:

using (SqlConnection con = new SqlConnection(constr))
using (SqlDataAdapter da = new SqlDataAdapter("Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE", con))
{
    var cmd = da.SelectCommand;
    cmd.Parameters.Add("@PERSONCODE", SqlDbType.NVarChar);
    cmd.Parameters.Add("@FORENAME", SqlDbType.NVarChar);
    cmd.Parameters.Add("@SURNAME", SqlDbType.NVarChar);

    foreach (string name in StringNames)
    {
        string[] StuName = name.Split('|');
        cmd.Parameters["@PERSONCODE"].Value = StuName[0];
        cmd.Parameters["@FORENAME"].Value = StuName[1];
        cmd.Parameters["@SURNAME"].Value = StuName[2];

         DataTable ndt = new DataTable();
         da.Fill(ndt);
         dt.Merge(ndt);
    }
}

Answer 2

foreach (string name in StringNames)
{
   using(SqlCommand cmd = new SqlCommand())
   {
        string[] StuName = name.Split('|');
        cmd.Parameters.AddWithValue("@PERSONCODE", StuName[0]);
        cmd.Parameters.AddWithValue("@FORENAME", StuName[1]);
        cmd.Parameters.AddWithValue("@SURNAME", StuName[2]);
        cmd.CommandText = "Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE";
        cmd.CommandType = CommandType.Text;

        //your other stuff and merging to the main dt.
   }
}

SqlCommand foreach. , .

: , :

foreach (string name in StringNames)
{
    cmd.Parameters.Clear();
    //other stuff.
}

Answer 3

, . , .

-, , , :

using(SqlCommand cmd = new SqlCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE";

    cmd.Parameters.Add("@PERSONCODE", SqlDbType.NVarChar);
    cmd.Parameters.Add("@FORENAME", SqlDbType.NVarChar);
    cmd.Parameters.Add("@SURNAME", SqlDbType.NVarChar);
    foreach (string name in StringNames)
    {
        string[] StuName = name.Split('|');

        cmd.Parameters["@PERSONCODE"].Value = StuName[0]);
        cmd.Parameters["@FORENAME"].Value = StuName[1]);
        cmd.Parameters["@SURNAME"].Value = StuName[2]);

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                DataTable ndt = new DataTable();
                sda.Fill(ndt);
                dt.Merge(ndt);
            }
        }
    }
} 

You can even declare a data adapter outside the loop, as Tim says in his answer.