ASP.Net State Management Movies uploaded to Google Videos

ASP.Net State Management Movies uploaded to Google Videos

Here are the hyperlinks to state management videos

QueryString

Session Variable

Profile

WPF and Silverlight Creating Class with Constructors

(I have updated this blog on Mar 23, 2010 to showcase Silverlight 4 and Visual Studio 2010.
The same will work with WPF)















Create a new Silverlight 4 Application in Visual Studio 2010.

Creating a Silverlight Application Project creates two projects one a Silverlight Project and another a Web Project used for Hosting Silverlight.

Select Silverlight Project, Add a class by selecting Project, Add class menu option. Give the class a good name say Bank.

So we are going to create a Bank Class with two properties and one method.







In this article we are going to take up the issue of creating a class in C#.

C# has the notion of read write, read only and write only properties. Here we are going to have one read write properties, Bank.
For creating a class there property procedures. For read write property we should have both getter and setter and a private memory variable which actually holds the value. Getter is used for returning the value of the private memory variable and Setter is used for setting a value to the private memory variable which in effect reflects the value of the property.

The methods are Deposit and Withdraw.

I am also implementing a default and a parameterized constructor.



Here is the code of Bank class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WpFCSClass
{
class Bank
{
public Bank()
{
miBalance = 5000;
}

public Bank(int mAmount)
{
Balance = mAmount;
}
private int miBalance;
public int Balance
{
get {
return miBalance;
}
set {
miBalance = value;
}
}

public void Deposit(int Amount)
{
Balance = Balance + Amount;
}

public void Withdraw(int Amount)
{
Balance = Balance - Amount;
}
}
}

Having created the class we can shift out attention to the presentation layer. The presentation layer can be anything ranging from Windows Forms Application, Web Forms Application, WPF etc. I decided to choose WPF.




















Bank ShalvinBank = new Bank(2000);
private void Window_Loaded(object sender, RoutedEventArgs e)
{
ShowBal();
}
private void ShowBal()
{
lblCurBal.Content = ShalvinBank.Balance.ToString();
txtAmount.Text = "";
txtAmount.Focus();
}

private void btnDeposit_Click(object sender, RoutedEventArgs e)
{
ShalvinBank.Deposit(Int32.Parse(txtAmount.Text));
ShowBal();
}

private void btnWithdraw_Click(object sender, RoutedEventArgs e)
{
ShalvinBank.Withdraw(Int32.Parse(txtAmount.Text));
ShowBal();
}

Happy Programming

shalvin@gmail.com

Transact Sql

SQL (Structured Query Language) is the computer language meant for working with RDBMS (Relational Database Management Systems).
Transact-Sql (T Sql) is the Microsoft SQL Server's  implementation of SQL.

 

For working with SQL Server we can use SQL Server Management Studio (SSMS) or light weight options like sqlcmd command line option.

Types of Sql Commands 

DDL
Data Definition Language
Eg : CREATE TABLE

DML Data Manipution Lanaguage
Eg : INSERT, UPDAT and DELETE

DCL
Data Control Language
Eg: GRANT and REVOKE

Creating a database

create database Shalvin

Switching to the newly created database

use Shalvin

 
Creating a new table, setting an auto increment field and primary key
IDENTY is used for creating an auto increment field. The parameters are seed and increment.

create table Categories (
CategoryId int identity(1,1) primary key, 
CategoryName varchar(40), 
Description varchar(60))

 

 
Inserting values

insert into Categories values ('Beverages', 'Tea, Coffee, soft drinks, etc')
insert into Categories values ('Condiments', 'Sweets and savory sauces')
insert into Categories values ('Confections', 'Desserts, candies and sweet breads')
insert into Categories values ('Diary', 'Cheese')

 

 

Inserting values only to specific field(s)

insert into Categories (CategoryName) 
    values ('Milk Products')

Updating a value in a Table

update Categories set Description = 'Bakery items' 
WHERE  CategoryId = 2

Selecting Records

select * from Categories

Selecting Specific record(s) using WHERE clause 

-- Here I am using the Northwind database that is coming with Sql Server 2000.

use Northwind

SELECT CategoryId, CategoryName, Description FROM Categories WHERE CategoryId = 1

select * from categories where categoryid = 1 or categoryid = 2<br />

In Clause

select * from Categories where CategoryId in(1, 2)

Creating a table for foreign key constraint

create table Products (ProductId int identity(10, 1) primary key, 
CategoryId int foreign key references Categories(CategoryId),
 ProductName varchar(40), UnitPrice numeric(6,2))

insert into Products values (1, 'Bru Coffee 100 g', 10)

insert into Products values (10, 'Tomato Sauce 200 ml', 20) 

Adding constraints to an existing column in a Table

alter table expense add constraint expense_uk unique(expensename)

Demonstrating foreign key vioation error
--Here there is no record in Categories table with CategoryId 5

insert into Products values (5, 'ASP.Net 3.5 Unleashed', 10)

 

 Following is the complete SELECT query. In the bracket I have specified the execution order.

SELECT (5)

FROM (1)

WHERE (2)

GROUP BY (3)

HAVING (4)

ORDER BY (6)

OFFSET - FETCH  (7)

1. FROM defined the data set

2. WHERE defines the ROW filter using predicates (a logical expression that evaluates to either true or false)

3. GROUP BY - Individual rows can be combined into groups based on grouping expression.

4. HAVING - Group filer

5 SELECT - Selects the record

6. ORDER BY - Presentation Order

7. OFFSET - FETCH - Paging Specification

 

SELECT WITHOUT FROM

SELECT 123, 2*3, sqrt(3)

123    6    1.73205080756888

 

The above command will not work with Oracle. It requires a dummy table.

 

SELECT 123 as Numbers, 2*3 as Multiple, sqrt(3) as Square

The above command introduce alias so there can be column name.

SELECT * from Products

Select * from <TABLENAME> extracts all the data from table.

 

Selecting records from multiple Tables

SELECT Categories.CategoryName, Products.ProductName,
 Products.UnitPrice
 FROM Categories, Products 
WHERE Categories.CategoryId = Products.CategoryId

Selecting Records from Multiples Tables using Table Alias

SELECT C.CategoryName, P.ProductName, P.UnitPrice 
FROM Categories C , Products P 
WHERE C.CategoryId = P.CategoryId

Need for Normalization
Lets take the case of an unnormalized Table. My intention is the track the employee information and their transactions.

create table NNEmployee (TraId int identity(1,1) primary key,EmployeeId int, EmployeeName varchar(40), DOJ DateTime, Department varchar(40), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09', 'Jan', 22)
insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09','Feb', 21)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Jan', 22)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Feb', 20)

select * from NNEmployee

Here I am duplication information like Date of Join, Department, etc.

Instead I can create three tables holding Department, Employee Master and Transaction Information.

Create table Dept (DeptNo int identity(1,1) primary key, DName varchar(40))

insert into Dept values ('IT')
insert into Dept values ('Networking')

select * from Dept
create table Emp (EmpNo int identity(10, 1) primary key, DeptNo int references Dept(DeptNo), EName varchar(40), DOJ DateTime)

insert into Emp values (1, 'Shalvin', '12-Jan-08')
insert into Emp values (2, 'Ajith', '1-Jan-08')

select * from emp

select E.EmpNo, D.DName, E.EName, E.DOJ FROM Dept D, Emp E WHERE D.DeptNo = E.DeptNo

create table EmpTran (TranId int identity(100, 1) primary key,EmpNo int references Emp(EmpNo), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into EmpTran values (10, '09', 'Jan', 22)insert into EmpTran values (10, '09', 'Feb', 20)
insert into EmpTran values (11, '09', 'Jan', 22)insert into EmpTran values (11, '09', 'Feb', 21)
select * from EmpTran

 
Aggregate Functions
Aggregated Functions act on a set of values and returns a single result row.
select max(sal) from emp
select min(sal) from emp
select avg(sal) from emp
select sum(sal) from emp;
select count(*) from emp;
Aritmetic Functionsselect power(5,2)
select sqrt(25)

Date Functions
select getdate()
Result - 2009-07-22 16:00:03.590

Extracting the current year
select datepart(year, getdate())
Result - 2009

Extracting the current month
select datepart(month, getdate())
Result - 7

Extracting the current day
select datepart(day, getdate())
Result - 22

SubQuery
Subquery is a query nested inside another query.
For example, for extracting all the products from Products table of category 'Beverages' the subquery is

SELECT * FROM Products WHERE CategoryId IN (SELECT CatgoryId from Categories WHERE CategoryName = 'Beverages')
More Info

Renaming databasesp_renamedb 'Shalvin', 'ShalvinPD'

or
Alter Database Shalvin Modify Name = ShalvinPD

Listing all databases in an sql server Instancesp_helpdb


Listing all tables within a databaseSELECT * FROM sysobjects WHERE xtype = 'u'

Listing the structure of a tablesp_help Categories

Versions of Sql Server and Developer Tools
Sql Server 7
Sql Server 2000 - Query Analyser, Enterprise Manager
Sql Server 2005 - Sql Server Management Studio
Sql Server 2008

Related Blog
Sql Server Stored Procedures

sqlcmd and Sql Server 2005

Email : shalvin@gmail.com

.Net Multi-Tier Application

This blog takes up the issue of creating multi tier application with VB.Net (Windows Forms) and C# (Asp .Net).

Start up with creating two stored procedures.

create procedure spAllCategories
as
select * from Categories
go


create procedure spInsertCategories (@pCategoryName varchar(40), @pDescription varchar(60))
as
insert into Categories values (@pCategoryName, @pDescription)
go

Create an new class Library project called ShalvinLib and add two methods that will call the stored procedures. This is going to act as the business tier.

Imports System.Data.SqlClient
Public Class BusinessLayer
Public Shared Function GetCategories() As DataTable
Dim cnn As New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind;Data Source=.\sqlexpress")
cnn.Open()

Dim ds As New DataSet
Dim da As New SqlDataAdapter("spAllCategories", cnn)
da.Fill(ds, "Cat")
Return ds.Tables("Cat")
End Function

Public Shared Sub InsertCategories(ByVal mCategoryName As String, ByVal mDescription As String)
Dim cnn As New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind;Data Source=.\sqlexpress")
cnn.Open()
Dim cmd As SqlCommand

cmd = New SqlCommand("spInsertCategories " + mCategoryName + ", " + mDescription, cnn)

cmd.ExecuteNonQuery()

End Sub

End Class

C# and Asp.Net
Here instead of using Windows Forms I am opening a Asp.Net Application and Instead of starting a new class Library I am adding a Class to the existing project.

web.config




using System.Data.SqlClient;
public static DataTable GetCategories()
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("Cnn"));
SqlDataAdapter da = new SqlDataAdapter("spAllCategories", cnn);
DataSet ds = new DataSet();
da.Fill(ds, "Cat");
return ds.Tables["Cat"];
}

public static void InsertCategories(string mCategoryName, string mDescription)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("Cnn"));
cnn.Open();
string strSql = "spInsertCategory '" + mCategoryName + "', '" + mDescription + "'";
SqlCommand cmd = new SqlCommand(strSql , cnn);
cmd.ExecuteNonQuery();
}


Having created the class library dll, we can proceed to create a windows forms application.
Set a reference to the class library. Create the visual interface and invoke the methods of class library.

Dim bl As New ShalvinLib.BusinessLayer
Private Sub frmShowCategories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DataGridView1.DataSource = bl.GetCategories
End Sub

C# and Asp .Net
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = BusinessLayer.GetCategories();
DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
BusinessLayer.InsertCategories("Books", ".Net Books");
Response.Write("Record Saved");
}

Happy programming