Beginner requests Help with Mysql and C#

I'm trying to build a program that uses a C# to work with a MySQL DB. I g开发者_运维知识库et the C# syntax, and can write the language, but I don't have much experience with the libraries, and I feel a bit lost.

Could someone post examples of how a program would be built (in technical terms, syntax would be nice, but pseudo code is fine, too)?

I understand the theory of how it works, but need a hands on approach to it.

Thank you.

EDIT

I forgot to add that I want to learn how to do it with the .NET v.2.0 framework / VS2005 / MySQL v5.0 combination.

EDIT # 2

2.0 .NET will only be supported. =)

---

Here is tutorial for Entity Framework + MySQL.

There are lots of other ways to operate with DB, depending on what you need:

1. If you need execute raw sql queries against DB - use OdbcConnection + OdbcCommand
2. Need to manipulate items in DB as objects - use ORM (EntityFramework, NHibernate, Linq2Sql)
3. Like old-style DB interop? - DataSets is your choice.

I really like EF. Easy thing to start with.

---

PS: And before mixing UI and DB-interop, please read about Separation of concerns. MVC is interesting to read about too. About "libraries": create another project in your solution and add DB-interop logics there. Don't mix it in one assembly, because when your project becomes bigger than "Hello DataBase!" application it will create a big mess in code and logics, really.

UPDATE: Using VS2005 and .net 2.0 is mysterious idea, really. Lots of tools and assemblies where made since 2.0 release. Linq, Orm-s, etc. Live without them is hard and all the benefits of C# are lost. I highly recommend to use latest techniques, if there is no strict reasons to use 2.0.

If using SqlServer - ObdcCommand and OdbcConnection can be replaced to SqlCommand and SqlConnection. (thanks @Abe Miessler comment)

---

Here is an example swiped from MSDN:

public void InsertRow(string connectionString, string insertSQL)
{
    using (OdbcConnection connection = 
               new OdbcConnection(connectionString))
    {
        // The insertSQL string contains a SQL statement that
        // inserts a new row in the source table.
        OdbcCommand command = new OdbcCommand(insertSQL, connection);

        // Open the connection and execute the insert command.
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the
        // code exits the using block.
    }
}

If you want to read records in a DB, look at this example:

public static void ReadData(string connectionString)
{
    string queryString = "SELECT DISTINCT CustomerID FROM Orders";

    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        OdbcCommand command = new OdbcCommand(queryString, connection);

        connection.Open();

        // Execute the DataReader and access the data.
        OdbcDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine("CustomerID={0}", reader[0]);
        }

        // Call Close when done reading.
        reader.Close();
    }
}

FYI i am just copy/pasting these directly from MSDN. I highly recommend reading over their documentation and looking at their examples if you are just getting started.

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.aspx

---

Here is a blog post getting you started with MySql and C#.

http://blog.bobcravens.com/2010/06/the-repository-pattern-with-linq-to-fluent-nhibernate-and-mysql/

Hope that gets you started.

Bob