MSDE Installation & Configuration

web.config (connect to local/sqlexpress & aspnetdb.mdf)

DataControl and SqlDataSource Operation

Gridview ( select, Edit, Update , Cancel , NOT insert)

Update (Using SqlDataSource) Direct Access to Database

PLS : SqlDataSource> configure Datasource> Advanced > Check (Generate Insert, Update and Delete Statement)>Uncheck (Use optmistic concurrency)

1 Select SqlDataSource of GridView >Properties>Data>Update Query>Query Builder
* Add Table > Choose Columns to be updated ( Zip, State, City ) > New Value ( @State, @Zip,@city) >fileter (empid=@empid)

2.Set Field want to be update wtih all necessary parameters (type in) (Uncheck AutoGenerated Field)
eg. UPDATE Employees SET Name =@Name, Address =@Address, Mobilephone =@Mobilephone WHERE ID=@original_ID
3. Add Parameter> Name, Address Mobilephone, original_ID > parameter source=None > OK
4. Select SqlDataSource >Go Source <<Update Parameter >> > Type=" datatype " for all parameters. (optional)

PLS: GV's properties> Data>DataKeyNames=ID (*****)

Update (Using ObjectDataSource) Create Access Layer to DB

1: Open objds.xsd> Add query (employeeTableAdapter) > Update Query Type > user Query builder >@param....@Original_ID

Query Builder

UPDATE Employees SET Name = @Name, State = @State, Zip = @Zip, Extension = @Extension WHERE (EmployeeID = @Original_EmployeeID);

SELECT Name, State, Zip,Extension, EmployeeID FROM Employees WHERE (EmployeeID = @EmployeeID)

2: Gridview> Configure datasource> Choose Update tab > Choose created method above > next finish
3: In ObjectDataSource Properties> Data> Update Method (UpdateUmployee) > Update parameter ( list of param to be updated)

pls: If you dont want A filed to be updatible > edit column > choose A column > Behavior >ReadOnly=True

GridView Selection
1. GV smart task> Enable Selection> GV's properties> Data> DataKeyNames=ID

Display from gridview (select)
1. Use GridView
*Choose DataSource > SqlDataSource
*Select columns to be displayed > Where > column (empID) >operator (=) > Source (control) > ControlID ( Gridview)
*EmployeeID=@EmployeeID , Gridview.SelectedValue
*Done

Hyperlink
1. In GridView, > configure Datasource > Add New column> Choose Hyperlink Field
2. Fill in Hyperlink Text > Get Data from data field (EmployeeID)> URL format string
eg. details.aspx?EmployeeID={0}{0}

In detail.aspx
1. Use DetailViews> Configure Datasource > Parameter Source (query string) > QueryStringField (EmployeeID) > finish

Search (textbox & button)
1. Create select method on TableAdapter
* Open objds.xsd > Add Query> Query Builder > bulder quey with parameter (filter)
eg. SELECT Address, City, EmployeeID, Extension, Homephone, Name, State, Username, Zip
FROM Employees WHERE (State = @state)

2. New page, create textbox (txtstate) and button
* Drag GridView > ObjectDataSource >Select method (param) > Paramsource=control & controlID=txtstate>finish
* Customize GridView (edit, update , delete)

Back

FormView (Insert data via webform , Support Insert, using ObjDataSource & SqlDataSource)

1.Create Insert method on TableAdapter
* open dataset.xsd > right click EmployeeTableAdapter>Add query
* User SQL statement>Insert>Querybuilder & Next > Name function.

SqlDataSource>Insert Query> "INSERT INTO Contacts(Name, State, Major) VALUES (@Name, @State, @Major) "

Parameter> Name, State (ddlstate.Selectedvalue) .....so on

2.Create insertion form (Data>DataKeyNames> ID)
* Drag FormView > configure ObjectDatasource
* FormView properties> Behavior > DefaultMode=Insert
* FormView task > Edit Template > InsertItemTemplate
* Create Table (3 cols) > Validator control > HeaderText> modify Insert button>done

Convert field to DropdownList

1. Detailsview>configure datasource>Edit Field>choose State
2. click "Convert this field to template field" > OK > Edit Template > Display: InsertItemTemplate
3. Delete textbox control> drag dropdownlist > DataBinding> objectdatasource > method

pls: When an item convert to template field. There are 4 choice of template for each converted field
1. ItemTemplate
2. EditItemTemplate
3. InsertItemTemplate
4. HeaderTemplate
5. AlternatingTemplate

Sql Cache Dependency

cache (sql2000)
1. SqlDataSource properties> Cache> EnableCaching=true >CacheDuration=Infinite & 60
2. Aspnet_regsql -s servername -E -D databasename -ed //enable Database for SQL cache dependency
3. Aspnet_regsql -s servername -E -D databasename -t Employees -et //enable table for SQL Cache dependency
4. AspNet_SqlCacheTablesForchangeNotification table is added
5 web.config
<system.web>
<Caching>
<sqlCacheDependency enabled="true" pollTime="5000">
<add name="aspnetdb" connectionStringName="aspnetConnectionString" >
</sqlCacheDependency>
</Caching>
</system.web>

Back

Feedback page (one page)

1. Use Wizard [ | ]> properties>SideBarStyle > Width=100px
2. Wizard Properties >Add/Remove Step > Only 1 step > Step Type=Auto
3. right panel, use table > create form using control and validators
3. Click on finish (changable) button> swap to contact.aspx.cs, add following code

using System.Net.Mail

protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SendMail(txtemail.Text, txtComments.Text);
}

protected void SendMail(String from , String body)
{

MailMessage objmail=new MailMessage( from , "soonlim@vivianleow.com" , "feeback" , body);
SmtpClient mailclient=new SmtpClient();
mailclient.Host="smtp.gate.com" ;
mailclient.Send(objmail);
objmail.Dispose();
lblemail.Text = "Your email has been sent";
ClearBox();

}

protected void ClearBox()
{
txtfirstname.Text=" ";
txtlastname.Text=" ";
txtemail.Text = " ";
txtphone.Text=" ";
txtComments.Text=" ";
}

MultiView

1. Multiview> views (inside multiviews) > components (inside views)
2. Dropdownlist or radiobuttonlist> Check Enable AutoPostBackput > Edit Item> Add Value>Text: Value (search : 1,2,3) >following code default.aspx.cs

protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{
MultiView1.ActiveViewIndex = DropDownList2.SelectedIndex;
}

Back