Operations On Database Using .net C#

Code Snippets 4 U
using System;
using System.Drawing;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace InsertUpdateDelete
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=test;UID=root;PASSWORD=;");
        

        private void Form1_Load(object sender, EventArgs e)
        {
            // create panel
            Panel pl = new Panel();
            pl.Size = new Size(400, 400);
            pl.Location = new Point(0, 0);
            this.Controls.Add(pl);
            int y = 0;
            // open connection 
            try
            {
                conn.Open();
                addLabel("Opening Connection...", y);
                y += 20;
            }
            catch(MySqlException me)
            {
                addLabel("Unable to Open Connection...", y);
                return;
            }
            MySqlCommand comm;
            // create table block
            try
            {
                comm = new MySqlCommand("Create Table xyz (id int(10) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20))", conn);
                comm.ExecuteNonQuery();
                addLabel("Table xyz Created", y);
                y += 20;
            }
            catch(MySqlException me)
            {
                addLabel("Table is already created. Or some error occurred.",y);
                y += 20;
            }

            // insert block
            try
            {
                comm = new MySqlCommand("INSERT INTO xyz (name) VALUES ('sachin'),('jitender'),('Bhains'),('baloo')",conn);
                comm.ExecuteNonQuery();
                addLabel("Insert Complete",y);
                y += 20;
            }
            catch (MySqlException me)
            {
                addLabel("Error While Inserting Data", y);
                y += 20;
            }

            // update block

            try
            {
                comm = new MySqlCommand("UPDATE xyz SET name='Life' WHERE name='Bhains'",conn);
                comm.ExecuteNonQuery();
                addLabel("Changed the name from Bhains to Life",y);
                y += 20;
            }
            catch(MySqlException me)
            {
                addLabel("Error While Updating the Database", y);
                y += 20;
            }

            // delete block

            try
            {
                comm = new MySqlCommand("Delete From xyz WHERE name = 'baloo'",conn);
                comm.ExecuteNonQuery();
                addLabel("Deleted the row with name 'baloo'",y);
                y += 20;
            }
            catch(MySqlException me)
            {
                addLabel("Error While Deleting record from Table xyz",y);
                y += 20;
            }
            // function to add label to form
            void addLabel(string str, int z)
            {
                Label l = new Label();
                l.Text = str;
                l.AutoSize = true;
                l.Location = new Point(0, z);
                pl.Controls.Add(l);

            }
            
            conn.Close();

        }
    }
}

Leave a Reply

Your email address will not be published. Required fields are marked *

four + 6 =