Using ADO.Net(with C#.Net) to communicate with SQL Server 2008

            Hello friends, am very happy to share this small thing to you all, This might be small thing but, this is base to all Database connection and manipulation.

here i used the ADO.Net with fully coded (instead of Data binding)connection, so that every one can understand easily.

Prerequisites


  1. MS SQL Server (any version)
  2. Visual Studio 2008

Source Code


using System.Data.SqlClient;
//we are going to connect with sql server so we need to import the sqlclient namespace
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;

namespace sameple_DB
{
    public partial class Form1 : Form
    {
        SqlConnection vCon = new SqlConnection("Data Source=srm2;Initial Catalog=sample;Persist Security Info=True;User ID=sa;Password=adminsql");
        //creating Connection with Connection string
        //Here i used SQL Server authentication
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                vCon.Open(); //Trying to open connection
            }
            catch (Exception ex)
            {
                MessageBox.Show("error occured " + ex.Message);
                this.Dispose();
            }

        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            string vsql = string.Format("insert into student values('{0}',{1})", txtName.Text, Int32.Parse(txtAge.Text));
            //Creating the SQL Statement to execute
            SqlCommand vCom = new SqlCommand(vsql, vCon);
            //command to execute the sql statement
            try
            {
                vCom.ExecuteNonQuery(); //executing the command
                vCom.Dispose(); //releasing the command object 
                MessageBox.Show("The data stored ");
                //clearing the text boxes
                txtAge.Text = "";
                txtName.Text = "";
                txtName.Focus(); //setting the focus towards name text box
            }
            catch (Exception ex)
            {
                MessageBox.Show("error occured " + ex.Message);

            }
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            SqlCommand vCom = new SqlCommand("select * from student", vCon);
            DataSet vds = new DataSet();
            //here we are using the disconnected database concept for to reterive whole records form the table
            SqlDataAdapter vDa = new SqlDataAdapter(vCom);
            vDa.Fill(vds, "res");
            //now filling the result into the dataset and am naming the result as "res" for further identification
            dataGridView1.DataSource = vds.Tables["res"];
            //now am setting the datasource with the resulted datatable(named as "res" in vds dataset)
            vCom.Dispose();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            vCon.Close();
            Application.Exit();
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            txtAge.Text = "";
            txtName.Text = "";
            txtName.Focus(); //setting the focus towards name text box
        }
    }
}

Screenshot

Form Design

Video Tutorial

Thank you

Comments

Popular posts from this blog

Know more about Azure Website KUDU?

How to check the Hybrid Connection in Azure Website (Webapp)

A Technical Talk on Microservices and Cloud Deployment