Thursday, September 27, 2007
CACHING IN ASP.NET 2.0+SQL SERVER
CACHING IN ASP.NET 2.0+SQL SERVER

It’s so easy to implement sql caching in asp.net applications.
In first, you need to enable the cache notifications. To enable that you want to execute

aspnet_regsql -ed -E -d databasename (here you need to give your database name)

in command line. After executing this command in visual studio command prompt, a table SqlCacheTablesForChangeNotification in the database you given. The SqlCacheTablesForChangeNotification has the columns tableName, notificationCreated, and changeId to track the changes.

Also you need to enable notification for the individual table.

aspnet_regsql -et -E -d databasename -t tablename (here you need to give your table name)

This will generate a trigger for the table you have mentioned

CREATE TRIGGER dbo.[tblEmployee_AspNet_SqlCacheNotification_Trigger] ON [tblEmployee]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'tblEmployee'
END


[Sqlserver enterprise manager->select the a table in your database->all tasks->manage trigger->choose the trigger tblEmployee_AspNet_SqlCacheNotification_Trigger from the dropdown name]


Implementing sql caching in Asp.net 2.0+c#

Web.config
In we.config, you need to mention


















Namespace

using System.Web.Caching;


In the code behind


DataTable _dt = new DataTable ();
_dt = (DataTable) Cache ["cachename"]; //example Cache ["test"];
// you can provide any cache name, but remember to use the same in all the cases.

if (_dt == null)//_dt null only when first time and on a db change
{
_dt = DBAccess.GetData ();
// binding data you want from sql server db put your own code here

SqlCacheDependency _dep = new SqlCacheDependency("databasename", "tablename");
//here the databasename will be the name that you have given in the
// SqlCacheDependency in web.config and table name will be
//the table you have enabled the cache notification. sample given //below

Cache.Insert ("cachename", _dt, _dep);

}
dgData.DataSource = _dt; // dgdata is a sample grid view control
dgData.DataBind();















Hope this will help you
posted by Giri @ 12:30 AM   0 comments
Wednesday, September 26, 2007
DATALIST--INSERT, UPDATE AND DELETE operations















The DataList Web server control is a data-bound container control that allows to customize the display of data. We can perform the insert, edit, update, delete operations.
This is just similar to the grid view control in dot net 2005.

How to bind data to a datalist?

1) Drag a datalist to the web form
2) Using Item template you can add the controls to display the data
(Look at the html view of the sample page)
3) Set the data source of the datalist.


What is the logic behind the Edit, Update, Cancel and Delete?
1) Add link buttons Edit with command name “Edit”, Delete with Command Name
”Delete” in Item template.
2) Also set the Edit Item Template of the datalist to show the data in a textbox
(See the html view of the edit item template), also add the link buttons Update, Cancel with corresponding command name “Update and Cancel”.
3) Set the events edit command, update command, delete command and cancel
Command of datalist similar in the grid view.
4) Also put the code given in the sample cs code.



What is the logic behind the INSERT?

1) Inserting a blank row at the position 0 of the data table.
2) Just renaming the existing “edit button” to insert and hiding the delete button
3) In the dataList1_UpdateCommand of the datalist, just checking the row index.
If row index is 0, it will be an insert operation.


Note: By setting the datakeyfield of the datalist, we can easily identify which row is going to update or delete.



HTML VIEW OF THE PAGE



























Code Sample
#region Namespaces

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.Data.SqlClient;

#endregion

public partial class DataListTest : System.Web.UI.Page
{

#region Events

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindDataList();
}
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = -1;
bindDataList();
}
protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
{
SqlConnection _con = getConnection();
_con.Open();
SqlCommand _cmd = new SqlCommand("delete from tblEmployee where id=" + DataList1.DataKeys[e.Item.ItemIndex], _con);
_cmd.ExecuteNonQuery();
_con.Close();
DataList1.EditItemIndex = -1;
bindDataList();
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
SqlConnection _con = getConnection();
_con.Open();
SqlCommand _cmd = new SqlCommand();
if (e.Item.ItemIndex != 0)
_cmd = new SqlCommand("update tblEmployee set Name='" + ((TextBox)e.Item.FindControl("txtEmployeeName")).Text + "' where id=" + DataList1.DataKeys[e.Item.ItemIndex], _con);
else
_cmd = new SqlCommand("insert into tblEmployee (Name)values('" + ((TextBox)e.Item.FindControl("txtEmployeeName")).Text + "')", _con);
_cmd.ExecuteNonQuery();
_con.Close();
DataList1.EditItemIndex = -1;
bindDataList();


}
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = e.Item.ItemIndex;
bindDataList();
}

#endregion

#region Private Function

///
/// To bind the data
///
private void bindDataList()
{
SqlConnection _con = getConnection();
_con.Open();
SqlDataAdapter _da = new SqlDataAdapter("select * from tblEmployee", _con);
DataSet _ds = new DataSet();
_da.Fill(_ds);
_con.Close();
DataRow dr = _ds.Tables[0].NewRow();
_ds.Tables[0].Rows.InsertAt(dr, 0);
DataList1.DataSource = _ds;
DataList1.DataBind();
if (DataList1.EditItemIndex != 0)//just for the first row
{

((LinkButton)DataList1.Items[0].FindControl("lnkEdit")).Text = "Insert";
((Label)DataList1.Items[0].FindControl("lblFirstName")).Text = "click insert to add new employee";
((LinkButton)DataList1.Items[0].FindControl("lnkDelete")).Visible = false;
}

}
///
/// PURPOSE:To get the connection
///
///
private SqlConnection getConnection()
{
return new SqlConnection("server=.;uid=sa;pwd=;database=employeedb");
}

#endregion
}


Hope this will help you while dealing with data lists.
posted by Giri @ 5:31 AM   0 comments
About Me


Name: Giri
Home:
About Me:
See my complete profile

Previous Post
Archives