How to add parameters to SQL when using

Question

Given the this C# code fragment how do I use a C# variable in the SQL query? I understand the best way to do this is to use "parameters" and I've looked at many examples but I can not "put it together" so far.

   ...
using MySql.Data.MySqlClient;

       public partial class Form1 : Form
        {
            private string server;
            private string database;
            private string uid;
            private string password;
            private MySqlConnection connection;

            public Form1()
            { 
                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {

                webBrowser1.Navigate("127.0.0.1/box3.php");

                server = "localhost";
                database = "realestate_db";
                uid = "root";
                password = "";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlDataAdapter mySqlDataAdapter;
                mySqlDataAdapter = new MySqlDataAdapter("SELECT `ID`, `lat` , `long` FROM `house` ", connection); // want to uses a C# variable in this SQL query

                DataSet DS = new DataSet();
                mySqlDataAdapter.Fill(DS);
                dataGridView1.DataSource = DS.Tables[0];

            }
     ....       

Thanks.

What's wrong with the code you have now? Error messages? Unexpected behavior? — eddie_cat, Sep 29 '14 at 20:30
@eddie_cat the code is incomplete. He wants to know how to extend to include something a like a filter in the where clause of the sql string without leaving himself open to sql injection. — Joel Coehoorn, Sep 29 '14 at 20:33
Whoops, glancing at it I thought he was already trying to parameterize and just having issues getting his code to work. Didn't see the comment. — eddie_cat, Sep 29 '14 at 20:35
possible duplicate of [c# Using Parameters.AddWithValue in SqlDataAdapter](http://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter) — Bearcat9425, Sep 29 '14 at 20:44
@Bearcat9425 I agree it's possibly a duplicate, but let's find a different question to use as the original. The AddWithValue() method is not your friend. — Joel Coehoorn, Sep 29 '14 at 20:58

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

This is a repeat of a very commonly asked question and I am using code copy and pasted from another article describing, link is here Creating and then working with parameters in queries . You can use the addWithValue method on your dataadapter Select command, or the add method.

da = new MySqlDataAdapter("SELECT `ID`, `lat` , `long` FROM `house` where `ID` = ?ID", connection);
// As most are suggesting Create the parameters with the Add Method, Passing the MySqlDbType  
da.SelectCommand.Parameters.Add("?ID",MySqlDbType.Int32).Value = ID;
 // Can also Use AddWithValue Method as well  
da.SelectCommand.Parameters.AddWithValue("?ID",<Your Variable>);

edited May 23 '17 at 11:57

Community

1
1

answered Sep 29 '14 at 20:35

Bearcat9425

1,580
1
11
12

If it's very commonly asked you should flag it as a duplicate, not answer it here – eddie_cat Sep 29 '14 at 20:35
You're also answering for Sql not MySql – Nyra Sep 29 '14 at 20:37
MySql uses ?'s- see my answer below – Nyra Sep 29 '14 at 20:39
Actually that is false i have working code where the @ sysmbol works just fine. – Bearcat9425 Sep 29 '14 at 20:40
Good to know- odd the MySql doc doesn't say you can use it- but if it works so be it. – Nyra Sep 29 '14 at 20:48
Documentation does use ? but I am utilizing version of the connector 6.6.5.0, and I have no issues using it. Not sure if I am doing special, one thing I do not do is prepend the @ to the name string in the AddWithValueMethod. Its possible I could run into issues later down the road but nothing is occurring at the moment. For the sake of conforming to Documentation I will update. I also see that the link was for SqlAdapter in which I will update as well. There are a number of samples for MySqlAdapter as well on Stack. – Bearcat9425 Sep 29 '14 at 20:55
Personally if the @ works I'd leave it as it shows another alternative. idk if that is good SO practice or not. I wasn't trying to give you sh** for it :) – Nyra Sep 29 '14 at 20:58
None taken :) but for conformity to Documentation might as well make sure the right information is given. I just know I have used it with @ and not had any problems. I just like that capability so I can move between sql and mysql commands or dataadapters pretty easily :) – Bearcat9425 Sep 29 '14 at 21:00

score 0 · Answer 2 · answered Sep 29 '14 at 20:31

From MySqlDataAdapter

  public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
  {
    MySqlDataAdapter da = new MySqlDataAdapter();
    MySqlCommand cmd;
    MySqlParameter parm;
    // Create the SelectCommand.
    cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
    cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
    cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
    da.SelectCommand = cmd;
    // Create the InsertCommand.
    cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id,?name)", conn);
    cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
    cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );

    da.InsertCommand = cmd;  
    return da;
  }

Joel Coehoorn · Answer 3 · 2014-09-29T21:02:22.313

First of all, abstract all your data access out to it's own class or assembly:

public class DAL
{

    private string server = "localhost";
    private string database = "realestate_db";
    private string uid = "root";
    private string password = "";
    private string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

    public DataSet GetHouse(int ID) 
    {
        //...
    }
}

Then your existing code will call into this method:

public DataSet GetHouse(int ID)
{
    string sql = "SELECT `ID`, `lat` , `long` FROM `house` WHERE ID= ?ID ";
    DataSet result = new DataSet();

    using (var cn = new MySqlConnection(connectionString) )
    using (var cmd = new MySqlCommand(sql, cn) )
    using (var da = new MySqlDataAdapter(cmd) )
    {
       cmd.Parameters.Add("?ID", MySqlDbType.Int32).Value = ID;

       da.Fill(result);
    }
    return result;
}

How to add parameters to SQL when using

3 Answers3

Linked