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
– eddie_cat, Commented Sep 29, 2014 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
– Joel Coehoorn, Commented Sep 29, 2014 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
– eddie_cat, Commented Sep 29, 2014 at 20:35
possible duplicate of c# Using Parameters.AddWithValue in SqlDataAdapter — Bearcat9425
– Bearcat9425, Commented Sep 29, 2014 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
– Joel Coehoorn, Commented Sep 29, 2014 at 20:58

Community · Accepted Answer · 2017-05-23 11:57:41Z

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, 2017 at 11:57

CommunityBot

11 silver badge

answered Sep 29, 2014 at 20:35

Bearcat9425

1,6081 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

eddie_cat Over a year ago

If it's very commonly asked you should flag it as a duplicate, not answer it here

Nyra Over a year ago

You're also answering for Sql not MySql

Nyra Over a year ago

MySql uses ?'s- see my answer below

Bearcat9425 Over a year ago

Actually that is false i have working code where the @ sysmbol works just fine.

Nyra Over a year ago

Good to know- odd the MySql doc doesn't say you can use it- but if it works so be it.

|

Nyra · Accepted Answer · 2014-09-29 20:31:42Z

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 · Accepted Answer · 2014-09-29 21:02:22Z

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;
}

Collectives™ on Stack Overflow

How to add parameters to SQL when using

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related