Published 4/30/2007 1:45:07 AM  in  MySQL

Not lets change the function. We want to read a specific city as specified by an inparameter:

        public DataTable GetData_MySQL(long lCityId)
        {
            MySql.Data.MySqlClient.MySqlConnection oConn = new MySql.Data.MySqlClient.MySqlConnection();
            oConn.ConnectionString = "Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka";
            oConn.Open();

            DataSet oDataSet = new System.Data.DataSet("tab1");
            //Get data
            MySql.Data.MySqlClient.MySqlCommand command =
                new MySql.Data.MySqlClient.MySqlCommand("select * from city where city_id=?city_id", oConn);

            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?city_id",
                DbType.Int32));

            // Now, add a value to it and later execute the command as usual.
            command.Parameters[0].Value = lCityId;


            MySql.Data.MySqlClient.MySqlDataAdapter oAdapter =
                new MySql.Data.MySqlClient.MySqlDataAdapter(command);
            oAdapter.Fill(oDataSet, "tab1");
            DataTable dt = oDataSet.Tables["tab1"];
            oConn.Close();

            return dt;


        }

The whole trick is knowing that '?' is used as prefix, otherwise it's pretty much the same as for SQL Server.