"Cannot implicitly convert type IDbConnection to SqlConnection"

Question

I need some help please.

I tried make an INSERT into my SQL database but I can't because give me an error in this code line:

 commandoSQL.Connection = dbcon;

I get this error:

Assets/NGUI/Scripts/Interaction/ChamarVariavel.cs(43,29): error CS0266: Cannot implicitly convert type System.Data.IDbConnection' toSystem.Data.SqlClient.SqlConnection'. An explicit conversion exists (are you missing a cast?)"

I hope somebody can help me with this.

Thanks

My code:

public class ChamarVariavel : MonoBehaviour {

    public UISlider slider;

    // Use this for initialization
    void Start () {
    }

    // Update is called once per frame
    void Update () {
    }

    void OnGUI(){
        // Connection DB
        string connectionString = "Data Source=(local);Initial Catalog=Test;User ID=******;Password=*******";

        IDbConnection dbcon;

        dbcon= new SqlConnection(connectionString);

        dbcon.Open();
        //DB Online

        float x = slider.value * 100;
        GUI.Label(new Rect( 570, 238, 70, 30 ), "(" + x.ToString("f2") + ")");

        string qInsert = string.Format(@"INSERT INTO Fuel (fuel) VALUES ('{0}')", x);

        SqlCommand commandoSQL = new SqlCommand(qInsert);

        commandoSQL.Connection = dbcon;

        try
        {
            commandoSQL.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            GUI.Label(new Rect( 300, 40, 300, 300 ), ex.ToString());
        }

        dbcon.Close();
        //DB offline
    }
}

Solution 1

The error is literally telling you the problem, you need to explicitly cast the object because the SqlCommand is looking for a SqlConnection, consider doing this:

new SqlCommand(qInsert, (SqlConnection)dbcon);

and remove this line:

commandoSQL.Connection = dbcon;

Another option is to just define dbcon as a SqlConnection:

SqlConnection dbcon

and then you could do this:

new SqlCommand(qInsert, dbcon);

Finally, have a look at a blog post I wrote a while back; you need to change how you're using your objects.

Solution 2

an alternative approach there, which helps generalize your data access code a bit: let the connection create the command:

using(var commandoSQL = dbcon.CreateCommand()) {
    commandoSQL.CommandText = SQL;
    //..
}

In other news: use parameters, not string.Format. SQL injection is a huge problem.

Solution 3

Well this would work:

commandoSQL.Connection = (SQLConnection)dbcon;

Because dbcon is declared as an IDBConnection but is actually a SQL connection but that is a bit daft.

Simply do

var dbcon = new SqlConnection(connectionString);

and remove

IDbConnection dbcon;

and it will be declared as the correct type. There is no reason I can see why it needs to by declared as IDBconnection.