How can I access a MySQL database with C# from functions within a class?

Question

I'm new to C# and asp.net programming and currently use MsSQL DataBases linked to my project with a DBML, using Linq to SQL, with my connection string in my web.config and all my database functions are stored in class managers. Examples below.

Connection string in web.config:

<add name="ConnectionString" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=UserId;Password=password" providerName="System.Data.SqlClient" />

Example Class Manager:

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Web;

public class DataManager
{
    private DataContext dc;

    public DataManager()
    {
        dc = new DataContext();
    }

    public News GetNews(int newsId)
    {
        return dc.News.Where(x => x.NewsId == newsId).Where(x => x.Deleted == false).FirstOrDefault();
    }

    public News News_Save(News n)
    {
        if (n.NewsId == 0)
            dc.News.InsertOnSubmit(n);

        dc.SubmitChanges();

        return n;
    }
}

What I would like to know is how I can have a similar setup to this but to access a MySQL database. All the examples I have seen require creating a connection string within the page each time you want to use a function which seems wasteful and unnecessary to me. Is there anyway I can use my current structure but access a MySQL database with or without a DBML?

The way we used to do it in school was store our connection string in a globals file that we could access from our data access layer. See this: eggheadcafe.com/community/aspnet/2/10011720/… — Grahame A
– Grahame A, Commented Feb 12, 2011 at 0:11

Markus Johnsson · Accepted Answer · 2011-02-12 00:05:09Z

1

AFAIK, there is no up-to-date Linq-to-SQL provider for MySQL. But there is a MySQL provider for Entity Framework: Connector/Net. Entity Framework also supports LINQ, so your code would be pretty much the same.

You can use the built-in tools in Visual Studio (Project > Add New Item ... > ADO.NET Entity Data Model) to create your entities from an existing database and connection info will be stored in a .config file (I guess that would be web.config for a web/asp.net project).

answered Feb 12, 2011 at 0:05

Markus Johnsson

4,01927 silver badges32 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:04:23Z

0

Microsoft doesn't have a library to access mysql through linq but there are 3rd party providers. See this post. That should mean you can keep your current manager class as-is (or maybe minor modifications to accommodate the 3rd party linq-to-mysql library).

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Feb 11, 2011 at 23:46

Sem Vanmeenen

2,15112 silver badges13 bronze badges

Collectives™ on Stack Overflow

How can I access a MySQL database with C# from functions within a class?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related