wcode.net

C# with MySQL Database

// October 30th, 2009 // Programming

If you want direct connection from C# to MySQL you can use MySQL.Data library from this resource. I’ve write down my own DataAccess class to make my work in other class easier.

Settings is setting class in .NET you can click on project->properties->settings to generate this class. You can find this file in Debug folder that have .config extension.

Here is my Data Access class manage command from C# to MySQL database.

    public class DataAccess
    {
        static internal string GetConnectionString()
        {
            return String.Format("server={0};user id={1};Password={2};database={3}",
                Settings.Default.DbServer, Settings.Default.DbUserId, Settings.Default.DbPassword,
                Settings.Default.DbName);
        }

        static DataAccess()
        { }

        public static DataTable ExecuteSelectCommand(MySqlCommand command)
        {
            DataTable table = null;
            try
            {
                command.Connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);
                reader.Close();
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show(ex.Data.ToString());
            }
            finally
            {
                command.Connection.Close();
            }
            return table;
        }

        public static MySqlCommand CreateCommand()
        {
            string dataProviderName = Settings.Default.DbProvoderName;
            string connectionString = GetConnectionString();
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = connectionString;
            MySqlCommand comm = conn.CreateCommand();
            return comm;
        }

        public static int ExecuteNonQuery(MySqlCommand command)
        {
            int affectedRows = -1;
            try
            {
                command.Connection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Connection.Close();
            }
            return affectedRows;
        }
    }

Now here’s the sample to call that DataAccess class

    public class Golongan
    {
        private int idGolongan;
        private string namaGolongan;

        public int IdGolongan
        {
            get { return idGolongan; }
            set { idGolongan = value; }
        }

        public string NamaGolongan
        {
            get { return namaGolongan; }
            set { namaGolongan = value; }
        }

        public Golongan()
        { }

        public Golongan(int idGolongan, string namaGolongan)
        {
            this.idGolongan = idGolongan;
            this.namaGolongan = namaGolongan;
        }

        public static DataTable SelectAllGolongan()
        {
            MySqlCommand command = DataAccess.CreateCommand();
            command.CommandText = "select * from golongan";
            DataTable table = DataAccess.ExecuteSelectCommand(command);
            return table;
        }

        public int InsertGolongan()
        {
            MySqlCommand commmand = DataAccess.CreateCommand();
            commmand.CommandText = "insert into golongan (golongan) " +
                                    "values(    '" + this.namaGolongan + "')";

            return DataAccess.ExecuteNonQuery(commmand);
        }

        public int UpdateGolongan()
        {
            MySqlCommand commmand = DataAccess.CreateCommand();
            commmand.CommandText =
                String.Format("update golongan set golongan = '{0}' where idgolongan = {1}",
                this.namaGolongan, this.idGolongan);

            return DataAccess.ExecuteNonQuery(commmand);
        }

        public int DeleteGolongan()
        {
            MySqlCommand commmand = DataAccess.CreateCommand();
            commmand.CommandText =
                String.Format("delete from golongan where idgolongan = {0}",
                this.idGolongan);

            return DataAccess.ExecuteNonQuery(commmand);
        }
}

Hope it’s help
Willy Achmat Fauzi