NotesASPdotNet
Contents
=========
Tutorial
Structure
ASP.Net Configuration
Connecting Events
HTML Control Class
Web / Server Control Class
ASP postback - eg like AJAX
Dispose, Finalize, Finally, Close
ADO.Net - Accessing Data the Easy Way
ADO.Net - Example
ADO.Net - command parameters
ADO.Net - Data Binding
ADO.Net - Data Binding - DataList
ADO.Net - Styles and Templates summary
Windows Service Applications
VS Setup Projects
Understanding ADO.Net - from AppDev video tutorial
Using MySQL in Visual Studio
Configuring MySQL with ASP.Net
DropDownList
DataGrid - Sorting
GridView
GridView - Sorting
GridView - Selecting Rows, Clickable Rows
WebService in ASP.Net
ASP.Net Custom Control
Strings
How to Add Google Maps to your ASP.Net page
ASP.Net inline Tags <%...%>
ASP.Net calling Javascript client side
Tutorial
=========
1. Visual Studio 2005 -> Help -> Contents -> .Net Development
-> Web Applications -> ASP.Net Quickstart Tutorials
Structure
===========
System.Web.UI - Generic Page Class
|
V
custom page class (my *.cs file, eg HelloClass.cs)
|
V
custom aspx file (my *.aspx file, eg Hello.aspx)
|
V
Page object
System.Web.HttpApplication
|
V
class GlobalApp (global.cs)
|
V
global.asax -> contain event handling code, each application can
have one global.asax.
The classes are linked together like this:
VS2005: Global.asax no longer created automatically but can be added by:
Add new item -> Global Application Class.
Also the code behind global.asax.cs is no longer created, instead put
the code inline in global.asax
Hello.aspx
-----------
<%@ Page Language="CS" Inherits="HelloClass" Src="HelloClass.cs" %> -
if source is supplied
<%@ Page Language="CS" Inherits="HelloClass" %> - if dll is supplied
<html>
<body>
<form id="Form" runat="server">
<asp:Label id="lblTest" runat="server" />
</form>
....
HelloClass.cs
--------------
public class HelloClass : System.Web.UI.Page {
protected System.Web.UI.WebControls.Label lblTest;
private void Page_Load(){
lblTest.Text = "Hello World";
}
}
some basic event available to global.asax inherited from HttpApplication:
Application_OnStart
Application_OnEnd
Application_OnBeginRequest
Application_OnEndRequest
Application_OnError
Session_OnStart
Session_OnEnd
ASP.Net Configuration
======================
1. machine.config - one per server, located at:
Microsoft.Net/Framework/Version/Config/
2. web.config - one per application, as well as one in each virtual
sub-directory.
3. Structure and contents of web.config
+++++++++++++
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!-- custome settings - using your own keyword -->
<appSettings>
<add key="ConnectionString" value="Data Source=localhost;Initial
Catalog=Pubs;User ID=sa"/>
<add key="SelectSales" value="Select * FROM Sales"/>
</appSettings>
<system.web>
<httpRuntime />
<pages />
<compilation />
<customErrors />
<authentication />
<authorization />
<identity />
<trace />
<sessionState />
<httpHandlers />
<httpModules />
<globalization />
</system.web>
</configuration>
+++++++++++++
4. To make use of the special custom settings, eg.
++++++++++
using System;
using System.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Project
{
public partial class ShowCustomConfigSettings : System.Web.UI.Page
{
protected Label lblTest;
protected void Page_Load(object sender, EventArgs e)
{
lblTest.Text = "This app will connect with the connection
string <br><br>";
lblTest.Text +=
ConfigurationSettings.AppSettings["ConnectionString"];
lblTest.Text += "<br><br> and will execute the SQL statement <br>";
lblTest.Text += ConfigurationSettings.AppSettings["SelectSales"];
}
}
}
++++++++++
Connecting Events
===================
case: A button called id="Convert" on the aspx page. Functionality is
in the function called
"Convert_ServerClick"
On the aspx page, have the attribute in the <%@Page .... >
AutoEventWireup="false"
To connect the function "Convert_ServerClick" to the event:
1. protected override void OnInit(EventArgs e) {
InitializeComponent();
base.OnInit(e);
}
2. private void InitializeComponent() {
Convert.ServerClick += new EventHandler(Convert_ServerClick);
}
Note that EventHandler is a DELEGATE that connects a the function
"Convert_ServerClick"
The EventHandler delegate would have the same prototype of the
function it points to, ie.:
protected void Convert_ServerClick(Object sender, EventArgs e)
The ServerClick would be a type of the EventHandler delegate; perhaps
public event EventHandler ServerClick
perhaps defined under the Convert's class - HtmlInputButton
HTML Control Class
=======================
System.Object
System.Web.UI.Controls
HtmlControl ---> System.Web.UI.HtmlControls namespace
HtmlImage
HtmlInputControl
HtmlInputButton
HtmlInputCheckBox
HtmlInputFile
HtmlInputHidden
HtmlInputImage
HtmlInputRadioButton
HtmlInputText
HtmlContainerControl
HtmlAnchor
HtmlButton
HtmlForm
HtmlGenericControl
HtmlSelect
HtmlTable
HtmlTableCell
HtmlTableRow
HtmlTextArea
Web / Server Control Class
============================
System.Object
System.Web.UI.Controls
Repeater ---> System.Web.UI.WebControls
WebControl
AdRotator
Calendar
ValidationSummary
BaseDataList (abstract)
DataGrid
DataList
ListControl (abstract)
CheckBoxList
DropDownList
ListBox
RadioButtonList
Button
CheckBox
RadioButton
Hyperlink
Image
ImageButton
Label
BaseValidator (abstract)
CompareValidator
CustomValidator
RangeValidator
RegularExpressionValidator
RequiredFieldValidator
LinkButton
Panel
Table
TableCell
TableHeaderCell
TableRow
TextBox
ASP postback - eg like AJAX
============================
1. Web control events that can use POSTBACK, when AutoPostBack property is set
to true:
Events Web Control
======= ===========================
Click Button, ImageButton
TextChange TextBox
CheckChanged CheckBox, RadioButton
SelectedIndexChange DropDownList, ListBox, CheckBoxList, RadioButtonList
2. ASP.Net automatically adds these to the HTML page:
<input type="hidden" name="__EVENTTARGET" value=""/>
<input type="hidden" name="__EVENTARGUMENT" value=""/>
<script language="javascript">
<!--
function __doPostBack(eventTarget, eventArgument){
var theform = document.Form1;
theform.__EVENTTARGET.value = eventTarget;
theform.__EVENTARGUMENT.value = eventArgument;
theform.submit();
}
-->
3. Add event handlers to the InitializeComponent() method
i.e. <button>.<Event> += new EventHandler(<functionName>)
eg txt.TextChanged += new EventHandler(CtrlChanged)
4. Implement using the added function:
private void CtrlChanged(Object sender, EventArgs e) { }
ADO.Net - Accessing Data the Easy Way
=============================
To retrieve information
1. Create Connection, Command and DataReader objects
2. Use DataReader to retrieve info and display it on web
3. Close connection.
4. Send page to user.
To add or update information
1. Create new Connection and Command objects.
2. Execute the Command
ADO.Net - Example
=========
using System.Data;
using System.Data.SqlClient; // for MS SQL
using System.Data.OleDb; // for general connector
// Define the connection to Database
<x>={Sql, OleDb, ...}
<x>Connection myConn = new <x>Connection();
myConn.ConnectionString = "Provider=SQLOLEDB.1; Data
Source=localhost;" + " Initial Catalog=Pubs; User ID=root";
// the provider is need when <x>=OleDb, but not when <x>=Sql.
// Password is not passed here if using "Integrated Windows Authentication"
myConn.ConnectionString = "Provider=SQLOLEDB.1; Data
Source=localhost;" + " Initial Catalog=Pubs; Integrated
Security=SSPI";
// Making the connection
try{
myConn.Open();
lblInfo.Text="<b>Server Version: </b>" + myConn.ServerVersion;
lblInfo.Text+="<br><b> Connection is:</b> " + myConn.State.ToString();
} catch(Exception err) {
lblInfo.Text = "Error reading the database.";
lblInfo.Text += err.Message;
} finally {
myConn.Close();
lblInfo.Text += "<br> Now Connection is";
lblInfo.Text += myConn.State.ToString();
}
//Alternative connection
using{
myConn.Open();
lblInfo.Text="<b>Server Version: </b>" + myConn.ServerVersion;
lblInfo.Text+="<br><b> Connection is:</b> " + myConn.State.ToString();
}
lblInfo.Text += "<br> Now Connection is";
lblInfo.Text += myConn.State.ToString();
// Creating the SQL statement and assigning to Command
<x>Command myCmd = new <x>Command();
myCmd.Connection = myConn;
myCmd.CommandText = "Select * from Authors";
//Alternative
<x>Command myCmd = new <x>Command("Select * from Authors", myConn);
// Using Command with DataReader
<x>DataReader myReader;
myReader = myCmd.ExecuteReader();
// Reading objects
myReader.Read(); // reads an object at a time sequentially
// Cleanup
myReader.Close();
myConn.Close();
ADO.Net - command parameters
==============================
Instead of using this:
insertSQL "Insert INTO authors (au_id) VALUES ('txtID.Text')"
.... where txtID is a from the GUI on the webpage.
This method can be hacked via SQL Injection. To solve this, Command
Parameters are used:
insertSQL "Insert INTO authors (au_id) VALUES (?)"
cmd.Parameters.Add("?", txtID.Text);
where cmd is a OleDBCommand, and note that "?" is for OleDB only.
MySQL or MSSQL may have different symbols or notation for their
command parameters.
For MSSQL,
// don't ever do this!
// SqlCommand cmd = new SqlCommand(
// "select * from Customers where city = '" + inputCity + "'";
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
// 3. add new parameter to command object
cmd.Parameters.Add(param);
ADO.Net - Data Binding
=======================
Simple Data Binding:
1) in the aspx page, have something like:
<asp:Label id="lbLabel" runat="server">
There were <%# TransactionCount %>
</asp:Label>
2) In the code behind, have something like:
private void Page_Load(...){
TransactionCount = 10;
this.DataBind();
Alternative to data bind above is to do this in-code.
3) private void Page_Load(...){
TransactionCount = 10;
lblDynamic.Text = "There were " + TransactionCount.ToString();
Multiple Binding:
1. Form an array list from whereever:
eg. ArrayList fruit = new ArrayList();
fruit.Add("Kiwi");
or from database
2. Define binding for list controls; eg
MyListBox.DataSource = fruit;
MyDropDownListBox.DataSource = fruit;
MyHTMLSelect.DataSource = fruit;
MyCheckBoxList.DataSource = fruit;
MyRadioButtonList.DataSource = fruit;
3. Activate the binding, eg.
this.DataBind(); // this refers to the current page.
Multiple Binding with hashtables:
1. Create hashtable with key,val pair
Hashtable fruit = new Hashtable();
fruit.Add(1, "Kiwi");
fruit.Add(2, "Pear");
2. Binding specific fields
MyListBox.DataTextField = "Value" -> put the Value of hastable
into the Text of the Control
MyListBox.DataValueField = "Key" -> put the Key of hastable into
the value of the Control
3. Define databinding and activate
MyListBox.DataSource = fruit;
4. The result is the following HTML will be rendered:
<select name="MyListBox" id="MyListBox">
<option value="1">Kiwi</option>
Databinding with databases:
1. Some possible namespaces (when using OleDB):
using System.Data;
using System.Data.OleDb;
2. Making data using dataset example:
DataSet ds = new DataSet();
ds.Tables.Add("Users");
ds.Tables["Users"].Columns.Add("Name");
ds.Tables["Users"].Columns.Add("Country");
DataRow dr = ds.Tables["Users"].NewRow();
dr["Name"] = "John";
dr["Country"] = "Uganda";
ds.Tables["Users"].Rows.Add(rd);
3. Bind table to data source:
lstUser.DataSource = ds.Tables["Users"];
lstUser.DataTextField = "Name";
or Bind whole DataSet to data source:
lstUser.DataSource = ds;
lstUser.DataMember = "Users";
lstUser.DataTextField = "Name";
4. Activate the Binding:
this.DataBind();
or to bind just the list box
lstItems.DataBind()
ADO.Net - Data Binding - DataList
=====================================
1. Code Behind:
public class BasicAuthorList: Page
{
protected DataList listAuthor;
// (Initialisation code omitted)
private string connectString ="Provider ......."
private void Page_Load(Object sender, EventArgs e)
{
string SQL = "SELECT * FROM AUTHORS";
OleDbConnection con = new OleDbConnection (connectString);
OleDbCommand cmd = new OleDbCommand(SQL, con);
OleDbAdapter adapt = new OleDbAdapter(cmd);
DataSet pubs = new DataSet();
con.Open();
adapter.Fill(pubs, "Authors");
con.Close();
//Bind the DataSet and activate the data bindings for the page
listAuthor.DataSource = pubs.Tables["Authors"];
this.DataBind();
2. ASPX DataList template
<asp:DataList id=listAuthor runat="server">
<ItemTemplate>
<font face="Verdana" size="2">
<b><%# DataBinder.Eval(Container.DataItem, "au_fname") %>
<%# DataBinder.Eval(Container.DataItem, "au_lname") %></b></font>
<br> Address: <%# DataBinder.Eval(Container.DataItem, "address") %>
<br> City: <%# DataBinder.Eval(Container.DataItem, "city") %>
</font>
</ItemTemplate>
</asp:DataList>
3. Formating Values - for DataBinder
eg DataBinder.Eval(Container.DataItem, "Price", "{0:C}")
{0:C} Currency
{0:E} Scientific (Exponential)
{0:P} Percentage
{0:F?} Fixed Decimal
see MSDN Help for more.
4. Adding different styles to Templates
a) Manual option - hand coding on aspx file
<HeaderTemplate>
<h2> title </h2>
</HeaderTemplate>
<ItemTemplate>
(Item style1)
</ItemTemplate>
<AlternatingItemTemplate>
(Item style2)
</AlternatingItemTemplate>
<SeparatorTemplate>
<h2> title </h2>
</SeparatorTemplate>
<FooterTemplate>
<h2> title </h2>
</FooterTemplate>
b) Using VS.Net right-click DataList properties
c) AutoFormat link from the DataList properties
d) To make columns and rows, check out the RepeatDirection and
RepeatColumns properties.
ADO.Net - Data Binding - DataGrid
=====================================
1) DataList may have columns and rows, but DataGrid has columns such
that the columns
correspond to certain fields of the data item; eg phone - city - zip
code as different
columns.
2) Features include: automatic paging, sorting, editing, selecting.
3) Examples that show the Columns tag and the use of styles:
<asp: DataGrid id=gridauthor runat="server" AutoGenerateColumns="false"
BorderColor="#..." BorderStyle="None" CellSpacing="2"
BackColor="#..." CellPadding="3" BorderWidth="1px">
<FooterStyle ForeColor="#..." BackColor="#..." ></FooterStyle>
<HeaderStyle ForeColor="#..." BackColor="#..." ></HeaderStyle>
<ItemStyle ForeColor="#..." BackColor="#..." ></ItemStyle>
<Columns>
<asp:TemplateColumn HeaderText="AuthorName">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "au_fname") %>
<%# DataBinder.Eval(Container.DataItem, "au_lname") %>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
4) DataGrid has special columns including:
TemplateColumn - contents here include <ItemTemplate> and other stuff
that can go into
DataList.
BoundColumn - a particular field from database
ButtonColumn
EditCommandColumn
HyperlinkColumn
All these columns should exist within <Columns> ... </Columns>
ADO.Net - Data Binding - Repeater
=====================================
Repeater has not much formatting by itself. It depends on formatting provided
in the html. Example:
<asp:Repeater id=repeatauthor runat="server">
<HeaderTemplate> <table> </HeaderStyle>
<ItemTemplate><tr>
<%# DataBinder.Eval(Container.DataItem, "au_fname") %>
</ttr></ItemTemplate>
<FooterTemplate> </table> </FooterStyle>
</asp:DataGrid>
ADO.Net - Styles and Templates summary
=======================================
Styles
DataList DataGrid Repeater
-----------------------------------------------------------
AlternatingItemStyle AlternatingItemStyle None
EditItemStyle EditItemStyle
FooterStyle FooterStyle
HeaderStyle HeaderStyle
ItemStyle ItemStyle
SelectedItemStyle SelectedItemStyle
SeparatorStyle PagerStyle
Template
DataList DataGrid* Repeater
-----------------------------------------------------------
AlternatingItemTemplate FooterTemplate AlternatingItemTemplate
EditItemTemplate HeaderTemplate FooterTemplate
FooterTemplate ItemTemplate ItemTemplate
HeaderTemplate EditItemTemplate SeparatorTemplate
ItemTemplate
SelectedItemTemplate
SeparatorTemplate
* only supported by Template column
1) Do not bind the grid in the Page.Load event handler, otherwise info is
lost about which button user clicked or item selected.
private void Page_Load......
if(!this.PostBack) {
DataSet ds = GetDataSet();
BindGrid(ds);
.........
}
private DataSet GetDataSet(){
string SQL = "...."
OldDbConnection ...
OldDbCommand cmd =
OleDbAdapter = ...
DataSet dsPubs = ...
adapter.Fill(dsPubs, "Authors");
con.Close();
return dsPubs;
}
private void BindGrid(DataSet ds){
gridAuthor.DataSource = ds.Table["Authors"];
this.DataBind();
}
Windows Service Applications
=============================
1. Do not have any in/output to screen or Windows, or it will crash the app.
2. Error messages should be logged in the Windows event log rather
than raised in the user interface.
3. To control, use Services Control Manager, or Server Explorer, or
the ServiceController class (use this
to control the service from another app).
4. States of the service include: start, stop, pause, resume
Corresponding methods in code: OnStart, OnStop, OnPause,
OnContinue, OnShutdown, OnCustomCommand, OnPowerEvent.
5. Two types of services: Win32OwnProcess, Win32ShareProcess.
6. VisualStudio has installation components that can install
resources, register the service and let Services Controller
Manager know. Add these installers to the app and also create a
separate setup project.
7. Must inherit from System.ServiceProcess.ServiceBase class. The
project must contain installation components.
To create the Service:
- set ServiceName property
- create installers
- override methods for OnStart and OnStop.
8. System.ServiceProcess.ServiceProcessInstaller and
System.ServiceProcess.ServiceInstaller
—You use these classes to install and uninstall your service.
9. Execution Process:
Add Installers in project
Build
Install: installutil yourproject.exe / Uninstall: installutil
/u yourproject.exe
Start the Service
10. Debugging:
Install service
Start service
In VS, Debug->Process
Click Show System Processes
Attach process
add any break points
use Services Control Manager to control the service.
11. To add Installers:
-In Solution Explorer, go to Design View of the service.
-Click on background of designer and AddInstaller.
-A new class, ProjectInstaller, and two installation components,
ServiceProcessInstaller and ServiceInstaller,
are added to your project, and property values for the service are
copied to the components.
-Click the ServiceInstaller component and verify that the value
of the ServiceName property is set to the
same value as the ServiceName property on the service itself.
-To determine how your service will be started, click the
ServiceInstaller component and set the StartType property
to the appropriate value. { Manual, Automatic, Disabled }
- To determine the security context in which your service will
run, click the ServiceProcessInstaller
component and set the appropriate property values. For more
information, see How to: Specify the Security Context for Services.
- Override any methods for which you need to perform custom
processing. For more information, see How to: Override Default Methods
on Installation Components.
- Perform steps 1 through 7 for each additional service in your project.
Note: For each additional service in your project, you must
add an additional ServiceInstaller component
to the project's ProjectInstaller class. The ServiceProcessInstaller
component added in step
three works with all of the individual service installers in the project.
- Create your setup project and custom action to deploy and
install your service.
For more information on setup projects, see Setup Projects.
- After you add installers to your application, the next step is to
create a setup project that will install
the compiled project files and run the installers needed to install
your service. To create a complete
setup project, you must add the service project's output to the
setup project and
then add a custom action to have your service installed. For more
information on setup projects, see Setup Projects. For more
information on custom actions, see Walkthrough: Creating a Custom
Action.
(see VS Setup Projects for more details)
VS Setup Projects
=====================
1. Design, code, build your project in VS Studio say project ProjA in
solution SolnA.
2. Create the setup project SetupA, in SolnA: Add Project -> New
Project -> Other Project Types -> Setup and Deployment.
3. Right click setupA -> Install -> Add Project Output. Select ProjA
and choose "Primary Output".
4. Right click setupA -> View -> Custom Actions.
5. Right click on Custom Actions -> Add Custom Option. Select
Application Folder -> Primary output from ProjA.
6. Build.
7. The msi and exe file will be created.
Understanding ADO.Net - from AppDev video tutorial
====================================================
Object Model:
Connected Object (Specific to DB engine or provider)
-> .Net Data Provider (incl. MySql.Data.MySqlClient or
SystemData.ODBC|OleDB|SqlClient|OracleClient)
-> Connection: Transaction
-> Command: Parameters (eg to execute some command)
-> DataReader:
-> DataAdapter:
-> Fills DataTable: SelectComm
-> Updates Database: InsertComm, UpdateComm, DeleteComm
DisConnected Object (can work with data in a disconnected state)
-> DataSet (analogous to Database) <-> XML
-> DataTableCollection
-> DataTable:
-> DataRowCollection - storing the data
-> DataColumnCollection - names and properties of columns
-> ConstraintCollection - eg unique key or foreign key constraints.
-> DataRelationCollection
-> DataView
-> Row Filter
-> Sort
-> Extended support for Data Binnding
-> DataTableReader
-> A DataReader for DataTables and DataSets
-> Fast forward only reading
-> resilient to adding or deleting rows while reading
... to be continued at Data Caching Object
Using MySQL in Visual Studio
=============================
1. View -> Server Explorer
2. Look for database server and connect
Configuring MySQL with ASP.Net
===============================
It is easy to use MySQL with ASP.Net. becasue by default the local
ASP.Net enables you with FULL TRUST permission.
This section shows how to configure ASP.Net to work with MySQL where
the ASP.Net is stored in a ISP's server,
which usually uses MEDIUM TRUST, not FULL TRUST.
To the SQL administrator:
1. There are two options, one is to modify the web_mediumtrust.config;
the other option is to copy and rename the config file.
The web_mediumtrust.config is located typically in
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\
Add the SOCKPermission to this file.
<SecurityClass .............
<SecurityClass Name="SocketPermission"
Description="System.Net.SocketPermission, System, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
...
...
<IPermission
class="SocketPermission"
version="1"
Unrestricted="true"/>
2. If we choose the second option in step 1. to make a new file and
called web_mediumMySQLtrust.config, then we need
to register this in the
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\web.config with
the following line:
<trustLevel name="Medium" policyFile="web_mediumtrust.config" />
.... already there
<trustLevel name="MediumMySQL"
policyFile="web_mediumMySQLtrust.config" /> .... add this new one
Also in the same file, add this within the <system.web>
<trust level="Medium" originUrl="" />
or
<trust level="MediumMySQL" originUrl="" />
To the Developer:
1. In the web application, in the web.config file, insert the line
about <trust> just before </system.web>
eg.
<system.web>
......
......
<trust level="Medium" originUrl="" />
</system.web>
DropDownList
============
To add first element, add a ListItem and the property
AppendDataBoundItems set to true.
Eg.
<asp:dropdownlist id="suburbDropList" AppendDataBoundItems="true"
AutoPostBack="True" runat="server" >
<asp:ListItem Text="(Select a Suburb)" Value="" />
</asp:dropdownlist>
DataGrid - Sorting
===================
To allow for sorting on the DataGrid, the following are needed.
1. Right click on the GUI design of the DataGrid and set
"AllowSorting" to "true".
2. Add the following in the aspx code file in the DataGrid item:
OnPageIndexChanging="PageChangeFunction" OnSorting="SortingFunction"
3. Set property AutoGenerateColumns to true,
i.e.TestMenu.AutoGenerateColumns = true;
GridView
==========
Namespace: System.Web.UI.WebControls
Assembly: System.Web (in system.web.dll)
Version: since .Net 2.0
Summary: Displays the values of a data source in a table where each
column represents a field and each row represents a record. The
GridView control allows you to select, sort, and edit these items.
Event: OnRowDataBound()
- can be used to set the width of the grid view once data is bound.
- Example:
<code>
protected void gvShop_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType != DataControlRowType.DataRow) return;
GridView gv = (GridView)sender;
//e.Row.Cells[0].Width = 100; // Free Size
e.Row.Cells[1].Width = 100; //name
e.Row.Cells[2].Width = 300; //address
</code>
GridView - Sorting
===================
This describes how to implement the sorting event of a GridView object.
1. Sorting must be allowed in the GridView object. On the aspx page,
the GridView object must look something like:
<code>
<asp:GridView ID="gvShop" runat="server" AllowSorting="True"
OnRowDataBound="gvShop_RowDataBound"
OnPageIndexChanging="gvShop_PageIndexChanging"
OnSorting="gvShop_Sorting" >
</code>
The important properties are AllowSorting set to True and OnSorting
specified with the name of the method that performs the sorting. By
Default, the event name is given by the object ID (gvShop) combined
with the event (Sorting) hence the name gvShop_Sorting.
2. Before implementing the GridView sorting event, let us define two
strings at the start of the class.
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
These specific strings ASC and DESC are required in the sorting
expressions later.
3. Define the property called gvSortDirection - to hold the state of
whether it is currently sorted in ascending or descending fashion.
<code>
public SortDirection gvSortDirection {
get {
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}
</code>
4. Finally, the sorting event is implemented as below:
<code>
protected void gvShop_Sorting(object sender, GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;
string strSort = null;
if (gvSortDirection == SortDirection.Ascending)
{
gvSortDirection = SortDirection.Descending;
strSort = sortExpression + " " + DESCENDING;
}
else
{
gvSortDirection = SortDirection.Ascending;
strSort = sortExpression + " " + ASCENDING;
}
DataTable dc1 = (DataTable)gvShop.DataSource;
dc1.DefaultView.Sort = strSort;
gvShop.DataBind();
}
</code>
A few things to note:
- when the user press on the header of the column, the data will be
sorted in ascending, then descending way alternately. This is possible
by the if loop which changes the direction of the sorting each time it
is sorted.
- the strSort is made up of the name of the column to be sorted, and
whether it is ascending or descending.
- The most important step is to assign the DefaultView.Sort = strSort.
Note that in this case, the GridView was originally sourced from a
DataTable, hence in this example, the DataSource is casted into a
DataTable dc1 object. Finally the GridView need to be binded.
GridView - Selecting Rows, Clickable Rows
==========================================
In ASP.Net, a simple GridView presents a table with rows of data, in
different columns. All the data are usually obtained from some
DataSource which may come from a database. This section shows how to
add an Extra column where all the entries in this column are clickable
and is linked to the row on which it is clicked. An example of a
Gridview with the Extra column as the first column:
clickable Col 1 Col 2
select John Smith
select Jane Doe
select Alice Wong
select Bob Chan
In the example, the first row are column names. Each of the "select"
words are clickable and can be used to launch an action, and identify
which row was clicked. The implement of this involves the following
steps:
1. On the aspx page, the code looks like:
<code>
<asp:GridView ID="gvShop" runat="server" AllowSorting="True"
OnRowDataBound="gvShop_RowDataBound"
OnPageIndexChanging="gvShop_PageIndexChanging"
OnSorting="gvShop_Sorting" >
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True"
ForeColor="Navy" />
<Columns>
<asp:BoundField ReadOnly="True" >
<ItemStyle Width="0px" />
</asp:BoundField>
<asp:TemplateField HeaderText="clickable">
<ItemTemplate>
<asp:LinkButton ID="lnk_ShopName" runat="server" Text="select"
Tooltip="some tip"
CommandArgument='<%# Eval("Col 1") %>'
style="background-image: url(images/icon.gif);"
OnClick="lnk_ShopName_Click">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</code>
The important things to note are the following:
- The <asp:TemplateField> tag - see the word "clickable" as the name
of the column. The TemplateField can be generated from the GUI in the
Design mode of the aspx page, by right clicking the GridView rectangle
and right clicking the top right corner and select "edit columns".
- The <ItemTemplate> tag - this contain a LinkButton object whose Text
is "select". So the words "select" is displayed in the GridView
column.
- The CommandArgument is very important, the argument inside Eval,
must be exactly the same name as one of the column names. This means
that when a row is clicked, the CommandArgument will take the value at
that row of the specified column. In this example, Eval("Col 1") means
either John, Jane, Alice or Bob will be taken along the event.
- Instead of the words "select", an image or icon can be used, and
this is done by the background-image attribute in the LinkButton.
- For the event to do something it has to be linked. Here the
LinkButton is connected to the function called "lnk_ShopName" which is
called whenever an event occurs.
2. Implementing the event or action on the aspx.cs page. In this
example, the event triggers the method called "lnk_ShopName" to be
executed. The method can be something like:
<code>
protected void lnk_ShopName_Click(object sender, EventArgs e)
{
System.Web.UI.WebControls.LinkButton linkBut;
linkBut = (LinkButton)sender;
string sName = linkBut.CommandArgument;
// do something with sName
}
</code>
The information about which row is clicked can be extracted from the
sender object.
Since the Control was a LinkButton, the sender is cast into the LinkButton.
The CommandArgument will contain the data at the row that was clicked,
at the column specified in the Eval argument on the aspx page. In this
case, Col 1 was chosen as the column.
WebService in ASP.Net
======================
*.asmx - web service file
WSDL - Web Service Description Language, describes the methods
and parameters in a web service.
The elements of WSDL are: definitions, types, message,
portType, binding, service.
SOAP - way to encode info to pass to web service
HTTP - protocol through which SOAP messages are sent
DISCO - discovery standard that contains links to web services
UDDI - business registry that lists all info about companies
and web services they provide, with
URLs for their WSDL contracts and DISCO.
Data types: Basic / primitives, Enumerations, DataSets, XmlNode,
Custom Objects, Arrays
Testing the web service
------------------------
http://<domain>/<webservice>.asmx -> to view the web service directly
http://<domain>/<webservice>.asmx?WSDL -> to view the WSDL.
http://<domain>/<webservice>.asmx?op=<methodName> -> to access
the web service method directly
The use of web services in C# ASP.Net is illustrated with the example below:
----------------------------------
1.<%@ WebService Language="C#" Class="Util" %>
2. using System.Web.Services;
3. using System;
4. [WebService(Namespace="http://www.contoso.com/")]
5. public class Util: WebService
6. {
7. [ WebMethod]
8. public long Multiply(int a, int b)
9. {
10. return a * b;
11. }
12. }
----------------------------------
or
----------------------------------
in file: Util.asmx
1.«%@ WebService Language="C#" CodeBehind="Util.asmx.cs" Class="Util" %»
in file: Util.asmx.cs
2. using System.Web.Services;
3. using System;
4. [WebService(Namespace="http://www.contoso.com/", Description="test utility")]
5. public class Util: WebService
6. {
7. [WebMethod(Description=" test a method "]
8. public long Multiply(int a, int b)
9. {
10. return a * b;
11. }
12. }
----------------------------------
1. Declare the webservice using «%@ WebService....
Option 1: Place the above code inside *.asmx file with the C# code.
Option 2: Put only the <%@ WebService in the asmx class and put the C#
code somewhere else.
Then the declaration would be:
<%@ WebService Language="C#" Class="MyName.MyWebService,MyAssembly" %>
... where MyAssembly is the name of the assembly.
2. Deriving the WebService class - this is optional. Lines 2,5.
3. Apply WebService attribute and specify domain to avoid confusion
with other webservices.
see line 4.
4. Define the web service method using [ WebMethod ] - see line 7.
5. State Management for Web Services is achived by deriving from the
WebService class. It provides
access to ASP.NET objects such as:
Session - client specific state information
Application - used to store data globally and available to all clients
Server - utility functions
User - info about current client including authentication
Context - provides access to Request, Response and Cache
7. Storing and access state in a client session:
Declare: [ WebMethod(EnableSession=true) ]
Store: Session["MyServiceUsage"] = 1;
Access: Session["MyServiceUsage"] = ((int) Session["MyServiceUsage"]) + 1;
8. Storing and access state in Web application hosting the Web service:
Declare: [ WebMethod ]
Store: Application["appMyServiceUsage"] = 1;
Access: Application["appMyServiceUsage"] = ((int)
Application["appMyServiceUsage"]) + 1;
ASP.Net Custom Control
=======================
The following describes Custom Control for web applications (*.aspx).
The Custom Control for console
applications are similar, but not exactly.
1. To add a user control, right click on the web application project
in the Solution Explorer. Click
New Item, then choose Web User Control, and name it WUC.
2. In Visual Studio, this will auto create 3 files:
- WUC.ascx - with Design and Code view
- WUC.ascx.cs - the code to be edited or added.
- WUC.ascx.designer.cs
3. Put the collection of controls in your custom control withing the
WUC.ascx file. For example,
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="WUC.ascx.cs"
Inherits="ParentWebApp.WUC" %>
<asp:Label ID="lblFooter" runat="server" Height="87px" Text="Label"
Width="214px"></asp:Label>
4. The Label control above can be also created by using the WUC.ascx
design view. Just
drag the Label button from the toolbox onto the form.
5. In the code behind file WUC.ascx.cs, make the WUC class inherit
from System.Web.UI.UserControl. And
in the Page_Load, enter code for what the controls need to do. For example,
public partial class WebUserControlFooter : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
lblFooter.Text = "This page was served at ";
lblFooter.Text += DateTime.Now.ToString();
}
}
6. To use this custom control on any aspx web page:
a) Register the custom control on those aspx web pages, eg.
<%@ Register TagPrefix="cr" TagName="WUC" Src="~/WUC.ascx" %>
b) Add the custom control, eg.
<cr:WUC id=Footer1 runat="server" />
Note that the TagPrefix name is the alias for the custom control to be
used in the aspx web page.
Strings
========
\r\n - Carriage Return, then Newline - this is neeeded for
example in TextBox component.
How to Add Google Maps to your ASP.Net page
=============================================
1. In Visual Studio, go to the aspx page where you want to include the
Google Map.
For example, if the aspx page is called test.aspx, then in the
Solution Explorer there is at least 3 code files:
test.aspx
- test.aspx.cs
- test.aspx.designer.cs
For this example, choose the test.aspx. In the main editor panel, look
at the grey bar below the panel and ensure it is "Design" and not
"Source".
2. Add HTML components
Open the Toolbox. Either by View -> Toolbox or go to the right panel
and select the Toolbox tab.
Then in the Toolbox, go to the HTML section of components. Do not
remain at the Standard section of components.
Drag and drop the following onto the page:
- Input(Button) - for activating / drawing the map
- Input(Hidden) - for lattitude information
- Input(Hidden) - for longitude information
- Input(Hidden) - for other information to be printed on the map
- Div - actual area where map is displayed.
Click on each one of the components, and name them in the "id" field
with these names:
butMap,
hidLatitude,
hidLongitude,
hidDetails,
map
3. Make HTML components into ASP.NET components.
From the GUI editor page, click on the "Source" tab to edit the html
source code.
Go to the HTML section where the above 5 components are defined and
add the following for each 5 components: runat="server".
The final HTML code for the 5 components should look like:
<code>
Map Display
<input id="butMap" runat="server" type="button" value="Draw
Map" onclick="drawGmap()" />
<input id="hidLatitude" runat="server" type="hidden" />
<input id="hidLongitude" runat="server" type="hidden" />
<input id="hidShopDetails" runat="server" type="hidden" />
<div id="map" runat="server" style="width: 400px; height:
400px" visible="true"> </div>
</code>
At this stage just make sure the attributes of id, type and runat are correct.
By adding the runat="server" to the HTML code, then compile the code
in Visual Studio, this would make the HTML elements recognizable by
ASP.Net.
To check this, go to the test.aspx.designer.cs file and check the
following lines exist after compilation with runat="server":
protected global::System.Web.UI.HtmlControls.HtmlInputButton butMap;
protected global::System.Web.UI.HtmlControls.HtmlInputHidden
hidLatitude;
protected global::System.Web.UI.HtmlControls.HtmlInputHidden
hidLongitude;
protected global::System.Web.UI.HtmlControls.HtmlInputHidden
hidShopDetails;
protected global::System.Web.UI.HtmlControls.HtmlGenericControl map;
4. Ensure that somewhere in the test.aspx.cs code file, the html input
fields on the front end are filled with values. For example:
hidDetails.Value = "some details";
hidLatitude.Value = "20.312";
hidLongitude.Value = "132.12";
5. In between the header and the body section of the html code:
<script type="text/javascript"
src="http://www.google.com/jsapi?key=ABQIAAAAXg-1e7LbaB0NNB4KY0h0-BQWFt-7OBZKMPMlsVYvuUdvLO58RBSi6mMmr-RK5Y90BBnpyvt7UFoIfA"></script>
<script type="text/javascript"> google.load("maps", "2.x"); </script>
<script type="text/javascript">
function drawGmap() {
var dLat = document.getElementById("hidLatitude").value;
var dLong = document.getElementById("hidLongitude").value;
var sAddress = document.getElementById("hidDetails").value;
var map = new google.maps.Map2(document.getElementById("map"));
map.setUIToDefault();
var point = new GLatLng(dLat, dLong);
map.addOverlay(new GMarker(point));
map.setCenter(new google.maps.LatLng(dLat, dLong), 15);
var shtml = "100 <b>He</b>llo";
map.openInfoWindowHtml(map.getCenter(), sAddress);
//map.openInfoWindow(map.getCenter(),
document.createTextNode(hidText.value));
}
</script>
<body onunload="GUnload()">
In addition, ensure that the button or some other component call the
function drawGmap(). For example, from the above HTML Button :
<input id="butMap" runat="server" type="button" value="Draw
Map" onclick="drawGmap()" />
ASP.Net inline Tags <%...%>
==============================
These are tags that may be used on the aspx / html page, that enable
interaction with server side functions. The tags and a brief
explanation is given below:
<% ... %>
Allows C# code to be run within the tags.
Example:
<code>
<% string s = "test"; %>
</code>
<%= ... %>
Extracts values from server side objects.
Example:
<code>
<%= DateTime.Now.ToShortDateString() %>
</code>
<%# .. %>
Mostly used for binding expressions like Eval or Bind
Example: usage in Gridview in the example below where the Eval is used
to extract the value in the DB under the "name" column or field, for
each row in the Gridview.
<code>
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:LinkButton ID="lnkname" runat="server"
Text='<%#Eval("Name") %>'
PostBackUrl='<%#"~/Details.aspx?ID="+Eval("ID") %>'>
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</code>
<%$ ... %>
This is also called ASP.Net Expression and is used to extract
AppSettings, ConnectionsStrings or Resources
Example: using as connection string in DB
<code>
<asp:SqlDataSource ID="party" runat="server" ConnectionString="<%$
ConnectionStrings:myString %>" SelectCommand="SELECT * FROM [table]"
/>
</code>
<%@ ... %>
Specifying ASP.Net directives at the top of the aspx page.
Example:
<code>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Test.aspx.cs"
Inherits="_parent" Title="Test Page" %>
</code>
<%-- ... --%>
Comments are embedded here. These comments can be seen on the aspx
page, but after compilation into html page, the client would not be
able to see these parts.
Example:
<code>
<%-- Here are some comments --%>
</code>
ASP.Net calling Javascript client side
========================================
This section shows how to call a Javascript function defined on the
aspx page, from the C# code-behind aspx.cs code.
1. The javascript is defined in the aspx page between the head and
body tags, like:
<code>
<script type="text/javascript">
function drawGmap() { ......}
</script>
</code>
2. In the aspx.cs page, to call the javascript function drawGmap(...),
<code>
if (!Page.ClientScript.IsStartupScriptRegistered("alert")) {
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert",
"drawGmap();", true);
}
</code>
The name of the function is simply placed at the third position.
Showing posts with label visual studio. Show all posts
Showing posts with label visual studio. Show all posts
Wednesday, June 01, 2011
Thursday, February 18, 2010
NotesMSSQL - Notes on Microsoft SQL Server
NotesMSSQL
===========
General / FAQ
Starting the server
Login to server
Stopping the server
Converting CSV to MSSQL
Visual Studio Database Project
Views and Stored Procedures
Transact SQL
Transact SQL Data Types vs DB Datatypes
Transact SQL functions
Checking if table, stored procedures, synonyms, etc EXISTS
sys.object constants
Configuring Permissions
Deleting tables, DB, etc
Timing
Bulk Insert Selected Column
How to use Merge in SQLserver 2008
Installing SQL Server 2008 with Visual Studio 2008
How to use SQL Server 2008 Projects in Visual Studio 2008
Link to other servers
Using SQL Server Import and Export Wizard (SSIS)
Backup and Restore Database with MS SQL Server Management Studio
Difference Between nVarChar vs VarChar and nChar vs Char
Unicode
Using Variables / Parameters in Stored Procedures
Dynamic SQL
Partitioned Tables
Using SQL Server Management Studio
SQL Server Indexes
General / FAQ
===============
Where is the actual data stored?
Reference in MSDN:
T-Sql reference
Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Language Reference
MSDN Library - Servers and Enterprise Development - SQL Server - SQL Server 2008 - Product Documentation - SQL Server 2008 Book Online - Database Engine - Technical Reference - Transact-SQL Reference
Sql Server Project
Development Tools and Languages - Visual Studio 2005 - Visual Studio - .Net Framework Programming in Visual ... - Accessing Data - Creating SQL Server 2005 Objects in ...
Run a Data Generation Plan to generate data
http://msdn.microsoft.com/en-us/library/dd193262.aspx
Development Tools and Languages - Visual Studio 2005 - Visual Studio Team System - Team Edition for Database Professionals - Generating Data with Data Generator - Data Generation Plans
Development Tools and Languages - Visual Studio 2008 - Visual Studio Team System - Database Edition - Managing Changes to Datatabse and D.. - Verifying Existing Database code - Generate Test Data for Databases by ....
Database Tuning
Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Tutorials - SQL Server Tools Tutorials - Database Engine Tuning Advisor Tutorial
Export / Import Data - SQL Server Integration Services (SSIS)
Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Overview - SQL Server Integration Services (SSIS)
Export / Import Data - SQL Server Import and Export Wizard
Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Integration Services (SSIS) - Designing and Creating Integration Services Packages - Creating Packagaes using the SQL server Import and Export Wizard.
Starting the server
====================
- net start server; or
- osql /Usa -P
Login to server
================
- use SQL Server Enterprise Manager
- use SQL Query Analyser
osql /U [login_id] /P [password] /S [servername]
Stopping the server
====================
SQL Server Enterprise Manager
SQL Server Service Manager
SHUTDOWN statement
net stop mssqlserver
Control Panel
CTRL+C
Converting CSV to MSSQL
=========================
1. Assume the CSV data file is available and it has first row as headings for the table.
2. Open the CSV file with MS Access 2007. Open up the table of data.
3. In MS Access, click Database Tools - SQL Server. This opens up an upsizing wizard.
4. In the wizard, select Create New DB - click Next.
5. In the wizard, choose DB server, Use Trusted Connection, enter a name for the new SQL DB, click Next.
6. In the wizard, select the Tables to be exported - click Next.
7. In the wizard, select the following: Indexes, Validation Rules, Defaults,
Table Relationships, Use DRI, click Next.
7. In the wizard, select No Application Changes - click Next. Click Finish. The DB gets converted into MSSQL.
Note that the Table Structure and Data would be migrated to MSSQL in the above procedure.
BUT, for very large datasets, when there are incompatibilities with the data types, sometimes the
migration will fail to migrate data but the structure will have been created.
The additional steps below is for the situation where the Table Structure has been migrated
successfully to MSSQL, but not the data. Assume that the original data is still in the CSV file. The
do the following.
1. Create a new Visual Studio Project: Add New Project - Database Projects - Microsoft SQL Server
- SQL-CLR - C# SQL Server Project.
2. In the project created above, in the Solutions Explorer, go to its folder called Test Scripts,
and create a new Test Script.
3. The test script eg. foo.sql can be any kind of script and can be run interactively.
4. To create a script to copy data from the CSV file to SQL DB, write the following script and execute:
BULK
INSERT
FROM 'file.cxv'
WITH
(
FIRSTROW = 2, -- starts importing data in row 2
--LASTROW = 40, -- imports until row 40
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
5. If there are any import errors, then consider modifying the data type of the table.
Example if a column is type int, then in the data file, the data may be !#NUM. One way to deal with
this is to change the column data type to nvarchar.
Visual Studio Database Project
================================
1. In Solution Explorer of VS.Net, do Add New Project.
2. Choose Microsoft Database Project - SQL Server 2005 Wizard
Give this DB project name: DB_A
3. Select:
Organize my project by Schema / Object type
Default schema: dbo
Include Schema name in the file name
Enable SQL CLR
4. Select:
select everything except "Numeric round abort" - this is the default
Database Collation: SQL_Latin1_General_CP1_CS_AS
5. Import existing schema - this is where you choose your existing database.
Import the DB called: DB_B
Also select: Script the column collation .....
6. Select:
Build output path: .\sql\
IMPORTANT - Target Database name: DB_B
this should be the name of the actual DB that is being pointed to.
Block incremental deployment ....
Click Finish.
Views and Stored Procedures
=================
Stored Procedures belong to the database itself. VS.Net allow the creation of stored
procedures through the IDE via the Database project.
1. From the Solution Explorer, go to the DB project, navigate the tree:
Schema Objects - Schemas - dbo - Stored Procedures.
2. Click Add New Item - Programmability - Procedure.
3. A stored procedure template is created and ready to be edited.
A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL
statements that execute as a batch. Views are queried like tables and do not accept parameters.
Stored procedures are more complex than views. Stored procedures can have both input
and output parameters and can contain statements to control the flow of the code,
such as IF and WHILE statements. It is good programming practice to use stored procedures for
all repetitive actions in the database.
---View-
CREATE VIEW vw_Names
AS
SELECT ProductName, Price FROM Products;
GO
-- Testing the View
SELECT * FROM vw_Names;
GO
--- Stored Proc
CREATE PROCEDURE pr_Names @VarPrice money
AS
BEGIN
-- The print statement returns text to the user
PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
-- A second statement starts here
SELECT ProductName, Price FROM vw_Names
WHERE Price < @varPrice;
END
GO
--- Testing Stored Proc
EXECUTE pr_Names 10.00;
GO
To run Stored Procedures for Views from MS VS.Net, just highlight the SQL command and
right click Execute SQL!
Transact SQL
============
Ref: MSDN - Enterprise and Servers Development - SQL Server - SQL Server 2005 Documentation -
SQL Server Books Online - SQL Server Database Engine - Designing and Creating Databases
SET NOCOUNT ON instructs SQL Server not to count the rows in the result set
NOLOCK is a SQL hint to not issue a shared lock, not honor exclusive locks, and
maybe permit a dirty read. In the delete procedure, the row count is left
on (by default) and the number of rows deleted is returned
IF blahCondition
blah
blah
ELSE
blah
END; Semicolon need at the end
------ Example
USE master;
GO
--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
DROP DATABASE TestData;
END
--Create a new database called TestData.
CREATE DATABASE TestData;
USE TestData
GO
CREATE TABLE dbo.Products
(ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription text NULL)
GO
-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO
-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO
UPDATE dbo.Products
SET ProductName = 'Flat Head Screwdriver'
WHERE ProductID = 50
GO
-- Returns only two of the records in the table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
WHERE ProductID < 60
GO
-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
FROM dbo.Products
GO
Passing Arguments into Stored Procedures
- In the example below, schm and Tsource arguments have default values assigned.
-------
Create PROCEDURE [dbo].[testPERD_XPOS] (
@tblName sysname,
@schm sysname = 'dbo',
@Tsource char(50) = NULL
Transact SQL Data Types vs DB Datatypes
=========================================
datetime - DT_DBTIMESTAMP
money - DT_CY
Transact SQL functions
=========================
-- To get current database name
Select db_name()
-- To check compatibility level DB
exec sp_dbcmptlevel 'RatingsHistoryDB'
Result: 100 means version 10.0
Result: 90 means version 9.0
-- QUOTENAME(
')
If quote character is omitted, then square brackets is used.
Example:
SELECT QUOTENAME('abc[]def')
result is: [abc[]]def]
-- RTRIM
Returns a character string after truncating all trailing blanks
-- EXEC
To execute a stored procedure from a Query window (not from within Stored Procedure).
exec
-- To find rows of all tables
SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC
-- To find size (in bytes) of a table
exec sp_spaceused
-- To reduce size of database
Using SQL Server Management Studio, right click on DB - Tasks - Shrink - Files | Database.
Checking if table, stored procedures, synonyms, etc EXISTS
==============================================================
To check that a table exists:
IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
Print 'Table Exists'
ELSE
Print 'Table Does Not Exists'
Note the type of object 'U' represents a Table. For more object type codes, see section on: "sys.object constants"
sys.object constants
======================
principal_id -- int -- ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.
Is NULL if there is no alternate individual owner.
Is NULL if the object type is one of the following:
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
TA = Assembly (CLR-integration) trigger
TR = SQL trigger
UQ = UNIQUE constraint
type -- char(2) -- Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
type_desc -- nvarchar(60) -- Description of the object type:
AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
TABLE_TYPE
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE
Configuring Permissions
=========================
-- give permission to access instance of SQL DB
CREATE LOGIN [computer_name\Mary]
FROM WINDOWS
WITH DEFAULT_DATABASE = [TestData];
GO
-- give permission to access TestData DB
USE [TestData];
GO
CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO
-- give permission to access Stored Proc
GRANT EXECUTE ON pr_Names TO Mary;
GO
Deleting tables, DB, etc
==========================
USE TestData;
GO
--Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO
-- Use the DROP statement to remove permission for Mary to access the TestData database:
DROP USER Mary;
GO
--
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [
GO
-- Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO
-- Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO
-- Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO
-- Use the DROP statement to remove the Products table:
DROP Table Products;
GO
-- You cannot remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database:
USE MASTER;
GO
DROP DATABASE TestData;
GO
Timing
========
Bulk Insert Selected Column
============================
--CREATE VIEW testView
--AS
--SELECT exposure_class
--FROM ASB_IPRE_CommProperty
BULK INSERT testView
FROM 'H:\workCBA\myVS2005\StressTest\TestRiskRatedPFConsole\Data\testjunk.csv'
WITH
(
--FIRSTROW = 2,
----LASTROW = 10,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
How to use Merge in SQLserver 2008
====================================
This is a description of how to use the SQL Server 2008 feature - "Merge ... Using".
The operation here involves 2 Tables.
Tsrc = Source table where the information is downloaded from.
Trgt = Local table, typically our working version of the table.
The real life situation / scenario is that from time to time, Tsrc will be changed. At different times, we need to update our local table
Trgt such that it reflects the source table (Tsrc) but also require to keep the records of old data.
The process involve a merge between the local and the source tables and the results are kept in the local table.
- The source table is unchanged.
- The local table has extra columns RUN_I and Clatest.
- Column RUN_I keeps track of the runs each time we perform a download and merge from the source database.
- Column Clatest keeps track of whether the record is the latest record or has been modified.
Define the two tables as follows:
CREATE TABLE [dbo].[Tsrc](
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Trgt](
[RUN_I] [int] NOT NULL, -- a run id
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL,
[Clatest] [int] NOT NULL -- is 0 if record is old, is 1 if record is the new/latest.
) ON [PRIMARY]
The Merge process actually occurs in a temporary table-space facilitated by a VIEW. The view can be defined as follows:
CREATE VIEW [dbo].[VTemp]
AS
SELECT RUN_I, C1, C2, C3, Clatest
FROM dbo.Trgt
WHERE (Clatest = 1)
There is also a temporary table called UpdateRecords which are created from the Merge process. This table contain the meta-data about
which records have changed from the Merge process.
The description below is based on the complete Insert Into .. Select .. Merge operations.
The results from the merge operation, which return multiple records, are inserted into the local table Trgt.
Overall Description:
1. Get desired data from source table. Lines 5-13
2. Merge results from step 1 with local table. Lines 4-26. In this process there are three conditions and one post processing.
3. Within the merge operation, the 3 conditions are: i) when there is a match between source and local table conditions; ii) when record
is in source but not in local iii) when record is in local but not in source. Within each of these conditions, there are operations that
perform a change to the local table.
4. At the end of the merge operation, as a post process, meta-data can be outputted as a temporary table (eg UpdateRecords table).
5. From the temporary table of the Merge operation, select the records which have been replaced. Lines 2-28.
6. From step 6, insert the results back into the local table Trgt. Line 1.
Detailed Description: From the innnermost loop ......
Lines 8-10: Downloads records from Source table, with relevant conditions.
Lines 5-13: The downloaded source data is aliased as src(C1,C2,C3), to be used as the source of the Merge operation
Line 14: Specify conditions for merge.
Lines 15-17: Columns C1, C3 are info to identify records between source and local tables. When identified, then if the C2 info is
different between source and local tables, or either one is null, then update C2 info and Run_I identifier.
Lines 18-21: When records in source but not in local, then put data from source to local.
Lines 22-24: When records in local but not in source, then mark the record as obsolete by setting Clatest = 0.
Line 4: This merging process does not merged with the local table, but rather it merges with the sub-table of the local table, where
the entries are the most current ones, ie Clatest = 1.
Line 26: Outputs the merge process including special value $action which has values 'UPDATE', 'INSERT', etc. And also Deleted and
Inserted values.
Lines 04-26: The complete merge process. At the end of this, both view and its real data in the local table Trgt, would have been
changed. It also outputs meta data including inserted information and deleted information.
Eg. Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest, Inserted.Clatest AS newClatest
Lines 27: The results from the merge process, acting like a temporary table for the Select command.
Lines 02-28: Selection of META results from the merge process to extract information of updated records. Note in the actual merge process
of lines 04-26, for the case when a row is being updated, the Trgt table and view will hold the updated record only, the previous record
is not kept. Example
Before merge: {RUN_I, C1, C2, C3, Clatest} = {23, 1, 2, 3, 1} -> old
After merge: {RUN_I, C1, C2, C3, Clatest} = {24, 1, 5, 3, 1} -> new
There are no extra rows added to keep track of the old record. However at the end of the merge process, there are Meta-fields available
which keep track of the old record as: Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest. In this example:
{Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest} = {23, 1, 2, 3, 1}
In Line 26, these Meta-fields are selective extracted into the temporary table called UpdateRecords. Also a new field called prevClatest
is created with values 0 to indicate old results.
Finally, in line 2, the select statement chooses {prevRunId, C1, prevCQC, C3, prevClatest}, in the example this is:
{23, 1, 2, 3, 0}
And ultimately in Line 1, the results from Line 2 which represent replaced records, are added back into the local table Trgt.
Lines 01-28: Add records to local table Trgt, those records selected by Lines 02-28 which are in fact old records which have been updated
and use prevClatest into the Clatest column.
01 INSERT INTO Trgt
02 SELECT prevRunId, C1, prevCQC, C3, prevClatest
03 FROM (
04 MERGE VTemp AS trgt
05 USING (SELECT crr.C1, crr.C2, crr.C3
06 FROM Tsrc AS crr
07 INNER JOIN (
08 SELECT DISTINCT C1
09 FROM Tsrc
10 WHERE C1>2 AND C2 in ('A','A0')
11 ) as c
12 ON c.C1 = crr.C1
13 ) AS src (C1, C2, C3)
14 ON trgt.C1 = src.C1 AND trgt.C3 = src.C3
15 WHEN MATCHED AND ((trgt.C2 != src.C2) OR (ISNULL(trgt.C2,'') != ISNULL(src.C2,'')))
16 -- record exists for given client and rating date, but rating value has changed so update record
17 THEN UPDATE SET C2 = src.C2, RUN_I = @RUN_I
18 WHEN NOT MATCHED BY TARGET
19 -- records in source that are not in target, insert new records in target
20 THEN INSERT (RUN_I, C1, C2, C3, Clatest)
21 VALUES (@RUN_I, src.C1, src.C2, src.C3, 1)
22 WHEN NOT MATCHED BY SOURCE
23 -- records in target that are no longer in source, expire target records
24 THEN UPDATE SET Clatest = 0
25 -- record exists for given client and rating date, but rating value has changed so insert the previous record with old rating
26 OUTPUT $action, Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest,
Inserted.Clatest AS newClatest)
27 AS UpdatedRecords (Action, prevRunId, C1, prevCQC, C3, prevClatest, newClatest)
28 WHERE Action = 'UPDATE' AND newClatest = 1
Installing SQL Server 2008 with Visual Studio 2008
====================================================
Visual Studio 2008 Team Suite
Visual Studio 2008 Team Suite SP1
SQL Server 2008
Microsoft® Visual Studio Team System 2008 Database Edition GDR R2
(http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en#filelist)
Visual Studio 2008 Team Explorer
Showstoppers during installation:
Message: Setup could not verify the integrity of the file Update.inf. Make sure the Cryptographic service is running on this computer.
Solution: First solution works in: http://support.microsoft.com/kb/822798
Message: The following error has occurred: Error 1316.A network error occurred while attempting to read from the file
............\SSCERuntime-enu.msi
Solution: see http://support.microsoft.com/kb/970876
Message: The following error has occurred: Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error
Solution:
If Visual Studio is installed to a non-default location on X64 machine and X64 BIDS are installed, the SQL Server setup fails.
This failure does not apply when setup runs on X86 machines or on X64 machines when the BIDS are installed in WOW mode (X86 on X64).
Solution:
See https://connect.microsoft.com/SQLServer/feedback/details/363017/sql-server-2008-rtm-upgrade-failed-due-to-the-following-error-the-error-code-is-2147467259-message-unspecified-error
How to use SQL Server 2008 Projects in Visual Studio 2008
===========================================================
Prerequisites: see section "Installing SQL Server 2008 with Visual Studio 2008"
There can be various reasons why we need to create a SQL Server 2008 project as a Visual Studio 2008 project. The reason in this case is so that we can create an image of the DB to deploy to other systems. Hence the initial step would be to create a DB itself in the DB server. Then we create the following Visual Studio - Sql Server project. Having this Visual Studio - Sql Server project enables us to link to the actual DB and deploy any changes to the real DB or to a new DB.
This section explains the process to create the Visual Studio - Sql Server project.
1. In Visual Studio: File - New - Project.
2. In the Project dialog: Database Projects - SQL Server 2008 - SQL Server 2008 Wizard.
3. In the New Project Wizard,
i) Type of project: select "A database project to manage changes to a user-defined database.
ii) In the SQL Script File section, select "By type of object".
To Link the VS project to the actual Database:
1. Once the vS SQL Server project has been created, go to Solution Explorer and right click on the DB project.
2. Select "Import database objects and settings"
3. In the Import Database Wizard, choose the connection to the real DB and fill in any other options.
4. Press Start.
Now that the VS SQL Server project DB has been created, and has also linked to the actual real DB, we can build this VS DB project and re-create the DB in other DB servers. To do this, go to the VS SQL Server project and Build, then Deploy.
Link to other servers
======================
- List all databases in the server:
exec sp_databases
- List the servers being connected to:
select * from sys.servers
- List all tables in the database
exec sp_tables
- To connect to a remote DB which is also MSSQL server
exec sp_addlinkedserver @server='
Note that 'SQL Server' is a special fixed string - do not modify.
Ref:
http://blogs.techrepublic.com.com/datacenter/?p=133
If @srvproduct is not 'SQL Server', then @provider may be necessary. @provider may have the following values:
SQL Server - SQLNCLI
Oracle - MSDAORA
Oracle, version 8 and later - OraOLEDB.Oracle
Access/Jet and Excel - Microsoft.Jet.OLEDB.4.0
ODBC data source - MSDASQL
IBM DB2 Database - DB2OLEDB
If the remote server cannot be accessed, then perhaps permissions need to be configured,
---- see master.dbo.sp_addlinkedsrvlogin
Linked Server - can be done from Management Studio, by:
- Server Objects - Linked Servers, right click to Add - New Linked Server
Using SQL Server to Import to data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. Follow instructions in the wizard to Select source.
3. In the "Choose a Destination" dialog, choose "Flat File Destination" in the "Destination" drop down list.
Then select the file name to save the data. Also check "Column names in the first data row".
4. In the "Specify Table Copy or Query" dialog, choose "Copy data from one or more tables or viewa".
5. In the "Configure Flat File Destination" dialog, choose the table to be copied. By clicking the "Edit Mappings" button, the columns of the table can be modified.
6. In the "Save and Run Package", select "Run Immediately" and "Save SSIS Package" and "File System".
7. In the "Save SSIS Package", select the destination to where the package is to be saved.
8. The table will be saved in two files:
WARNING - When importing to flat file , MSSQL - SSIS transforms the original data types and usually saves them into strings. When exporting these data back into the DB, they are still strings, but will be able to populate columns which have the original data type.
Using SQL Server to Export from data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. In the "Choose Data Source" dialog, choose "Flat File Source" in the "Source" drop down list.
Then select the file name. Also check "Column names in the first data row".
3. In the "Choose a Destination" dialog, selec the local DB server. Then also select the DB from the Database drop down list.
4. In the "Select Source Tables and Views", select the table needed.
5. Review the data mapping in the dialog.
6. In the "Save and Run Package", select "Run Immediately".
7. In the "Save SSIS Package", select the destination to where the package is to be saved.
Transfer or Copy Database using Backup and Restore with MS SQL Server Management Studio
=================================================================
This function allows the transfer of an entire Database (DB) at one go by using the backup and restoration facility of MSSMS.
1. Open up MS SQL Server Management Studio (MSSMS).
2. In the Object Explorer, navigate to the specific DB to be backed up. Right click on it, then select Tasks - Back Up...
3. In the Backup DB dialog in the General tab, ensure the following settings:
Source - Database: name of database is correct
- Backup type: Full
- Backup Component: Database
Destination - Disk
the backp file path will be displayed in the text box.
4. In the Backup DB dialog in the Options tab, there are various options that control the backup such as:
Overwrite / Append backup file
Reliability: verifying backup, continue on error
Compression
5. Click OK to begin backup. The backup file is usually
6. To copy over the backed up DB to a new server, open up MSSMS and connect to the new server.
7. In the Object Explorer, under the DB server, right click on the "Databases" and select "Restore Database".
8. In the Restore DB dialog, in the General tab, fill in the following:
To Database: type in the name of the new DB
Source To Restore: From Device, then click on the button to choose the backup DB file.
In the list of DB to restore, put a tick in the DB to be restored.
9. In the Restore DB dialog, in the Options tab, fill in the following:
Restore Options: choose from various options as needed.
In the table "Restore the DB files as", under the "Restore As" column, manually edit the path to where the DB files should be created.
Recovery State options: choose from various options as needed.
10. Click OK to begin the Transfer / Recovery of DB.
Difference Between nVarChar vs VarChar and nChar vs Char
===========================================================
The var or char with the letter n in front means Unicode character is allowed. N stands for 'National'.
Note the nchar, nvarchar takes at least twice as much storage than the non-n version.
Unicode
========
Unicode allows data to be stored in characters beyond ASCII characters. It allows letters from other languages.
1. Unicode data type are: nChar, nVarchar
2. Unicode string, eg.
set @tmp = N'Select * from blah'
Using Variables / Parameters in Stored Procedures
===================================================
- Using database name as a variable
DECLARE @Database VARCHAR(10)
SET @Database = 'TWO'
EXEC('USE ' + @DATABASE)
- Using sp_executesql example:
use blah
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
N'@RECORDCNT int out, @ORDERID int',
@RECCNT out,
@ORDID
print 'The number of records that have an OrderId' + ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5))
In the example above, @ORDID -> @ORDERID as input, then @RECORDCNT -> @RECCNT as output.
Dynamic SQL
=============
Ref: "The Curse and Blessings of Dynamic SQL" http://www.sommarskog.se/dynamic_sql.html
- Safest way of using Dynamic SQL is through Stored Procedures in T-SQL, rather than Stored Procedures in C#, .Net, and rather than sending SQL statements to DB server.
- use sp_executesql rather than EXEC() in Stored Procedures
- Use dbo to prefix table names
- Use a @debug parameter in SP for easy debugging, eg.
CREATE PROCEDURE blah @debug bit = 0 , @tblname sysname
AS
blah
DECLARE @sql nvarchar(max)
SET @sql = 'select * from dbo.' + QUOTENAME(@tblname)
IF @debug = 1 PRINT @sql
- When table name is variable, eg @tableName, use QUOTENAME(@tableName).
Note that QUOTENAME can only be used with sp_executesql, but not in EXEC(). The equivalent of using EXEC() is:
EXEC('Select * from ' + @tblname)
- When passing variable tablename eg @tblname in the previous example, use the "sysname" data type.
- When using DB or Linked Servers, use SYNONYM
CREATE SYNONYM otherDB FOR other.DB.table
Partitioned Tables
======================
??? See Books Online
Using SQL Server Management Studio (SSMS)
==========================================
The following applies to SSMS 2008
Tools in SMSS:
i) Activity Monitor - this allows you to monitor the performance of SQL Server. To Open the Activity Monitor:
- In object explorer, right click on the server name of the DB, and choose Activity Monitor
- In the Toolbar, click the icon that looks like a graph.
SQL Server Indexes
====================
Indexes are extra sets of information pointing to specified column data in a table. As its name suggests, it indexes a set of column data to provide something like pointers or addresses to the data. There are various types of indexes for different purposes that can be created.
Example: Select ProdID, ProdName, UnitPrice FROM Prod WHERE UnitPrice > 12.5
Non-Unique Index - In the example above, the non-unique index will sort the Unit Price, and produce an internal index that point the sorted UnitPrice to the original row position in the table.
Eg. CREATE INDEX Idx_Price ON Prod (UnitPrice)
Unique Index - the column to be indexed need to be unique.
- a Primary Key is automatically a unique index.
Eg. CREATE UNIQUE INDEX Idx_Price ON Prod (UnitPrice)
Clustered Index - instead of keeping an index to the indexed column, a clustered index RE-SORTS ALL columns based on the chosen column to be indexed
- having a primary key automatically makes the table into a clustered index.
- if there is no primary key, then the table should be made into a clustered index based on a certain column.
- a table can have only one clustered index.
- Every table should have a clustered indexe for performance reason.
- a clustered index can be unique or non-unique.
Eg. CREATE CLUSTERED INDEX Idx_Price ON Prod (UnitPrice)
Composite Index - where multiple columns are used as the index
- can be clustered or non-clustered
- if a primary key is compose of two columns, then those two columns are also Composite Indexes.
Eg. CREATE CLUSTERED INDEX Idx_Price_ProdName ON Prod (UnitPrice, ProdName)
When to use Indexes
- when Searching for records in queries with WHERE conditions, with SELECT, UPDATE or DELETE statements.
- when Sorting records, eg with ORDER BY keyword
- when Grouping records, eg with GROUP BY keyword
- when Covering queries use Composite Index. In the example below, apart from using Price as the index, the ProdName can also be used together as the index.
Eg Select ProdName, Price FROM Prod ORDER BY Price
- when Matching complex search, useful for searches like below
Eg SELECT * FROM Order WHERE OrderID = 1 AND ProdID = 2
When not to use Indexes
- when DB space is limited, because indexes take up extra spaces
- when queries modify data, eg with INSERT, UPDATE, DELETE statements; because the indexes need to be modified too which reduces performance.
Other Guidelines to use Indexes
- make indexes from columns with short data types, eg int, rather than long characters.
- columns where values are mostly distinct or unique.
- delete indexes which are not needed
-- to rename an index
EXEC sp_rename 'Prod.IX_UnitPrice', 'IX_Price'
-- to delete an index
DROP INDEX
-- to see a list of all indexes created on a table
EXEC sp_helpindex
-- to see the space used by Indexes
EXEC sp_spaceused
Subscribe to:
Posts (Atom)
Posts
