Connecting to mySQL database from ASP.Net (vb)

Question

HI, I am a fairly experienced ASP developer making the move to ASP.net.

I am trying to open a database connection to a MySQL database but I am finding it overly complicated and not at all user friendly.

Is there no way to open a database connection, get a recordset and Move, Insert, and Delete on the fly? Is there no .AddNew, .MoveNext or .Update functionality?

At the moment I am using the MySQLDataAdapter to fill a Dataset but I can't see how to update changes back to the Table.

Do I really have to construct INSERT and UPDATE sqlCommands and execute them?

Any example code (in VB) would be gratefully received.

Answer 1

Though not a full answer( sorry I do not know VB, but bet there are a lot of articles around and some one here might point you to the right one ), you might want to have a handy reference to connectionstrings.

Answer 2

You need to download the mySQL adapter. It functions very similar to how the .NET MSSQL adapter functions.

mySQL .NET Adapter

Answer 3

If you want simplicity (and abstract layer) then you can use ADO.NET Entity framework with MySQL .Net connector.

Answer 4

As you are transitioning from classic ASP, I would recommend getting familiar with ADO.NET basics, this article on MSDN will answer a lot of your questions. This article in particular will help a lot. I know it might seem like a wall of text, but coming from classic asp and Recordsets, etc, these links will prove beneficial.

Answer 5

After reading some very dull MSDN Articles I managed to do it.

---

    Dim dbConnection As New MySqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx; pooling=false;")
    Dim dbDataAdapter As New MySqlDataAdapter("SELECT * FROM table", dbConnection)
    Dim dbDataSet As DataSet = New DataSet
    dbDataAdapter.Fill(dbDataSet, "table")

    Dim dbNewRow As DataRow = dbDataSet.Tables("table").NewRow
    dbNewRow("data1") = "Some Data"
    dbNewRow("data2") = "Some More Data"
    dbNewRow("data3") = "Even More Data"

    dbDataSet.Tables("table").Rows.Add(dbNewRow)

    dbDataAdapter.InsertCommand = New MySqlCommand("INSERT into table(data1, data2, data3) VALUES (@data1, @data2, @data3)", dbConnection)
    dbDataAdapter.InsertCommand.Parameters.Add("data1", MySqlDbType.VarChar, 25, "data1")
    dbDataAdapter.InsertCommand.Parameters.Add("data2", MySqlDbType.VarChar, 25, "data2")
    dbDataAdapter.InsertCommand.Parameters.Add("data3", MySqlDbType.VarChar, 25, "data3")

    dbDataAdapter.Update(dbDataSet, "table")

---

Thanks to everyone for their help.