MERGE statement is introduced in SQL Server 2008 that can greatly enhance the functionality for Data Warehouse and the staging servers.
The Merge statement allows you to perform DML actions on target table based on whether
or not a row matches information found in source table.
A common Merge scenario is moving data from one table to another.
Like User and UserHistory
User Table :
CREATE TABLE [dbo].[User]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Is_Active] [bit] NULL,
[CREATE_DATE] [date] NOT NULL
)
GO
ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CREATE_DATE] DEFAULT (getdate()) FOR [CREATE_DATE]
GO
AND
UserHistory Table is like:
CREATE TABLE UserHistory
(
HistoryId INT IDENTITY(1, 1),
Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Is_Active BIT NULL,
Create_Date DATE NOT NULL
)
------------------------------------------------------------------------------------
Now insert some data in user table and try running this query more than once and look
on the results that are produced by OUTPUT and $action clauses:
MERGE INTO UserHistory AS UH
USING [User] as U
ON U.Id = UH.Id
WHEN MATCHED THEN
UPDATE SET UH.Id = U.Id, UH.Name = U.Name
,UH.Is_Active = U.Is_Active, UH.Create_date = U.Create_date
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Age, Is_Active, Create_Date)
VALUES (Id, Name, Age, Is_Active, Create_Date)
WHEN NOT MATCHED BY SOURCE THEN
Update SET IS_ACTIVE = 0
Output $action, inserted.*;
-------------------------------------------------------------------------------------
You can perform insert, update and delete operation by matching the Source Table (User)
and the Target Table (UserHistory) in a single operation and can have the details about the
action from OUTPUT clause and using $action you can know the operation performed
Thursday, September 17, 2009
Wednesday, September 16, 2009
SQL Server 2008:Output Clause Sample
So with SQL Server 2008 we have output clause available inside the query that means we can access the inserted, deleted or updated value form the output clause inside a Stored Procedure and we need not to write a trigger
Ex.
Suppose we have a user table on inserting the data we need to insert the same value to a table type variable, we can accomplish this task as given below:
DECLARE @User TABLE (ID INT, Name VARCHAR(50), Age INT, Is_Active BIT, CREATE_DATE DATETIME)
INSERT INTO [User] (Name, Age, Is_Active, CREATE_DATE)
OUTPUT inserted.* INTO @User VALUES ('TESTING', '26', 1, GETDATE())
SELECT * FROM @User
Ex.
Suppose we have a user table on inserting the data we need to insert the same value to a table type variable, we can accomplish this task as given below:
DECLARE @User TABLE (ID INT, Name VARCHAR(50), Age INT, Is_Active BIT, CREATE_DATE DATETIME)
INSERT INTO [User] (Name, Age, Is_Active, CREATE_DATE)
OUTPUT inserted.* INTO @User VALUES ('TESTING', '26', 1, GETDATE())
SELECT * FROM @User
Tuesday, June 30, 2009
Create class variables and properties from database table
Use the below given query to create variables and properties from your schema.
Just replace the @tableName variable with the table name for which you want to generate the variables and properties.
This makes use of systypes and syscolumns system defined views to get the name of columns from the table and their type. Also add more types as per your requirement.
-----------------------------------------------------------------------------
declare @tableName varchar(50)
set @tableName = 'tbTest'
SELECT 'private ' + (
case systypes.name
when 'varchar' then 'string'
when 'decimal' then 'decimal'
when 'datetime' then 'DateTime'
when 'int' then 'int'
else systypes.name end) + ' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ';'
FROM syscolumns inner join systypes on
syscolumns.xtype = systypes.xtype
where id = Object_ID(@tableName)
order by colorder
SELECT 'public ' + (
case systypes.name
when 'varchar' then 'string'
when 'decimal' then 'decimal'
when 'datetime' then 'DateTime'
when 'int' then 'int'
else systypes.name end)+ ' ' + syscolumns.name + char(10) +
'{' + char(10) + 'get { return' +
' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + '; }'
+ char(10) + 'set { _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ' = value; }' + char(10) + '}'
FROM syscolumns inner join systypes on
syscolumns.xtype = systypes.xtype
where id = Object_ID(@tableName)
order by colorder
-----------------------------------------------------------------------------
Thursday, June 18, 2009
Saturday, March 14, 2009
Power of Reflection Emit
Some time ago I came across a situation where the end user of my application was willing to add new types at his own. Obviously I was not having any other solution rather using Reflection Emit.
It was great creating new types, dynamic methods at run time.
So as I did earlier similarly I will produce some code (to create new type (compiled class) with some properties and methods) out here with a little description.
But before working with Emit you should look on the MSIL Disassembler to know how the things looks alike after compilation and before JIT compiler processing.
------------------------------ Creating A Whole New Type ------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using DataFramework;
using System.Data;
namespace Generator
{
public class ClassGenerator
{
public ClassGenerator()
{
}
public void GenerateClass(string assemblyName, string className, List fieldWithType)
{
AssemblyName an = new AssemblyName(assemblyName);
AppDomain ad = AppDomain.CurrentDomain; // Use same app domain otherwise to use
// the generated classes you need to use
// remoting like - object handle unwrap
AssemblyBuilder ab = ad.DefineDynamicAssembly(an, AssemblyBuilderAccess.RunAndSave, AppDomain.CurrentDomain.BaseDirectory);
ModuleBuilder mb = ab.DefineDynamicModule(an.Name, "CustomClasses.dll");
TypeBuilder tb = mb.DefineType(className, TypeAttributes.Class | TypeAttributes.Public);
ConstructorInfo ci = tb.DefineDefaultConstructor(MethodAttributes.Public);
foreach (FieldStructure fieldName in fieldWithType)
{
Type type = GetType(fieldName.type);// Type.GetType(fieldWithType[fieldName], false, true);
FieldBuilder fb = tb.DefineField(fieldName.name, type, FieldAttributes.Private);
CreateProperty(tb, type, fb, fieldName.name);
}
Type generatedType = tb.CreateType();
ab.Save("CustomClasses." + className + ".dll");
}
#region Create Property for field
private void CreateProperty(TypeBuilder tb, Type fieldType, FieldBuilder fb, string key)
{
PropertyBuilder pb = tb.DefineProperty(SystemConstants.GetPropertyName(key), System.Reflection.PropertyAttributes.HasDefault, fieldType, Type.EmptyTypes);
MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;
MethodBuilder mbGet = tb.DefineMethod(SystemConstants.getPrefix + key, getSetAttr, fieldType, Type.EmptyTypes);
ILGenerator getIL = mbGet.GetILGenerator();
// For an instance property, argument zero is the instance. Load the
// instance, then load the private field and return, leaving the
// field value on the stack.
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, fb);
getIL.Emit(OpCodes.Ret);
MethodBuilder mbSet = tb.DefineMethod(SystemConstants.setPrefix + key, getSetAttr, null, new Type[] { fieldType });
ILGenerator setIL = mbSet.GetILGenerator();
// Load the instance and then the numeric argument, then store the
// argument in the field.
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, fb);
setIL.Emit(OpCodes.Ret);
// Last, map the "get" and "set" accessor methods to the
// PropertyBuilder. The property is now complete.
pb.SetGetMethod(mbGet);
pb.SetSetMethod(mbSet);
}
#endregion
#region Get type based on string representation
private Type GetType(string typeName)
{
switch (typeName.ToLower())
{
case "string":
return typeof(String);
case "int32":
return typeof(Int32);
case "long":
return typeof(long);
case "float":
return typeof(float);
case "decimal":
return typeof(decimal);
default:
return typeof(object);
}
}
#endregion
}
}
----------------------- Helper Classes (SystemConstants)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;
namespace Generator
{
public class SystemConstants
{
public const string getPrefix = "get_";
public const string setPrefix = "set_";
public const string variablePrefix = "m_";
public const string idVariableForClasses = "ID";
public const string idVariableType = "int32";
private static bool IsFieldDicContainsId(Dictionary fieldWithType)
{
foreach (string fieldName in fieldWithType.Keys)
{
if (fieldName.ToUpper() == idVariableForClasses)
{
return true;
}
}
return false;
}
public static Dictionary GetFinalDictionary(Dictionary fieldWithType)
{
if (IsFieldDicContainsId(fieldWithType))
{
if (fieldWithType.ContainsKey(idVariableForClasses))
{
fieldWithType.Remove(idVariableForClasses);
}
}
return fieldWithType;
}
internal static string GetPropertyName(string key)
{
// Implement Logic to create property like m_name ==> Name right now its NAME
return key.ToUpper();
}
}
}
----------------------- Helper Structure (FieldStructure)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;
namespace Generator
{
public struct FieldStructure
{
public string name;
public string type;
public bool isIdentity;
public bool isReference;
public string referenceClassName;
public string referenceColumnName;
}
}
--------------------------------- Code to be called from UI ------------------------------------
ClassGenerator cg = new ClassGenerator();
FieldStructure fStructA = new FieldStructure();
fStructA.name = "name";
fStructA.type = "string";
fStructA.isIdentity = false;
FieldStructure fStructB = new FieldStructure();
fStructB.name = "id";
fStructB.type = "int32";
fStructB.isIdentity = true;
List allFields = new List();
allFields.Add(fStructA);
allFields.Add(fStructB);
cg.GenerateClass("test", "Test", allFields);
}
Thats it. Try to use it, a .dll file will be saved in your application root directory, but beware if you are using web application and you intended to save any .dll file in the bin folder the application domain will reload and all your sessions will be lost as one of the application domain reloading is any change in the bin directory is.
For more reading you can look at this msdn link.
Let me know for your queries.
Shashank.... enjoy coding ....
It was great creating new types, dynamic methods at run time.
So as I did earlier similarly I will produce some code (to create new type (compiled class) with some properties and methods) out here with a little description.
But before working with Emit you should look on the MSIL Disassembler to know how the things looks alike after compilation and before JIT compiler processing.
------------------------------ Creating A Whole New Type ------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using DataFramework;
using System.Data;
namespace Generator
{
public class ClassGenerator
{
public ClassGenerator()
{
}
public void GenerateClass(string assemblyName, string className, List
{
AssemblyName an = new AssemblyName(assemblyName);
AppDomain ad = AppDomain.CurrentDomain; // Use same app domain otherwise to use
// the generated classes you need to use
// remoting like - object handle unwrap
AssemblyBuilder ab = ad.DefineDynamicAssembly(an, AssemblyBuilderAccess.RunAndSave, AppDomain.CurrentDomain.BaseDirectory);
ModuleBuilder mb = ab.DefineDynamicModule(an.Name, "CustomClasses.dll");
TypeBuilder tb = mb.DefineType(className, TypeAttributes.Class | TypeAttributes.Public);
ConstructorInfo ci = tb.DefineDefaultConstructor(MethodAttributes.Public);
foreach (FieldStructure fieldName in fieldWithType)
{
Type type = GetType(fieldName.type);// Type.GetType(fieldWithType[fieldName], false, true);
FieldBuilder fb = tb.DefineField(fieldName.name, type, FieldAttributes.Private);
CreateProperty(tb, type, fb, fieldName.name);
}
Type generatedType = tb.CreateType();
ab.Save("CustomClasses." + className + ".dll");
}
#region Create Property for field
private void CreateProperty(TypeBuilder tb, Type fieldType, FieldBuilder fb, string key)
{
PropertyBuilder pb = tb.DefineProperty(SystemConstants.GetPropertyName(key), System.Reflection.PropertyAttributes.HasDefault, fieldType, Type.EmptyTypes);
MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;
MethodBuilder mbGet = tb.DefineMethod(SystemConstants.getPrefix + key, getSetAttr, fieldType, Type.EmptyTypes);
ILGenerator getIL = mbGet.GetILGenerator();
// For an instance property, argument zero is the instance. Load the
// instance, then load the private field and return, leaving the
// field value on the stack.
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, fb);
getIL.Emit(OpCodes.Ret);
MethodBuilder mbSet = tb.DefineMethod(SystemConstants.setPrefix + key, getSetAttr, null, new Type[] { fieldType });
ILGenerator setIL = mbSet.GetILGenerator();
// Load the instance and then the numeric argument, then store the
// argument in the field.
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, fb);
setIL.Emit(OpCodes.Ret);
// Last, map the "get" and "set" accessor methods to the
// PropertyBuilder. The property is now complete.
pb.SetGetMethod(mbGet);
pb.SetSetMethod(mbSet);
}
#endregion
#region Get type based on string representation
private Type GetType(string typeName)
{
switch (typeName.ToLower())
{
case "string":
return typeof(String);
case "int32":
return typeof(Int32);
case "long":
return typeof(long);
case "float":
return typeof(float);
case "decimal":
return typeof(decimal);
default:
return typeof(object);
}
}
#endregion
}
}
----------------------- Helper Classes (SystemConstants)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;
namespace Generator
{
public class SystemConstants
{
public const string getPrefix = "get_";
public const string setPrefix = "set_";
public const string variablePrefix = "m_";
public const string idVariableForClasses = "ID";
public const string idVariableType = "int32";
private static bool IsFieldDicContainsId(Dictionary
{
foreach (string fieldName in fieldWithType.Keys)
{
if (fieldName.ToUpper() == idVariableForClasses)
{
return true;
}
}
return false;
}
public static Dictionary
{
if (IsFieldDicContainsId(fieldWithType))
{
if (fieldWithType.ContainsKey(idVariableForClasses))
{
fieldWithType.Remove(idVariableForClasses);
}
}
return fieldWithType;
}
internal static string GetPropertyName(string key)
{
// Implement Logic to create property like m_name ==> Name right now its NAME
return key.ToUpper();
}
}
}
----------------------- Helper Structure (FieldStructure)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;
namespace Generator
{
public struct FieldStructure
{
public string name;
public string type;
public bool isIdentity;
public bool isReference;
public string referenceClassName;
public string referenceColumnName;
}
}
--------------------------------- Code to be called from UI ------------------------------------
ClassGenerator cg = new ClassGenerator();
FieldStructure fStructA = new FieldStructure();
fStructA.name = "name";
fStructA.type = "string";
fStructA.isIdentity = false;
FieldStructure fStructB = new FieldStructure();
fStructB.name = "id";
fStructB.type = "int32";
fStructB.isIdentity = true;
List
allFields.Add(fStructA);
allFields.Add(fStructB);
cg.GenerateClass("test", "Test", allFields);
}
Thats it. Try to use it, a .dll file will be saved in your application root directory, but beware if you are using web application and you intended to save any .dll file in the bin folder the application domain will reload and all your sessions will be lost as one of the application domain reloading is any change in the bin directory is.
For more reading you can look at this msdn link.
Let me know for your queries.
Shashank.... enjoy coding ....
Tuesday, May 06, 2008
Microsoft Product Code Names
Know Microsoft Product Code Names:
Current
CodeNames
| Avalon | Windows Presentation Foundation, WPF is the presentation subsystem class libraries in
|
| Aero | UI in Windows |
| | Windows Media Platform for Blackcomb |
| | New Set of asynchonous technologies for web applications enabling richer user experiences |
| Blackcomb | Successor to |
| | Base collection of technologies which largely evolved into the core technologies in 1.Vista. |
| Cider | VS Designer for Windows Presentation Foundation |
| Longhorn | Windows |
| Monad | Longorn Command Shell. Msh is the new |
| | Office Live Communication Server 2005 |
| Whidbey | Visual Studio .NET 2005 |
| Orcas | Visual Studio 2008 |
| | Future version of Visual Studio (after the Orcas release). |
| | VS.NET 2005 Design Tool Suite |
|
| The set of next-generation managed APIs provided by Microsoft |
| | SQL Server 2005 scripting and automation environment for Windows |
| Acrylic | Microsoft Expression Graphic Designer. A new professional graphics software. |
| Sparkle | Microsoft Expression Interactive Designer. |
| Quartz | Microsoft Expression Web Designer. |
|
| Next-generation Unified Storage Sub-system for Windows. |
Operating System Components / Versions
| Avalon | Windows Presentation Foundation, WPF is the presentation subsystem class libraries in
|
| Asteroid | Windows 2000 SP1 |
| Aero | UI in Windows |
| Bearpaw | Windows Terminal Services for Windows Server 2003 |
| Blackcomb | Is the server version of |
| Bobcat | Windows Server 2003 Small Business Edition |
| | Base collection of technologies which largely evolved into the core technologies in |
| | Windows 95 |
| Daytona | Windows NT 3.5 |
| | Windows 95 OSR 2 |
| Freestyle | Windows XP |
| Frosting | Windows 95 Plus! |
| Harmony | Windows XP |
| Impala | Windows NT4 Embedded |
| Indigo | Communications services used in |
| Janus | Windows 3.1 |
| Lonestar | Windows XP Tablet PC Edition 2005 |
| Longhorn | Windows |
| Mantis | Windows XP Embedded 5.1 |
| | Windows 98 |
| | Batch Management tools for Windows NT/Windows 2000 Server |
| Millenium/Georgia | Windows ME |
| Mir | Internal Mercury release |
| NAS 3.0 | Windows Storage Server 2003 |
| | Internet Explorer 4, for Windows 95 |
| | Cancelled successor to Windows 98 |
| Newshell | Alpha shell update for Windows NT 3.x |
| Panther | Abandoned |
| Slalom | |
| | Windows for Workgroups 3.1 |
| Springboard | Backporting of Vista technologies to Windows XP
|
| Snowball | Windows for Workgroups 3.11 |
| Sundown | Windows XP 64-bit |
| SUR | Patch to Windows 3.51 |
| Symphony | Windows XP |
| Whistler | Windows XP |
| Windows | Windows 1.0 |
| Windows 286 | Windows 2.0 |
|
| The set of next-generation managed APIs provided by Microsoft |
Server Products
SQL
| Starfighter | SQL Server 6.0 |
| Hydra | SQL Server 6.5 |
| Sphinx | SQL Server 7.0 |
| Plato | SQL Server 7.0 OLAP Services |
| | SQL Server 2000 |
| | SQL Server 2000 64-bit Edition |
| Rosetta | SQL Server 2000 Reporting Services |
| Aurum | SQL Server 2000, data mining toolkit |
| | SQL Server 2005 |
| Katmai/Acadia | SQL Server 2008 |
Exchange
| Osmium | Exchange Server 5.5 |
| Platinum | Exchange Server 2002 |
| Titanium | Exchange Server 2003 |
| Kodiak | Abandoned successor to Exchange 2003. |
| Magma | Embedded Exchange |
SharePoint
| Tahoe | Sharepoint Server 2001 |
| Matrix | Sharepoint Portal Server v2 |
BizTalk
| Latinum |
BizTalk 2000 |
| Bizet |
BizTalk 2002 |
Misc
| Falcon | Message Queue Server (MSMQ) |
| Viper | Transaction Server 2.0 |
| Hydra | Windows NT Terminal Services |
| | Host Integration Server |
| Cedar | COM Transaction Integrator for CICS and IMS (COMTI) |
| Wolfpack | Cluster Server |
| | Office Live Communication Server 2005 |
| Hermes | System Management Server |
| Kahiltna | Commerce Server 2.0 |
| Comet | ISA 2000 |
Mobile PC
| Lonestar | Windows XP Tablet PC Edition 2005 |
| Haiku | Post-2007 mobile PC hardware design |
Mobile Platforms
| Alder | Windows CE 2.1 |
| Apolllo | Windows CE for Auto PC |
| Axe | Embedded Toolkit for Windows CE 2.0 and 2.1 |
| Brich | Windows CE 2.11 and 2.12 |
| Cedar | Windows CE 3.0 |
| Chainsaw | Windows CE Platform Builder 3.0 |
| Galileo | Windows CE for Handheld PC 2000 |
| Goldeneye | Windows CE for AutoPC |
| Gryphon | Windows CE 2.0 for palm-sized PCs |
| Hai Ku | Multimedia Extension for Windows |
| Hermes | Windows CE for webphones |
| Jameson | Windows CE.NET Update |
| Jupiter | Windows CE for Handheld PC Pro 3.0 (or Macallan Windows CE.NET Corporate Edition??) |
| Laguna | Upcoming version of SQL Server, for Windows CE |
| Windows CE for Media2G0 | |
| Mercury | Windows CE for Hanheld PC |
| Merlin | Pocket PC 2002 (or Windows CE 2002??) |
| Mira | Windows CE for Smart Display Devices |
| Orion | Windows CE for Chinese Handheld PC |
| Ozone | Pocket PC 2003 |
| Pegasus | Windows CE 1.0 |
| Rapier | Windows CE for
PocketPC 2000 |
| Stinger | Smartphone 2002 (or Windows CE for Smartphones 1.0??) |
| Starlite | .NET Compact Framework 1.0 |
| Talisker | Windows CE .NET |
| Tazz | Microsoft Phone |
| Visine | Novell Netware Migration Tool |
| Venus | Windows CE for webTV |
| Wyvern | Windows CE for Color Palm PC |
Miscellaneous
| Trident | Dynamic HTML |
| Springboard | Microsoft's security excellence and review project, who's fruits were deployed via Office 2003 SP1, Windows XP SP1 and Windows Server 2003 SP1 |
| Palladium | An on-again off-again secure runtime environment called Next Generation Secure Computing Base (NGSCB |
| Services for wireless connections to Exchange | |
| Avalanche | Internet-Information-Services 5.0 |
| Argo | Query-Services for SQL-Server 2000 |
| | Windows Media Platform for Blackcomb |
| Athena | Outlook Express |
| | New prototype PC design (with HP) |
| Blackbird | MSN SDK |
| Blizzard | Business oriented Set of Services in .NET My Services |
| Basecamp | VPN dial-up networking update (PPTP) for win95 |
| | Smart-Card Technologies |
| Cascade | Windows Active-Directory |
| Cayman | NetMeeting 3.0 Version 3.01 4.4.3400 |
| Chrome | ChromeEffects (GUI-Feature) |
| Cirrus | Access 1.0 |
| Comet | Network-Tools for Windows 2000 |
| | Windows Media-Technologies 9.0 |
| Coyote | Distributed Views for SQL-Server 2000 |
| | Windows Installer 1.1 Version 1.1 |
| Dart | Setup for Oracle, SQL-Server ODBC-driver |
| Database-Design & Query-Tools | |
| | Active Server Pages |
| Europa | New GUI for the MSN-Community |
| Fahrenheit | A defunct collaboration between Microsoft & SGI Graphics to combine Direct3D and OpenGL by creating a unified 3D API. |
| Falcon | Windows Messaging Queuing Server |
| Fusion | Technologies for DLL improvements |
| Gemini | Succesor GUI "Europa" for MSN-Community |
| | Internet-Information-Server 1.0 Version 3.0 |
| Grizzly | Workflow-Designer for SQL-Server 2000 |
| Hailstorm | XML Message Interface |
| Hailstorm | .NET My Services |
| Internet Explorer Enhanced Security (Windows Server 2003) | |
| Hydra | Windows NT Terminal Services |
| | Preliminary/alpha version of the next Windows-Longhorn |
| | JAVA-Engine for Internet Explorer 3.0 |
| Kagera (Kajera??) | OLE-DB Provider for ODBC-Data |
| Kahuna | Windows Live Mail |
| | Internet-Information-Server 4.0 |
| Lightning | .NET Common Language Runtime (CLR) |
| Luna | Theme technology for Windows XP |
| | OLE-Provider-Services for |
| Magic Carpet | Passport-Technologien |
| Marvel | Microsoft Network (MSN) |
| Merlin | Microsoft® Internet Explorer for Dreamcast (cancelled circa 1999). |
| Mercury | DUN 1.2 update for Win95 |
| Monad | Longorn Command Shell. Msh is the new scripting and automation environment for Windows |
| NGWS | (Next Generation Windows/Web Services) .Net |
| | Microsoft Commercial Internet System (MCIS) |
| Oprah | Netmeeting |
| | OLE-DB |
| O'hare | Internet Explorer 1.00. |
| Plato | OLAP-Server |
| Project 42 | (from Lightning) .NET Common Language Runtime (CLR) |
| Ren | Outlook |
| Quartz | Direct Show |
| Sideshow | Task Shelf of Longhorn (MS research) |
| Slate | Microsoft Management-Console (MMC) |
| Spark | Integration of Windows
SharePoint Services and CMS |
| Steelhead | Windows NT Routing & Remote-Access Services (RAS) |
| | SharedView Beta2 |
| Talisman | 3D Graphic- und Multimedia Architecture |
| Tensor | OLE-DB Extensions for OLAP |
| Touchdown | Outlook 97 (Also mentioned:Public folders for MS Exchange) |
| Trident | Dynamic HTML (DHTML) |
| Tungsten | Microsoft Rights Management Services (RMS) |
| Whisper | Speech Recognition (speech-to-text) |
| Xenon | Xbox 360 |
Read Write and Search XML Document in ASP.NET
You often need to read, write or search some phrase in an XML document and you can use XmlDocument or any class among a lot present in .NET framework but what
if you wish to bind your business objects the list of business objects to the XmlDocument.
You can use the code below to do this.
What you need to do for this is to keep the name of properties of your business objects same as the name of attributes are present in the XML file you wanna load.
This class make use of XmlReader and XmlWriter base classes provided by .NET framework.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Xml;
using System.Reflection;
using System.IO;
///
/// XMLDataHelper provides way to access nodes and bind them directly to custom objects.
/// Also Save XMLFragment at the end of the Xml File
///
public class XMLDataHelper : IDisposable
{
#region Variables
private static string _baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
private static string _documentDirectory = _baseDirectory + "App_Data/";
protected string _query;
protected XmlReader xmlReader;
protected XmlWriter xmlWriter;
#endregion
#region constructors
public XMLDataHelper()
{
string xmlFile = ConfigurationManager.AppSettings["XmlFileName"].ToString();
xmlReader = XmlReader.Create(xmlFile);
}
public XMLDataHelper(string xmlFile)
{
xmlReader = XmlReader.Create(xmlFile);
}
public XMLDataHelper(string xmlFile, string xmlFileForWriter)
{
xmlReader = XmlReader.Create(xmlFile);
xmlWriter = XmlWriter.Create(xmlFileForWriter);
}
#endregion
#region Load Object
public T LoadObject(string nodeName, Dictionary attrFieldsLookUp) where T : new()
{
T obj = new T();
if (string.IsNullOrEmpty(nodeName))
{
throw new Exception("Node not specified.");
}
bool IsRequestedItem = true;
PropertyInfo[] objPropInfo = obj.GetType().GetProperties();
while (xmlReader.ReadToFollowing(nodeName))
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
XmlReader nodeXmlReader = xmlReader.ReadSubtree();
while (nodeXmlReader.Read())
{
IsRequestedItem = false;
foreach (string key in attrFieldsLookUp.Keys)
{
if (attrFieldsLookUp[key.ToUpper()] == nodeXmlReader.GetAttribute(key))
{
IsRequestedItem = true;
}
else
{
IsRequestedItem = false;
break;
}
}
if (IsRequestedItem)
{
GetObjectFromReaderNode(ref obj, ref nodeXmlReader, ref objPropInfo, true, nodeName);
}
}
}
}
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
}
return obj;
}
#endregion
#region GetObjectFromReaderNode
private void GetObjectFromReaderNode(ref T obj, ref XmlReader nodeXmlReader, ref PropertyInfo[] objPropInfo, bool AreElementsReq, string nodeName)
{
Dictionary xmlFileValues = new Dictionary();
while (nodeXmlReader.MoveToNextAttribute())
{
xmlFileValues.Add(nodeXmlReader.Name.ToUpper(), nodeXmlReader.Value);
}
foreach (PropertyInfo pi in objPropInfo)
{
if (pi.Name == "DEPTH")
{
pi.SetValue(obj, (object)(nodeXmlReader.Depth + 1), null);
}
else
{
string currAttrVal;
if (xmlFileValues.TryGetValue(pi.Name.ToUpper(), out currAttrVal))
{
if (pi.PropertyType == typeof(Boolean))
{
pi.SetValue(obj, Convert.ToBoolean(currAttrVal.ToLower()), null);
}
else
{
pi.SetValue(obj, (object)currAttrVal, null);
}
}
}
}
if (AreElementsReq)
{
bool firstOccur = true;
while (xmlReader.Read() && firstOccur)
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
string elementName = nodeXmlReader.Name;
// To Stop Retrieving Inner Elements value
if (elementName == nodeName)
{
firstOccur = false;
}
string currElementVal;
foreach (PropertyInfo pi in objPropInfo)
{
if (pi.Name.ToUpper() == elementName.ToUpper())
{
pi.SetValue(obj, (object)nodeXmlReader.ReadElementString(), null);
}
}
}
}
}
}
#endregion
#region LoadList
public List LoadList(string nodeName, bool includeElement) where T : new()
{
T obj = new T();
List retList = new List();
if (string.IsNullOrEmpty(nodeName))
{
throw new Exception("Node not specified.");
}
bool IsRequestedItem = true;
PropertyInfo[] objPropInfo = obj.GetType().GetProperties();
while (xmlReader.Read())
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
if (xmlReader.Name == nodeName)
{
obj = new T();
GetObjectFromReaderNode(ref obj, ref xmlReader, ref objPropInfo, includeElement, nodeName);
retList.Add(obj);
}
}
}
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
}
return retList;
}
#endregion
#region SaveXmlFragment
// Summary:
// To Save XmlFragment at the end of the file
public void SaveXmlFragment(string rawXmlFragment, string rootItem)
{
try
{
using (xmlReader)
{
while (xmlReader.Read())
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.Name == rootItem)
{
xmlWriter.WriteStartElement(rootItem);
xmlWriter.WriteRaw(xmlReader.ReadInnerXml());
xmlWriter.WriteRaw(rawXmlFragment);
xmlWriter.WriteEndElement();
}
else if (xmlReader.NodeType == XmlNodeType.Comment || xmlReader.NodeType == XmlNodeType.Text || xmlReader.NodeType == XmlNodeType.Whitespace)
{
}
else
{
xmlWriter.WriteNode(xmlReader, true);
}
}
xmlWriter.Flush();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
xmlReader.Close();
xmlWriter.Close();
FileInfo fi = new FileInfo(_documentDirectory + "SiteData.xml");
File.Copy(_documentDirectory + "_tempSiteData.xml", fi.FullName, true);
}
}
#endregion
#region Search Document
// Summary:
// To Save XmlFragment at the end of the file
public SortedDictionary> SearchDocument(string searchText, string nodeName, string propertyNameToLookFor) where T : new()
{
List searchWords = new List();
string[] allWords = searchText.Split(' ');
for (int i = 0; i <>> retDictionary = new SortedDictionary>();
T obj = new T();
PropertyInfo[] piAll = obj.GetType().GetProperties();
List retList = this.LoadList(nodeName, true);
foreach (T item in retList)
{
string content = "";
int relevance = 0;
foreach (PropertyInfo pi in piAll)
{
if (pi.Name == propertyNameToLookFor)
{
content = Convert.ToString(pi.GetValue(item, null));
}
}
string[] contentSplitted = content.Split(' ');
foreach (string word in searchWords)
{
for (int i = 0; i <> relCorrList = new List();
relCorrList.Add(item);
retDictionary.Add(relevance, relCorrList);
}
else
{
retDictionary[relevance].Add(item);
}
}
}
return retDictionary;
}
#endregion
#region IDisposable Members
public void Dispose()
{
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
xmlWriter.Close();
}
}
#endregion
}
if you are getting it difficult to understand or have some queries you can put a reply or mail me at
shashank.abes at gmail.com
if you wish to bind your business objects the list of business objects to the XmlDocument.
You can use the code below to do this.
What you need to do for this is to keep the name of properties of your business objects same as the name of attributes are present in the XML file you wanna load.
This class make use of XmlReader and XmlWriter base classes provided by .NET framework.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Xml;
using System.Reflection;
using System.IO;
///
/// XMLDataHelper provides way to access nodes and bind them directly to custom objects.
/// Also Save XMLFragment at the end of the Xml File
///
public class XMLDataHelper : IDisposable
{
#region Variables
private static string _baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
private static string _documentDirectory = _baseDirectory + "App_Data/";
protected string _query;
protected XmlReader xmlReader;
protected XmlWriter xmlWriter;
#endregion
#region constructors
public XMLDataHelper()
{
string xmlFile = ConfigurationManager.AppSettings["XmlFileName"].ToString();
xmlReader = XmlReader.Create(xmlFile);
}
public XMLDataHelper(string xmlFile)
{
xmlReader = XmlReader.Create(xmlFile);
}
public XMLDataHelper(string xmlFile, string xmlFileForWriter)
{
xmlReader = XmlReader.Create(xmlFile);
xmlWriter = XmlWriter.Create(xmlFileForWriter);
}
#endregion
#region Load Object
public T LoadObject
{
T obj = new T();
if (string.IsNullOrEmpty(nodeName))
{
throw new Exception("Node not specified.");
}
bool IsRequestedItem = true;
PropertyInfo[] objPropInfo = obj.GetType().GetProperties();
while (xmlReader.ReadToFollowing(nodeName))
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
XmlReader nodeXmlReader = xmlReader.ReadSubtree();
while (nodeXmlReader.Read())
{
IsRequestedItem = false;
foreach (string key in attrFieldsLookUp.Keys)
{
if (attrFieldsLookUp[key.ToUpper()] == nodeXmlReader.GetAttribute(key))
{
IsRequestedItem = true;
}
else
{
IsRequestedItem = false;
break;
}
}
if (IsRequestedItem)
{
GetObjectFromReaderNode
}
}
}
}
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
}
return obj;
}
#endregion
#region GetObjectFromReaderNode
private void GetObjectFromReaderNode
{
Dictionary
while (nodeXmlReader.MoveToNextAttribute())
{
xmlFileValues.Add(nodeXmlReader.Name.ToUpper(), nodeXmlReader.Value);
}
foreach (PropertyInfo pi in objPropInfo)
{
if (pi.Name == "DEPTH")
{
pi.SetValue(obj, (object)(nodeXmlReader.Depth + 1), null);
}
else
{
string currAttrVal;
if (xmlFileValues.TryGetValue(pi.Name.ToUpper(), out currAttrVal))
{
if (pi.PropertyType == typeof(Boolean))
{
pi.SetValue(obj, Convert.ToBoolean(currAttrVal.ToLower()), null);
}
else
{
pi.SetValue(obj, (object)currAttrVal, null);
}
}
}
}
if (AreElementsReq)
{
bool firstOccur = true;
while (xmlReader.Read() && firstOccur)
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
string elementName = nodeXmlReader.Name;
// To Stop Retrieving Inner Elements value
if (elementName == nodeName)
{
firstOccur = false;
}
string currElementVal;
foreach (PropertyInfo pi in objPropInfo)
{
if (pi.Name.ToUpper() == elementName.ToUpper())
{
pi.SetValue(obj, (object)nodeXmlReader.ReadElementString(), null);
}
}
}
}
}
}
#endregion
#region LoadList
public List
{
T obj = new T();
List
if (string.IsNullOrEmpty(nodeName))
{
throw new Exception("Node not specified.");
}
bool IsRequestedItem = true;
PropertyInfo[] objPropInfo = obj.GetType().GetProperties();
while (xmlReader.Read())
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.NodeType != XmlNodeType.Document)
{
if (xmlReader.Name == nodeName)
{
obj = new T();
GetObjectFromReaderNode
retList.Add(obj);
}
}
}
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
}
return retList;
}
#endregion
#region SaveXmlFragment
// Summary:
// To Save XmlFragment at the end of the file
public void SaveXmlFragment(string rawXmlFragment, string rootItem)
{
try
{
using (xmlReader)
{
while (xmlReader.Read())
{
if (xmlReader.NodeType == XmlNodeType.Element && xmlReader.Name == rootItem)
{
xmlWriter.WriteStartElement(rootItem);
xmlWriter.WriteRaw(xmlReader.ReadInnerXml());
xmlWriter.WriteRaw(rawXmlFragment);
xmlWriter.WriteEndElement();
}
else if (xmlReader.NodeType == XmlNodeType.Comment || xmlReader.NodeType == XmlNodeType.Text || xmlReader.NodeType == XmlNodeType.Whitespace)
{
}
else
{
xmlWriter.WriteNode(xmlReader, true);
}
}
xmlWriter.Flush();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
xmlReader.Close();
xmlWriter.Close();
FileInfo fi = new FileInfo(_documentDirectory + "SiteData.xml");
File.Copy(_documentDirectory + "_tempSiteData.xml", fi.FullName, true);
}
}
#endregion
#region Search Document
// Summary:
// To Save XmlFragment at the end of the file
public SortedDictionary
{
List
string[] allWords = searchText.Split(' ');
for (int i = 0; i <>> retDictionary = new SortedDictionary
T obj = new T();
PropertyInfo[] piAll = obj.GetType().GetProperties();
List
foreach (T item in retList)
{
string content = "";
int relevance = 0;
foreach (PropertyInfo pi in piAll)
{
if (pi.Name == propertyNameToLookFor)
{
content = Convert.ToString(pi.GetValue(item, null));
}
}
string[] contentSplitted = content.Split(' ');
foreach (string word in searchWords)
{
for (int i = 0; i <> relCorrList = new List
relCorrList.Add(item);
retDictionary.Add(relevance, relCorrList);
}
else
{
retDictionary[relevance].Add(item);
}
}
}
return retDictionary;
}
#endregion
#region IDisposable Members
public void Dispose()
{
if (xmlReader.ReadState != ReadState.Closed)
{
xmlReader.Close();
xmlWriter.Close();
}
}
#endregion
}
if you are getting it difficult to understand or have some queries you can put a reply or mail me at
shashank.abes at gmail.com
Some Useful Sql Server Queries
Some Useful Sql Server Queries
Get Number Of Days in a Month:
SQL Query to get No of Days in a month:-
SELECT DAY(DATEADD(MONTH, 1, GETDATE()) - DAY(DATEADD(MONTH, 1, GETDATE())))
Reading XMl from Sql Server
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = Full Path To Xml \General.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
END
SELECT @FileContents as FileContents
DROP TABLE #tempXML
Grouping Records in Sql Server 2005
declare @NO_OF_PARTITION int
set @NO_OF_PARTITION = 10
SELECT c.First_Name, c.Last_Name , C.DIVISION_ID
,NTILE(@NO_OF_PARTITION)
OVER(PARTITION BY DIVISION_ID ORDER BY DIVISION_ID ASC) AS 'GROUP_ID'
FROM EMPLOYEE C
WHERE division_id = 2
ORDER BY DIVISION_ID ASC
Replacement to IN and NOT IN from INTERSECT and EXCEPT
SELECT EMPLOYEE_ID FROM EMPLOYEE
INTERSECT
SELECT EMPLOYEE_ID FROM LEAVE_DETAILS
SELECT EMPLOYEE_ID FROM EMPLOYEE
EXCEPT
SELECT EMPLOYEE_ID FROM LEAVE_DETAILS
Using NTILE to tile (group) your records
SELECT c.First_Name, c.Last_Name , C.DIVISION_ID
,NTILE(2)
OVER(PARTITION BY DIVISION_ID ORDER BY DIVISION_ID ASC) AS 'GROUP_ID'
FROM EMPLOYEE C
WHERE EMPLOYEE_ID < 13
ORDER BY DIVISION_ID ASC
Get Serial No. with the query result (ROW_NUMBER function SQL Server 2005)
SELECT ROW_NUMBER() OVER (ORDER BY DIVISION_ID ASC) AS ROWID, * FROM EMPLOYEE
Using Pivot Keyword SQL Server 2005
CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO
INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO
SELECT Y,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT Y, Q, Sales
FROM SalesByQuarter) s
(
SUM(Sales)
FOR Q IN ([1],[2],[3],[4])
) p
ORDER BY [Y]
GO
DROP TABLE dbo.SalesByQuarter
GO
Saturday, April 05, 2008
Generic List Predicates
Have you ever considered of searching on a list based upon a single value, for example we have a List
There are many ways to search for it, we can walk through a foreach loop, or call our database, or whatsoever But we also have functions called Find and FindAll that expects a function the returns a bool value.
Now how we can use it. Suppose we are having a list of Employees say :
List
and we want to search an employee that contains EmpCode = 0.
We can write a function like:
int _empCode = 4;
Employee empWithCode = lstEmployee.FindAll(new Predicate
private void GetSingleEmployeeForCode
{
if (emp.EmpCode == _empCode)
{
return true;
}
return false;
}
This will return us the employee with EmpCode 4.
Now how can we search for items that may have same value for different objects in list.
We have function like :
List
maleEmpList = lstEmployee.FindAll(new Predicate
private void GetMaleEmployees (Employee emp)
{
if (emp.Gender == "Male")
{
return true;
}
return false;
}
How to perform Sort operation in List Generic List
Today I am coming with a small but efficient code for performing Sort operation in List
self understandable.
List
braList = _branManager.GetBranchList();
braList.Sort
(
delegate(Branch braA, Branch braB)
{
if (CompanySortDirection == SortDirection.Ascending)
{
return braA.BranchCode.CompareTo(braB
}
else// if (CompanySortDirection == SortDirection.Descending)
{
return braB.BranchCode.CompareTo(braA
}
}
);
Depending upon the Sort Direction the list get sorted.
Subscribe to:
Posts (Atom)
