Asp.Net : Implementing Date Selection without CalendarExtender Ajax Control

Thought implementing Date Selection options with CalendarExtender Ajax Control is a breeze, many ASP.Net Programmers are still working with Asp.net 1.x.
With a bit of nostalgic tint let me blog on the code I used to write to implement the same functionality in Asp.Net 1.x.

I start out with placing three combo boxes for Day, Month and Year and fill the controls with valuues. Then catenated values of three comboxes will be committed to table.

Here is the code:

using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection cnn;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillDayCombo();
FillMonthCombo();
FillYearCombo();
DataBind();
}
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin;Data Source=.\\sqlexpress");
cnn.Open();
}

protected void FillDayCombo()
{
for (int i = 1; i < 31; i++)
ddlDay.Items.Add(i.ToString());
}
protected void FillMonthCombo()
{
string[] strMonths = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "July", "Aug", "Sep", "Oct", "Nov", "Dec" };
ddlMonth.DataSource = strMonths;
}
protected void FillYearCombo()
{
for (int i = 1990; i < 2020; i++)
ddlYear.Items.Add(i.ToString());
}

protected void Button1_Click(object sender, EventArgs e)
{
string strDate = ddlDay.Text.ToString() + "-" + ddlMonth.Text.ToString() + "-" + ddlYear.Text.ToString();
Response.Write(strDate);
cmd = new SqlCommand("Insert into DateTest values ('" + strDate + "')", cnn);
cmd.ExecuteNonQuery();
Response.Write("Record Saved");
}
}


MultiTier

Now let's implement the same solution in Multi tier scenario.
I am creating a class called DBConnect.

public class DbConnect
{
public ArrayList GetDays()
{
ArrayList alDays = new ArrayList();
for (int i = 1; i < 31; i++)
alDays.Add(i.ToString());
return alDays;
}
public string[] GetMonths()
{
string[] strMonths = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "July", "Aug", "Sep", "Oct", "Nov", "Dec" };
return strMonths;
}
public ArrayList GetYears()
{
ArrayList alYears = new ArrayList();
for (int i = 1990; i < 2020; i++)
alYears.Add (i.ToString());
return alYears;
}

Coming back to the form I am instantiating the DbConnect class and callilng its methods.
DbConnect dbc = new DbConnect();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlDay.DataSource = dbc.GetDays();
ddlMonth.DataSource = dbc.GetMonths();
ddlYear.DataSource = dbc.GetYears();
DataBind();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
string str = ddlDay.Text + "-" + ddlMonth.Text + "-" + ddlYear.Text;
Response.Write(str);
}

Happy Programming
Shalvin@gmail.com

ASP.Net Profile

Profile is a ASP.Net 2.0 State Management option.

The greatest drawback with other state management options like Query String, Session Variable, Cookie and ViewState is lack of intellisense, which is the cause for hard to track bugs.
For example if you define a QueryString called Name in Default.aspx and refer to the same as Nam in Welcome.aspx, you won't get an error message instead a new QueryString variable called Nam will be created so obviously unpredicted result.

Profile supports intellisense.

Profile is strongly typed and you can specify default value.


Start out be creating Profile propertis in web.config












Make sure you run the application before using the profile properties.

You will get intellisense of Profile properties.

//Default.aspx
protected void btnSubmit_Click(object sender, EventArgs e)
{
Profile.Name = txtName.Text;
Profile.Blog = txtBlog.Text;
Profile.Rate = 400 + 100;
Response.Redirect("Welcome.aspx");
}


Here note that we are not assigning value to Specialization Profile property and we can apply arithmetic operations on Rate Profile property since its type is Int32.

We are accessing the value in Welcome.aspx

//Welcome.aspx
protected void Page_Load(object sender, EventArgs e)
{
Response.Write(String.Format("{0} 's blog is {1} specializing in {2} hourly rate is {3} INR", Profile.Name, Profile.Blog, Profile.Specialization, Profile.Rate ));
}

Group of Personalization Properties

lt;profile>

<properties>
<group name ="Person">
<add name ="Name"/>
<add name ="Location"/>
</group>
</properties>
</profile>

//Default.aspx
protected void btnSubmit_Click(object sender, EventArgs e)
{
Profile.Person.Name = "Shalvin";
Profile.Person.Location = "Kochi";
Response.Redirect("Welcome.aspx");

}

//Welcome.aspx
protected void Page_Load(object sender, EventArgs e)
{
Response.Write(String.Format("I am {0} my location is {1}", Profile.Person.Name, Profile.Person.Location));
}


Using Sql Server Instance Database

>aspnet_reqsql -E -S .\shalvin -d ShalvinProfile -A p



<connectionStrings>
<add name="ShalvinProfileConnectionString"
connectionString="Data Source=.\shalvin;Initial Catalog=ShalvinProfile;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>

<system.web>

<profile>
<providers>
<clear/>
<add name="AspNetSqlProfileProvider"
type="System.Web.Profile.SqlProfileProvider"
connectionStringName="ShalvinProfileConnectionString"/>
</providers>
<properties>
<add name="Name"/>
</properties>
</profile>
</system.web>







Related Blog

Query String Asp .Net : Working with multiple values

Session Asp .Net : Storing DataSet

System.IO Namespace

Knowledge of working with streams is very essential. Make sure you master these skills before venturing into Cryptography, Compression, Serialization, etc.

File and Directory classes of System.IO namespace contains static method for working with Files and Directories.

Creating a text file and writing to it

The following example demonstrates the easiest way to create a file and writing into it using StreamWriter class.

using System.IO;
StreamWriter sw;

private void btnCreateText_Click(object sender, EventArgs e)
{
sw = File.CreateText(@"f:\ShalvinPD.txt");
sw.WriteLine("Shalvin P D");
sw.WriteLine("Web site : shalvin.com");
sw.Close();
}


Creating an Html Page on Fly

Certain job sites creates as web page for you based on the information you provided about yourself. Let's see how to do it in .Net.

StreamWriter sw;
private void btnCreateHtm_Click(object sender, EventArgs e)
{
sw = File.CreateText(@"f:\1Hello.htm");
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("Shalvin P D");
sw.WriteLine("
");
sw.WriteLine("Web Site : shalvin.com");
sw.WriteLine("");
sw.WriteLine("");
sw.Close();
System.Diagnostics.Process.Start("iexplore", @"f:\1Hello.htm");
}

Listing All Drives
Listing all drives is as simple as creating an array of DriveInfo class, calling the DriveInfo.GetDrives methos and Iteraing throught the DriveInfo collection.

StreamWriter sw;
private void Form1_Load(object sender, EventArgs e)
{ DriveInfo[] diDrives = DriveInfo.GetDrives();
foreach (DriveInfo diDrive in diDrives)
listBox1.Items.Add(diDrive);
}

Retriving the Drive Type, Free Space and Total Size of Drives

StreamWriter sw;
private void Form1_Load(object sender, EventArgs e)
{try
{
DriveInfo[] diDrives = DriveInfo.GetDrives();
foreach (DriveInfo diDrive in diDrives)
{
listBox1.Items.Add(diDrive);
listBox1.Items.Add(String.Format("Drive Type : {0}", diDrive.DriveType)); listBox1.Items.Add(String.Format("Free Space : {0}", diDrive.AvailableFreeSpace)); listBox1.Items.Add(String.Format("Total Size : {0}", diDrive.TotalSize));
listBox1.Items.Add(" ");
}
}
catch (Exception ex) { }
}

Adding Line Numbers to a File


StreamReader sr = new StreamReader("c:\\Code.txt");
StreamWriter sw = new StreamWriter("c:\\S1.txt");
private void button1_Click(object sender, EventArgs e)
{
int i = 1;
while (! sr.EndOfStream)
{
string s = sr.ReadLine();
sw.WriteLine(i.ToString() + " " + s);
i++;
}
sw.Close();
System.Diagnostics.Process.Start("notepad", "c:\\S1.txt");
}



Related Blogs
.Net Serialization (Code Snippets)

Email : shalvin@gmail.com

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

Used to create and modify database object like Tables, Views, etc.
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

Asp.Net 2.0 Membership videos posted in Google Videos

I make extensive use of short focused videos for training.
I have uploaded a few ASP.Net membership videos.
Here are the hyper links to those videos.

Asp.Net Membership II

CreateUserWizard

Asp.Net 2.0 Membership IV Roles

Asp.Net 2.0 Membership V Role and Membership Classes

Asp.Net 2.0 Membership V Denying Anonymous users

Asp.Net 2.0 Membership VIII Protecting a folder