Jquery vs ExtJx : http://www.grantshepert.com/post.cfm/jquery-and-or-extjs
Threading - http://www.albahari.com/threading/
Threading - http://www.albahari.com/threading/
Wednesday, December 22, 2010
Thursday, June 17, 2010
.Net Security
The .NET Framework includes a simple but very flexible identity-based security mechanism. By using it, you can exercise very fine-grained control over who is allowed to use your programs and which functions those users can perform. The key is a detailed understanding of the Principal and Identity objects.
The Microsoft .NET Framework provides a robust and flexible security infrastructure that can be split into two sections:
1. Code access security
2. Identity-based security.
The code access security (CAS) model focuses on code identity instead of user identity. Using CAS, you set policies and permissions on assemblies according to their publisher or where they were loaded from, for example. The user identity of the hosting process is irrelevant in the permission granting logic.
Compared with CAS, identity-based security represents a classic approach with which most programmers are confident. However, you'll need to master some important concepts to get the most from the .NET Identity Based security infrastructure:
Identity-based Security Important classes are:
1. Identity
2. Principal objects
The .NET Framework abstracts the concepts of user and roles, making them independent of the underlying Windows platform. .NET identity-based security relies on two simple concepts: Identity objects and Principal objects.
Identity object:
An Identity object is basically a user account. Any .NET class implementing the IIdentity interface is a valid Identity object to use in .NET authorization mechanisms. IIdentity exposes three simple properties: AuthenticationType, IsAuthenticated and Name.
The .NET Framework comes with four Identity classes out of the box:
• WindowsIdentity
• FormsIdentity
• PassPortIdentity
• GenericIdentity
The Generic Identity class is provided to ease the development of custom authentication mechanisms, but you can define your custom Identity class from scratch implementing IIdentity because there is no significant code you inherit from the GenericIdentity class.
You can match a .NET identity with a Windows Identity—that's what WindowsIdentity is for. (Later, you'll see how to do that.) The important point is that nothing makes a Windows Identity different from other Identity objects, as far as .NET is concerned.
The standard .NET authorization mechanisms are designed to accept and work with any kind of .NET Principal and Identity objects, totally oblivious of the underlying OS.
Of course, you can't bypass OS-related security checks. When you access an NTFS-protected file or any kind of resource that the OS is aware of, Windows triggers its authorization mechanisms against the identity of the process that is trying to access the resource (or eventually against the identity the process is impersonating).
Principal object:
A Principal object is a holder for all the roles the user belongs to (according to the active authentication mechanism). Again, any .NET class implementing the IPrincipal interface is a valid Principal object. The IPrincipal interface exposes the Identity property (returning the underlying Identity object) and the IsInRole method.
The .NET Framework class library comes with two Principal objects: WindowsPrincipal and GenericPrincipal. When a WindowsIdentity-WindowsPrincipal pair is used, the Principal role list is built using the Windows groups the Windows user belongs to.
Permission objects – define permissions:
For user base security – PrincipalPermission Class is used.: IPermission
For CAS – CodeAccessPermission Derived classes is used. : IPermission
IPermission Interface – Demand method – verifies if caller higher on the stack trace have not been granted permission specified by the permission object.
User Based Security:
1. Identity Objects : represents the identity of the user. Implement IIdentity Interface ( Authentication type, IsAuthenticated, Name of user)
Cas is all about setting or getting the trustworthiness of an assembly.i.e What code you can trust and to what degree. Same application can have assemblies with different trust level
Different degree of trust can be set at – assemble level,class level or even at method level.
CAS can only work with verifiable type safe code. i.e CLS must be able to verify the type safety of assemble when it is loaded into the memory. So if PInvoke is use to load an assemble CAS wouldn’t work.
Security in .net is based on two fundamentals:
If code is granted SkipVerificaiton – then it is possible to bypass verification process.
Not all .net languages generate verifiable type safe code. Vb.net does always, C# does if unsafe is not used.
PEVerify.exe can be used to test if code is verifiable type safe.
Verifiable means some features according to JIT verification definition.
SkipVeification assemblies can only execute if they are fully trusted.
Denying and Demanding the permission
Using good code by some malicious client is called luring attack.
Using Demand code can verify if it has all permission required to execute before starting the execution.
Use deny to limit the permission required for its execution – this is used to avoid luring attack
Stack Walking – if a method demand certain permission CLR check all it’s caller from immediate caller to up in the hierarchy for the required permission.
Working
Security permissions are configured not at assemble level but at code groups level.
There are four levels:
Lower level can not have more liberal permission than higher level.
Membership condition categorize the assemblies into different code groups.
Permission set define the permission granted to particular code group.
Assemble can fall in more than one group. Then permission can be evaluated by intersection of permissions on that groups.
Each code group define Membership condition and has only one permission set. One permission set can define number of permission.
Configuring tools –
Some member ship conditions are:
Imperative:
2. Using CodeAccessPermission Classes – in this construct a derived codeaccesspermission class and call it’s method like demand,deny or assert to automatically validate the permission.There are many derived classes for specific purposes like
SecutityAction Class:
Permission set is used to combine many permission in single object and than called demand or deny on that class.
Permission set class can also be configured in configuration file.
The call stack is grows downward.Method lower on the call stack are called by method higher on the call stack.Each method being called has it’s own area in the call stack called staclframe, which contains parameters and local variables of the current method.
1. Code access security
2. Identity-based security.
Identity-based Security Important classes are:
2. Principal objects
• FormsIdentity
• PassPortIdentity
• GenericIdentity
A Principal object is a holder for all the roles the user belongs to (according to the active authentication mechanism). Again, any .NET class implementing the IPrincipal interface is a valid Principal object. The IPrincipal interface exposes the Identity property (returning the underlying Identity object) and the IsInRole method.
For user base security – PrincipalPermission Class is used.: IPermission
For CAS – CodeAccessPermission Derived classes is used. : IPermission
IPermission Interface – Demand method – verifies if caller higher on the stack trace have not been granted permission specified by the permission object.
User Based Security:
- GenericIdentity – represent the identity of the user authenticated by custom authentication.
- Idenity gid = new GenericIdentity(Auth type, IsAuth., Name of user)
- WindowsIdentity – represent identity of the user authenticated by windows authentication.
- Idenity wid = new WindowsIdentidy(User token, AuthType., windows Account Type, IsAuth)User token is of IntPtr data type which refers to a user account token and can be a pointer or a handle.This can be obtained via unmanaged win32 APIs such as logonUser
- Windows identity object can also be obtained using GetCurrent and GetAnonymous methods of it’s class.
- FormIdentity
- PasssportIdentity
- IPrincipal ip = Thread.CurrentPrincipal.
- IPrincipal interface
- IIdentity – represents the identity object associated with principal object.
- IsInRole – take string role as param and verify if principal object roles collection contains given role.
- GenericPrincipal Object
- Constructed using GenericIdentity and Roles array. New GenericPrincipal(myGenericIdentity, srint() Roles)
- WindowsPrincipal Object
- Constructed by windows identity object as roles for particular identity are already mapped in windows
- By default principal policy on appdomain is GenericPrincipal so if authentication mode is windows we need to explicitly set the principal policy to windows else we wouldn’t get the user information we interested in. Also we need to cast the generic principal object retrived from Thread.CurrentPrincipal to windows principal object.
- Either user if statement to check role and name as required like
- If Principal.idenitty.name.Equals(“Gauri”) …..
- If Principal.Isinrole(“Admin”) ..
- Or use PrincipalPermission object demand method
- Pp = New PrincipalPermission(username , Role) Pp.Demand() – this line verify the username and role from principal objectr against username and role provided in the principalpermission object and if not verified will throw SecurityException.
- Or use Declarative Approach
- [PrincipalPermission(SecurityActin.Demand,Name = “Gauri”] – this method can only be executed by Gauri.
Cas is all about setting or getting the trustworthiness of an assembly.i.e What code you can trust and to what degree. Same application can have assemblies with different trust level
Security in .net is based on two fundamentals:
- Type safety of managed code: It means
- Bound checking is performed on all data types
- Prevent inappropriate or dangerous type conversion.
- Prevents direct memory manipulations.
- Vigilance of CLR
If code is granted SkipVerificaiton – then it is possible to bypass verification process.
Not all .net languages generate verifiable type safe code. Vb.net does always, C# does if unsafe is not used.
PEVerify.exe can be used to test if code is verifiable type safe.
Verifiable means some features according to JIT verification definition.
SkipVeification assemblies can only execute if they are fully trusted.
Using good code by some malicious client is called luring attack.
Using Demand code can verify if it has all permission required to execute before starting the execution.
Working
Security permissions are configured not at assemble level but at code groups level.
There are four levels:
- Enterprise level
- Machine level
- User level
- Appdomain level
Lower level can not have more liberal permission than higher level.
Each code group define Membership condition and has only one permission set. One permission set can define number of permission.
- Mscorcfg.msc – for manual configuration.
- Caspol.exe – for automated scripting
Some member ship conditions are:
- Publisher
- Site
- Strong Name – providing digital signature
- URL with protocol.
- Zone
- ….
Imperative:
- Using evidence classes (System.securityPolicy)
- Using derived codeAccessPermission classes.
Declarative:
- Get Evidence object, get it enumerator and loop thru it’s attributes. Like - Zone, URL, Hash, Strong Name, Site…
Ienumerator en = ev.GetEnumerator();
While en.MoveNext
Object item = en.Current
If Item.StrongName = “MyAssembly”
Do smth Secure
- webPermission
- FileIOPermission
- UrlIdentityPermission
- ZonalIdentityPermission
Imp method of this class are:
- Assert: it request the permission starting from calling function to down the stack trace if all code down the stack trace will have the permission as requested by assert calling object.Even if higher heriarchy denied the permission assert can be used to get it for some operations. Only one assert is allowed per stackframe else security exception is thrown.Use cautiously as it may introduce luring attack.
- Demand: It is used to verify the that calling method have the permission it does this by performing the a stalk walk checking the permission of each method found higher on the call stack
- Deny: use to restrict the permission down the stack. Only one deny per stack frame. UrlIdentityPermissin uip = new UrlIdentityPermissin(file://c:/./....) Uip.Demand()
- Declaritive:
- [UrlIdentityPermission(SecurityAction.LinkDemand,Url=”file://c:/../...”] [
( )]
- Assert
- Demand
- Deny
- LinkDemand
- PermitOnly
- RequestMinimum etc…
[assembly:UIPermission(SecurityAction.RequestMinimum,Clipboard=uipermissionclipbooard.allclipboard)]
assembly will be loaded only if it has minimum permission to access clipboard
Permission set class can also be configured in configuration file.
Saturday, June 12, 2010
Generics
Usage / Need:
To maintian a list of orders for a customer you have 3 options - either maintain an array list or an custom list of orders say Orderlist or use generic List of Orders.
public class Customer
{
Arraylist Array_orderlist; {OR}
Orderlist Custom_orderList; {OR}
List Generic_orderList;
}
//Order Class
Public Class Order : IOrder
{
//Actual implementation.
}
Prior to Generics we would have used Arraylist but in this explicit conversion to order objects are required when retrieving the objects. so this casting is a performance hit.
Order o = (Order) Array_orderlist[0]
to avoid this you can create your own list of Orders, where you implement IEnumerable and some common method of list like add, remove etc.
public class Orderlist : IEnumerable
{
private ArrayList orders = new ArrayList();
public int Add(Order value) {//Actual code.....}
public void Remove(Order value) {//Actual code.....}
//Other methods...
public IEnumerator GetEnumerator() {//Enumerate Orders Arraylist... & return IEnumerator}
}
So this code is use only to maintain list of Order, If we want to make it generic we have to write code which cast the passed type to object each time for each opertion.
To resolve this m/s has given support for generics, so now you can create a generic list which can work for any data type.
Let's create generic List
public class Orderlist : IEnumerable, , IEnumerable
{
private ArrayList orders = new ArrayList();
public int Add(T value) {//Actual code.....}
public void Remove(T value) {//Actual code.....}
//Other methods...
public IEnumerator GetEnumerator() {//Enumerate Orders Arraylist... and return IEnumerator}
}
Now this is generic list and can be used not just with Order type but with any data type. No explicit cast is required to retrive the object from this list.
This type of list is actually wrtinnen in FCL as List so we can use List to accomplish our tasks .. it's as simple :-)
But suppose you have to use any method of Order class in generic list, in that case instead of making your list just compatible with order type you can make it for all types implementing IOrder interface by applying constraints on OrderList.
For ex say you have a method
private static T Min (T o1, To2)
{
if (o1.CompareTo(o2) <0 return o1; else retrun o2;
}
Now this method will work only if T has method compare in it so you can put constrain on method like.
private static T Min (T o1, To2) where T : Icomparable
{ if (o1.CompareTo(o2) <0 return o1; else retrun o2;}
so not T will only be of type implementing Icomparable.
So constraints are restriction which define the type you can use in your generic class else compile time error.
so while compiling compiler will verify that the generic type should work for all the types define today.
There are three types of constraints:
1. Primary Constraints : there can be 0 or 1 primary constraints. These constraints restrict the type to either Refrence type (or it's derived type) or Value type. ex where T : Stream so T can be file stream/Iostream etc.
two special constraints are Class - T can be any reference type, Strut - T can be any value type.
2. Secondary Constraints: Zero or more. This put restriction on interfaces that T implements.
3. Constructor Constraints:Zero or one. This specified that type T will be a non-abstract type that implements public parameter less constructor. - where T : new(). As of now only parameter less constructors are suported.
Apart from these constraints, some verification also done by compiler like:
Overloading is not allowed on name of T or on constraints. you can overload types based only on Arity only.
Class AType
Class AType //Error.
Overriding will have the same no. of parameters and inherit the constraints.
Most of the operators (+,-,*..) can't be applied to generics type because compiler doesn't know the type at compile time.
Comparig (= =) generic type with null or with other generic type wouldn't work. (Unless there is a contraint of reference type). because value type can't be null or applied with == operator.
Casting
T obj
int x = (int) obje //Error as T may be of type that can't be converted to int.
int x = (int)(Object) obje // no error. There may be run time exception of Invalidcast.
So basically if any thing is not true for all the available types than compiler will throw error.
Generics in General:
To maintian a list of orders for a customer you have 3 options - either maintain an array list or an custom list of orders say Orderlist or use generic List of Orders.
public class Customer
{
Arraylist Array_orderlist; {OR}
Orderlist Custom_orderList; {OR}
List
}
//Order Class
Public Class Order : IOrder
{
//Actual implementation.
}
Prior to Generics we would have used Arraylist but in this explicit conversion to order objects are required when retrieving the objects. so this casting is a performance hit.
Order o = (Order) Array_orderlist[0]
to avoid this you can create your own list of Orders, where you implement IEnumerable and some common method of list like add, remove etc.
public class Orderlist : IEnumerable
{
private ArrayList orders = new ArrayList();
public int Add(Order value) {//Actual code.....}
public void Remove(Order value) {//Actual code.....}
//Other methods...
public IEnumerator GetEnumerator() {//Enumerate Orders Arraylist... & return IEnumerator}
}
So this code is use only to maintain list of Order, If we want to make it generic we have to write code which cast the passed type to object each time for each opertion.
To resolve this m/s has given support for generics, so now you can create a generic list which can work for any data type.
Let's create generic List
public class Orderlist
{
private ArrayList orders = new ArrayList();
public int Add(T value) {//Actual code.....}
public void Remove(T value) {//Actual code.....}
//Other methods...
public IEnumerator GetEnumerator() {//Enumerate Orders Arraylist... and return IEnumerator
}
Now this is generic list and can be used not just with Order type but with any data type. No explicit cast is required to retrive the object from this list.
This type of list is actually wrtinnen in FCL as List
But suppose you have to use any method of Order class in generic list, in that case instead of making your list just compatible with order type you can make it for all types implementing IOrder interface by applying constraints on OrderList
For ex say you have a method
private static T Min (T o1, To2)
{
if (o1.CompareTo(o2) <0 return o1; else retrun o2;
}
Now this method will work only if T has method compare in it so you can put constrain on method like.
private static T Min (T o1, To2) where T : Icomparable
{ if (o1.CompareTo(o2) <0 return o1; else retrun o2;}
so not T will only be of type implementing Icomparable
So constraints are restriction which define the type you can use in your generic class else compile time error.
so while compiling compiler will verify that the generic type should work for all the types define today.
There are three types of constraints:
1. Primary Constraints : there can be 0 or 1 primary constraints. These constraints restrict the type to either Refrence type (or it's derived type) or Value type. ex where T : Stream so T can be file stream/Iostream etc.
two special constraints are Class - T can be any reference type, Strut - T can be any value type.
2. Secondary Constraints: Zero or more. This put restriction on interfaces that T implements.
3. Constructor Constraints:Zero or one. This specified that type T will be a non-abstract type that implements public parameter less constructor. - where T : new(). As of now only parameter less constructors are suported.
Apart from these constraints, some verification also done by compiler like:
Overloading is not allowed on name of T or on constraints. you can overload types based only on Arity only.
Class AType
Class AType
Overriding will have the same no. of parameters and inherit the constraints.
Most of the operators (+,-,*..) can't be applied to generics type because compiler doesn't know the type at compile time.
Comparig (= =) generic type with null or with other generic type wouldn't work. (Unless there is a contraint of reference type). because value type can't be null or applied with == operator.
Casting
T obj
int x = (int) obje //Error as T may be of type that can't be converted to int.
int x = (int)(Object) obje // no error. There may be run time exception of Invalidcast.
So basically if any thing is not true for all the available types than compiler will throw error.
Generics in General:
- Generic is a form of code reuse : Algorithm re-use
- Generic type can be of reference type/ value type. CLR allows generic interfaces and delegates also.
- Advantages:
- Source code protection
- Type Safety
- Cleaner code
- Better Performance.
- As of now mainly colleciton classes are generics.
- Specifying type argument (generics) doesn't have anythig to do with inheritance.
- Code Explosion: CLR keep generating code for every method/type combination. There are two optimization done by clr on top of this:
- If an two or more assembly in same appdomain, using generic for a particular type than that will be compile just once. e.x. if one assembly using List
and other also uses List than List will compile only once. - CLR consider all reference type arguments to be identical, so again the code can be shared. For e.x. code compiled for List
's method can be used for List 's method. - Eamples
- Generic Interfaces : IList,ICollection, IEnumerator, IEnumerable, IComparer, ICombarable. all the have
as argument, IDictionary - Generic Classes: List, Stack, Queue, LinkedList ..
. Dictionary - Generic Delegates: void Action
(T obj), int Comparison - Generic Methods.
Thursday, June 3, 2010
Extended Stored Proc vs CLR Integration.
source - MSDN and code project and other sites. (Not meant for publishing).
A. Extended Stored Procedure: Older way of extend SP functionality in programming language like c,c++. This wouldn't be supported in future version of sql server. this is call extended as you call a c dll function in a stored proc just like you are calling UDF/SPs.
What's typically done is create a dll which exposes a funciton, register this dll with sql server master DB and create a UDF in DB where you need to call this function, this udf internally call the c dll and execute the code written there.
Parameters can be passed to extended stored procedures, and they can return results and return status.
Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
permission should be granted to user to execute the stored proc. Extended stored procedures can be added only to the master database.
Adding extended stored proc to Database - sp_addextendedproc 'xp_hello', 'c:\hello.dll' this db proc will register the ext sp to database. xp_hello is the name of the funciton which extended the functionality of sp. so any call to Master.dbo.xp_hello will load and execute the dll routine.
Issues with ext sp:
Address space:
As a DLL, it is loaded in the address space of the calling process. In the case of the extended stored procedure, the process is SQL Server. If a DLL is improperly accessing memory or if it is not thread safe, you can adversely affect the process. You must perform thorough testing to make sure that the DLL maintains the integrity of the process. If you are at all concerned that an extended stored procedure may be adversely affecting SQL Server, you must address this problem immediately.
Memory leaks
Any project may have a problem where allocated memory, a handle, or a similar resource is not being released properly. It is paramount to any DLL test suite that the suite makes sure that the DLL is releasing all resources correctly.
Thread safety
Applications such as Microsoft Internet Information Server (IIS) and Microsoft SQL Server are thread pooling, multi-threaded applications. Make sure that all code paths are thread-safe
-------------------------------------------------------------------------------------------------
B. CLR SQL Integration:
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS) to prevent assemblies from performing certain operations. SQL Server uses CAS to help secure the managed code and prevent compromise of the operating system or database server.
The CLR provides various functions and services, to these DB objects also ,required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
You can build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR). Managed code that runs inside of Microsoft SQL Server is referred to as a "CLR routine." These routines include: Scalar UDFs / Tabular UDFs / SP / Triggers.
Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.
Managed code can run on a client computer, while Transact-SQL cannot.
while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of the CLR and SQL Server. With CLR integration, you have a more secure way than extended stored procedures to write the stored procedures you need to perform tasks not possible in Transact-SQL.
In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework.
CLR routines are mapped to public, static methods of a class. UDTs and user-defined aggregates are mapped to entire .NET Framework classes.
Design Goal of CLR Integration:
1. Reliability (Safety)
User code should not be allowed to perform operations that compromise the integrity of the Database Engine process, such as popping a message box requesting a user response or exiting the process. User code should not be able to overwrite Database Engine memory buffers or internal data structures.
2. Scalability
SQL Server and the CLR have different internal models for scheduling and memory management. SQL Server supports a cooperative, non-preemptive threading model in which the threads voluntarily yield execution periodically, or when they are waiting on locks or I/O. The CLR supports a preemptive threading model.
The CLR does not distinguish between virtual and physical memory, but SQL Server directly manages physical memory and is required to use physical memory within a configurable limit.
3. Security
User code running in the database must follow SQL Server authentication and authorization rules when accessing database objects such as tables and columns. In addition, database administrators should be able to control access to operating system resources, such as files and network access, from user code running in the database.
How it's done
CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. In order to synchronize between multiple threads, the CLR calls SQL Server synchronization objects. This allows the SQL Server scheduler to schedule other tasks when a thread is waiting on a synchronization object. The ability to hook CLR threads to SQL Server threads implies that the SQL Server scheduler can identify "runaway" threads in the CLR and manage their priority. Such runaway threads are suspended and put back in the queue.
The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.
SQL Server allows users to specify the reliability and security requirements for code deployed into the database. When assemblies are uploaded into the database, the author of the assembly can specify one of three permission sets for that assembly: SAFE, EXTERNAL_ACCESS and UNSAFE.
Managed code in sql server shouldn't it's maintain state across invocations. For SAFE and EXTERNAL_ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time and fails the creation of such assemblies if it finds the use of static data members and variables.
Coding
The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:
System.Data / System.Data.Sql / Microsoft.SqlServer.Server / System.Data.SqlTypes
1. Enable CLR integration in sql server (by default it is disabled).
2. Create a public class and a static method in it wihch is decorated with
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld(out string text)
{
SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
text = "Hello world!"; }}
[Microsoft.SqlServer.Server.SqlProcedure] attribute which signify this function as an DB sp.
3. Create reference in sql server -
CREATE ASSEMBLY helloworld from 'D:\hellowWorld.dll' WITH PERMISSION_SET = SAFE
4. Create stored proc in sql server which will wrap the call to this assembly and handles input and out put parameter
5. Call this procedure from anywhere in sql server
DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J
If we user visual studio to create SQL SERVER project than most of the work done by VS behind the scene you just need to create a class with static method and project properties has a deploy menu which register this assemmbly in DB and create a wrapper sp for u.
Supported Libraries
Not all .net library is supported by sql server. only libraries which are tested to ensure that they meet reliability and security standards for interaction with SQL Server. Supported libraries do not need to be explicitly registered on the server before they can be used in your code; SQL Server loads them directly from the Global Assembly Cache (GAC).
Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.
pending:
SQL Server Data Types in the .NET Framework
Since SQL data type and CLR data types are different like Null has proper datatype in SQL not is clr so some operand retrun true/false/unknown if both compared values are null to handle this sqldata type has SQLboolean type which supports these three values. same way decimal data type in clr and sql has different precision so sqldata type has SqlDecimal to map with sql data type.
All these sqldata types are part of System.data.SqlTypes namespace which is in System.Data library which is the part of .net base class libraries. in short .Net Framework provides a sql data types in framework via system.data.sqltypes namespace.
Types supported in CLR integration are:
CLR User defined functions - scalar-valued/ Table-values / User defined aggregates.
CLR user defined types (classes)
CLR proc / CLR Triggers.
Examples of all db object types.
For more topics - http://msdn.microsoft.com/en-us/library/ms131046(v=SQL.105).aspx
-------------------------------------------------------------------------------------------------
A. Extended Stored Procedure: Older way of extend SP functionality in programming language like c,c++. This wouldn't be supported in future version of sql server. this is call extended as you call a c dll function in a stored proc just like you are calling UDF/SPs.
What's typically done is create a dll which exposes a funciton, register this dll with sql server master DB and create a UDF in DB where you need to call this function, this udf internally call the c dll and execute the code written there.
Parameters can be passed to extended stored procedures, and they can return results and return status.
Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
permission should be granted to user to execute the stored proc. Extended stored procedures can be added only to the master database.
Adding extended stored proc to Database - sp_addextendedproc 'xp_hello', 'c:\hello.dll' this db proc will register the ext sp to database. xp_hello is the name of the funciton which extended the functionality of sp. so any call to Master.dbo.xp_hello will load and execute the dll routine.
Issues with ext sp:
Address space:
As a DLL, it is loaded in the address space of the calling process. In the case of the extended stored procedure, the process is SQL Server. If a DLL is improperly accessing memory or if it is not thread safe, you can adversely affect the process. You must perform thorough testing to make sure that the DLL maintains the integrity of the process. If you are at all concerned that an extended stored procedure may be adversely affecting SQL Server, you must address this problem immediately.
Memory leaks
Any project may have a problem where allocated memory, a handle, or a similar resource is not being released properly. It is paramount to any DLL test suite that the suite makes sure that the DLL is releasing all resources correctly.
Thread safety
Applications such as Microsoft Internet Information Server (IIS) and Microsoft SQL Server are thread pooling, multi-threaded applications. Make sure that all code paths are thread-safe
-------------------------------------------------------------------------------------------------
B. CLR SQL Integration:
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS) to prevent assemblies from performing certain operations. SQL Server uses CAS to help secure the managed code and prevent compromise of the operating system or database server.
The CLR provides various functions and services, to these DB objects also ,required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
You can build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR). Managed code that runs inside of Microsoft SQL Server is referred to as a "CLR routine." These routines include: Scalar UDFs / Tabular UDFs / SP / Triggers.
Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.
Managed code can run on a client computer, while Transact-SQL cannot.
while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of the CLR and SQL Server. With CLR integration, you have a more secure way than extended stored procedures to write the stored procedures you need to perform tasks not possible in Transact-SQL.
In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework.
CLR routines are mapped to public, static methods of a class. UDTs and user-defined aggregates are mapped to entire .NET Framework classes.
Design Goal of CLR Integration:
1. Reliability (Safety)
User code should not be allowed to perform operations that compromise the integrity of the Database Engine process, such as popping a message box requesting a user response or exiting the process. User code should not be able to overwrite Database Engine memory buffers or internal data structures.
2. Scalability
SQL Server and the CLR have different internal models for scheduling and memory management. SQL Server supports a cooperative, non-preemptive threading model in which the threads voluntarily yield execution periodically, or when they are waiting on locks or I/O. The CLR supports a preemptive threading model.
The CLR does not distinguish between virtual and physical memory, but SQL Server directly manages physical memory and is required to use physical memory within a configurable limit.
3. Security
User code running in the database must follow SQL Server authentication and authorization rules when accessing database objects such as tables and columns. In addition, database administrators should be able to control access to operating system resources, such as files and network access, from user code running in the database.
How it's done
CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. In order to synchronize between multiple threads, the CLR calls SQL Server synchronization objects. This allows the SQL Server scheduler to schedule other tasks when a thread is waiting on a synchronization object. The ability to hook CLR threads to SQL Server threads implies that the SQL Server scheduler can identify "runaway" threads in the CLR and manage their priority. Such runaway threads are suspended and put back in the queue.
The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.
SQL Server allows users to specify the reliability and security requirements for code deployed into the database. When assemblies are uploaded into the database, the author of the assembly can specify one of three permission sets for that assembly: SAFE, EXTERNAL_ACCESS and UNSAFE.
Managed code in sql server shouldn't it's maintain state across invocations. For SAFE and EXTERNAL_ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time and fails the creation of such assemblies if it finds the use of static data members and variables.
Coding
The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:
System.Data / System.Data.Sql / Microsoft.SqlServer.Server / System.Data.SqlTypes
1. Enable CLR integration in sql server (by default it is disabled).
2. Create a public class and a static method in it wihch is decorated with
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld(out string text)
{
SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
text = "Hello world!"; }}
[Microsoft.SqlServer.Server.SqlProcedure] attribute which signify this function as an DB sp.
3. Create reference in sql server -
CREATE ASSEMBLY helloworld from 'D:\hellowWorld.dll' WITH PERMISSION_SET = SAFE
4. Create stored proc in sql server which will wrap the call to this assembly and handles input and out put parameter
CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld (sqlAssembly.classname.mthname)
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld (sqlAssembly.classname.mthname)
5. Call this procedure from anywhere in sql server
DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J
If we user visual studio to create SQL SERVER project than most of the work done by VS behind the scene you just need to create a class with static method and project properties has a deploy menu which register this assemmbly in DB and create a wrapper sp for u.
Supported Libraries
Not all .net library is supported by sql server. only libraries which are tested to ensure that they meet reliability and security standards for interaction with SQL Server. Supported libraries do not need to be explicitly registered on the server before they can be used in your code; SQL Server loads them directly from the Global Assembly Cache (GAC).
Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.
pending:
SQL Server Data Types in the .NET Framework
Since SQL data type and CLR data types are different like Null has proper datatype in SQL not is clr so some operand retrun true/false/unknown if both compared values are null to handle this sqldata type has SQLboolean type which supports these three values. same way decimal data type in clr and sql has different precision so sqldata type has SqlDecimal to map with sql data type.
All these sqldata types are part of System.data.SqlTypes namespace which is in System.Data library which is the part of .net base class libraries. in short .Net Framework provides a sql data types in framework via system.data.sqltypes namespace.
Types supported in CLR integration are:
CLR User defined functions - scalar-valued/ Table-values / User defined aggregates.
CLR user defined types (classes)
CLR proc / CLR Triggers.
Examples of all db object types.
For more topics - http://msdn.microsoft.com/en-us/library/ms131046(v=SQL.105).aspx
-------------------------------------------------------------------------------------------------
Subscribe to:
Posts (Atom)