Write a program in ASP.NET - C# that use to connects to Database and fetch the data using data adapter and bind it in grid view using dataset.

 Posted by Nagasundar_Tn on 12/3/2012 | Category: ASP.NET Interview questions | Views: 2519 | Points: 40
Answer:

I faced this question in my two interviews. If interviewer wants some more detailed answer from us they ask us to write the core logic. This question is
the basic logic of establishing the connection to database and fetch the records from it and assigning it to grid or some other data controls.

(Pls include the necessary namespaces, I couldn't add due to maximum character exceeding)
public partial class Example : System.Web.UI.Page
{
SqlConnection sqlCon = null;
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindGridView();
}
}
public void BindGridView()
{
try
{
sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM tbl_EmployeePrim";
cmd.Connection = sqlCon;
sqlCon.Open();
cmd.ExecuteReader();
sqlCon.Close();
da.Fill(ds);
grdview.DataSource = ds;
grdview.DataBind();
}
catch (Exception exx)
{
throw exx;
}
finally
{
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
}
}
}


Here I have declared a function called BindGridView() which I called in !Ispostback condition. So that the data will not be fetched every time we refresh the page.In BindGridView() function I have written the connection string value from ConfigurationManager class. Now my sqlCon object contains ConnectionString value which we defined in WebConfig file.I have created Sqlcommand object, Dataadapter object and initialized with command object. I am assigning the select query in command object after createing dataset.

Assigning connection property of command with Sql connection object "sqlcon". Using Execute Reader method I am reading all the values of Employee table and fill the data to dataset object. Then I am binding the data set to gridview.

Important point to be noted is I am opening the connection only at the preivous line of ExecuteReader() and immediately closing the connection after ExecuteReader().This will improve the performance since establishing connection to the database is very costly operaion. One more thing is we should not directly use select query, insert or update query in the page. We need to use Stored procedure with input and output parameters to fetch or modify the database records. For example purpose I wrote Select query here which I did in my interview


Asked In: CTS | Alert Moderator 

Comments or Responses

Login to post response