Binding GridView Dyanamically
A single public method to get records from the database, which can be used to bind a GridView.
The Concept
In any type of application, we perform certain tasks quite frequently. One of such task can be getting a set of records from the Database and binding it to a GridView. So, instead of writing code in every page for these types of operations, it will be a good practice to create a single public method which will take a query as parameter and return a set of rows and columns. Now, that set of rows and columns returned by the method can be used to bind to a GridView.
Code
using System.Data.SqlClient;
namespace DataAccessLayer
{
public class DataAccess
{
private SqlConnection con;
private SqlDataAdapter sqlda;
private DataTable dt;
public DataAccess()
{
//
// TODO: Add constructor logic here
//
}
public SqlConnection GetConnection()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString());
return con;
}
public DataTable GetRecords(string Query)
{
con = GetConnection();
con.Open();
sqlda = new SqlDataAdapter(Query, con);
dt = new DataTable();
sqlda.Fill(dt);
con.Close();
return dt;
}
}
}
Code Explanation
To use the above code, create a class file named "DataAcccess.cs" within App_Code folder of your project and copy the above code and paste it within the class. The class contains within a name space called "DataAccessLayer". So, you have to add the namespace at page level by writing using DataAccessLayer; statement.
The class has two methods. The first method GetConnection() just gets the connection string from the Web.Config file and return it as a string. The second method GetRecords(string Query) takes a Select query and returns a DataTable, which can be used to bind any Data Controls
Note: You don't have to add the System.Data.SqlClient namespace at page level. It will be added in the class level only.
How to use the code?
1) Create a table named "TblStudents" with the following structure:
CREATE TABLE TblStudents(
sid VARCHAR(10) PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
sadd VARCHAR(50),
smarks INT NOT NULL)
2) Store connection string in the connectionStrings section of the Web.config file, with the following statement:
<connectionStrings>
<add name="sqlcon" connectionString="Your Connection String" />
</connectionStrings>
3) Create an aspx page, drag a GridView control inside the page. On the Page_Load, write the following code:
using DataAccessLayer;
protected void Page_Load(object sender, EventArgs e)
{
string query;
DataTable dt;
DataAccess dal = new DataAccess();
query = "SELECT * FROM TblStudents";
dt = dal.GetRecords(query);
GridView1.DataSource = dt;
GridView1.DataMember = "TblStudents";
GridView1.DataBind();
}
Thanks & Regards
Paritosh Mohapatra