using System.Collections.Generic;
namespace CustomerOrdersSample.Domain
{
   public class Customer
   {
       public virtual int Id { get; set; }
       public virtual string Name { get; set; }
       public virtual IList<Order> Orders { get; set; }
   }
}

using System;
using System.Collections.Generic;
namespace CustomerOrdersSample.Domain
{
   public class Order
   {
       public virtual int Id { get; set; }
       public virtual DateTime OrderDate { set; get; }
       public virtual Customer Customer { get; set; }
       public virtual IList<OrderItem> OrderItems { set; get; }
   }
}

namespace CustomerOrdersSample.Domain
{
   public class OrderItem
   {
       public virtual int Id { get; set; }
       public virtual Product Product { get; set; }
       public virtual int Quntity { get; set; }
       public virtual Order Order { set; get; }
   }
}

namespace CustomerOrdersSample.Domain
{
   public class Product
   {
       public virtual int Id { set; get; }
       public virtual string Name { get; set; }
       public virtual decimal UnitPrice { get; set; }
   }
}

    create table Customers (
      CustomerId INT IDENTITY NOT NULL,
      Name NVARCHAR(255) null,
      primary key (CustomerId)
   )

   create table Orders (
      OrderId INT IDENTITY NOT NULL,
      OrderDate DATETIME null,
      CustomerId INT null,
      primary key (OrderId)
   )

   create table OrderItems (
      OrderItemId INT IDENTITY NOT NULL,
      Quntity INT null,
      ProductId INT null,
      OrderId INT null,
      primary key (OrderItemId)
   )

   create table Products (
      ProductId INT IDENTITY NOT NULL,
      Name NVARCHAR(255) null,
      UnitPrice NUMERIC(19,5) null,
      primary key (ProductId)
   )

   alter table Orders
       add constraint fk_Customer_Order
       foreign key (CustomerId)
       references Customers

   alter table OrderItems
       add constraint fk_Product_OrderItem
       foreign key (ProductId)
       references Products

   alter table OrderItems
       add constraint fk_Order_OrderItem
       foreign key (OrderId)
       references Orders

SET IDENTITY_INSERT [Customers] ON;
GO
INSERT INTO [Customers] ([CustomerId],[Name]) VALUES (1,N'Customer1');
GO
SET IDENTITY_INSERT [Customers] OFF;
GO
SET IDENTITY_INSERT [Products] ON;
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (1,N'Product1',1000.00000);
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (2,N'Product2',2000.00000);
GO
INSERT INTO [Products] ([ProductId],[Name],[UnitPrice]) VALUES (3,N'Product3',3000.00000);
GO
SET IDENTITY_INSERT [Products] OFF;
GO
SET IDENTITY_INSERT [Orders] ON;
GO
INSERT INTO [Orders] ([OrderId],[OrderDate],[CustomerId]) VALUES (1,{ts '2011-01-07 11:25:20.000'},1);
GO
SET IDENTITY_INSERT [Orders] OFF;
GO
SET IDENTITY_INSERT [OrderItems] ON;
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (1,10,1,1);
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (2,5,2,1);
GO
INSERT INTO [OrderItems] ([OrderItemId],[Quntity],[ProductId],[OrderId]) VALUES (3,20,3,1);
GO
SET IDENTITY_INSERT [OrderItems] OFF;
GO

var list = session.QueryOver<Customer>().List();

foreach (var customer in list)
{
      foreach (var order in customer.Orders)
      {
          foreach (var orderItem in order.OrderItems)
          {
                Console.WriteLine("{0}:{1}:{2}", customer.Name, order.OrderDate, orderItem.Product.Name);
           }
       }
}

Customer1:2011/01/07 11:25:20 :Product1
Customer1:2011/01/07 11:25:20 :Product2
Customer1:2011/01/07 11:25:20 :Product3

var list = session
                   .QueryOver<Customer>()
                   .Fetch(c => c.Orders).Eager
                   .Fetch(c => c.Orders.First().OrderItems).Eager
                   .Fetch(c => c.Orders.First().OrderItems.First().Product).Eager
                   .List();

foreach (var customer in list)
{
     foreach (var order in customer.Orders)
     {
           foreach (var orderItem in order.OrderItems)
           {
               Console.WriteLine("{0}:{1}:{2}", customer.Name, order.OrderDate, orderItem.Product.Name);
            }
      }
}

...
.TransformUsing(NHibernate.Transform.Transformers.DistinctRootEntity)
.List();

var list = session
                   .Query()
                   .FetchMany(c => c.Orders)
                   .ThenFetchMany(o => o.OrderItems)
                   .ThenFetch(p => p.Product)
                   .ToList();

CREATE TABLE [tblWSTest](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [f1] [nvarchar](50) NULL,
 [f2] [nvarchar](500) NULL,
CONSTRAINT [PK_tblWSTest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [tblWSTest] ON
INSERT [tblWSTest] ([id], [f1], [f2]) VALUES (1, N'a1', N'a2')
INSERT [tblWSTest] ([id], [f1], [f2]) VALUES (2, N'b1', N'b2')
INSERT [tblWSTest] ([id], [f1], [f2]) VALUES (3, N'c1', N'c2')
INSERT [tblWSTest] ([id], [f1], [f2]) VALUES (4, N'd1', N'd2')
INSERT [tblWSTest] ([id], [f1], [f2]) VALUES (5, N'e1', N'e2')
SET IDENTITY_INSERT [dbo].[tblWSTest] OFF

CREATE PROCEDURE GetAllData
AS
 SELECT f1,
        f2
 FROM   tblWSTest

CREATE ENDPOINT GetDataService
   STATE = STARTED
AS HTTP(
          PATH = '/GetData',
          AUTHENTICATION = (INTEGRATED),
          PORTS = (CLEAR),
          CLEAR_PORT = 8080,
          SITE = '*'
      )
FOR SOAP(
           WEBMETHOD 'GetAllData'
           (NAME = 'testdb2009.dbo.GetAllData'),
           WSDL = DEFAULT,
           DATABASE = 'testdb2009',
           NAMESPACE = DEFAULT
       )

         <security mode="TransportCredentialOnly">
           <transport clientCredentialType="Windows" proxyCredentialType="None"
               realm="" />
           <message clientCredentialType="UserName" algorithmSuite="Default" />
         </security>

using System;
using System.Data;
using System.Windows.Forms;

namespace WebServiceTest
{
   public partial class Form1 : Form
   {
       public Form1()
       {
           InitializeComponent();
       }

       private void Form1_Load(object sender, EventArgs e)
       {
           ServiceReference1.GetDataServiceSoapClient data =
               new ServiceReference1.GetDataServiceSoapClient();
           dataGridView1.DataSource = (data.GetAllData()[0] as DataSet).Tables[0];
       }
   }
}

public class CustomRoleProvider : System.Web.Security.RoleProvider
    {
        public override bool IsUserInRole(string username, string roleName)
        {
            //if (username.ToLowerInvariant() == "ali" && roleName.ToLowerInvariant() == "User")
            //    return true;
            // blabla ...  
            return true;
        }

        public override string[] GetRolesForUser(string username)
        {
            using (var context = new PublishingContext())
            {
                var user = context.Users.Where(x => x.Username == username).FirstOrDefault();

                var roles = from ur in user.Roles
                            from r in context.Roles
                            where ur.Id == r.Id
                            select r.Role; //نام نقش
                if (roles != null)
                {
                    return roles.ToArray();
                }
            }
            
            return new string[] {};
        }
}

public void CheckSQLServerStat(Exception ex)
{
    try
    {
        SqlException ar = (SqlException) ex;
        switch (ar.Number)
        {
            case 2:
            case 11:
            case 17:
            case 40:
            case 4060:
            case 1326:
            case 17142:
            case 18456:
                HttpContext.Current.Response.Write("<br/>" + "اتصال با سرور اس کیوال قطع شده است. لطفا با مسئول مربوطه هماهنگ نمائید." + "<br/> SQLErr:" + ar.Number + "<br/>");
                break;
        }
    }catch{}
}

------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Database 'dbName' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
------------------------------

use master;
alter database dbName set OFFLINE;
alter database dbName set online;

    [AttributeUsage(AttributeTargets.Property,Inherited = true)]
    public class RequestBodyField:Attribute
    {
        public string Field;
        public RequestBodyField(string field)
        {
            this.Field = field;
        }
    }

    public class EmployeesRequestBody
    {
        [RequestBodyField("current")]
        public  int CurrentPage { get; set; }

        [RequestBodyField("rowcount")]
        public int RowCount { get; set; }

        [RequestBodyField("searchPhrase")]
        public string SearchPhrase { get; set; }

        [RequestBodyField("sort")]
        public NameValueCollection SortDictionary { get; set; }
    }

 public T GetFromQueryString<T>() where T : new()
        {
            var obj = new T();

            var queryString = HttpContext.Current.Request.QueryString;
            var queries = HttpUtility.ParseQueryString(queryString.ToString());

            var properties = typeof(T).GetProperties();
            foreach (var property in properties)
            {
                foreach (Attribute attribute in property.GetCustomAttributes(true))
                {
                    var requestBodyField = attribute as RequestBodyField; 
                    if (requestBodyField == null) continue;

                    //get value of query string
                    var valueAsString = queries[requestBodyField.Field];

                    var converter = TypeDescriptor.GetConverter(property.PropertyType);
                    var value = converter.ConvertFrom(valueAsString);

                    if (value == null)
                        continue;

                    property.SetValue(obj, value, null);
                }
            }
            return obj;
        }

HttpContext.Current.Request.QueryString

   var converter = TypeDescriptor.GetConverter(property.PropertyType);
   var value = converter.ConvertFrom(valueAsString);

if (valueAsString == null)
                    {
                        var keys = from key in queries.AllKeys where key.StartsWith(requestBodyField.Field) select key;

                        var collection = new NameValueCollection();

                        foreach (var key in keys)
                        {
                            var openBraketIndex = key.IndexOf("[", StringComparison.Ordinal);
                            var closeBraketIndex = key.IndexOf("]", StringComparison.Ordinal);

                            if (openBraketIndex < 0 || closeBraketIndex < 0)
                                throw new Exception("query string is corrupted.");

                            openBraketIndex++;
                            //get key in [...]
                            var fieldName = key.Substring(openBraketIndex, closeBraketIndex - openBraketIndex);
                            collection.Add(fieldName, queries[key] );
                        }
                        property.SetValue(obj, collection, null);
                        continue;
                    }

        public T GetFromQueryString<T>() where T : new()
        {
            var obj = new T();
            var properties = typeof(T).GetProperties();

            var queryString = HttpContext.Current.Request.QueryString;
            var queries = HttpUtility.ParseQueryString(queryString.ToString());

            foreach (var property in properties)
            {
                foreach (Attribute attribute in property.GetCustomAttributes(true))
                {
                    var requestBodyField = attribute as RequestBodyField; 
                    if (requestBodyField == null) continue;

                    //get value of query string
                    var valueAsString = queries[requestBodyField.Field];

                    if (valueAsString == null)
                    {
                        var keys = from key in queries.AllKeys where key.StartsWith(requestBodyField.Field) select key;

                        var collection = new NameValueCollection();

                        foreach (var key in keys)
                        {
                            var openBraketIndex = key.IndexOf("[", StringComparison.Ordinal);
                            var closeBraketIndex = key.IndexOf("]", StringComparison.Ordinal);

                            if (openBraketIndex < 0 || closeBraketIndex < 0)
                                throw new Exception("query string is corrupted.");

                            openBraketIndex++;
                            //get key in [...]
                            var fieldName = key.Substring(openBraketIndex, closeBraketIndex - openBraketIndex);
                            collection.Add(fieldName, queries[key]);
                        }
                        property.SetValue(obj, collection, null);
                        continue;
                    }

                    var converter = TypeDescriptor.GetConverter(property.PropertyType);
                    var value = converter.ConvertFrom(valueAsString);

                    if (value == null)
                        continue;

                    property.SetValue(obj, value, null);
                }
            }
            return obj;
        }

public virtual ActionResult GetEmployees()
{
     var request = new Requests().GetFromQueryString<EmployeesRequestBody>();
}