Load Records Into Datagridview (Best Way To Fill Datagridview With Data)
This article is on the best way to fill datagridview with large amount of data though there are many solutions to c# gridview large data but am pretty sure this sample code is aim at how to speed up datagridview c#. This project is a combination of loading records inside datagridview and filtering datagridview with textbox.
Create a project DatagridRec
On your form1, drag a textbox (name= searchgud) and a datagridview(dataGridView1) control to your form. Note: let your textbox be untop of the datagridview control.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Threading;
using System.Collections;
namespace DatagridRec
{
public partial class Form1 : Form
{
String db = “Provider=Microsoft.JET.OLEDB.4.0;Jet OLEDB:Database Password=SecureEdu;Data Source=|DataDirectory|\\db\\Inventory.mdb”;
//Note: db is a folder name inside your main directory
DataSet memoryDataset;
private OleDbConnection con = null;
private OleDbCommand command = null;
private OleDbDataAdapter dataAdapter = null;
public DatagridRec ()
{
InitializeComponent();
}
private void DatagridRec _Load(object sender, EventArgs e)
{
ReloadGrid(); // To prevent Double Declaration
}
public void ReloadGrid()
{
// Load The MemoryDataset while loading the Form
String Query8 = “SELECT bkID,bkname,bkNo,bkemail,bkDescription,datecreated,serID FROM booking ORDER BY bkID”;
memoryDataset = LoadDataSetByQuery(Query8, “Data”, db);
try
{
//Get the DataColumnCollection in The Dataset
DataColumnCollection dcc2 = memoryDataset2.Tables[0].Columns;
//Count Row Result(s)
int rowCnt = memoryDataset2.Tables[0].Rows.Count;
rowCount.Text = rowCnt + ” Record(s) Found for your search”;
//Bind the DataTable from Dataset Loaded to the DataGridView
dataGridView1.DataSource = memoryDataset2.Tables[0];
}
catch (Exception e) { MessageBox.Show(e.Message); } // Error Trapper
}
// INITIALIZE AND CONFIGURE DATABASE
public void InitiateDatabaseConfiguration(String connect)
{
try
{
//Initialize SQLDB.
con = null;
con = new OleDbConnection(connect);
}
catch (Exception)
{
MessageBox.Show(“Unable to Connect to the Database.”, “”, MessageBoxButtons.OK);
}
}
public DataSet LoadDataSetByQuery(String Query, String DatasetName, String connection)
{
InitiateDatabaseConfiguration(connection);
DataSet dataSet = new DataSet();
try
{
//Initialize OleDbCommand to Execute SQL Query
command = new OleDbCommand(Query, con); // Execute ‘query’ from con instance using
// OleDbCommand
//Initialize OleDbDataAdapter to retrieve and Populate Dataset with the Data in the
// OleDbCommand
dataAdapter = new OleDbDataAdapter(command);
// Before Filling the Dataset from the dataAdapter
// open the OleDbConnection ‘con’ first
con.Open();
// Then Fill the dataset with the data in the dataAdapter
dataAdapter.Fill(dataSet, DatasetName);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, “Bambam Messenger”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Return the Dataset Object
return dataSet;
}
public void HandleFilter()
{
// Load The MemoryDataset while loading the Form
if (searchgud.Text != “”)
{
Query = “SELECT Item_ID,itemName,itemQty,perItem,profit,Category,datecreated FROM items WHERE itemName LIKE ‘%” + searchgud.Text + “%’ ORDER BY Item_ID”;
}
else
{
Query = “SELECT Item_ID,itemName,itemQty,perItem,profit,Category,datecreated FROM items WHERE active='” + act + “‘ ORDER BY Item_ID”;
}
//Reload MemoryDataset from The Query
memoryDataset = api.LoadDataSetByQuery(Query, “Data”, db);
try
{
//Count Row Result(s)
int rowCnt = memoryDataset.Tables[0].Rows.Count;
rowCount.Text = rowCnt + ” Record(s) Found for your search”;
//Bind the Dataset Loaded to the DataGridView
dataGridView1.DataSource = memoryDataset.Tables[0];
dataGridView1.Refresh();
}
catch (Exception e) { MessageBox.Show(e.Message); } // Just to Trap the Error
}
private void searchgud_TextChanged(object sender, EventArgs e)
{
if (searchgud.Text != “”)
{
HandleFilter();
}
}
Recommended: Filter Datagridview Using Textbook
Please contact me for further explanation. Thanks
