Database connection with C#


To connect the database with c#(vs 2010) is very easy I connect my database to c# using the query method and add the data grid vied in project to display the table content.
To connect the c# apps to SQL data base use the following steps:

1)      Write this code in using part  “using System.Data.SqlClient;”

2)      Declare one variable connection string type.

3)      Declare SQLconnection():it make connection between database and c# Apps

4)      Declare SQL command(): for execute the command.

5)      Declare SqldataAdapter() for binding the connection with database

6)      Declare dataset.

Download you can download this Example from: http://www.4shared.com/file/TnR6qUzE/data.html

Code for display the data in data grid view:

        if(dataGridView1.DataSource != null)
               ((DataView)dataGridView1.DataSource).Table.Clear();
con = new SqlConnection(connection);
qry = "select * from customer";
if (con.State == ConnectionState.Open)
    con.Close();
 else
     con.Open();
sda = new SqlDataAdapter(qry, con);
sda.Fill(ds, "customer");
this.dataGridView1.DataSource = ds.Tables["customer"].DefaultView;
dataGridView1.Columns[1].ReadOnly = true;

this Code will check the data grid view is null or not if it is not null then it’s clear the data:

</pre>
if(dataGridView1.DataSource != null)
((DataView)dataGridView1.DataSource).Table.Clear();

Code for insert the record in table:

qry = "insert into customer values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')";
con = new SqlConnection(connection);
cmd = new SqlCommand(qry, con);
if (con.State == ConnectionState.Open)
con.Close();
else
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Recored is inserted");

Code for update the record:


qry = "update customer set id='" + textBox1.Text + "',name='" + textBox2.Text + "',number='" + textBox3.Text + "',email='" + textBox4.Text + "'where id='" + textBox1.Text + "'";

con = new SqlConnection(connection);cmd = new SqlCommand(qry, con);

if (con.State == ConnectionState.Open)
con.Close();

else

con.Open();

if( cmd.ExecuteNonQuery()==1)

MessageBox.Show("Recored is update");

else

MessageBox.Show("unable to update or Id not found.");

con.Close();

Code for delete the record:


qry = "delete from customer where id='" + textBox1.Text + "'";

con = new SqlConnection(connection);

cmd = new SqlCommand(qry, con);

if (con.State == ConnectionState.Open)

con.Close();

else

con.Open();

if (cmd.ExecuteNonQuery() == 1)

MessageBox.Show("Recored is deleted");

else

MessageBox.Show("unable to delete record or Id not found.");

con.Close();

when we check the sql connection is open or not if it is open the close the connection

            if (con.State == ConnectionState.Open)
                con.Close();

qry = “delete from customer where id='” + textBox1.Text + “‘”;

con = new SqlConnection(connection);

cmd = new SqlCommand(qry, con);

if (con.State == ConnectionState.Open)

con.Close();

else

con.Open();

if (cmd.ExecuteNonQuery() == 1)

MessageBox.Show(“Recored is deleted”);

else

MessageBox.Show(“unable to delete record or Id not found.”);

con.Close();

About these ads

14 thoughts on “Database connection with C#

  1. Hy ,
    I am beginner in C sharp programing and i can’t figure out the SQl database connection with my SQL server .
    My question is how look a dataconnection with a SQL server in a C# program.
    Weil in connection string must include the server name ,user id ,password and the database name.

    Thanks in advanced,
    Beginner in C#

    • hi Mako Ferenc,
      1).string connection = “Data Source=.;Initial Catalog=info;Integrated Security=True”;
      2) qry = “insert into customer values(‘” + textBox2.Text + “‘,'” + textBox3.Text + “‘,'” + textBox4.Text + “‘)”;
      3) SqlConnection con = new SqlConnection(connection);
      4) SqlCommand cmd = new SqlCommand(qry, con);
      5) if (con.State == ConnectionState.Open)
      6) con.Close();
      7) else
      8) con.Open();
      9) cmd.ExecuteNonQuery();
      1 to 9 step are the data base connection –
      this code is resposible for making the connection with database
      here “Data Source=.” is use for indicate that use the local data base.
      and “initial catalog=info” is define the DataBase name,and “Integrated Security=True” is use for default connect the SQL SERVER with default user name and password.if you not using the Integrated security set your user name and password,remember that user name,password is similar to sql server user name and password.
      in 2 one customer is table name which is created inside the database “info”.
      i think this will help you.
      good luck…

  2. Hi,
    i m beginer in c#..
    i am trying to make SQL tutor and need to connect to the SQL but i want to select database at run time.
    So how?
    hoping for reply.
    thanks.

    • hi sumit,
      wellcome in infynet.wordpress.com
      you want to select data base at run time k
      in the connection string give one textbox in the place of database name and then try to connect.i think this may help you.
      ex:
      1) create one text box called "txt_dbname.Text" in textbox you give the database name
      connection = "Data Source=.;Initial Catalog=" + txt_dbname.Text +";Integrated Security=True";
      MessageBox.Show(connection);

  3. hello i am beginner in c# but want to become strong programmer very soon (inshallah)
    my problem is that my database connectivity fails every my master page does not support content pages…..every thing goes wrong what to do????????
    hoping for reply

    • yes ,
      I wish you become a strong programmer,i want to help you, can you tell me which type of error will you receive and sample code of master page and the child aspx page of your project.

  4. Hey I want to access the database by giving Id into one combo box the its name should be come into another textbox and when I will click to button then the data of table should display into data gridview

    • Hi BhagyaShri,
      thanks For your comment.check you mail for full demo Code.

      First you need to create a database(demo) and table(Employee) in Sql server
      and the query is

      'for create the database
      create database demo
      'for create the table Employee
      USE [demo]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TABLE [dbo].[Employee](
      [EmpID] [int] IDENTITY(1,1) NOT NULL,
      [EmpName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [EmpAddress] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [EmpPhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
      ) ON [PRIMARY]

      And C# code:

      string connString = "Data Source=.;Initial Catalog=demo;Integrated Security=True"; //connection string DB name=demo
      SqlConnection SqlConn; //sql connection variable
      private void Form1_Load(object sender, EventArgs e)
      {
      SqlConn = new SqlConnection(connString);
      if (SqlConn.State == ConnectionState.Open)
      {
      SqlConn.Close();
      SqlConn.Open();
      }
      else
      {
      SqlConn.Open();
      }
      FnBindComboBox();
      }

      private void FnBindComboBox()
      {
      string query = "select EmpID,EmpName from Employee";
      SqlDataAdapter adapter;
      DataSet dataset=new DataSet();
      adapter = new SqlDataAdapter(query, SqlConn);
      adapter.Fill(dataset);
      cmbEmpList.DataSource = dataset.Tables[0]; //binding the combox with dataset
      cmbEmpList.DisplayMember = "EmpName"; //bind the employee name for display
      cmbEmpList.ValueMember = "EmpID"; //bind the emplouee id for value member
      }

      private void cmbEmpList_SelectedIndexChanged(object sender, EventArgs e)
      {
      txtEmpId.Text = cmbEmpList.SelectedValue.ToString();
      txtEmpName.Text = cmbEmpList.Text;

      }

      private void btnDisplay_Click(object sender, EventArgs e)
      {
      string query = "select EmpID as 'ID',EmpName as 'Name',EmpAddress as 'Address', EmpPhone as 'Phone No.' from Employee where EmpID="+cmbEmpList.SelectedValue.ToString();
      SqlDataAdapter adapter;
      DataSet dataset = new DataSet();
      adapter = new SqlDataAdapter(query, SqlConn);
      adapter.Fill(dataset);
      dataGridView1.DataSource = dataset.Tables[0];
      }

  5. SIR,
    my project has a module of silent remote installation of software on client machine from server. consider that the installation on client is going on from server and suddenly the server goes down and client is no more reciving any traffic from server then i want to write a service onto client machine that will continuously keep on analyzing the traffic coming from server and when it recieves no packets from server it should call recovery routine i.e resend the request to server to start the fresh installation…..
    can u plz help me out with code for this service or guide for it…..
    plz take my request into consideration and help me out at soonest…
    Thank you.

  6. Good post. I learn something new and challenging on websites I stumbleupon on a daily basis.
    It will always be interesting to read articles from other authors and practice something from other websites.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s