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