(MySQL) Membuat Kustomisasi ID Otomatis C#

Buat database menggunakan MySQL

Disini saya membuat contoh database dengan nama perusahaan


1. Buat database perusahaan

MariaDB [(none)]> Create Database perusahaan;

2. Gunakan database perusahaan.

MariaDB [(none)]> Use perusahaan;

3. Membuat tabel user.

MariaDB [perusahaan]> Create table user(
    -> id_user varchar(5) not null,
    -> nama varchar(35) not null,
    -> primary key (id_user)
    -> )Engine = InnoDB;

4. Deksripsi tabel user.

MariaDB [perusahaan]> Desc user;
+---------+-------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id_user | varchar(5)  | NO   | PRI | NULL |           |
| nama    | varchar(35) | NO  |        | NULL   |           |
+---------+-------------+------+-----+---------+-------+

5. Buka Visual Studio

Saya membuat tampilan seperti gambar di atas

6. Tambahkan Textbox dan Button.

Tetxtbox dengan id  : txtid
Button   dengan id  : btn_getid

7. Coding dalam form.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Perusahaan.Class;
/*
Grizenzio
On 12 May 2017
*/
namespace Perusahaan
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_getid_Click(object sender, EventArgs e)
        {
            UserService us = new UserService();
            txtid.Text = us.IDKostum();
        }
    }
}

8. Tambahkan references untuk menghubungkan ke database MySQL. (Download)

MySQL.Data.dll

Klik kanan referesnces dan add references

 Cari di folder mana anda menyimpan MySql.Data.dll, centang dan ok.

9. Tambahkan Kofigurasi Class MySQLConn.cs dan MySQLService.cs.

MySQLConn.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
/*
Grizenzio
On 12 May 2017
*/
namespace Perusahaan.Class
{
    abstract class MySQLConn
    {
        //Penggunaan ExecNonQuery untuk Insert, Delete dan Update
        public abstract int ExecNonQuery(String query);

        //Penggunaan ExecQuery untuk Select
        public abstract DataTable ExecQuery(String query);
    }
}

MySQLService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using System.Data;
using MySql.Data.MySqlClient;

/*
Grizenzio
On 12 May 2017
*/

namespace Perusahaan.Class
{
    class MySQLService : MySQLConn
    {
        private String strCon = "";
        private MySqlConnection sConn;
        private MySqlCommand sComm;
        private MySqlDataAdapter dtAdp;
        public String Query;

        public MySQLService()
        {
            strCon = Constr();
            sConn = new MySqlConnection(strCon);
            sComm = new MySqlCommand();
            dtAdp = new MySqlDataAdapter();
        }

        public String Constr()
        {
            String data;
            data = "datasource=localhost;port=3306;Initial Catalog='perusahaan';username=root;password=";
            return data;
        }

        public void BukaKoneksi()
        {
            if (sConn.State == ConnectionState.Closed)
            {
                try
                {
                    sConn.Open();
                }
                catch (Exception)
                {}
            }
        }

        public void TutupKoneksi()
        {
            sConn.Close();
        }

        public override int ExecNonQuery(String Query)
             {
                 int retVal = -1;

                 try
                 {
                     BukaKoneksi();
                     sComm.Connection = sConn;
                     sComm.CommandText = Query;
                     retVal = sComm.ExecuteNonQuery();
                 }
                 catch (Exception) { }
                 finally
                 {
                     TutupKoneksi();
                 }
                 return retVal;
             }

        public override DataTable ExecQuery(String Query)
             {
                 DataTable retVal = new DataTable();
                 try
                 {
                     BukaKoneksi();
                     sComm.Connection = sConn;
                     sComm.CommandText = Query;
                     dtAdp.SelectCommand = sComm;
                     dtAdp.Fill(retVal);
                 }
                 catch (Exception) { }
                 finally
                 {
                     TutupKoneksi();
                 }
                 return retVal;
             }
     }
 }

10. Membuat Class User dan UserService

User.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
/*
Grizenzio
On 12 May 2017
*/
namespace Perusahaan
{
    class User
    {
        //Membuat class untuk menampilkan id
        private String id;

        public User()
        {
            id = "";
        }

        public String ID
        {
            set { id = value; }
            get { return id; }
        }
    }
}

UserService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Perusahaan.Class;
using System.Data;
/*
Grizenzio
On 12 May 2017
*/
namespace Perusahaan
{
    class UserService : User
    {
        MySQLService mysql;
        DataTable dt;

        public UserService()
        {
            mysql = new MySQLService();
            dt = new DataTable();
        }

        public String IDKostum()
        {
            String kode = "";
            int idx = 0;
            mysql.Query = "Select isnull(MAX(Right(id_user,4)),0) as jml From user";
            dt = mysql.ExecQuery(mysql.Query);

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dtRow in dt.Rows)
                {
                    idx = Convert.ToInt32(dtRow.Field<String>("jml"));
                }
            }

            if (idx >= 0 && idx <= 8)
            {
                kode = "U" + "000" + Convert.ToString(idx + 1);
            }
            else if (idx >= 9 && idx <= 98)
            {
                kode = "U" + "00" + Convert.ToString(idx + 1);
            }
            else if (idx >= 99 && idx <= 998)
            {
                kode = "U" + "0" + Convert.ToString(idx + 1);
            }
            else if (idx >= 999 && idx <= 9998)
            {
                kode = "U" + Convert.ToString(idx + 1);
            }
            return kode;
        }
    }
}

11. Jalankan Program.
12. Untuk Video Cek disini https://youtu.be/XCnZYkZxa-w