VISUAL PROGRAMMING

Engr. Anees Ahmed Soomro

Assistant Professor
CS QUEST Nawabshah

https://anees-soomro.neocities.org
 Database Connectivity and CRUD Operations

• Connecting to SQL Server Database

• Connecting app to Database
• Performing CRUD Operations
• DataGridView Control
Installing SQL MS Server Database
• Before connecting your application to database, you need to
install the database application first.
• We will use MS SQL Server.
• Download and Install SQL Server express edition to connect
your app to the database.
Connecting to SQL Server Database

1.Create a new windows forms application project

2.Open the server explorer window
3.Right click Data Connections and choose Create New SQL Server Database
4.In server name enter, <your-pc-name>\SQLEXPRESS. In my case pc name is
shanpc:
5. Type in your new database name in the field New Database
name. In this case mydb2 is new database name.
6. Right click the database name to show its properties.
The properties window shows Connection String property that is
used to connect application with the database.

7. To view the tables in this database, expand the database name and
then tables. For new database, there will be no tables in it. You can
add new Tables by right clicking Tables and clicking Add New
Table.

This will show the table creation form above.

Inserting the table columns for user table:
Connecting app to Database:
1. To use the SQL classes, use the following namespace:
using System.Data.SqlClient;

2.Copy the Connection String Property value to use in code to connect to this database.
private string connetionString = @"Data Source=shanpc\
SQLEXPRESS;Initial Catalog=mydb2;Integrated Security=True";

3. On connect to database button click(better use it in constructor or another method) connect

to the database:
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new
SqlConnection(connetionString); conn.Open();
MessageBox.Show("Connection Open
!"); conn.Close();
}

4. When the connection is no more used, call Close method of connection

object close the connection in order to release the resource:
conn.Close();
 CRUD Operations
• After establishing a connection to the database system, you can perform the CRUD
operations.
Insert Record Into Database:
• Add a new button to the form named add user.
• For the database table named users, inserting a user can be done on this button click
as:
SqlCommand command;
string query = "insert into users (user_id, username, email) values (2, 'user2',
'[email protected]')";
command = new SqlCommand(query, conn);
command.ExecuteNonQuery();
command.Dispose();

Read Records From Database:

• Add a new button on the form named show users. The code below reads and shows users in the
table:
string query = "select * from users";
string users = "";
SqlCommand command = new SqlCommand(query,
conn); SqlDataReader dataReader =
command.ExecuteReader(); while
(dataReader.Read()){
users = users + dataReader.GetValue(0) + " -
"
+ dataReader.GetValue(1) + " - " +
dataReader.GetValue(2) + "\n";
}
MessageBox.Show(users);
Update Record in Database:
Add a new button on the form named update user. The code below updates a user with its user_id
field in the table:
string query = "update users set username='shan' where
user_id=1"; SqlCommand command = new
SqlCommand(query, conn);
int rowsAffected = command.ExecuteNonQuery();
command.Dispose();

Delete Record from Database:

Add a new button on the form named delete user. Double click the button to add its event handler method.
In the event handler, write the code below that deletes a user in the table:
string query = "delete from users where user_id=2";
SqlCommand command = new SqlCommand(query,
conn); int rowsAffected =
command.ExecuteNonQuery();
command.Dispose();
This completes the CRUD operations on a database.

Releasing Resources:
Note that every after every function call, the Dispose or Close method is called to release the resources. This is a
good practice to release any resources used in the app.

Using an Adapter
Another method to get data from the database is by using a SqlDataAdapter. This results in a DataTable or
DataSet (for multiple resultsets) containing the database data. They are like in-memory GridViews. They even
track if a row was changed and can automatically generate update, insert or delete commands when used with a
SqlCommandBuilder.
 DataGridView
The DataGridView control in .NET, displays data in a grid. For example, you can retrieve data from SQL
Server, i.e. via a query, and display the data on the DataGridView control in a Windows Forms .NET
application. Using the DataGridView control's features and properties, you can further process the data and
customize their display.
Now add a new Button named “Refresh”. On this button click, the code below executes to load the current data in the
users table. "Refresh" button's click event code handling block:
private void button6_Click(object sender, EventArgs e)
{
string query = "SELECT * FROM users";
SqlDataAdapter dataadapter =
new SqlDataAdapter(query, conn);
DataSet ds = new DataSet();
dataadapter.Fill(ds, "users");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "users";
}

Insert, Update and Delete Records in DataGridView

On your form, add a Label, TextBox, Button, and DataGridView controls from the ToolBox. The
code below manipulates the DataGridView:
//Insert Data
private void btn_Insert_Click(object sender, EventArgs e)
{
if (txt_Name.Text != "" && txt_email.Text != "")
{

Lecture
notes 11
 cmd = new SqlCommand("insert into students(Name,State) values(@name,@email)", con);
con.Open();
cmd.Parameters.AddWithValue("@name",txt_Name.Text);
cmd.Parameters.AddWithValue("@email", txt_email.Text); cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Inserted Successfully"); DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Fill Details!");
}
}
//Display Data in DataGridView private void
DisplayData()
{
con.Open();
DataTable dt=new DataTable();
adapt=new SqlDataAdapter("select * from students",con); adapt.Fill(dt);
dataGridView1.DataSource = dt; con.Close();
} 12
 //Clear Data
private void ClearData()
{
txt_Name.Text = "";
txt_Email.Text = ""; ID = 0;
}//dataGridView1 RowHeaderMouseClick Event
private void dataGridView1_RowHeaderMouseClick(object sender,
DataGridViewCellMouseEventArgs e)
{
ID =
Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
txt_Name.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
txt_Email.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
}
//Update Record
private void btn_Update_Click(object sender, EventArgs e)
{
if (txt_Name.Text != "" && txt_Email.Text != "")
{
cmd = new SqlCommand("update students set Name=@name, Email=@email where
ID=@id", con);
con.Open(); cmd.Parameters.AddWithValue("@id", ID); 13
 cmd.Parameters.AddWithValue("@name", txt_Name.Text);
cmd.Parameters.AddWithValue("@state", txt_Email.Text); cmd.ExecuteNonQuery();
MessageBox.Show("Record Updated Successfully"); con.Close();
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Update");
}
}
//Delete Record
private void btn_Delete_Click(object sender, EventArgs e)
{
if(ID!=0)
{
cmd = new SqlCommand("delete student where ID=@id",con); con.Open();
cmd.Parameters.AddWithValue("@id",ID); cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Deleted Successfully!"); DisplayData(); 14
 ClearData();
}
else
{
MessageBox.Show("Please Select Record to Delete");
}
}

The above methods perform these CRUD operations on the DataGrid.

Lecture
notes 15