CODEWITHSUNDEEP
c#.netmysql
Jack M
Jack M

Reputation: 81

Writing to MySQL

I've some some light database programming in school, before, but I'm trying to learn best practices now that I'm writing more sophisticated applications. Writing to a MySQL database isn't hard, but I'm wondering what a best practice is for having distributed applications write to a remote database on Amazon EC2.

As of right now, I'll have a MySQL database hosted in the cloud that's able to receive incoming connections. I questioned this as by default, it only enabled the local host to connect to it.

Is there a more secure or professional way to write to a MySQL instance in the cloud than just doing some kind of DbConnect, hard coding my server's online host name, root username and password? I just don't want to make a production application that's not accessing the database in the best fashion.

Thanks! You guys are always awesome.

Upvotes: 1

Views: 426

Answers (2)

davethecoder
davethecoder

Reputation: 3932

the most viable to date option for database work is to use something like entity framework your model is the model, your database connection sits on its own and you can easily port the application to another database provider if need... I.E mysql to msSQL without really having to do much in the way of work.

Entity framework code first is the easiest i think to port over. Also for mysql you just need the mysql.net connector from the provider themselves, this works with entity framework.

also by working with models and using linq you further help reduce prodlems with SQL injection as you wont actually be writing or sending any SQL.

Given that, should you have a massive database, EF is a pain in the butt and is really slow and for this a simple database class would do, you can have a few functions like this:

private readonly MySqlConnection _conn = new MySqlConnection();
private MySqlCommand _myCommand = new MySqlCommand();

private readonly string _dbConn = ConfigurationManager.AppSettings["dbConn"];

public void Closedb()
{
    try
    {
        _conn.Dispose();
        _conn.Close();
    }
    catch (Exception ex)
    {

    }
}

public void UpdateDatabaseWithSql(string mysql)
{
    Closedb();

        _conn.ConnectionString = _dbConn;
        _conn.Open();


    _myCommand= new MySqlCommand(mysql,_conn);

    _myCommand.ExecuteNonQuery();

    Closedb();
}

a DB connection for mysql looks like this, in the web.config file:

<add name="MvcCustomContext" 
     connectionString="server=[ip];User Id=[user];password=[pass];Persist Security Info=True;database=[dbname]" 
     providerName="MySql.Data.MySqlClient" />

hope this helps

Upvotes: 1

Mephy
Mephy

Reputation: 1

The best way is to have another application running on the server and listening a port on TCP to make the actions on the database. You can encrypt the message and decrypt on server application, having a safe username/password and data traveling.

Upvotes: 0

Related Questions