Author Archives: Yogeshwar Singh Chauhan

Difference Between Array and Arraylist

Array

Arrays are strongly typed collection of same datatype and these arrays are fixed length that can’t be changed during runtime. Arrays we will store values with index basis that will start with zero. If we want to access values from arrays we need to pass index values

Declaration of Arrays

We can declare arrays as shown below.

  1. using System;
  2. namespace WebApplication2
  3. {
  4.     public partial class _Default: System.Web.UI.Page
  5.     {
  6.         protected void Page_Load(object sender, EventArgs e)
  7.         {
  8.             // Initialize the array items during declaration
  9.             string[] strNames = new string[]
  10.             {
  11.                 “Ramakrishna”,
  12.                 “Praveenkumar”,
  13.                 “Indraneel”,
  14.                 “Neelohith”
  15.             };
  16.             // Declare array and assign values using index
  17.             string[] strNamesObj = new string[4];
  18.             strNamesObj[0] = “Ramakrishna”;
  19.             strNamesObj[1] = “Praveenkumar”;
  20.             strNamesObj[2] = “Indraneel”;
  21.             strNamesObj[3] = “Neelohith”;
  22.         }
  23.     }
  24. }

Arraylist

Array lists are not strongly type collection. It will store values of different datatypes or same datatype. Array list size will increase or decrease dynamically it can take any size of values from any data type. These Array lists will be accessible with “System.Collections” namespace.

Declaration of Arraylist

To know how to declare and store values in array lists check below code.

  1. using System;
  2. using System.Collections;
  3. namespace WebApplication2
  4. {
  5.     public partial class _Default: System.Web.UI.Page
  6.     {
  7.         protected void Page_Load(object sender, EventArgs e)
  8.         {
  9.             ArrayList arrListObj = new ArrayList();
  10.             // Adding string value to arraylist
  11.             arrListObj.Add(“Ramakrishna Basagalla”);
  12.             // Adding integer value to arraylist
  13.             arrListObj.Add(1981);
  14.             // Adding float value to arraylist
  15.             arrListObj.Add(11.99);
  16.         }
  17.     }
  18. }

In the above example we have not specified any size to an array list, we can add any number of data there is no size limit.

Difference between Array and ArrayList

ArrayArrayList
Arrays are strongly typed collection of same datatype.ArrayList are not strongly typed collection we can store different types of data.
Data is fixed length.We can increase data dynamically.
Arrays belongs to System.Array namespace.ArrayList belongs to System.Collection namespace.
Data can be added using array index.Data can be added using Add method.

Hope this blog will help you in understanding the Array and ArrayList. Happy Coding.

Marshaling native arrays back as managed arrays without copying

I’ve come up with a trick that can be used in some very specific scenarios in order to avoid extra array copying when calling into native code from managed code (e.g. C#). This won’t usually work for regular P/Invokes into all your favorite Win32 APIs, but I’m hopeful it’ll be useful for someone somewhere. It’s not even evil! No hacks required.

Many native methods require the caller to allocate the array and specify its length, and then the callee fills it in or returns an error code indicating that the buffer is too small. The technique described in this post is not necessary for those, as they can already be used optimally without any copying.

Instead, let’s talk about the general problem if you’re calling a native method which does the array allocation and then returns it. You can’t use it as a “managed array” unless you copy it into a brand new managed array (don’t forget to free the native array). In other words, native { T* pArray; size_t length; } cannot be used as a simple managed T[] as-is (or even with modification!). The managed runtime didn’t allocate it, won’t recognize it, and there’s nothing you can do about it. Very few managed methods will accept a pointer and a length, and will require a managed array. This is particularly irksome when you want to use System.IO.Stream.Read() or Write() with bytes from a native-side buffer.

Paint.NET uses a library written in classic C called General Polygon Clipper (GPC), from The University of Manchester, to perform polygon clipping. This is used for, among other things, when you draw a selection with a mode such as add (union), subtract (exclude), intersect, and invert (“xor”). I blogged about this 4 years ago when version 3.35 was about to be released: using GPC made these operations immensely faster, and I saved a lot of time and headache by purchasing a commercial use license for the library and then integrating it into the Paint.NET code base. tl;dr: The algorithms for doing this are nontrivial and rife with special corner cases, and I’d been struggling to find enough sequential time to implement and debug it on my own.

Anyway, the data going into and coming out of GPC is an array of polygons. Each polygon is an array of points, each of which is just a struct containing X and Y as double-precision floating point values. To put it simply, it’s just a System.Windows.Point[][] (I actually use my own geometry primitives nowadays, but that’s another story, and it’s the same exact thing).

Getting this data into GPC from the managed side is easy. You pin every array, and then hand off the pinned pointers to GPC. Since you can’t use the “fixed” expression with a dynamic number of elements, I use GCHandle directly and stuff them all into GCHandle[] arrays for the duration of the native call. This is great because on the managed side I can work with regular ol’ managed arrays, and then send them off to GPC as “native arrays” by pinning them and using the pointers obtained from GCHandle.AddrOfPinnedObject().

Now, here’s the heart breaking part. GPC allocates the output polygon using good ol’ malloc*. So when I get the result back on the managed side, I must copy every single last one so that I can use it as a Point[] (a managed array). This ends up burning a lot of CPU time, and can cause virtual address space claustrophobia on 32-bit/x86 systems when working with complex selections (e.g. Magic Wand), as you must have enough memory for 2 copies of the result while you’re doing the copying. (Or you could free each native array after you copy it into a managed array, but that’s an optimization for another day, and isn’t as straightforward as you’d think because freeing the native memory requires another P/Invoke, and those add up, and so it might not actually be an optimization.)

But wait, there’s another way! Since the code for GPC is part of my build, I can modify it. So I added an extra parameter called gpc_vertex_calloc:

    typedef (gpc_vertex *)(__stdcall * gpc_vertex_calloc_fn)(int count);

    GPC_DLL_EXPORT(
void, gpc_polygon_clip)(
gpc_op                set_operation,
gpc_polygon          *subject_polygon,
gpc_polygon          *clip_polygon,
// result_polygon holds the arrays of gpc_vertex, aka System.Windows.Point)
gpc_polygon          *result_polygon,
gpc_vertex_calloc_fn  gpc_vertex_calloc);

(“gpc_vertex” is GPC’s struct that has the same layout as System.Windows.Point: X and Y, defined as a double.)

In short, I’ve changed GPC so that is uses an external allocator by passing in a function pointer it should use instead of malloc. And now if I want I can have it use malloc, HeapAlloc, VirtualAlloc, or even the secret sauce detailed below.

On the managed side, the interop delegate definition for gpc_vertex_calloc_fn gets defined as such:

    [UnmanagedFunctionPointer(CallingConvention.StdCall)]
public delegate IntPtr gpc_vertex_calloc_fn(int count);

And gpc_polygon_clip’s interop defintion is like so:

    [DllImport(“PaintDotNet.SystemLayer.Native.x86.dll”, CallingConvention = CallingConvention.StdCall)]
public static extern void gpc_polygon_clip(
[In] NativeConstants.gpc_op set_operation,
[In] ref NativeStructs.gpc_polygon subject_polygon,
[In] ref NativeStructs.gpc_polygon clip_polygon,
[In, Out] ref NativeStructs.gpc_polygon result_polygon,
[In] [MarshalAs(UnmanagedType.FunctionPtr)] NativeDelegates.gpc_vertex_calloc_fn gpc_vertex_calloc);

So, we’re half way there, and now we need to implement the allocator on the managed side.

    internal unsafe sealed class PinnedManagedArrayAllocator<T>
: Disposable
where T : struct
{
private Dictionary<IntPtr, T[]> pbArrayToArray;
private Dictionary<IntPtr, GCHandle> pbArrayToGCHandle;

        public PinnedManagedArrayAllocator()
{
this.pbArrayToArray = new Dictionary<IntPtr, T[]>();
this.pbArrayToGCHandle = new Dictionary<IntPtr, GCHandle>();
}

// (Finalizer is already implemented by the base class (Disposable))

        protected override void Dispose(bool disposing)
{
if (this.pbArrayToGCHandle != null)
{
foreach (GCHandle gcHandle in this.pbArrayToGCHandle.Values)
{
gcHandle.Free();
}

                this.pbArrayToGCHandle = null;
}

            this.pbArrayToArray = null;

            base.Dispose(disposing);
}

        // Pass a delegate to this method for “gpc_vertex_calloc_fn”. Don’t forget to use GC.KeepAlive() on the delegate!
public IntPtr AllocateArray(int count)
{
T[] array = new T[count];
GCHandle gcHandle = GCHandle.Alloc(array, GCHandleType.Pinned);
IntPtr pbArray = gcHandle.AddrOfPinnedObject();
this.pbArrayToArray.Add(pbArray, array);
this.pbArrayToGCHandle.Add(pbArray, gcHandle);
return pbArray;
}

        // This is what you would use instead of, e.g. Marshal.Copy()
public T[] GetManagedArray(IntPtr pbArray)
{
return this.pbArrayToArray[pbArray];
}
}

(“Disposable” is a base class which implements, you guessed it, IDisposable, while also ensuring that Dispose(bool) never gets called more than once. This is important for other places where thread safety is very important. This class is specifically not thread safe, but it should be reasonably easy to make it so.)

And that’s it! Well, almost. I’m omitting the guts of the interop code but nothing that should inhibit comprehension of this part of it. Also, the above code is not hardened for error cases, and should not be used as-is for anything running on a server or in a shared process. Oh, and I just noticed that my Dispose() method has an incorrect implementation, whereby it shouldn’t be using this.pbArrayToGCHandle, specifically it shouldn’t be foreach-ing on it, and should instead wrap that in its own IDisposable-implementing class … exercise for the reader? Or I can post a fix later if someone wants it.

After I’ve called gpc_polygon_clip, instead of copying all the arrays using something like System.Runtime.InteropServices.Marshal.Copy(), I just use GetManagedArray() and pass in the pointer that GPC retrieved from its gpc_vertex_calloc_fn, aka AllocateArray(). When I’m done, I dispose the PinnedManagedArrayAllocator and it unpins all the managed arrays. And this is much faster than making copies of everything.

Now, this isn’t the exact code I’m using. I’ve un-generalized it in the real code so I can allocate all of the arrays at once instead of incurring potentially hundreds of managed <—> native transitions for each allocation. The above implementation also doesn’t have a “FreeArray” method; I had one, but I ended up not needing it, so I removed it.

So the next time you find yourself calling into native code which either 1) allows you to specify an external allocator, or 2) is part of your build, and that 3) involves lots of data and thus lots of copying and wasted CPU time, you might just consider using the tactic above. Your users will thank you.

Creating a Web service with VB.NET

Creating a Web service with VB.NET

Web services technology is based on HTTP, Simple Object Access Protocol (SOAP), and XML. Since Web services use open standards, calling Web services is fairly simple.

VB.NET allows you to use Web services as if they were entirely local objects since most of the marshaling between the client and the server is taking place in the background. This tip shows you how to create a simple Web service.

Example

I will create a Web service and add a Web function that will return the current machine’s IP address. Here are the steps for creating a Web service:

  1. Open Visual Studio.Net and select Create New Website under VB.NET.
  2. Select Web Service from the options listed.
  3. Once you get the code window open, add the following code:
<WebMethod()> _

Public Function GetMachineIPAddress() As String

Dim strHostName As String = “”

Dim strIPAddress As String = “”

Dim host As System.Net.IPHostEntry

strHostName = System.Net.Dns.GetHostName()

strIPAddress = System.Net.Dns.GetHostEntry(strHostName).HostName.ToString()

host = System.Net.Dns.GetHostEntry(strHostName)

Dim ip As System.Net.IPAddress

For Each ip In host.AddressList

Return ip.ToString()

Next

Return “”

    End Function

Once you debug the example, you will see a screen that looks like Figure A. Figure A

Figure A

It will list two available Web methods: the one that was generated (HelloWorld) and the one I created (GetMachineIPAddress). Click the link for GetMachineIPAddress, and you will see a screen allowing you to get more information about this method and invoking it (Figure B). Figure B

Figure B

Click the Invoke button, and the result will look like Figure C. Figure C

Figure C

 

C#: A Review on Generics

I think a good C# developer needs to get a handle on .NET generics.  Most of the advanced features in C# deal with lots of generics and having a very good understanding of generics will help considerably, most especially when dealing with generic delegates.  So here in this post, we will review generics.

Generic type definitions can be methods, classes, structures, and interfaces.

// an example generic class
public class MyGenericClass<T>
{
    public T MyProperty;
}

 

 

The placeholders (e.g. <T>) are called generic type parameters, or type parameters.

You specify the actual types to substitute for the type parameters during instantiation.

// instantiate generic class with string type
MyGenericClass<string> c = new MyGenericClass<string>();

 

 

When instantiated, a generic type definition becomes a constructed generic type.

You can place limits or constraints on generic type parameters.

// limit type to value types except Nullable
public class MyGenericClass<T> where T : struct {/*...*/}

// limit type to reference types which can include classes,
//  interfaces, delegates, and array types
public class MyGenericClass<T> where T : class {/*...*/}

// limit type to types with public parameterless constructor
// must be specified last in multiple constraints
public class MyGenericClass<T> where T : new() {/*...*/}

// limit type to specified base class or to types derived from it
public class MyGenericClass<T> where T : MyBaseClass {/*...*/}

// limit type to specified interface or to types that implement it
public class MyGenericClass<T> where T : IMyInterface {/*...*/}

// limit type to specified type parameter    

// in a generic member function, it limits its type to the type 
//  parameter of the containing type
public class MyGenericClass<T>
{
    void MyMethod<U>(List<U> items) where U : T {/*...*/}
}

// in a generic class, it enforces an inheritance relationship
//  between the two type parameters
public class MyGenericClass<T, U> where U : T {/*...*/}

// type parameter can have multiple constraints 
//  and constraints can also be generics
//  and constraints can be applied to multiple type parameters
public class MyGenericClass<T, U> 
    where T : MyClass, IMyInterface, System.IComparable<T>, new()
    where U : struct
{
    // ...
}

 

 

A method is considered a generic method definition if it has two parameter lists: a list of type parameters enclosed in <> and a list of formal parameters enclosed in ().  A method belonging to a generic or non-generic type does not make the method generic or non-generic.  Only the existence of the two parameter lists will make the method generic, as in the example below.

public class MyClass
{
    // a generic method inside a non-generic class
    T MyGenericMethod<T>(T arg) {/*...*/}
}

 

 

A type nested in a generic type is considered by CLR to be generic even if it doesn’t have generic type parameters of its own.  When you instantiate a nested type, you need to specify the type arguments for all enclosing generic types.

// generic type
public class MyGenericType<T, U>
{
    // nested type
    public class MyNestedType
    {
        // ...
    }
}

// ... somewhere in code you instantiate the nested type   
MyGenericType<string, int>.MyNestedType nt = 
    new MyGenericType<string, int>.MyNestedType();

 

 

The following are some common generic collection counterparts provided by the .NET framework:

 

There are also generic interface counterparts for ordering and equality comparisons and for shared collection functionality:

  • System.IComparable<T> and System.IEquatable<T> which define methods for ordering comparisons and equality comparisons.
  • IComparer<T> and IEqualityComparer<T> in the System.Collections.Generic namespace, which offer alternative way for types that do not implement System.IComparable<T> and System.IEquatable<T>.  They are used by methods and constructors of many of the generic collection classes.  An example would be passing a generic IComparer<T> object to the constructor of SortedDictionary<TKey, TValue> to specify a sort order.  Generic classes Comparer<T> and EqualityComparer<T> are their base class implementations.
  • ICollection<T> which provides basic functionality for adding, removing, copying, and enumerating elements in a generic collection type.  It inherits from IEnumerable<T> and the nongeneric IEnumerable.
  • IList<T> which extends ICollection<T> with methods for indexed retrieval.
  • IDictionary<TKey, TValue> which extends ICollection<T> with methods for keyed retrieval.  Generic dictionary types also inherit from nongeneric IDictionary.
  • IEnumerable<T> which provides a generic enumerator structure used by foreach.  It inherits from nongeneric IEnumerator because MoveNext and Reset methods appear only on the nongeneric interface.  This means consumer of the nongeneric interface can also consume the generic interface because the generic interface provides for nongeneric implementation.

 

You also have generic delegates in .NET framework.  An example is the EventHandler<TEventArgs> which you can use in handling events with custom event arguments.  No need to declare your own delegate type for the event.  If you need to brush up on events and delegates, see my post on raising events and nongeneric delegates.

public event EventHandler<PublishedEventArgs> Published;

 

 

There are also a bunch of useful generic delegates available for manipulating arrays and lists

  • Action<T> which allows you to perform action on an element by passing an Action<T> delegate instance and an array to the generic method Array.ForEach<T>.  You can also pass an Action<T> delegate instance to the nongeneric method List<T>.ForEach.
  • Predicate<T> which allows you to specify a search criteria to Array’s Exists<T>, Find<T>, FindAll<T>, and so on and also to List<T>’s Exists, Find, FindAll, and so on.
  • Comparsion<T> which allows you to provide a sort order.
  • Converter<TInput, TOutput> which allows you to convert between two types of arrays or lists.

 

Ok so that’s all we have for generics.  Just a review of the basics that a C# developer need to know.

SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database

There are two different methods of retrieving the list of Primary Keys and Foreign Keys from database.

Method 1: INFORMATION_SCHEMA

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO

Method 2: sys.objects

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO

I am often asked about my preferred method of retrieving list of Primary Keys and Foreign Keys from database. I have a standard answer. I prefer method 3, which is querying sys database. The reason is very simple. sys. schema always provides more information and all the data can be retrieved in our preferred fashion with the preferred filter.

Let us look at the example we have on our hand. When Information Schema is used, we will not be able to discern between primary key and foreign key; we will have both the keys together. In the case of sys schema, we can query the data in our preferred way and can join this table to another table, which can retrieve additional data from the same.

Let us play a small puzzle here. Try to modify both the scripts in such a way that we are able to see the original definition of the key, that is, create a statement for this primary key and foreign key.

If I get an appropriate answer from my readers, I will publish the solution on this blog with due credit.

Understanding LINQ to SQL Object-Relational Mapping

Understanding LINQ to SQL Object-Relational Mapping

According to Wikipedia, Object-relational mapping is:

a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages.

This is the LINQ to SQL sample code at the beginning of this series:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    var results = from product in database.Products
                  where product.Category.CategoryName == "Beverages"
                  select new
                  {
                      product.ProductName,
                      product.UnitPrice
                  };
    foreach (var item in results)
    {
        Console.WriteLine(
            "{0}: {1}", 
            item.ProductName, 
            item.UnitPrice.ToString(CultureInfo.InvariantCulture));
    }
}

According to this post, the above query expression will be compiled to query methods:

var results = database.Products.Where(product => product.Category.CategoryName == "Beverages")
                               .Select(product => new
                                                      {
                                                          product.ProductName,
                                                          product.UnitPrice
                                                      });

It is querying the ProductName and UnitPrice fields of the Products table in the Northwind database, which belong to the specified CategoryName. To work with SQL Server representations (fields, tables, databases) in C# representations (object models), the mappings between SQL representations and C# representations need to be created. LINQ to SQL provides an Object-relational mapping designer tool to create those objects models automatically.

Create C# models from SQL schema

The easiest way of modeling is to use Visual Studio IDE. This way works with:

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2

Take the Northwind database as an example. First, setup a data connection to the Northwind database:

image

Then, create a “LINQ to SQL Classes” item to the project:

image

By creating a Northwind.dbml file, the O/R designer is opened:

image

Since the above query works with the Products table and the Categories table, just drag the 2 tables and drop to the O/R designer:

image

In the designer, the modeling is done. Please notice that the foreign key between Categories table and Products table is recognized, and the corresponding association is created in the designer.

Now the object models are ready to rock. Actually the designer has automatically created the following C# code:

  • Category class: represents each record in Categories table;
    • CategoryID property (an int): represents the CategoryID field; So are the other properties shown above;
    • Products propery (a collection of Product object): represents the associated many records in Products table
  • Product class: represents each record in Products table;
    • ProductID property (an int): represents the ProductID field; So are the other properties shown above;
    • Category propery (a Category object): represents the associated one records in Products table;
  • NorthwindDataContext class: represents the Northwind database;
    • Categories property (a collection of the Category objects): represents the Categories table;
    • Products property (a collection of the Product objects): represents the Products table;

Besides, database, tables, fields, other SQL stuff can also be modeled by this O/R designer:

image

SQL representationC# representationSample
DatabaseDataContext derived classNothwindDataContext
Table, ViewDataContext derived class’s propertyNothwindDataContext.Categories
RecordEntity classCategory
FieldEntity class’s propertyCategory.CategoryName
Foreign keyAssociation between entity classesCategory.Products
Stored procedure, functionDataContext derived class’s methodNothwindDataContext.SalesByCategory()

Another way to generate the models is to use the command line tool SqlMetal.exe. Please check MSDN for details of code generation.

And, please notice that, the Category entity class is generated from the Categories table. Here plural name is renamed to singular name, because a Category object is the mapping of one record of Categories table. This can be configured in Visual Studio:

image

Implement the mapping

Now take a look at how the SQL representations are mapped to C# representations.

The Northwind.dbml is nothing but an XML file:

<?xml version="1.0" encoding="utf-8"?>
<!-- [Northwind] database is mapped to NorthwindDataContext class. -->
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
    <!-- Connection string -->
    <Connection Mode="WebSettings" ConnectionString="Data Source=qablog.qaitdevlabs.com;Initial Catalog=Northwind;Integrated Security=True" SettingsObjectName="System.Configuration.ConfigurationManager.ConnectionStrings" SettingsPropertyName="NorthwindConnectionString" Provider="System.Data.SqlClient" />

    <!-- Categories property is a member of NorthwindDataContext class. -->
    <Table Name="dbo.Categories" Member="Categories">
        <!-- [Categories] table is mapped to Category class. -->
        <Type Name="Category">
            <!-- [CategoryID] (SQL Int) field is mapped to CategoryID property (C# int). -->
            <Column Name="CategoryID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
            <!-- [CategoryName] (SQL NVarChar(15)) field is mapped to CategoryName property (C# string). -->
            <Column Name="CategoryName" Type="System.String" DbType="NVarChar(15) NOT NULL" CanBeNull="false" />
            <!-- Other fields. -->
            <Column Name="Description" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
            <Column Name="Picture" Type="System.Data.Linq.Binary" DbType="Image" CanBeNull="true" UpdateCheck="Never" />
            <!-- [Categories] is associated with [Products] table via a foreign key.
            So Category class has a Products peoperty to represent the associated many Product objects. -->
            <Association Name="Category_Product" Member="Products" ThisKey="CategoryID" OtherKey="CategoryID" Type="Product" />
        </Type>
    </Table>

    <!-- Products property is a member of NorthwindDataContext class. -->
    <Table Name="dbo.Products" Member="Products">
        <!-- [Products] table is mapped to Product class. -->
        <Type Name="Product">
            <!-- Fields. -->
            <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
            <Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
            <Column Name="SupplierID" Type="System.Int32" DbType="Int" CanBeNull="true" />
            <Column Name="CategoryID" Type="System.Int32" DbType="Int" CanBeNull="true" />
            <Column Name="QuantityPerUnit" Type="System.String" DbType="NVarChar(20)" CanBeNull="true" />
            <Column Name="UnitPrice" Type="System.Decimal" DbType="Money" CanBeNull="true" />
            <Column Name="UnitsInStock" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="UnitsOnOrder" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="ReorderLevel" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="Discontinued" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
            <!-- [Products] is associated with [Products] table via a foreign key.
            So Product class has a Category peoperty to represent the associated one Category object. -->
            <Association Name="Category_Product" Member="Category" ThisKey="CategoryID" OtherKey="CategoryID" Type="Category" IsForeignKey="true" />
        </Type>
    </Table>
</Database>

It describes how the SQL stuff are mapped to C# stuff.

A Northwind.dbml.layout file is created along with the dbml. It is also an XML, describing how the O/R designer should visualize the objects models:

<?xml version="1.0" encoding="utf-8"?>
<ordesignerObjectsDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5" name="Northwind">
    <DataContextMoniker Name="/NorthwindDataContext" />
    <nestedChildShapes>
        <!-- Category class -->
        <classShape Id="81d67a31-cd80-4a91-84fa-5d4dfa2e8694" absoluteBounds="0.75, 1.5, 2, 1.5785953776041666">
            <DataClassMoniker Name="/NorthwindDataContext/Category" />
            <nestedChildShapes>
                <!-- Properties -->
                <elementListCompartment Id="a261c751-8ff7-471e-9545-cb385708d390" absoluteBounds="0.765, 1.96, 1.9700000000000002, 1.0185953776041665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
            </nestedChildShapes>
        </classShape>

        <!-- Product class -->
        <classShape Id="59f11c67-f9d4-4da9-ad0d-2288402ec016" absoluteBounds="3.5, 1, 2, 2.7324039713541666">
            <DataClassMoniker Name="/NorthwindDataContext/Product" />
            <nestedChildShapes>
                <!-- Properties -->
                <elementListCompartment Id="6c1141a2-f9a9-4660-8730-bed7fa15bc27" absoluteBounds="3.515, 1.46, 1.9700000000000002, 2.1724039713541665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
            </nestedChildShapes>
        </classShape>

        <!-- Association arrow -->
        <associationConnector edgePoints="[(2.75 : 2.28929768880208); (3.5 : 2.28929768880208)]" fixedFrom="Algorithm" fixedTo="Algorithm">
            <AssociationMoniker Name="/NorthwindDataContext/Category/Category_Product" />
            <nodes>
                <!-- From Category class -->
                <classShapeMoniker Id="81d67a31-cd80-4a91-84fa-5d4dfa2e8694" />
                <!-- To Product class -->
                <classShapeMoniker Id="59f11c67-f9d4-4da9-ad0d-2288402ec016" />
            </nodes>
        </associationConnector>
    </nestedChildShapes>
</ordesignerObjectsDiagram>

A Northwind.designer.cs is also created, containing the auto generated C# code.

This is how the NorthwindDataContext looks like:

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    public Table<Category> Categories
    {
        get
        {
            return this.GetTable<Category>();
        }
    }

    public Table<Product> Products
    {
        get
        {
            return this.GetTable<Product>();
        }
    }
}

And this is the Category class:

[Table(Name = "dbo.Categories")]
public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged
{
    private int _CategoryID;

    private EntitySet<Product> _Products;

    [Column(Storage = "_CategoryID", AutoSync = AutoSync.OnInsert, 
        DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
    public int CategoryID
    {
        get
        {
            return this._CategoryID;
        }
        set
        {
            if ((this._CategoryID != value))
            {
                this.OnCategoryIDChanging(value);
                this.SendPropertyChanging();
                this._CategoryID = value;
                this.SendPropertyChanged("CategoryID");
                this.OnCategoryIDChanged();
            }
        }
    }

    // Other properties.

    [Association(Name = "Category_Product", Storage = "_Products", 
        ThisKey = "CategoryID", OtherKey = "CategoryID")]
    public EntitySet<Product> Products
    {
        get
        {
            return this._Products;
        }
        set
        {
            this._Products.Assign(value);
        }
    }
}

The Products looks similar.

Customize the mapping

Since the mapping info are simply stored in the XML file and C# code, they can be customized in the O/R designer easily:

image

After renaming Category class to CategoryEntity, the XML and C# is refined automatically:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
    <Table Name="dbo.Categories" Member="CategoryEntities">
        <Type Name="CategoryEntity">
            <!-- Fields -->
        </Type>
    </Table>
    <Table Name="dbo.Products" Member="Products">
        <Type Name="Product">
            <!-- Fields -->
            <Association Name="Category_Product" Member="CategoryEntity" Storage="_Category" ThisKey="CategoryID" OtherKey="CategoryID" Type="CategoryEntity" IsForeignKey="true" />
        </Type>
    </Table>
</Database>

and

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    public Table<CategoryEntity> CategoryEntities { get; }
}

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
}

[Table(Name = "dbo.Products")]
public partial class Product : INotifyPropertyChanging, INotifyPropertyChanged
{
    [Association(Name = "Category_Product", Storage = "_Category",
        ThisKey = "CategoryID", OtherKey = "CategoryID", IsForeignKey = true)]
    public CategoryEntity CategoryEntity { get; set; }
}

Properties, associations, and inheritances and also be customized:

image

For example, The ProductID property can be renamed to ProductId to be compliant to .NET Framework Design Guidelines.

More options are available to customize the data context, entities, and properties:

image

Please notice this mapping is one way mapping, from SQL Server to C#. When the mapping information is changed in O/R designer, SQL Server is not affected at all.

And, LINQ to SQL is designed to provide a simple O/R mapping, not supporting advenced functionalities, like multi-table inheritance, etc. According to MSDN:

The single-table mapping strategy is the simplest representation of inheritance and provides good performance characteristics for many different categories of queries.

Please check this link for more details.

Work with the models

The auto generated models are very easy and extensible.

Partial class

All the generated C# classes are partial classes. For example, it is very easy to add a NorthwindDataContext,cs file and a Category.cs file to the project, and write the extension code.

Partial method

There are also a lot of partial method in the generated code:

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    #region Extensibility Method Definitions

    partial void OnCreated();
    partial void InsertCategory(Category instance);
    partial void UpdateCategory(Category instance);
    partial void DeleteCategory(Category instance);
    partial void InsertProduct(Product instance);
    partial void UpdateProduct(Product instance);
    partial void DeleteProduct(Product instance);

    #endregion
}

For example, the OnCreated() can be implemented in the NorthwindDataContext,cs:

public partial class NorthwindDataContext
{
    // OnCreated will be invoked by constructors.
    partial void OnCreated()
    {
        // The default value is 30 seconds.
        this.CommandTimeout = 40;
    }
}

When the Northwind is constructed, the OnCreated() is invoked, and the custom code is executed.

So are the entities:

[Table(Name = "dbo.Categories")]
public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged
{
    #region Extensibility Method Definitions

    partial void OnLoaded();
    partial void OnValidate(ChangeAction action);
    partial void OnCreated();
    partial void OnCategoryIDChanging(int value);
    partial void OnCategoryIDChanged();
    partial void OnCategoryNameChanging(string value);
    partial void OnCategoryNameChanged();
    partial void OnDescriptionChanging(string value);
    partial void OnDescriptionChanged();
    partial void OnPictureChanging(Binary value);
    partial void OnPictureChanged();

    #endregion
}

For example, the OnValidated() is very useful for the data correction:

[Table(Name = "dbo.Categories")]
public partial class Category
{
    partial void OnValidate(ChangeAction action)
    {
        switch (action)
        {
            case ChangeAction.Delete:
                // Validates the object when deleted.
                break;
            case ChangeAction.Insert:
                // Validates the object when inserted.
                break;
            case ChangeAction.None:
                // Validates the object when not submitted.
                break;
            case ChangeAction.Update:
                // Validates the object when updated.
                if (string.IsNullOrWhiteSpace(this._CategoryName))
                {
                    throw new ValidationException("CategoryName is invalid.");
                }
                break;
            default:
                break;
        }
    }
}

When the category object (representing a record in Categories table) is updated, the custom code checking the CategoryName will be executed.

And, because each entity class’s Xxx property’s setter involves OnXxxChanging() partial method:

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
    [Column(Storage = "_CategoryName", DbType = "NVarChar(15) NOT NULL", CanBeNull = false)]
    public string CategoryName
    {
        get
        {
            return this._CategoryName;
        }
        set
        {
            if ((this._CategoryName != value))
            {
                this.OnCategoryNameChanging(value);
                this.SendPropertyChanging();
                this._CategoryName = value;
                this.SendPropertyChanged("CategoryName");
                this.OnCategoryNameChanged();
            }
        }
    }
}

Validation can be also done in this way:

public partial class CategoryEntity
{
    partial void OnCategoryNameChanging(string value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            throw new ArgumentOutOfRangeException("value");
        }
    }
}

INotifyPropertyChanging and INotifyPropertyChanged interfaces

Each auto generated entity class implements INotifyPropertyChanging and INotifyPropertyChanged interfaces:

namespace System.ComponentModel
{
    public interface INotifyPropertyChanging
    {
        event PropertyChangingEventHandler PropertyChanging;
    }

    public interface INotifyPropertyChanged
    {
        event PropertyChangedEventHandler PropertyChanged;
    }
}

For example, in the above auto generated CategoryName code, after setting the CategoryName, SendPropertyChanged() is invoked, passing the propery name “CategoryName” as argument:

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    protected virtual void SendPropertyChanged(String propertyName)
    {
        if (this.PropertyChanged != null)
        {
            this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

This is very useful to track changes of the entity object:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    Category category = database.Categories.Single(item => item.CategoryName = "Beverages");
    category.PropertyChanged += (_, e) =>
        {
            Console.Write("Propery {0} is changed", e.PropertyName);
        };

    // Work with the category object.
    category.CategoryID = 100;
    // ...
}

And this is used for change tracking by DataContext, which will be explained later.

Programmatically access the mapping information

The mapping information is stored in DataContext.Mapping as a MetaModel object. Here is an example:

public static class DataContextExtensions
{
    public static Type GetEntityType(this DataContext database, string tableName)
    {
        return database.Mapping.GetTables()
                               .Single(table => table.TableName.Equals(
                                   tableName, StringComparison.Ordinal))
                               .RowType
                               .Type;
    }
}

The method queries the mapping information with the table name, and returns the entity type:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    Type categoryType = database.GetEntityType("dbo.Categories");
}

Create SQL schema from C# models

Usually, many people design the SQL database first, then model it with the O/R designer, and write code to work with the C# object models. But this is not required. It is totally Ok to create POCO models first without considering the SQL stuff:

public partial class Category
{
    public int CategoryID { get; set; }

    public string CategoryName { get; set; }

    public EntitySet<Product> Products { get; set; }
}

Now it is already able to start coding with this kind of models.

Later, there are 2 ways to integrate the C# program with SQL Server database:

  • Generate object models from designed SQL Server database;
  • Decorate POCO models with mapping attributes, Invoke CreateDatabase() method of DataContext to create the expected database schema in SQL Server.

For example, the C# models can be polluted with O/R mapping knowledge like this:

[Table(Name = "Categories")]
public class Category
{
    [Column(DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true)]
    public int CategoryId { get; set; }

    [Column(DbType = "NVarChar(15) NOT NULL")]
    public string CategoryName { get; set; }

    [Association(Name = "Category_Products",
        ThisKey = "CategoryId", OtherKey = "CategoryId")]
    public EntitySet<Product> Products { get; set; }
}

[Table(Name = "Products")]
public class Product
{
    [Column(DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true)]
    public int ProductId { get; set; }

    [Column(DbType = "NVarChar(40) NOT NULL")]
    public string ProductName { get; set; }

    [Column(DbType = "Int")]
    public int CategoryId { get; set; }

    [Association(Name = "Category_Products", IsForeignKey = true,
        ThisKey = "CategoryId", OtherKey = "CategoryId")]
    public Category Category { get; set; }
}

[Database(Name = "SimpleNorthwind")]
public class SimpleNorthwindDataContext : DataContext
{
    public SimpleNorthwindDataContext(IDbConnection connection)
        : base(connection)
    {
    }

    public Table<Category> Categories { get; set; }

    public Table<Product> Products { get; set; }
}

Now it is ready to create database schema in SQL server:

using (SimpleNorthwindDataContext database = new SimpleNorthwindDataContext(new SqlConnection(
    @"Data Source=qablog.qaitdevlabs.com;Initial Catalog=SimpleNorthwind;Integrated Security=True")))
{
    if (database.DatabaseExists())
    {
        database.DeleteDatabase();
    }

    database.CreateDatabase();
}

Isn’t this easy? This is the generated SimpleNorthwind database in SQL Server:

image

Copy of Session Object in c#

Many times we try to make copy of session object, but in case when we are modifying copied object we might noticed that session object gets updated automatically, the reason is both copied object and session object are pointing to same location, even if you tried to use “new” operator while creating object.

Scenario
Let say you have Member Class as mentioned under
public class Member{

public string FirstName { get; set; }
public string LastName { get; set; }
}

Problem:
Member objMember = new Member();
objMember.FirstName = “Sachin”;
objMember.LastName = “Tendulkar”;

Then try to save object in Session
Session[“MemberObj”] = objMember;

This method will work good till we are just accessing object from session, but in case if we try to update object created from session it will update value of session also.

That is,
Member newMember = new Member(); //Even though object is created using “new” keyword.
newMember = (Member) Session[“MemberObj”];
newMember.FirstName = “YOGESHWAR”; //This will update session FirstName also.

Solution:
To make copies of session you need to create a clone method in class.

In above class create a method clone, to support copy of session.

public class Member{

public string FirstName { get; set; }
public string LastName { get; set; }

public Member clone()
{
Member cloneMember = new Member();
cloneMember.FirstName = this.FirstName;
cloneMember.LastName = this.LastName;
}
}

Now, while accessing session object, you can call clone method to make copy of session.

Member newMember = new Member();
newMember = ((Member) Session[“MemberObj”]).clone();

now if you try to modify object copied from session, will not update session object.
newMember.FirstName = “YOGESHWAR”; //Will not update session FirstName

Implement Lazy Loading in C# Using Lazy Class

Implement Lazy Loading in C# Using Lazy Class

Lazy loading is a nice and very important concept in the programming world. Sometimes it helps to improve performance and adapt best practices in application design. Let’s discuss why lazy loading is useful and how it helps to develop a high performance application.

Lazy loading is essential when the cost of object creation is very high and the use of the object is vey rare. So, this is the scenario where it’s worth implementing lazy loading.
The fundamental idea of lazy loading is to load object/data when needed.

At first we will implement a traditional concept of loading (it’s not lazy loading) and then we will try to understand the problem in this. Then we will implement lazy loading to solve the problem.

Have a look at the following code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleAPP

{

public class PersonalLoan

{

public string AccountNumber { get; set; }

public string AccounHolderName { get; set; }

public Loan LoanDetail { get; set; }

public PersonalLoan(string accountNumber)

{

this.AccountNumber = accountNumber;

this.AccounHolderName = “Sourav”;

this.LoanDetail = new Loan(this.AccountNumber);

}

}

public class Loan

{

public string AccountNumber { get; set; }

public float LoanAmount { get; set; }

public bool IsLoanApproved { get; set; }

public Loan(string accountNumber)

{

Console.WriteLine(“Loan loading started”);

this.AccountNumber = accountNumber;

this.LoanAmount = 1000;

this.IsLoanApproved = true;

Console.WriteLine(“Loan loading started”);

}

}

class Program

{

static void Main(string[] args)

{

PersonalLoan p = new PersonalLoan(“123456”);

 

 

Console.ReadLine();

}

}

}
This is not lazy loading since we are seeing that the LoadDetail property is being populated at the time of PersonalLoan object creation. If object creation of LoanDetail is very costly then it will be a very time and resource intensive operation to create an object of a PersonalLoad class.

Ok, somehow we will implement a mechanism to populate the LoadDetail property in delay, I mean if needed we will populate the property otherwise not.

It can solve our problem and obviously it will improve performance in the application. Have a look at the following example. We can see that the LoanDetail property will not be populated when an object of the PersonalLoan class is created.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleAPP

{

public class PersonalLoan

{

public string AccountNumber { get; set; }

public string AccounHolderName { get; set; }

public Loan LoanDetail { get; set; }

public PersonalLoan(string accountNumber)

{

this.AccountNumber = accountNumber;

this.AccounHolderName = “Sourav”;

}

}

public class Loan

{

public string AccountNumber { get; set; }

public float LoanAmount { get; set; }

public bool IsLoanApproved { get; set; }

public Loan(string accountNumber)

{

Console.WriteLine(“Loan loading started”);

this.AccountNumber = accountNumber;

this.LoanAmount = 1000;

this.IsLoanApproved = true;

Console.WriteLine(“Loan loading started”);

}

}

class Program

{

static void Main(string[] args)

{

PersonalLoan p = new PersonalLoan(“123456”);

 

//Load Detail started to load

p.LoanDetail = new Loan(“123456”);

Console.WriteLine(p.LoanDetail.AccountNumber);

Console.WriteLine(p.LoanDetail.IsLoanApproved);

Console.WriteLine(p.LoanDetail.LoanAmount);

 

Console.ReadLine();

}

}

}
So, we are seeing that the LoanDetail Property is still null. As soon as we load the property in our code , it will be populated.

Finally

Lazy loading is a nice feature of application development, the developer should implement it wisely to enhance performance and reduce the cost of application execution.

Implement lazy loading using Lazy<T> class

As we know, lazy loading is a nice feature of applications, not only to improve the performance of the application but also it helps to manage memory and other resource efficiently. Basically we can use lazy initialization when a large object is created or the execution of a resource- intensive task in particular when such creation or execution might not occur during the lifetime of the program.

And there are many choices to implement lazy initialization. We can use our own implementation to delay object population or we can use the Lazy<T> class of the .NET library to do it.

To prepare for lazy initialization, you create an instance of Lazy<T>. The type argument of the Lazy<T> object that you create specifies the type of the object that you want to initialize lazily. The constructor that you use to create the Lazy<T> object determines the characteristics of the initialization. Lazy initialization occurs the first time the Lazy<T>.Value property is accessed.

The Lazy<T> class contains two properties by which we can detect the status of the lazy class.

IsValueCreated

This property will tell us whether or not the value is initializing in a lazy class.

Value

It gets the lazy initialized value of the current Lazy<T> instance.

Fine. We will now implement one simple class and we will see how lazy<T> works with it. Have a look at the following example.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace ConsoleAPP

{

public class Test

{

private List<string> list = null;

public Test()

{

Console.WriteLine(“List Generated:”);

list = new List<string>() {

“Sourav”,”Ram”

};

}

public List<string> Names

{

get

{

return list;

}

}

}

class Program

{

static void Main(string[] args)

{

Lazy<Test> lazy = new Lazy<Test>();

Console.WriteLine(“Data Loaded : ” + lazy.IsValueCreated);

Test t = lazy.Value;

 

foreach (string tmp in t.Names)

{

Console.WriteLine(tmp);

}

Console.ReadLine();

}

}

}
The Test class has been declared and the instance (lazy) has created a Lazy<T> class. We are then checking whether the value is populated or not in the Test class. In the output we are seeing the value is “False” so, the value is still not populated.

Whenever the line “Test t = lazy.Value” executes, the value will be populated in the Test class and this is how the Test class will initialize lazily.

In the next line we are accessing the property of the test class that will return a string array and we are printing it. Here is sample output in the following.

The question may occur to you, is lazy<T> thread safe ?

By default, all public and protected members of the Lazy<T> class are thread safe and may be used concurrently from multiple threads. These thread-safety guarantees may be removed optionally and per instance, using parameters to the type’s constructors.

Thanks for reading, Happy learning. In the next article we will see how to implement lazy loading in Entity Framework.

Introducing U-SQL – A Language that makes Big Data Processing Easy

Microsoft announced the new Azure Data Lake services for analytics in the cloud that includes a hyper-scale repository, a new analytics service built on YARN that allows data developers and data scientists to analyze all data, and HDInsight, a fully managed Hadoop, Spark, Storm and HBase service. Azure Data Lake Analytics includes U-SQL, a language that unifies the benefits of SQL with the expressive power of your own code. U-SQL’s scalable distributed query capability enables you to efficiently analyze data in the store and across relational stores such as Azure SQL Database. In this blog post I will outline the motivation for U-SQL, some of our inspiration, and design philosophy behind the language, and show you a few examples of the major aspects of the language.

Why U-SQL?

If you analyze the characteristics of Big Data analytics, several requirements arise naturally for an easy to use, yet powerful language:

  • Process any type of data. From analyzing BotNet attack patterns from security logs to extracting features from images and videos for machine learning, the language needs to enable you to work on any data.
  • Use custom code easily to express your complex, often proprietary business algorithms. The example scenarios above may all require custom processing that is often not easily expressed in standard query languages, ranging from user defined functions, to custom input and output formats.
  • Scale efficiently to any size of data without you focusing on scale-out topologies, plumbing code, or limitations of a specific distributed infrastructure.

How do existing Big Data languages stack up to these requirements?

SQL-based languages (such as Hive and others) provide you with a declarative approach that natively does the scaling, parallel execution, and optimizations for you. This makes them easy to use, familiar to a wide range of developers, and powerful for many standard types of analytics and warehousing. However, their extensibility model and support for non-structured data and files are often bolted on and harder to use. For example, even if you just want to quickly explore your data in a file or remote data source, you need to create catalog objects to schematize file data or remote sources before you can query them, which reduces your agility. And although SQL-based languages often have several extensibility points for custom formatters, user-defined functions, and aggregators, they are rather complex to build, integrate, and maintain, with varying degrees of consistency in the programming models.

Programming language-based approaches to process Big Data, for their part, provide an easy way to add your custom code. However, a programmer often has to explicitly code for scale and performance, often down to managing the execution topology and workflow such as the synchronization between the different execution stages or the scale-out architecture. This code can be difficult to write correctly, and optimized for performance. Some frameworks support declarative components such as language integrated queries or embedded SQL support. However, SQL may be integrated as strings and thus lacking tool support, the extensibility integration may be limited or – due to the procedural code that does not guard against side-effects – hard to optimize, and does not provide for reuse.

Taking the issues of both SQL-based and procedural languages into account, we designed U-SQL from the ground-up as an evolution of the declarative SQL language with native extensibility through user code written in C#. This unifies both paradigms, unifies structured, unstructured, and remote data processing, unifies the declarative and custom imperative coding experience, and unifies the experience around extending your language capabilities.

U-SQL is built on the learnings from Microsoft’s internal experience with SCOPE and existing languages such as T-SQL, ANSI SQL, and Hive. For example, we base our SQL and programming language integration and the execution and optimization framework for U-SQL on SCOPE, which currently runs hundred thousands of jobs each day internally. We also align the metadata system (databases, tables, etc.), the SQL syntax, and language semantics with T-SQL and ANSI SQL, the query languages most of our SQL Server customers are familiar with. And we use C# data types and the C# expression language so you can seamlessly write C# predicates and expressions inside SELECT statements and use C# to add your custom logic. Finally, we looked to Hive and other Big Data languages to identify patterns and data processing requirements and integrate them into our framework.

In short, basing U-SQL language on these existing languages and experiences should make it easy for you to get started and powerful enough for the hardest problems.

Show me U-SQL!

Let’s assume that I have downloaded my Twitter history of all my tweets, retweets, and mentions as a CSV file and placed it into my Azure Data Lake Store.

 

In this case I know the schema of the data I want to process, and for starters I want to just count the number of tweets for each of the authors in the tweet “network”:

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

 

@res = SELECT author

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author;

 

OUTPUT @res TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

The above U-SQL script shows the three major steps of processing data with U-SQL:

  1. Extract data from your source. Note that you just schematize it in your query with the EXTRACT statement. The datatypes are based on C# datatypes and I use the built-in Extractors library to read and schematize the CSV file.
  2. Transform using SQL and/or custom user defined operators (which we will cover another time). In the example above, it is a familiar SQL expression that does a GROUP BY aggregation.
  3. Output the result either into a file or into a U-SQL table to store it for further processing.

Note that U-SQL’s SQL keywords have to be upper-case to provide syntactic differentiation from syntactic C# expressions with the same keywords but different meaning.

Also notice that each of the expressions are assigned to a variable (@t and @res). This allows U-SQL to incrementally transform and combine data step by step expressed as an incremental expression flow using functional lambda composition (similar to what you find in the Pig language). The execution framework, then, composes the expressions together into a single expression. That single expression can then be globally optimized and scaled out in a way that isn’t possible if expressions are being executed line by line. The following picture shows you the graph generated for the next query in this blog post:

 

Going back to our example, I now want to add additional information about the people mentioned in the tweets and extend my aggregation to return how often people in my tweet network are authoring tweets and how often they are being mentioned. Because I can use C# to operate on the data, I can use an inline C# LINQ expression to extract the mentions into an ARRAY. Then I turn the array into a rowset with EXPLODE and apply the EXPLODE to each row’s array with a CROSS APPLY. I union the authors with the mentions, but need to drop the leading @-sign to align it with the author values. This is done with another C# expression where I take the Substring starting at position 1.

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

@m = SELECT new SQL.ARRAY<string>(

tweet.Split(‘ ‘).Where(x => x.StartsWith(“@”))) AS refs

FROM @t;

 

@t = SELECT author, “authored” AS category

FROM @t

UNION ALL

SELECT r.Substring(1) AS r, “mentioned” AS category

FROM @m CROSS APPLY EXPLODE(refs) AS Refs(r);

 

@res = SELECT author

, category

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author, category;

 

OUTPUT @res TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

As a next step I can use the Azure Data Lake Tools for Visual Studio to refactor the C# code into C# functions using the tool’s code-behind functionality. When I then submit the script, it automatically deploys the code to the service.

 

I can also deploy and register the code as an assembly in my U-SQL metadata catalog. This allows me and other people to use the code in future scripts. The following script shows how to refer to the functions, assuming the assembly was registered as TweetAnalysis:

REFERENCE ASSEMBLY TweetAnalysis;

 

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

 

@m = SELECT Tweets.Udfs.get_mentions(tweet) AS refs

FROM @t;

 

@t = SELECT author, “authored” AS category

FROM @t

UNION ALL

SELECT Tweets.Udfs.cleanup_mentions(r) AS r, “mentioned” AS category

FROM @m CROSS APPLY EXPLODE(refs) AS Refs(r);

 

@res = SELECT author

, category

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author, category;

 

OUTPUT @res

TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

Because I noticed that I need to do a bit more cleanup around the mentions besides just dropping the @ sign, the assembly also contains a cleanup_mentions functions that does additional processing beyond dropping the @.

This is why U-SQL!

I hope you got a glimpse at why we think U-SQL makes it easy to query and process Big Data and that you understand our thinking behind the language. Over the next couple of weeks we will be expanding more on the language design philosophy and provide more sample code and scenarios over at our Big Data topic in the Azure blog. We’ll also dive in deeper into many of the additional capabilities such as:

  • Operating over set of files with patterns
  • Using (Partitioned) Tables
  • Federated Queries against Azure SQL DB
  • Encapsulating your U-SQL code with Views, Table-Valued Functions, and Procedures
  • SQL Windowing Functions
  • Programming with C# User-defined Operators (custom extractors, processors)
  • Complex Types (MAP, ARRAY)
  • Using U-SQL in data processing pipelines
  • U-SQL in a lambda architecture for IOT analytics

U-SQL makes Big Data processing easy because it:

  • Unifies declarative queries with the expressiveness of your user code
  • Unifies querying structured and unstructured data
  • Unifies local and remote queries
  • Increases productivity and agility from Day 1 for YOU!

Not Just U-SQL – Azure Data Lake provides Productivity on All Your Data

U-SQL is just one of the ways that we are working to make Azure Data Lake the most productive environment for authoring, debugging and optimizing analytics at any scale. With rich support for authoring and monitoring Hive jobs, a C# based authoring model for building Storm jobs for real time streaming, and supporting every stage of the job lifecycle from development to operationalization, the Azure Data Lake services let you focus more on the questions you want to answer than spending time debugging distributed infrastructure. Our goal is to make big data technology simpler and more accessible to the greatest number of people possible: big data professionals, engineers, data scientists, analysts and application developers.

Magic Table in SQL Server

Magic Tables
Magic tables are nothing but the logical tables maintained by SQL server internally.
There are two types of Magic tables available in SQL server:
  • Inserted
  • Deleted
We can not see or access these tables directly, not even their data-type. The only method to have access to these tables is Triggers operation either After Trigger or Instead of trigger.
Inserting into Table (Inserted Table):
Whenever we do  insert anything in our base table in database, a table gets created automatically by the
SQL server, named as INSERTED. In this table current updated or inserted record will be available. we can access this table of record via triggers.
 
Updating Table (Inserted & Deleted Table):
Whenever we do any deletion operation on our base table, in spite of one, two tables are created, one is INSERTED and another is called DELETED. Deleted table consist of the current record after the deletion operation and  Inserted table consists of the previous record. We can access it via Triggers functionality.
 
Deleting (Deleted Table):
Whenever we do deletion in base table in database, a table gets created automatically by the SQL server, named as
DELETED table. This table consist of current updated record after deletion operation. Again we can have access to these records via triggers.