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
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#
* 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);
}
{
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);
}
0 Comment to "sql insert , Update , delete using c#"
Post a Comment