Showing posts with label .NET Apps. Show all posts
Showing posts with label .NET Apps. Show all posts

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);

                }


    Tuesday, 22 October 2013

    Datagridview Cellvaluechanged Event VB.net



    DatagridView is a windows forms grid Control . It was first introduced in 
    .Net framework 1.0 and It comes with advanced and improved features in .Net Framework 2.0  It allows you to show the data in the tabular form. It contains data organized in rows and columns. It can be used to retrieve data in Tabular from from Database . It can be bound to Sql Database or Microsoft-Access Database.


         
    Datagridview Cellvaluechanged Event in VB.net is used to make the change to occur or to call an event when value within particular cell is changed. In this app I have put event on each . when value of cell is changed its corresponding value in Maskedtextbox will change


    Download Datagridview cellvaluechanged App



    Design For Datagridview Cellvaluechanged Event VB.net


    -- Add DatagridView To Form


    1. Click on Tools


    2. Then Scroll down and Find Datagridview .






    3. Now Double click on it to put it on windows form.




    -- Add Columns To Datagridview

    1. Single Click on Datagridview


    2. Click on small arrow on Top-right of Datagridview


    3. A pop-up Menu appears. Click on "Add Column"





    4. Now in Add Column Dialog Box Change HeaderText to "Name" and Click on Add . This Adds column Name to Datagridview


    5. Now in second column give HeaderText to "Salary"


    6. Now in Third column give HeaderText to "Bonus"


    7. Now in Fourth column give HeaderText to "Total Salary"


    8. Now Add Four Labels to Form and change its text property to :-


    ( I ) Name

    ( II ) Salary
    ( III )Bonus
    ( IV )Total Salary

    9. Take four Masked Textbox from Toolbox and drag them to Windows form






    10. Take a Button and Change its Text Property to "Get Data".





    How To Operate


    1 click on Get Data Button


    2. It will load Default data in datagridview


    3. If you change data in any Datagridview column Then the corresponding value in MaskedTextbox also change




    Code For Datagridview 


    Cellvaluechanged Event vb.net App



    Public Class Form5 Public isdirty As Boolean Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click DataGridView1.Rows(0).Cells(0).Value = "Jorge" DataGridView1.Rows(0).Cells(1).Value = 12000 DataGridView1.Rows(0).Cells(2).Value = 2900 DataGridView1.Rows(0).Cells(3).Value = DataGridView1.Rows(0).Cells(1).Value + DataGridView1.Rows(0).Cells(2).Value End Sub Private Sub MaskedTextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MaskedTextBox1.TextChanged DataGridView1.Rows(0).Cells(0).Value = MaskedTextBox1.Text End Sub Private Sub MaskedTextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MaskedTextBox2.TextChanged DataGridView1.Rows(0).Cells(1).Value = MaskedTextBox2.Text DataGridView1.Rows(0).Cells(3).Value = Val(DataGridView1.Rows(0).Cells(1).Value) + Val(DataGridView1.Rows(0).Cells(2).Value) End Sub Private Sub MaskedTextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MaskedTextBox3.TextChanged DataGridView1.Rows(0).Cells(2).Value = MaskedTextBox3.Text DataGridView1.Rows(0).Cells(3).Value = Val(DataGridView1.Rows(0).Cells(1).Value) + Val(DataGridView1.Rows(0).Cells(2).Value) End Sub Private Sub MaskedTextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MaskedTextBox4.TextChanged DataGridView1.Rows(0).Cells(3).Value = MaskedTextBox4.Text DataGridView1.Rows(0).Cells(3).Value = Val(DataGridView1.Rows(0).Cells(1).Value) + Val(DataGridView1.Rows(0).Cells(2).Value) End Sub Private Sub EndEdit(ByVal sender As System.Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged If DataGridView1.IsCurrentCellDirty Then DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit) End If End Sub Private Sub DataGridView1_TextChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged If e.RowIndex = -1 Then isdirty = True End If If e.ColumnIndex = 0 Then MaskedTextBox1.Text = DataGridView1.Rows(0).Cells(0).Value End If If e.ColumnIndex = 1 Then MaskedTextBox2.Text = DataGridView1.Rows(0).Cells(1).Value DataGridView1.Rows(0).Cells(3).Value = Val(DataGridView1.Rows(0).Cells(1).Value) + Val(DataGridView1.Rows(0).Cells(2).Value) End If If e.ColumnIndex = 2 Then MaskedTextBox3.Text = DataGridView1.Rows(0).Cells(2).Value DataGridView1.Rows(0).Cells(3).Value = Val(DataGridView1.Rows(0).Cells(1).Value) + Val(DataGridView1.Rows(0).Cells(2).Value) End If If e.ColumnIndex = 3 Then MaskedTextBox4.Text = DataGridView1.Rows(0).Cells(3).Value Dim c As Integer = DataGridView1.Rows(0).Cells(3).Value Dim str As String = CInt(c) If Len(str) = 1 Then MaskedTextBox4.Mask = "0" ElseIf Len(str) = 2 Then MaskedTextBox4.Mask = "00" ElseIf Len(str) = 3 Then MaskedTextBox4.Mask = "0,00" ElseIf Len(str) = 4 Then MaskedTextBox4.Mask = "0,000" ElseIf Len(str) = 5 Then MaskedTextBox4.Mask = "00,000" ElseIf Len(str) = 6 Then MaskedTextBox4.Mask = "0,00,000" ElseIf Len(str) = 7 Then MaskedTextBox4.Mask = "00,00,000" End If End If End Sub Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load MaskedTextBox4.Mask = "0000000" '' End Sub End Class




    OUTPUT :-




    After Changing value of Cell of Datagridview :- 




    Friday, 11 October 2013

    Masked TextBox in .NET

    Masked TextBox Control in .NET are used to Restrict the input from user . Masked Edit controls are also used to change the format of output that is to be delivered . In vb 6 we have Mask Edit control But in VB.NET we can get Maskedtextbox 
    from Toolbox. 

    Saturday, 5 October 2013

    Connect Access Database with C#


    Connect Access Database with C#

    To connect Database with your Windows Application to make Application for querying the database and retrieve the desired result from database . Database in widely used in management software's . Them main purpose of making Management software is to store the database of the work being done in the organisation in to a secure place and easily accessible place .

    Database dependent applications are widely used in the market . ERP projects of organisation are highly Database dependent applications . The main two types of database 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#

    Microsoft Access Database is most simplest approach to connect your windows application to database . Microsoft is easy to use and very effective database Software that uses simple Query approach.  In old Microsoft Access Database is used with VB 6 to connect VB 6 Applications to Database and Microsoft also extend it for VB.NET to connect .NET Platform applications to Database

    DOWNLOAD This Demo To Connect Access Database with C#


    Steps to Make Microsoft Access Database:

    1. Go to desired location you want to make your Database
    2. Right click then click on new and 
    3. Then click Microsoft Access 2007 Database
    4. Create Desired Table with required Fields 
    5. Save it as Microsoft Access database 2003 format 
    6. Click OK 

    DOWNLOAD THIS Demo Database To Connect Access Database with C#


    Steps To Make This Demo Project :-


    1. Start your Visual Studio 2010 
    2. At start page click on Create New Project 
    3. In Dialog Box provide Project name and path where to save this project and click ok
    4. Now from Toolbox Drag four Textboxes 
    5. Drag 1 Button and make design as show in below image

    Design of Form 1 


    6. Now copy the code given in code section below :-


    Code To Connect Access Database with 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 System.Data.OleDb;
    namespace access_db_csharp
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            OleDbConnection con=new OleDbConnection ();
            OleDbCommand cmd=new OleDbCommand ();
            string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\test.mdb";
                    
                   private void button1_Click(object sender, EventArgs e)
            {
                cmd.CommandText = "insert into info_table values (" + textBox1 .Text + ",'" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + ")";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Done");


                        }

            private void Form1_Load(object sender, EventArgs e)
            {
                con.ConnectionString = connectionstring;
                con.Open();
            }
        }
    }

    Explanation of Code


    Imports System.Data.OleDb


    This line of code tells we are importing Oledb Namespace. Oledb stands for Object Linking and Embedding . For using Access database with .net applications we have to inherit classes from this oledb Namespace . Oledb Namespace contains all classes that are required to connect vb.net/c# application to Microsoft Access
    Database




    Public Class Form1
        Dim connection As New OleDbConnection
        Dim command As New OleDbCommand
        Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\test.mdb
    End Class

    Now In These lines of code we declared 3 things
    1. OledbConnection :- OledbConnection is a class contained in System.Data.Oledb Namespace and it is used to make connectivity to Access Database . It receives the connection string that tells the path of Microsoft Access Database to connect to it. In this line of code we have created instance of Oledbconnection class

    2. OledbCommand :- OledbCommand is a class contained in S
    ystem.Data.Oledb Namespace and it is used to define or specify the command that we are going to execute on Microsoft Access Database. In this line of code we are creating instance of OledbCommand to use this instance in further code

    3. Connection string :- Now we are creating a variable named connectionString that will receive the string or path that tell how we connect to our Access database. It receives two parameters :-

    Provider=Microsoft.Jet.OLEDB.4.0 -- 
    Data Source=" & "C:\test.mdb

    Now Provider is main part here in connection string . Provider is different for different approaches used for connecting data to database . The Connection string contains information that the Provider needs to know to connect to database Once connected then rest of job is done by provider


        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            connection.ConnectionString = ConnectionString
            connection.Open()
        End Sub

    Now in this block of code we have initialized our OledbConnection instance with connection string variable that we have initialized in previous step. So in this code we have given the Oledbconnectin instance means connection some information about Provider to use and location of database file on computer

    Then we have used connection.open() method opens port for Enter into Access Database. this opens a pipe to execute query in database 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            command.CommandText = "insert into info_table values (" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "'," & TextBox4.Text & ")"
            command.CommandType = CommandType.Text
            command.Connection = connection
            command.ExecuteNonQuery()
            MsgBox("Record inserted ")
        End Sub


    Now at last we have used insert query that is coded on Button Click Event . In this code I have told which commandtext to choose and what will be commandType test or storeprocedure . and also we have initialized the command with Oledbconnection instance. Then we have execute command using ExecuteNonQuery() method. This method returns the number of rows affected the database 

    Sunday, 22 September 2013

    string to double in vb.net

    DEMO PROJECT TO CONVERT STRING VALUE TO DOUBLE USING VB2010


    To convert a string  to double in .net there are many methods available . A string value is collection of number of characters within a string variable eg:-

    Dim a as string = " A String Value "

    Double values are  floating point numbers . These are values with precession To convert a string value to double value the main avaliable methods are :-

    * CDBL Method    :-                 Cdbl ( string_value ) 

    * Convert . ToDouble   :-          Convert . ToDouble ( string_value )  

    * TryParse Method 

    The Best Method to convert string value to double from methods given above is TryParse . TryParse will first check whether given string value can be converted to double or not If these values cannot be converted into Double then it does not return anything . TryParse method is used with if condition to check whether string value can be converted to double or not If it cannot be converted to Double value then we can Pass string in message box that "This Value Cannot Be Converted To  Double"

    Design of Project string to double in vb.net



    Code for string to double in vb.net


    Public Class Form1

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim string_value As String
            string_value = TextBox1. Text
            Dim double_value As Double
            If Double.TryParse(string_value, double_value) Then
                MsgBox(double_value)
            Else
    MsgBox("This String Value Cannot Be Converted To Double")
            End If
        End Sub

    End Class

    Explanation of Code :- string to double in vb.net



    1. Public class Form1
      In first line, i have declared a class . The access specifier of class is Public so that in case of using inheritance other class can easily access this class by using an instance or object of this class
    2.  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    3. Here, We have declared a Private Sub Procedure name Button1. This sub-procedure is of object Named or control named Button1 . 
    • Button1_Click , Here we are declaring an Event for Button Control. We are using click event so that the procedure will execute or code that is written under this Button1 control will run when click the button at runtime.
    • Dim String_value as string , In this line of code we have simply declared a variable named "string_value" . Dim is a keyword that is used in visual basic to declare a variable . followed by name of variable by keeping in mind the rules for declaring the variable in visual baisc. Then 'as ' is also a keyword this keyword is used to specify followed by it that what type of data we are going to store in the variable that we are creating now. Then we will provide the datatype of variable

       4. " string_value = TextBox1. Text" :- Now we are simply assigning the value that we enter in the textbox at runtime into string variable we have created in previous step

      5.  Dim double_value As Double , In this line of code we have simply declared a variable named "double_value" . Dim is a keyword that is used in visual basic to declare a variable . followed by name of variable by keeping in mind the rules for declaring the variable in visual baisc. Then 'as ' is also a keyword this keyword is used to specify followed by it that what type of data we are going to store in the variable that we are creating now. Then we will provide the datatype of variable

      6. In code below we have used Try.Parse this is mainly used to try that the value given as parameter of type string can be converted into double value or not . If this value cannot be converted from string to double in .net then it will execute the else statement telling the Trying to Parse string value into double value is unsuccessfull . If given value can be converted then it will convert it and given the message along with string to double converted value
       If Double.TryParse(string_value, double_value) Then
                  MsgBox(double_value)
              Else
      MsgBox("This String Value Cannot Be Converted To Double")
              End If

      7. Then End Sub will close the Button1_click subprocedure  and End Class will end the class form1

    Tuesday, 10 September 2013

    delete record from access database

    To delete Record from Access database we have used Dataset and Oledbdataadapters 

    DataAdapter :- DataAdapter  is like a pipeline or path or bridge that is used to execute the given query on database table . DataAdapter that we have used is OledbdataAdapter as we are using MS-Access as database . Data Adapter first takes the command to be executed on table data from Oledbcommand object and this object contains the query that is to be executed on database . Then it execute query on Database table data and store resultant data in form of rows and columns or Tabulated form in Dataset

    Dataset :- Dataset is generally a memory area where the result of the query that we have executed on database is placed . When a query is passed by using Oledbdataadapter to execute that query on database specified table . Then the result of the query has to be placed some to use it . Like to show it in Datagridview or to show that result data in Textboxes or to manupulate the data and then save that manipulated data again to Table in database

    DOWNLOAD this Demo Project from my gmail upload link :-

    Steps to Make Microsoft Access Database:

    1. Go to desired location you want to make your Database
    2. Right click then click on new and 
    3. Then click Microsoft Access 2007 Database
    4. Create Desired Table with required Fields 
    5. Save it as Microsoft Access database 2003 format 
    6. click ok 

    DOWNLOAD THIS Demo Database from my gmail upload link :-


    Steps To Make This Demo Project :-


    1. Start your Visual Studio 2010 
    2. At start page click on Create New Project 
    3. In Dialog Box provide Project name and path where to save this project and click ok
    4. Now from Toolbox Drag one ComboBox , one Label and One Button
    5. Now Change Label 'Text' Property to "ID_num"
    6. Now Change Button 'Text' Property to "Delete"
    7. Make Design as shown below

    Design of Form 1 


    8.  In Below image you can see there are two records in Data base Table 'info_table ' .


    9. Now Run the Application 
    10. Click on Down arrow of combobox Your ID-num will be shown as image below



    11. Now select an id_num from combobox items and then click on Delete


    12. Now Record will be deleted from database table as shown below



    Code for Application 


    Imports System.Data.OleDb


    'http://www.geeksprogrammings.blogspot.in

    Public Class Form1
        Dim connection As New OleDbConnection
        Dim Del_command As New OleDbCommand
        Dim select_command As New OleDbCommand
        Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
            "C:\test.mdb"
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            connection.ConnectionString = ConnectionString
            connection.Open()
            select_command.CommandText = "select id_num from info_table"
            select_command.CommandType = CommandType.Text
            Dim adapter As New OleDbDataAdapter("select id_num from info_table", connection)
            'adapter.SelectCommand = select_command
            Dim dt As New DataTable
            adapter.Fill(dt)
            select_command.Connection = connection
            select_command.ExecuteNonQuery()
            Dim i As New Integer
            For i = 0 To dt.Rows.Count - 1
                ComboBox1.Items.Add(dt.Rows(i).Item(0))
            Next
        End Sub

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Del_command.CommandText = "delete from info_table where id_num=@id_num"
            Del_command.Parameters.AddWithValue("@id_num", ComboBox1.SelectedItem)
            Del_command.CommandType = CommandType.Text
            Del_command.Connection = connection
            Del_command.ExecuteNonQuery()
            MsgBox("Record Deleted ")
        End Sub


    Explanation of Code


    Imports System.Data.OleDb


    This line of code tells we are importing Oledb Namespace. Oledb stands for Object Linking and Embedding . For using Access database with .net applications we have to inherit classes from this oledb Namespace . Oledb Namespace contains all classes that are required to connect vb.net/c# application to Microsoft Access
    Database




    Public Class Form1
        Dim connection As New OleDbConnection
        Dim command As New OleDbCommand
        Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\test.mdb
    End Class

    Now In These lines of code we declared 3 things
    1. OledbConnection :- OledbConnection is a class contained in System.Data.Oledb Namespace and it is used to make connectivity to Access Database . It receives the connection string that tells the path of Microsoft Access Database to connect to it. In this line of code we have created instance of Oledbconnection class

    2. OledbCommand :- OledbCommand is a class contained in S
    ystem.Data.Oledb Namespace and it is used to define or specify the command that we are going to execute on Microsoft Access Database. In this line of code we are creating instance of OledbCommand to use this instance in further code

    3. Connection string :- Now we are creating a variable named connectionString that will receive the string or path that tell how we connect to our Access database. It receives two parameters :-
    Provider=Microsoft.Jet.OLEDB.4.0 -- 
    Data Source=" & "C:\test.mdb

    Now Provider is main part here in connection string . Provider is different for different approaches used for connecting data to database . The Connection string contains information that the Provider needs to know to connect to database Once connected then rest of job is done by provider


        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            connection.ConnectionString = ConnectionString
            connection.Open()
        End Sub

    Now in this block of code we have initialized our OledbConnection instance with connection string variable that we have initialized in previous step. So in this code we have given the Oledbconnectin instance means connection some information about Provider to use and location of database file on computer

    Then we have used connection.open() method opens port for Enter into Access Database. this opens a pipe to execute query in database

    How To Use Access database with vb.net