Wednesday 21 May 2014

How to use mysql database in csharp

INTRODUCTION


In this article i am going to show how to connect a csharp database with mysql database and how to perform basic operations like inserting record in database , delete a specific record from database , updating a specific record and viewing all data and binding the data to datagridview . Mostly with Csharp Applications you have used MSSQL database but we can also use mysql database for this purpose
DOWNLOAD MYSQL WITH CSHARP APP


REQUIREMENTS

  • Visual Studio 2010 or Visual Studio 2012 
  • MYSQL Database installed with admin roles
  • Dot/connector connec

WHAT WE ARE GOING TO COVER


In this Article we are going to cover the following points
  1. Connect Csharp with mysql
  2. Insert data in mysql database with Csharp code
  3. Delete data in mysql database with Csharp code
  4. Update data in mysql database with Csharp code
  5. Select data in mysql database with Csharp code

CREATING DATABASE


First of Start your Wamp server or Xamp server services This will start Phpmyadmin where all database or SQL related work will be done . Phpmyadmin will be used for create new database schemas and other database oprations are done.

  • Start Wamp / xampp server

How to use mysql database in csharp
  • Then Click on phpMyAdmin as shown in Image above .
  • Now you will get a screen like the image below

    How to use mysql database in csharp
  • If you are using default password for Phpmyadmin then directly click on Go button . Then you will get a screen like below 
  • Then click on Databases as shown in image below to create a new database that will be required for this Application
    How to use mysql database in csharp
  • Then you will get window as shown below Fill up the Database name and Click on Create button
    How to use mysql database in csharp
  • After database is created Now Run the database script we have provided in this tutorial in next steps and run this script as shown in image below ........Just go to sql Tab and paste full script there.

    How to use mysql database in csharp
    • Remember one thing use same name of database as i have given in the snapshot

    DOWNLOAD DATABASE SCRIPT FILE

    Database Script File

    DOWNLOAD FULL PROJECT WITH CODE

    Download Complete project How to use mysql database in csharp

    DOWNLOAD MYSQL CONNECTOR DLL's FOR DOTNET

    For connecting Mysql with Csharp in dotnet you must have connector . you require Connector/NET for mysql connectivity with csharp . you can download Mysql connector / Net from link below :-

    Download Dotnet connector for mysql and csharp


    HOW TO USE MYSQL DLL's

    STEPS :- 

    • Right Click on Solution Name
    • In pop-up menu click on Add Reference
      How to use mysql database in csharp
    • Now select Mysql.Data and Click ok
      How to use mysql database in csharp
    • you can also browse for dll downloaded from Browse Tab
      How to use mysql database in csharp
    • Now MySQL.Data dll file will be added successfully to solution .

    INSERT RECORD IN MYSQL DATABASE USING CSHARP CODE


    How to use mysql database in csharp
    CODE :-
      con.Open();
                MySqlCommand cmd = new MySqlCommand("insert into table1(name,branch,phone) values(@name,@branch,@phone)", con);
                cmd.Parameters.AddWithValue("@name", textBox1.Text);
                cmd.Parameters.AddWithValue("@branch", textBox2.Text);
                cmd.Parameters.AddWithValue("@phone", Convert.ToInt32(textBox3.Text));

                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Inserted");

    EXPLANATION :- 
    1. First Connection is opened to mysql connection using connection string
    2. Then Using MySqlCommand class we are passing insert query to mysql database using con object that is used for establishing connection to mysql database
    3. Then @name,@branch,@phone are parameters given to query to dynamically pass data to query
    4. Then parameters are passed to query dynamically using following line of code
      cmd.Parameters.Addwithvalue('parametername' , 'value' );
    5. cmd.ExecuteNonQuery is used to execute the query in cmd object to database 
    6. con.close closes the connection to the database

    DELETE RECORD IN MYSQL DATABASE USING CSHARP CODE                                      


    How to use mysql database in csharp

    CODE :-

    con.Open();
                if (comboBox1.Text != "")
                {
                    MySqlCommand cmd = new MySqlCommand("delete from table1 where id=@id", con);
                    cmd.Parameters.AddWithValue("@id", comboBox1.Text);
                                cmd.ExecuteNonQuery();                                                                                                                   
                                 con.close();


    EXPLANATION :- 

    1. First Connection is opened to mysql connection using connection string
    2. Then Using MySqlCommand class we are passing insert query to mysql database using con object that is used for establishing connection to mysql database
    3. Then @id is parameter given to query to dynamically pass data to query
    4. Then parameters are passed to query dynamically using following line of code
      cmd.Parameters.Addwithvalue('parametername' , 'value' );
    5. cmd.ExecuteNonQuery is used to execute the query in cmd object to database 
    6. con.close closes the connection to the database

    UPDATE RECORD IN MYSQL DATABASE USING CSHARP CODE


    How to use mysql database in csharp

    CODE :-

     MySqlCommand cmd = new MySqlCommand("update table1 set name=@name,branch=@branch,phone=@phone where id=@id", con);
                cmd.Parameters.AddWithValue("@name",textBox6.Text );
                cmd.Parameters.AddWithValue("@branch",textBox5.Text );
                cmd.Parameters.AddWithValue("@phone",textBox4.Text);
                cmd.Parameters.AddWithValue("@id",comboBox2.Text  );
              
                cmd.ExecuteNonQuery();
                MessageBox.Show("Record Updated");

    EXPLANATION :- 

    1. First Connection is opened to mysql connection using connection string
    2. Then Using MySqlCommand class we are passing insert query to mysql database using con object that is used for establishing connection to mysql database
    3. Then @name,@branch,@phone,@id are parameters given to query to dynamically pass data to query to state which field value are to be replaced
    4. Then parameters are passed to query dynamically using following line of code
      cmd.Parameters.Addwithvalue('parametername' , 'value' );
    5. cmd.ExecuteNonQuery is used to execute the query in cmd object to database 
    6. con.close closes the connection to the database

    SELECT RECORD IN MYSQL DATABASE USING CSHARP CODE


    How to use mysql database in csharp

    CODE :-


     MySqlCommand cmd = new MySqlCommand("select * from table1 where id=@id", con);
                cmd.Parameters.AddWithValue("@id", comboBox2.Text);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    textBox6.Text =dt.Rows [0][1].ToString ();
                        textBox5.Text =dt.Rows [0][2].ToString ();
                        textBox4.Text = dt.Rows[0][3].ToString();
                }


    EXPLANATION :- 

    1. First Connection is opened to mysql connection using connection string
    2. Then Using MySqlCommand class we are passing insert query to mysql database using con object that is used for establishing connection to mysql database
    3. Then @name,@branch,@phone,@id are parameters given to query to dynamically pass data to query to state which field value are to be replaced
    4. Then parameters are passed to query dynamically using following line of code
      cmd.Parameters.Addwithvalue('parametername' , 'value' );
    5. Then we have used MySqlDataAdapter that act as a pipeline between database and csharp app . It takes or select query from csharp application and then make interface or link to database and execute the query in database and get result returned by query executed in database
    6. Then it fill the returned result in placeholder called DataTable that saves data returned from or retrieved from database into tabulated form
    7. Now if (dt.Rows.Count >0) checks if dt contains some item then put the data in respective text fields

    Full Code For Insert update delete and select Data from Mysql in Csharp Application  ( C# )


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

    namespace mysql
    {
        public partial class Form1 : Form
        {
           public  MySqlConnection con = new MySqlConnection("server=localhost;Uid=root;pwd=;Database=sqldotnet");
            public Form1()
            {
                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                retrieve();
            }
            public void retrieve()
            {

                {
                    MySqlCommand cmdsel = new MySqlCommand("select *  from table1 ", con);
                    MySqlDataAdapter dasel = new MySqlDataAdapter();
                    dasel.SelectCommand = cmdsel;
                    DataTable dtsel = new DataTable();
                    dasel.Fill(dtsel);
                    dataGridView1.DataSource = dtsel;
                }
                {
                    int cnt = comboBox1.Items.Count;
                    for (int counter = 0; counter < cnt; counter++)
                    {
                        comboBox1.Items.RemoveAt(0);
                        comboBox2.Items.RemoveAt(0);
                    }
                }
                MySqlCommand cmd = new MySqlCommand("select id  from table1 ", con);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    int cnt = dt.Rows.Count;
                    for (int counter = 0; counter < cnt; counter++)
                    {
                        comboBox1.Items.Add(dt.Rows[counter][0].ToString());
                        comboBox2.Items.Add(dt.Rows[counter][0].ToString());
                    }
                }
                comboBox1.Text = "";
                comboBox2.Text = "";
            }
       
    //Delete Record
            private void button2_Click_1(object sender, EventArgs e)
            {
                con.Open();
                if (comboBox1.Text != "")
                {
                    MySqlCommand cmd = new MySqlCommand("delete from table1 where id=@id", con);
                    cmd.Parameters.AddWithValue("@id", comboBox1.Text);
                    cmd.ExecuteNonQuery();
                    retrieve();
                }
                MessageBox.Show("Record Deleted");
            }
    //select Data
            private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
            {
                MySqlCommand cmd = new MySqlCommand("select * from table1 where id=@id", con);
                cmd.Parameters.AddWithValue("@id", comboBox2.Text);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    textBox6.Text =dt.Rows [0][1].ToString ();
                        textBox5.Text =dt.Rows [0][2].ToString ();
                        textBox4.Text = dt.Rows[0][3].ToString();
                }
            }
    //Update Data
            private void button3_Click_1(object sender, EventArgs e)
            {
                MySqlCommand cmd = new MySqlCommand("update table1 set name=@name,branch=@branch,phone=@phone where id=@id", con);
                cmd.Parameters.AddWithValue("@name",textBox6.Text );
                cmd.Parameters.AddWithValue("@branch",textBox5.Text );
                cmd.Parameters.AddWithValue("@phone",textBox4.Text);
                cmd.Parameters.AddWithValue("@id",comboBox2.Text  );
             
                cmd.ExecuteNonQuery();
                MessageBox.Show("Record Updated");
                retrieve();
            }
    //Insert Data
            private void button1_Click_1(object sender, EventArgs e)
            {
                con.Open();
                MySqlCommand cmd = new MySqlCommand("insert into table1(name,branch,phone) values(@name,@branch,@phone)", con);
                cmd.Parameters.AddWithValue("@name", textBox1.Text);
                cmd.Parameters.AddWithValue("@branch", textBox2.Text);
                cmd.Parameters.AddWithValue("@phone", Convert.ToInt32(textBox3.Text));

                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Inserted");
                retrieve();
            }
        }
    }


    Wednesday 7 May 2014

    sql insert , Update , delete using c#


    To connect Database from your Windows Application is required  to make Application for querying the database and retrieve the desired result from database . Database in widely used in management software's  and various applications to save record and search the record .

    Database dependent applications are widely used in the market . ERP projects of organisation are highly Database dependent applications . The main two types of databa se that are widely used in .NET Applications :-

    * Microsoft Access Database
    * MSSQL Server ( Microsoft Structured Query Language )


    The main Languages used in .NET Platform for connecting windows application to database are :-

    * C#
    * VB#

    Sql ( Structured Query Language ) is most widely used Database . It is free and Opensource Database . It has simple query structure to Manipulate data from database in Database applications 

    To Learn About SQL and how to learn SQL you can go to following links first then go forward :-

    SQL Tutorial with Examples and snapshots

    insert , update , delete , select queries sql

    Download Database Script file and execute it in your SQL Server

    Steps To Execute this Script file -

    1. Download Script file from link below .

    2. Now open it with MsSql .
    3. Now Create Database Named  'Accounts_database' in MsSql server 
    4. Now select this database from Dropdown 
    5. And Execute the Script code by clicking F5 .

    Design of Form 

    Download Complete Project File


    1. In Design We have some Fields to be filled to insert Data To database

    2. We are having 3 Buttons - One for Update and Insert Operation , Second For Delete Operation and Third for Exit or close form

    3. We are using Datagrid View to Instantly Showing The data in database and it is updated when we insert new data to database or we delete data from database

                                                       Insertion/Updation In Database 


    1. For insertion of data Fill the fields on form Only Code and Agency Name is required and rest of the fields are optional fields


    2. Now you can see filled data is successfully inserted and it is immediately shown in below datagridview


    CODE For Insert/Update


     try


     {
                    SqlCommand cmd1 = new SqlCommand("select * from Agency_detail where  Agency_code=" + textBox1.Text, con);
                    SqlDataAdapter da1 = new SqlDataAdapter();
                    DataTable dt1 = new DataTable();
                    da1.SelectCommand = cmd1;
                    da1.Fill(dt1);
                    if ((dt1.Rows.Count > 0) && (dt1.Rows[0][0].ToString() != ""))
                    {
                        if (MessageBox.Show("ID Already Exist Do you want to Update It", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                        {
                            SqlCommand cmdupdate = new SqlCommand("update Agency_detail set Agency_Name=@p1,Phone=@p2,fax=@p3,Mobile_Number=@p4,DOJ=@p5,opening=@p6,Remark=@p7,Address=@p8,city=@p9,state=@p10,zip_code=@p11,email=@p12 where Agency_code=" + textBox1.Text, this.con );
                            cmdupdate.Parameters.AddWithValue("@p1", textBox2.Text);
                            cmdupdate.Parameters.AddWithValue("@p2", textBox4.Text);
                            cmdupdate.Parameters.AddWithValue("@p3", textBox3.Text);
                            cmdupdate.Parameters.AddWithValue("@p4", textBox8.Text);
                            cmdupdate.Parameters.AddWithValue("@p5", dateTimePicker1.Value.Date);
                            cmdupdate.Parameters.AddWithValue("@p6", textBox6.Text);
                            cmdupdate.Parameters.AddWithValue("@p7", textBox5.Text);
                            cmdupdate.Parameters.AddWithValue("@p8", textBox17.Text);
                            cmdupdate.Parameters.AddWithValue("@p9", textBox16.Text);
                            cmdupdate.Parameters.AddWithValue("@p10", textBox15.Text);
                            cmdupdate.Parameters.AddWithValue("@p11", textBox14.Text);
                            cmdupdate.Parameters.AddWithValue("@p12", textBox13.Text);

                            //con.con.Open();
                            cmdupdate.ExecuteNonQuery();
                            MessageBox.Show("Updated");
                            retrieve_data();
                            clearall();
                        }
                        else
                        {

                        }
                    }
                    else
                    {

                        if ((textBox1.Text != "") && (textBox2.Text != ""))
                        {
                            SqlCommand cmd = new SqlCommand("insert into Agency_detail values(@para1,@para2,@para3,@para4,@para5,@para6,@para7,@para8,@para9,@para10,@para11,@para12,@para13)", con);
                            cmd.Parameters.AddWithValue("@para1", Convert.ToInt64(textBox1.Text));
                            cmd.Parameters.AddWithValue("@para2", textBox2.Text);
                            cmd.Parameters.AddWithValue("@para3", Convert.ToInt64(textBox4.Text));
                            cmd.Parameters.AddWithValue("@para4", Convert.ToInt64(textBox3.Text));
                            cmd.Parameters.AddWithValue("@para5", Convert.ToInt64(textBox8.Text));
                            cmd.Parameters.AddWithValue("@para6", dateTimePicker1.Value);
                            cmd.Parameters.AddWithValue("@para7", Convert.ToDouble(textBox6.Text));
                            cmd.Parameters.AddWithValue("@para8", textBox5.Text);
                            cmd.Parameters.AddWithValue("@para9", textBox17.Text);
                            cmd.Parameters.AddWithValue("@para10", textBox16.Text);
                            cmd.Parameters.AddWithValue("@para11", textBox15.Text);
                            cmd.Parameters.AddWithValue("@para12", Convert.ToInt64(textBox14.Text));
                            cmd.Parameters.AddWithValue("@para13", textBox13.Text);
                            //con.con.Open();
                            cmd.ExecuteNonQuery();
                            retrieve_data();
                            clearall();
                        }
                        else { MessageBox.Show("Agency ID and Name Must Required"); }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               



    Deletion Of Data


    To Delete data or  record from database :- 

    1. Enter the Required fields :- ID and Agency Name of Record to be deleted 

    2. Then press delete button and data will be deleted

    3. It will be immediately shown as deleted in datagridview



    CODE For DELETE



     try
                {
                    SqlCommand cmd = new SqlCommand("delete from Agency_detail where Agency_code =@code", this.con);
                    cmd.Parameters.AddWithValue("@code", Convert.ToInt32(textBox1.Text));
                    //con.con.Open();
                    cmd.ExecuteNonQuery();
                    retrieve_data();
                    clearall();
                }
                catch (Exception ex)
                {
                  MessageBox.Show(ex.Message);

                }