Bind GridView with Datatable

Add a GridView control, two lable controls, three textbox controls and button control in the page
Create datatable structure.

private DataTable CreateDataTable()
{
DataTable myDataTable = new DataTable();
DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "id";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "username";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "firstname";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "lastname";
myDataTable.Columns.Add(myDataColumn);
return myDataTable;
}


Insert data into datatable.

private void AddDataToTable(string username,string firstname,string lastname,DataTable myTable)
{
DataRow row;
row = myTable.NewRow();
row["id"] = Guid.NewGuid().ToString();
row["username"] = username;
row["firstname"] = firstname;
row["lastname"] = lastname;
myTable.Rows.Add(row);
}


//Add data to datatable which we have created

protected void btnAdd_Click(object sender, EventArgs e)
{
if (txtUserName.Text.Trim() == "")
{
this.lblTips.Text = "You must fill a username.";
return;
}
else
{
AddDataToTable(parameter);
this.txtFirstName.Text = "";
this.txtLastName.Text = "";
this.txtUserName.Text = "";
this.lblTips.Text = "";
}
}

// Page Load
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable myDt = new DataTable();
myDt = CreateDataTable();
this.GridView1.DataSource = myDt
this.GridView1.DataBind();
}
}

Update , Delete inside the grid view

//Take one Grid View and Bind all Column and take column inside templatefiels inside ItemTemplate and EditItemTemplate ALSO FOR Generate text box for updation
I have remove starting tag from the Html Tag because blog can't take Html field like Starting tag .....


asp:Label ID="lblId" runat="server" Visible="false" Text='%# Bind("Id") %>'>/asp:Label>



//Web.config file
add name="Master" connectionString ="Data Source=.;user id=; password=; database=Master; "/>


//aspx.cs

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Master"].ConnectionString);
SqlDataAdapter adp;
DataSet ds; SqlCommand cmd;


void fillgridview()
{
adp = new SqlDataAdapter("Select a.id,a.Name,a.Age,a.RollNo,b.Subject from student a join subject b on a.subjectid=b.subjectid", con);
ds = new DataSet();
adp.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

}
else { }
ds.Dispose();
adp = null;

}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillgridview();
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label lbnlid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblId");

con.Open();
cmd = new SqlCommand("delete from student where id= " + lbnlid.Text + " ", con);
cmd.ExecuteNonQuery();
con.Close();

GridView1.EditIndex = -1;
fillgridview();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
fillgridview();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
fillgridview();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName");
TextBox txtCity = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCity");
Label lbnlid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblId");

con.Open();
cmd = new SqlCommand("update student set Name= '"+txtName.Text +"' where id= "+lbnlid.Text+" ", con);
cmd.ExecuteNonQuery();
con.Close();


GridView1.EditIndex = -1;
fillgridview();
}

Disable Browser back Button in asp.net

//Make one .js file

function goNewWin()
{

window.open("default2.aspx",'TheNewpop','toolbar=1,location=1,directories=1,status=1,menubar=1,scrollbars=1,resizable=1');
self.close()

}
//Call that function and give the page name there which page you don't want browser back button....

Gridview sorting,paging

//Take one Grid View and Bind all the column through
//hdnSort is a Hidden field take one hidden field also.
asp:TemplateField
ItemTemplate and give the sort expression also and paging and sorting also true the property of GridView...

//Web.config file
add name="Master1" connectionString ="Data Source=.;user id=; password=; database=Master; "/>

//in aspx.cs
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Master1"].ConnectionString);
private void bindData(string sorting)
{
DataSet ds;
SqlDataAdapter da;
string sql = null;
ds = new DataSet();
da = new SqlDataAdapter();
try
{
con.Open();
}
catch (Exception ex)
{

}


sql = "Select student_id,last_name,first_name,Score from Student order by " + sorting;
da.SelectCommand = new SqlCommand(sql, con);
da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();

if (ds.Tables.Count == 0)
{

//lblMessage.Text = "No record found";
}
}
ds = null;
da = null;
try { con.Close(); }
catch (Exception ex) { }

}

//call Bind Method where you bind the Gridview perform all operation

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindData("student_id");

}
}

//for sorting

private string ConvertSortDirectiontoSql(SortDirection sort)
{
string newSortDirection = string.Empty;
switch (sort)
{
case SortDirection.Ascending:
newSortDirection = "Asc";
hdnSort.Value = "Asc";
break;
case SortDirection.Descending:
newSortDirection = "Desc";
hdnSort.Value = "Desc";
break;
}
return newSortDirection;

}



protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
//hdnSort is a Hidden field take one hidden field also.
if (hdnSort.Value.Length == 0 || hdnSort.Value == "Desc")
e.SortDirection = SortDirection.Ascending;
else
e.SortDirection = SortDirection.Descending;

string sorting = e.SortExpression + " " + ConvertSortDirectiontoSql(e.SortDirection);
if (hdnSort.Value.Length == 0)
hdnSort.Value = "Desc";
bindData(sorting);
}

//for paging

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
bindData("student_id desc");
}

Fill GridView

//Web.Config File Connection String
add name="Master1" connectionString ="Data Source=.;user id=; password=; database=Master; "/>


//aspx.cs
//Bind Function and call that function where you want..
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Master1"].ConnectionString);
private void bindData()
{
DataSet ds;
SqlDataAdapter da;
string sql = null;
ds = new DataSet();
da = new SqlDataAdapter();
try
{
con.Open();
}
catch (Exception ex)
{

}


sql = "Select student_id,last_name,first_name,Score from Student ;
da.SelectCommand = new SqlCommand(sql, con);
da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();

if (ds.Tables.Count == 0)
{

//lblMessage.Text = "No record found";
}
}
ds = null;
da = null;
try { con.Close(); }
catch (Exception ex) { }

}

Disable Mouse Right click over the .aspx page

//jscript.js file

var BM = 2; // button middle
var BR = 3; // button right


function mouseDown(e)
{
try { if (event.button==BM||event.button==BR) {return false;} }
catch (e) { if (e.which == BR) {return false;} }
}
document.oncontextmenu = function() { return false; }
document.onmousedown = mouseDown;

//page source code(Add .js file in page automatically handle the right click)
script src="JScript.js" type="text/javascript" language="javascript" />

What are cursors? Explain different types of cursors and Disadvantage of cursor?

Cursors allow row-by-row processing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 — 5000 hike Salary between 40000 and 55000 — 7000 hike Salary between 55000 and 65000 — 9000 hike. In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END