C# tutorial - linq to read, add, delete, and update rows from a Window Form


Using linq to read, add, delete, and update rows of a table of SQL Server database from a Window Form

In the previous page, you learnt how to read data, add new rows, update rows, and delete rows from SQL Server database in a Console Application by using LINQ. This tutorial talks about how to perform these tasks in a Windows Form Application. You will learn how to read data from a SQL Server database and place them in a ListView control, add, update, and delete rows from a Window Form.

Now, you need to create a table called Institution in your SQL Server database. This table stores information of institutions. Each institution has id, and name.

institution table

Then create a Windows Form Application in Visual Studio C# 2010 Express. Drag and drop a ListView control to the form (Form1). The ListView (listView1) is used to display records from the Institution table. Above the ListView, there are two buttons, delete and add new buttons. The delete button is clicked to delete a checked row or multiple checked rows. Below the ListView, you need to place a panel container. On this panel (panel1), there are two textboxes and one save button. One textbox is for entering id and another one for entering name of a institution. The save button is used to save a new record or any change to a row to the Institution table. The controls in the panel1 are used for two purposes: adding a new row and updating an existing row. The panel1 is not visible when the form firstly loads. It is visible when the user clicks the add button to add a row or when the user double clicks a row to update its data.

 

linq and listview

Now double click the form to open its code window. Before you can work with the table of the database, you need to create a class to map to the table. The Institution class below is used to map to the Institution table.

 

[Table(Name = "Institution")]
class Institution
{
[Column(IsPrimaryKey = true)]
public string id { get; set; }
[Column(Name = "name")]
public string name { get; set; }

}

In the Form1_Load procedure, you need to write the code as shown below. The code adds two colummns to the ListView, specifies properties of the ListView when it displays, connect to database, hides panel 1, initializes addr variable, specifies the size of the form1, and displays the records in the ListView.

//add two coumns to ListView
listView1.Columns.Add("ID", 100, HorizontalAlignment.Left);
listView1.Columns.Add("Name", 150, HorizontalAlignment.Left);
//Specify ListView properties
listView1.View = View.Details;
listView1.AllowColumnReorder = true;
listView1.FullRowSelect = true;
listView1.GridLines = true;
listView1.CheckBoxes = true;
//Connect to SQL Server database
db = new DataContext("Server=ASPIREV5\\SQLEXPRESS; Initial Catalog=testdb; Integrated Security=SSPI");
//add images to buttons (save, delete, and add new buttons)
btsave.Image = Image.FromFile(Environment.CurrentDirectory+"/images/saveicon.png");
btdelete.Image = Image.FromFile(Environment.CurrentDirectory + "/images/delicon.png");
btadd.Image = Image.FromFile(Environment.CurrentDirectory + "/images/addicon.png");
//hid panel1
panel1.Visible = false;
//initialize adding record action boolean to false
addr = false;
//specify size of the form when it firstly opens
this.Size = new System.Drawing.Size(this.Width, listView1.Height + 100);
//Display records from the database
showRecords();

Because the controls in the panel1 is used for different tasks (adding a new row, and updating an existing row), you need something that can be used to identify which task or action that is performed by the user. The Boolean variable addr is used to determine whether the user adds a new row to the table. Its default value is false. If the user clicks the add button, the value of addr variable will change from false to true.

In the code above calls a method called showRecords. This method uses Linq query to get all rows from the Institution table and place them in the ListView.

//read data from the Institution table
private void showRecords()
{
  if (listView1.Items.Count > 0)
   listView1.Items.Clear(); //remove old rows
  var x = from st in db.GetTable<Institution>()
   select st;
  foreach (var s in x)
   {

ListViewItem item = new ListViewItem(s.id);
item.SubItems.Add(s.name);
listView1.Items.Add(item);
 }
}

There are three additional methods that have to be created. These methods are addRecord, deleteRecord, and updateRecord. The addRecord will be called when the user clicks the add new button. The udateRecord is called when the save button is pushed and the value of the addr variable is set to false. The deleteRecord is called when the delete button is pushed. It will delete all checked rows from the ListView as well as from the database. For the complete code of the program and related files, you need download the zip file of the program from the here.



Comments

CAPTCHA image




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.