November 15, 2011

SQL Connection String

Standard Security
Data Source=myServerAddress;Initial Catalog=myDataBase;
User Id=myUsername;Password=myPassword;

Standard Security alternative syntax
Server=myServerAddress;Database=myDataBase;
User ID=myUsername;Password=myPassword;Trusted_Connection=False;

Trusted Connection
Data Source=myServerAddress;Initial Catalog=myDataBase;
Integrated Security=SSPI;

Trusted Connection alternative syntax
Server=myServerAddress;Database=myDataBase;
Trusted_Connection=True;

Connecting to an SQL Server instance
Server=myServerName\theInstanceName;
Database=myDataBase;Trusted_Connection=True;

Trusted Connection from a CE device
Data Source=myServerAddress;Initial Catalog=myDataBase;
Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

November 14, 2011

Command Pattern

public class Invoker
{
private ArrayList ObjList = new ArrayList();
public Invoker()
{
ObjList.Add(new File());
ObjList.Add(new Open());
ObjList.Add(new Print());
ObjList.Add(new Exit());
}

public Command getCommand(string strCommand)
{
foreach (object obj in ObjList)
{
Command objCommand = (Command)obj;
if (objCommand.strCommand == strCommand)
{
return objCommand;
}
}
return null;
}

November 13, 2011

Singleton Pattern

public class Employee
{
private static Employee singleObj;
private string Name;
private int Age;

private Employee(string name, int age)
{
Name = name;
Age = age;
}
public string getName(){
return Name;
}
public int getAge()
{
return Age;
}
public static Employee getInstance(string name, int age)
{
if (singleObj == null)
{
singleObj = new Employee(name, age);
}

return singleObj;
}
}

Factory Pattern


public class Factory
{
private Object myObject;
public Object getInstance(int objType)
{
switch (objType)
{
case 1: myObject = new InvoiceWithHeader(); break;
case 2: myObject = new InvoiceWithoutHeader(); break;
case 3: myObject = new Book(); break;
case 4: myObject = new Magazine(); break;
case 5: myObject = new Employee(); break;
}

return myObject;
}
}

November 08, 2011

Partial Entity Context Object

public partial class NorthwindEntities{

public static void OpenContext(){
if(HttpContext.Current != null){
HttpContext.Current.Session['nwind'] = new NorthwindEntities();
}
}

public static NorthwindEntities CurrentContext{
get {
if (HttpContext.Current != null) {
return ( NorthwindEntities)HttpContext.Current.Session['nwind'];
}
return null;
}

public static void CloseContext() {
var ctx = CurrentContext;
if (ctx != null) {
ctx.Dispose();
HttpContext.Current.Session['nwind'] = null;
}
}

}
===============
public class TransactionFilterAttribute:ActionFilterAttribute {
public override OnActionExecuting( ..., ....){
NorthwindEntities.OpenContext();
}

public override OnActionExecuted(...., ....){
NorthwindEntities.CurrentContext.SaveChanges();
NorthwindEntities.CloseContext();
}
}
===============
[TransactionFilter]
public ActionResult Index(){
var p = NorthwindEntities.CurrentContext.Products.First();
p.UnitPrice = 444;

return View();
}

November 04, 2011

User Defined Functions

=> Scalar - value
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end

return @return
end
-----------------
print dbo.WhichContinent('USA')

select dbo.WhichContinent(Customers.Country), customers.*
from customers

create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))

insert into test (country)
values ('USA')

select * from test

- - - - - - - - - - - - - - - - -

Country Continent
------------ ---------------------
USA--------- North America


************************************************
=> Inline Table-value
CREATE FUNCTION CustomersByContinent
(@Continent varchar(30))
RETURNS TABLE
AS
RETURN
SELECT dbo.WhichContinent(Customers.Country) as continent,
customers.*
FROM customers
WHERE dbo.WhichContinent(Customers.Country) = @Continent
GO

SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')


************************************************
=> Multi-Statement Table-Value
CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )
RETURNS
@CustomersbyCountryTab table (
[CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),
[ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),
[Address] [nvarchar] (60), [City] [nvarchar] (15),
[PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),
[Phone] [nvarchar] (24), [Fax] [nvarchar] (24)
)
AS
BEGIN
INSERT INTO @CustomersByCountryTab
SELECT [CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM [Northwind].[dbo].[Customers]
WHERE country = @Country

DECLARE @cnt INT
SELECT @cnt = COUNT(*) FROM @customersbyCountryTab

IF @cnt = 0
INSERT INTO @CustomersByCountryTab (
[CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax] )
VALUES ('','No Companies Found','','','','','','','','')

RETURN
END
GO
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')

November 02, 2011

Add Icon on browser status

<link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/favicon.ico" />