SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

SELECT *
INTO [Sales].[Copy_Orders]
FROM [Sales].[Orders];
GO

SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO

(33035 rows affected)
Table 'Copy_Orders'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 79 ms,  elapsed time = 762 ms.

CREATE NONCLUSTERED INDEX [IX_Copy_Orders_CustomerID]
ON [Sales].[Copy_Orders] (
[CustomerID]
)
INCLUDE (
[OrderID], [OrderDate]
);
GO

SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO

SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;
GO

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_CustomerID] ON [Sales].[Orders]
(
[CustomerID] ASC
)

SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]

SELECT TOP 10
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;

(10 rows affected)
Table 'Orders'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT
    [CustomerID],
    [OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 30000;

SELECT
    [CustomerID],
    [OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 60000;

(43595 rows affected)
Table 'Orders'. Scan count 1, logical reads 191, physical reads 1, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
CPU time = 31 ms,  elapsed time = 754 ms.


(13595 rows affected)
Table 'Orders'. Scan count 1, logical reads 131, physical reads 0, read-ahead reads 127, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
CPU time = 16 ms,  elapsed time = 276 ms.

• مطالب مرتبط با Defensive Programming 
  
• نکات کار با استثناءها در دات نت
  

متدی تحت عنوان ValidateEmail را تصور کنید. این متد از حیث بازگشت نتیجه به عنوان خروجی می‌تواند به اشکال مختلفی پیاده سازی شود که در ادامه مشاهده می‌کنیم:

متد ValidateEmail با خروجی Boolean

        public bool ValidateEmail(string email)
        {
            var valid = true;
            if (string.IsNullOrWhiteSpace(email))
            {
                valid = false;
            }

            var isValidFormat = true;//todo: using RegularExpression
            if (!isValidFormat)
            {
                valid = false;
            }

            var isRealDoamin = true;//todo: Code here that confirms whether domain exists.
            if (!isRealDoamin)
            {
                valid = false;
            }

            return valid;
        }

همانطور که در تکه کد زیر مشخص می‌باشد، استفاده کننده از متد بالا، امکان بررسی خروجی آن را در قالب یک شرط خواهد داشت و علاوه بر اینکه پیاده سازی آن ساده می‌باشد، خوانایی کد را نیز بالا می‌برد؛ ولی با این حال نمی‌توان متوجه شد مشکل اصلی آدرس ایمیل ارسالی به عنوان آرگومان، دقیقا چیست.

var email = "email@example.com";
var isValid = ValidateEmail(email);
if(isValid)
{
    //do something
}

متد ValidateEmail با صدور استثناء

        public void ValidateEmail(string email)
        {
            if (string.IsNullOrWhiteSpace(email)) throw new ArgumentNullException(nameof(email));

            var isValidFormat = true;//todo: using RegularExpression
            if (!isValidFormat) throw new ArgumentException("email is not in a correct format");

            var isRealDoamin = true;//todo: Code here that confirms whether domain exists.
            if (!isRealDoamin) throw new ArgumentException("email does not include a valid domain.")
        }

روش بالا هم جواب می‌دهد ولی بهتر است کلاس Exception سفارشی به عنوان مثال ValidationException برای این قضیه در نظر گرفته شود تا بتوان وهله‌های صادر شده از این نوع را در لایه‌های بالاتر مدیریت کرد.

متد ValidateEmail با چندین خروجی

برای این منظور چندین راه حل پیش رو داریم.

با استفاده از پارامتر out:

        public bool ValidateEmail(string email, out string message)
        {
            var valid = true;
            message = string.Empty;

            if (string.IsNullOrWhiteSpace(email))
            {
                valid = false;
                message = "email is null.";
            }

            if (valid)
            {
                var isValidFormat = true;//todo: using RegularExpression
                if (!isValidFormat)
                {
                    valid = false;
                    message = "email is not in a correct format";
                }
            }

            if (valid)
            {
                var isRealDoamin = true;//todo: Code here that confirms whether domain exists.
                if (!isRealDoamin)
                {
                    valid = false;
                    message = "email does not include a valid domain.";
                }
            }

            return valid;
        }

var email = "email@example.com";
var isValid = ValidateEmail(email, out string message);
if (isValid)
{
    //do something
}

Tuple<bool, List<string>> result = Tuple.Create<bool, List<string>>(true, new List<string>());

        public class OperationResult
        {
            public bool Success { get; set; }
            public IList<string> Messages { get; } = new List<string>();

            public void AddMessage(string message)
            {
                Messages.Add(message);
            }
        }

        public OperationResult ValidateEmail(string email)
        {
            var result = new OperationResult();

            if (string.IsNullOrWhiteSpace(email))
            {
                result.Success = false;
                result.AddMessage("email is null.");
            }

            if (result.Success)
            {
                var isValidFormat = true;//todo: using RegularExpression
                if (!isValidFormat)
                {
                    result.Success = false;
                    result.AddMessage("email is not in a correct format");
                }
            }

            if (result.Success)
            {
                var isRealDoamin = true;//todo: Code here that confirms whether domain exists.
                if (!isRealDoamin)
                {
                    result.Success = false;
                    result.AddMessage("email does not include a valid domain.");
                }
            }

            return result;
        }

استفاده از Exception برای نمایش پیغام برای کاربر نهایی

با صدور یک استثناء و مدیریت سراسری آن در بالاترین (خارجی ترین) لایه و نمایش پیغام مرتبط با آن به کاربر نهایی، می‌توان از آن به عنوان ابزاری برای ارسال هر نوع پیغامی به کاربر نهایی استفاده کرد. اگر قوانین تجاری با موفقیت برآورده نشده‌اند یا لازم است به هر دلیلی یک پیغام مرتبط با یک اعتبارسنجی تجاری را برای کاربر نمایش دهید، این روش بسیار کارساز می‌باشد و با یکبار وقت گذاشتن برای توسعه زیرساخت برای این موضوع به عنوان یک Cross Cutting Concern تحت عنوان Exception Management آزادی عمل زیادی در ادامه توسعه سیستم خود خواهید داشت.

به عنوان مثال داشتن یک کلاس Exception سفارشی تحت عنوان UserFriendlyException در این راستا یک الزام می‌باشد.

   [Serializable]
   public class UserFriendlyException : Exception
   {
       public string Details { get; private set; }
       public int Code { get; set; }

       public UserFriendlyException()
       {
       }

       public UserFriendlyException(SerializationInfo serializationInfo, StreamingContext context)
           : base(serializationInfo, context)
       {

       }

       public UserFriendlyException(string message)
           : base(message)
       {
       }

       public UserFriendlyException(int code, string message)
           : this(message)
       {
           Code = code;
       }

       public UserFriendlyException(string message, string details)
           : this(message)
       {
           Details = details;
       }

       public UserFriendlyException(int code, string message, string details)
           : this(message, details)
       {
           Code = code;
       }

       public UserFriendlyException(string message, Exception innerException)
           : base(message, innerException)
       {
       }

       public UserFriendlyException(string message, string details, Exception innerException)
           : this(message, innerException)
       {
           Details = details;
       }
   }

و همچنین لازم است در بالاترین لایه سیستم خود به عنوان مثال برای یک پروژه ASP.NET MVC یا ASP.NET Core MVC می‌توان یک ExceptionFilter سفارشی نیز تهیه کرد که هم به صورت سراسری استثنا‌ءهای سفارشی شما را مدیریت کند و همچنین خروجی مناسب Json برای استفاده در سمت کلاینت را نیز مهیا کند. به عنوان مثال برای درخواست‌های Ajax ای لازم است در سمت کلاینت نیز پاسخ‌های رسیده از سمت سرور به صورت سراسری مدیریت شوند و برای سایر درخواست‌ها همان نمایش صفحات خطای پیغام مرتبط با استثناء رخ داده شده کفایت می‌کند.

یک مدل پیشنهادی برای تهیه خروجی مناسب برای ارسال جزئیات استثنا رخ داده در درخواست‌های Ajax ای

    [Serializable]
    public class MvcAjaxResponse : MvcAjaxResponse<object>
    {
        public MvcAjaxResponse()
        {
        }

        public MvcAjaxResponse(bool success)
            : base(success)
        {
        }

        public MvcAjaxResponse(object result)
            : base(result)
        {
        }

        public MvcAjaxResponse(ErrorInfo error, bool unAuthorizedRequest = false)
            : base(error, unAuthorizedRequest)
        {
        }
    }
   

    [Serializable]
    public class MvcAjaxResponse<TResult> : MvcAjaxResponseBase
    {
        public MvcAjaxResponse(TResult result)
        {
            Result = result;
            Success = true;
        }

        public MvcAjaxResponse()
        {
            Success = true;
        }

        public MvcAjaxResponse(bool success)
        {
            Success = success;
        }

        public MvcAjaxResponse(ErrorInfo error, bool unAuthorizedRequest = false)
        {
            Error = error;
            UnAuthorizedRequest = unAuthorizedRequest;
            Success = false;
        }

        /// <summary>
        ///     The actual result object of AJAX request.
        ///     It is set if <see cref="MvcAjaxResponseBase.Success" /> is true.
        /// </summary>
        public TResult Result { get; set; }
    }

    public class MvcAjaxResponseBase
    {
        public string TargetUrl { get; set; }

        public bool Success { get; set; }

        public ErrorInfo Error { get; set; }

        public bool UnAuthorizedRequest { get; set; }

        public bool __mvc { get; } = true;
    }

    [Serializable]
    public class ErrorInfo
    {
        public int Code { get; set; }
        public string Message { get; set; }
        public string Detail { get; set; }
        public Dictionary<string, string> ValidationErrors { get; set; }

        public ErrorInfo()
        {
        }
        public ErrorInfo(string message)
        {
            Message = message;
        }
        public ErrorInfo(int code)
        {
            Code = code;
        }

        public ErrorInfo(int code, string message)
            : this(message)
        {
            Code = code;
        }

        public ErrorInfo(string message, string details)
            : this(message)
        {
            Detail = details;
        }

        public ErrorInfo(int code, string message, string details)
            : this(message, details)
        {
            Code = code;
        }
    }

        public async Task CheckIsDeactiveAsync(long id)
        {
            if (await _organizationalUnits.AnyAsync(a => a.Id == id && !a.IsActive).ConfigureAwait(false))
                throw new UserFriendlyException("واحد سازمانی جاری غیرفعال می‌باشد.");
        }

روش نام گذاری متدهایی که امکان بازگشت خروجی Null را دارند

public User GetById(long id);

CREATE DATABASE [BloggingCore2016]
GO

USE [BloggingCore2016]
GO

CREATE TABLE [Blog] (
    [BlogId] int NOT NULL IDENTITY,
    [Url] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Blog] PRIMARY KEY ([BlogId])
);
GO

CREATE TABLE [Post] (
    [PostId] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    [Content] nvarchar(max),
    [Title] nvarchar(max),
    CONSTRAINT [PK_Post] PRIMARY KEY ([PostId]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([BlogId]) ON DELETE CASCADE
);
GO

INSERT INTO [Blog] (Url) VALUES 
('https://www.dntips.ir/'), 
('http://blogs.msdn.com/dotnet'), 
('http://blogs.msdn.com/webdev'), 
('http://blogs.msdn.com/visualstudio')
GO

 dotnet ef dbcontext scaffold "Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true" Microsoft.EntityFrameworkCore.SqlServer -o Entities --context MyDBDataContext --verbose

    public partial class MyDBDataContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true");
        }

 Usage: dotnet ef dbcontext scaffold [arguments] [options]
Arguments:
  [connection]  The connection string of the database
  [provider] The provider to use. For example, Microsoft.EntityFrameworkCore.SqlServer
Options:
  -a|--data-annotations   Use DataAnnotation attributes to configure the model where possible. If omitted, the output code will use only the fluent API.
  -c|--context <name> Name of the generated DbContext class.
  -f|--force Force scaffolding to overwrite existing files. Otherwise, the code will only proceed if no output files would be overwritten.
  -o|--output-dir <path> Directory of the project where the classes should be output. If omitted, the top-level project directory is used.
  --schema <schema> Selects a schema for which to generate classes.
  -t|--table <schema.table> Selects a table for which to generate classes.
  -e|--environment <environment>  The environment to use. If omitted, "Development" is used.
  -h|--help   Show help information
  -v|--verbose   Enable verbose output

 dotnet ef dbcontext scaffold "Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true" Microsoft.EntityFrameworkCore.SqlServer -o Entities --context MyDBDataContext --verbose --force --data-annotations

dotnet ef migrations add InitialDatabase

using Microsoft.EntityFrameworkCore.Migrations;

namespace Core1RtmEmptyTest.DataLayer.Migrations
{
    public partial class InitialDatabase : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
        }
    }
}
 

dotnet ef database update

dotnet ef migrations add v2
dotnet ef database update

 Application: DNTProfiler.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Windows.Markup.XamlParseException
Stack:
at System.Windows.Markup.WpfXamlLoader.Load

 CommandLine: C:\Tests\DNTProfiler.1.0.808.0\DNTProfiler.exe
Symbol search path is: srv*
Executable search path is:
ModLoad: 00150000 00176000 DNTProfiler.exe
ModLoad: 76f30000 77098000 ntdll.dll
ModLoad: 72ee0000 72f36000 C:\Windows\SysWOW64\MSCOREE.DLL
ModLoad: 74be0000 74d20000 C:\Windows\SysWOW64\KERNEL32.dll
ModLoad: 756e0000 757af000 C:\Windows\SysWOW64\KERNELBASE.dll
(684.764): Break instruction exception - code 80000003 (first chance)
eax=00000000 ebx=00000000 ecx=37af0000 edx=00000000 esi=7eb8f000 edi=00000000
eip=76fe2d15 esp=002ff604 ebp=002ff630 iopl=0 nv up ei pl zr na pe nc
cs=0023  ss=002b  ds=002b  es=002b  fs=0053  gs=002b efl=00000246
*** ERROR: Symbol file could not be found.  Defaulted to export symbols for ntdll.dll -
ntdll!LdrResolveDelayLoadsFromDll+0xa89:
76fe2d15 cc   int   3

 0:000> g
ModLoad: 757b0000 75827000 C:\Windows\SysWOW64\ADVAPI32.dll
ModLoad: 05080000 05102000 Newtonsoft.Json.dll
(684.764): C++ EH exception - code e06d7363 (first chance)
(684.764): C++ EH exception - code e06d7363 (first chance)
(684.764): C++ EH exception - code e06d7363 (first chance)
(684.764): CLR exception - code e0434352 (first chance)
First chance exceptions are reported before any exception handling.
This exception may be expected and handled.
eax=002fd0a0 ebx=00000005 ecx=00000005 edx=00000000 esi=002fd164 edi=00000001
eip=756f3d67 esp=002fd0a0 ebp=002fd0f8 iopl=0 nv up ei pl nz ac pe nc
cs=0023  ss=002b  ds=002b  es=002b  fs=0053  gs=002b efl=00200216
*** ERROR: Symbol file could not be found.  Defaulted to export symbols for C:\Windows\SysWOW64\KERNELBASE.dll -
KERNELBASE!RaiseException+0x48:
756f3d67 8b4c2454   mov   ecx,dword ptr [esp+54h] ss:002b:002fd0f4=3fce1188

 0:000> sxe clr
0:000> g
(684.764): C++ EH exception - code e06d7363 (first chance)
(684.764): CLR exception - code e0434352 (first chance)
First chance exceptions are reported before any exception handling.
This exception may be expected and handled.
eax=002fe2a0 ebx=00000005 ecx=00000005 edx=00000000 esi=002fe368 edi=00000001
eip=756f3d67 esp=002fe2a0 ebp=002fe2fc iopl=0 nv up ei pl nz ac pe nc
cs=0023  ss=002b  ds=002b  es=002b  fs=0053  gs=002b efl=00200216
KERNELBASE!RaiseException+0x48:
756f3d67 8b4c2454   mov   ecx,dword ptr [esp+54h] ss:002b:002fe2f4=3fce2388

 0:000> !loadby sos clr
0:000> !CLRStack
*** ERROR: Symbol file could not be found.  Defaulted to export symbols for C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll -
PDB symbol for clr.dll not loaded
OS Thread Id: 0x764 (0)
Child SP IP Call Site
002fe3f4 756f3d67 [GCFrame: 002fe3f4]
002fe430 756f3d67 [GCFrame: 002fe430]
002fe528 756f3d67 [GCFrame: 002fe528]
002fe544 756f3d67 [HelperMethodFrame_2OBJ: 002fe544] System.RuntimeTypeHandle.CreateInstance(System.RuntimeType, Boolean, Boolean, Boolean ByRef, System.RuntimeMethodHandleInternal ByRef, Boolean ByRef)
DNTProfiler.App.Main()
002ff190 720e2652 [GCFrame: 002ff190]

 0:000> !PrintException
Exception object: 021e6f0c
Exception type: System.Reflection.TargetInvocationException
Message: Exception has been thrown by the target of an invocation.
InnerException: System.IO.FileNotFoundException, Use !PrintException 021e6950 to see more.
StackTrace (generated):
<none>
StackTraceString: <none>
HResult: 80131604
0:000> !PrintException 021e6950
Exception object: 021e6950
Exception type: System.IO.FileNotFoundException
Message: Could not load file or assembly 'System.Net.Http.Formatting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
InnerException: <none>
StackTrace (generated):
SP IP Function
00000000 00000001 DNTProfiler!DNTProfiler.Services.SelfHostConfig.getHostConfiguration(System.String, Boolean)+0x2
002FE298 006B4E54 DNTProfiler!DNTProfiler.Services.SelfHostConfig.OpenWait(System.String, Boolean)+0x14
002FE2B0 006B4A3F DNTProfiler!DNTProfiler.ViewModels.MainWindowViewModel.doStart(System.String)+0x1f
002FE2C0 006B368D DNTProfiler!DNTProfiler.ViewModels.MainWindowViewModel..ctor(DNTProfiler.Common.Mvvm.ICommonDialogsService, DNTProfiler.Common.Controls.DialogManagement.Contracts.IDialogManager)+0xcd
002FE2D8 006B04C9 DNTProfiler!DNTProfiler.MainWindow..ctor()+0x91

StackTraceString: <none>
HResult: 80070002

 0:000> g
0:000> sxe clr
0:000> g
0:000> !loadby sos clr
0:000> !CLRStack
0:000> !PrintException

IIS را باز کنید و در لیست درختی، سرور را انتخاب کنید. در قسمت FTP میتوانید گزینه‌ی Ftp logging را ببینید. تنظیمات این قسمت هم دقیقا همانند قسمت logging میباشد و همان موارد برای آن هم صدق می‌کند.

بررسی تگ آن در applicationhost

تگ این نوع لاگ در فایل applicationhost در زیر مجموعه‌ی تگ <site> به شکل زیر نوشته می‌شود:

<system.ftpServer>
   <log centralLogFileMode="Central">
      <centralLogFile enabled="true" />
   </log>
</system.ftpServer>

گزینه centralLogFileMode  دو مقدار central و site را می‌پذیرد. اگر گزینه‌ی central انتخاب شود، یعنی همه‌ی لاگ‌ها را داخل یک فایل در سطح سرور ثبت کن ولی اگر گزینه‌ی site انتخاب شده باشد، لاگ هر سایت در یک فایل ثبت خواهد شد.

گزینه‌ی logInUTF8  یک خصوصیت اختیاری است که مقدار پیش فرض آن true میباشد. در این حالت باید تمامی رشته‌ها به انکدینگ UTF-8 تبدیل شوند.

همانطور که می‌بینید تگ log در بالا یک تگ فرزند هم به اسم centralLogFile دارد که همان خصوصیات جدول بالا در آن مهیاست.

دسترسی به تنظیمات این قسمت توسط دستور Appcmd: 

appcmd.exe set config -section:system.ftpServer/log /centralLogFileMode:"Central" /commit:apphost

appcmd.exe set config -section:system.ftpServer/log /centralLogFile.enabled:"True" /commit:apphost

دسترسی به تنظیمات این قسمت توسط دات نت:

using System;
using System.Text;
using Microsoft.Web.Administration;

internal static class Sample
{
   private static void Main()
   {
      using (ServerManager serverManager = new ServerManager())
      {
         Configuration config = serverManager.GetApplicationHostConfiguration();

         ConfigurationSection logSection = config.GetSection("system.ftpServer/log");
         logSection["centralLogFileMode"] = @"Central";

         ConfigurationElement centralLogFileElement = logSection.GetChildElement("centralLogFile");
         centralLogFileElement["enabled"] = true;

         serverManager.CommitChanges();
      }
   }
}

دسترسی به تنظیمات این قسمت توسط Javascript: 

var adminManager = new ActiveXObject('Microsoft.ApplicationHost.WritableAdminManager');
adminManager.CommitPath = "MACHINE/WEBROOT/APPHOST";

var logSection = adminManager.GetAdminSection("system.ftpServer/log", "MACHINE/WEBROOT/APPHOST");
logSection.Properties.Item("centralLogFileMode").Value = "Central";

var centralLogFileElement = logSection.ChildElements.Item("centralLogFile");
centralLogFileElement.Properties.Item("enabled").Value = true;

adminManager.CommitChanges();

 CREATE TABLE [Geo](
[id] [int] IDENTITY(1,1) NOT NULL,
[Location] [geography] NULL
)

 insert into Geo( Location , long, lat ) values
( geography::STGeomFromText ('POINT(-121.527200 45.712113)', 4326))

using System.Data.Entity.Spatial;

namespace EFGeoTests.Models
{
    public class GeoLocation
    {
        public int Id { get; set; }
        public DbGeography Location { get; set; }
        public string Name { get; set; }
        public string Type { get; set; }

        public override string ToString()
        {
            return string.Format("Name:{0}, Location:{1}", Name, Location);
        }
    }
}

using System;
using System.Data.Entity;
using EFGeoTests.Models;

namespace EFGeoTests.Config
{
    public class MyContext : DbContext
    {
        public DbSet<GeoLocation> GeoLocations { get; set; }

        public MyContext()
            : base("Connection1")
        {
            this.Database.Log = sql => Console.Write(sql);
        }
    }
}

   private static DbGeography createPoint(double longitude, double latitude,  int coordinateSystemId = 4326)
  {
       var text = string.Format(CultureInfo.InvariantCulture.NumberFormat,"POINT({0} {1})", longitude, latitude);
       return DbGeography.PointFromText(text, coordinateSystemId);
  }

GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

        /// <summary>
        /// Gets the current shape in the collection
        /// </summary>
        public Shape Current
        {
            get 
            {
                if (_disposed) throw new ObjectDisposedException("Shapefile");
                if (!_opened) throw new InvalidOperationException("Shapefile not open.");
               
                // get the metadata
                StringDictionary metadata = null;
                if (!RawMetadataOnly)
                {
                    metadata = new StringDictionary();
                    for (int i = 0; i < _dbReader.FieldCount; i++)
                    {
                        string value = _dbReader.GetValue(i).ToString();
                        if (_dbReader.GetDataTypeName(i) == "DBTYPE_WVARCHAR")
                        {
                            // برای نمایش متون فارسی نیاز است
                            value = Encoding.UTF8.GetString(Encoding.GetEncoding(720).GetBytes(value));
                        }
                        metadata.Add(_dbReader.GetName(i),
                            value);
                    }
                }

using System.Collections.Generic;
using System.Linq;
using Catfood.Shapefile;

namespace EFGeoTests
{
    public class MapPoint
    {
        public Dictionary<string, string> Metadata { set; get; }
        public double X { set; get; }
        public double Y { set; get; }
    }

    public static class ShapeReader
    {
        public static IList<MapPoint> ReadShapeFile(string path)
        {
            var results = new List<MapPoint>();

            using (var shapefile = new Shapefile(path))
            {
                foreach (var shape in shapefile)
                {
                    if (shape.Type != ShapeType.Point)
                        continue;

                    var shapePoint = shape as ShapePoint;
                    if (shapePoint == null)
                        continue;


                     var metadataNames = shape.GetMetadataNames();
                    if(!metadataNames.Any())
                        continue;

                    var metadata = new Dictionary<string, string>();
                    foreach (var metadataName in metadataNames)
                    {
                        metadata.Add(metadataName,shape.GetMetadata(metadataName));
                    }

                    results.Add(new MapPoint
                    {
                        Metadata = metadata,
                        X = shapePoint.Point.X,
                        Y = shapePoint.Point.Y
                    });
                }
            }

            return results;
        }
    }
}

            var points = ShapeReader.ReadShapeFile("IranShapeFiles\\places.shp");
            using (var context = new MyContext())
            {
                context.Configuration.AutoDetectChangesEnabled = false;
                context.Configuration.ProxyCreationEnabled = false;
                context.Configuration.ValidateOnSaveEnabled = false;

                if (context.GeoLocations.Any())
                    return;

                foreach (var point in points)
                {
                    context.GeoLocations.Add(new GeoLocation
                    {
                        Name = point.Metadata["name"],
                        Type = point.Metadata["type"],
                        Location = createPoint(point.X, point.Y)
                    });
                }

                context.SaveChanges();
            }

            var tehran = createPoint(51.4179604, 35.6884243);

            using (var context = new MyContext())
            {
                // find any locations within 5 kilometers ordered by distance
                var locations = context.GeoLocations
                    .Where(loc => loc.Location.Distance(tehran) < 5000)
                    .OrderBy(loc => loc.Location.Distance(tehran))
                    .ToList();

                foreach (var location in locations)
                {
                    Console.WriteLine(location.Name);
                }
            }

            var tehran = createPoint(51.4179604, 35.6884243);
            using (var context = new MyContext())
            {
                // find any locations within 5 kilometers ordered by distance
                var tehranLocation = context.GeoLocations.FirstOrDefault(loc => loc.Location.SpatialEquals(tehran));
                if (tehranLocation != null)
                {
                    Console.WriteLine(tehranLocation.Type);
                }
            }

 PM> Install-Package Google.Apis
PM> Install-Package Google.Apis.auth
PM> Install-Package Google.Apis.Analytics.v3

using System;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using Google.Apis.Analytics.v3;
using Google.Apis.Analytics.v3.Data;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;

namespace GoogleAnalyticsAPIv3Tests
{
    public class AnalyticsQueryParameters
    {
        public DateTime Start { set; get; }
        public DateTime End { set; get; } 
        public string Dimensions { set; get; } 
        public string Filters { set; get; }
        public string Metrics { set; get; }
    }

    public class AnalyticsAuthentication
    {
        public Uri SiteUrl { set; get; }
        public string ApplicationName { set; get; }
        public string ServiceAccountEmail { set; get; }
        public string KeyFilePath { set; get; }
        public string KeyFilePassword { set; get; }

        public AnalyticsAuthentication()
        {
            KeyFilePassword = "notasecret";
        }
    }

    public class GoogleAnalyticsApiV3
    {
        public AnalyticsAuthentication Authentication { set; get; }
        public AnalyticsQueryParameters QueryParameters { set; get; }

        public GaData GetData()
        {
            var service = createAnalyticsService();
            var profile = getProfile(service);
            var query = service.Data.Ga.Get("ga:" + profile.Id,
                                QueryParameters.Start.ToString("yyyy-MM-dd"),
                                QueryParameters.End.ToString("yyyy-MM-dd"),
                                QueryParameters.Metrics);
            query.Dimensions = QueryParameters.Dimensions;
            query.Filters = QueryParameters.Filters;
            query.SamplingLevel = DataResource.GaResource.GetRequest.SamplingLevelEnum.HIGHERPRECISION;
            return query.Execute();
        }

        private AnalyticsService createAnalyticsService()
        {
            var certificate = new X509Certificate2(Authentication.KeyFilePath, Authentication.KeyFilePassword, X509KeyStorageFlags.Exportable);
            var credential = new ServiceAccountCredential(
                new ServiceAccountCredential.Initializer(Authentication.ServiceAccountEmail)
                {
                    Scopes = new[] { AnalyticsService.Scope.AnalyticsReadonly }
                }.FromCertificate(certificate));

            return new AnalyticsService(new BaseClientService.Initializer
            {
                HttpClientInitializer = credential,
                ApplicationName = Authentication.ApplicationName
            });
        }

        private Profile getProfile(AnalyticsService service)
        {
            var accountListRequest = service.Management.Accounts.List();
            var accountList = accountListRequest.Execute();
            var site = Authentication.SiteUrl.Host.ToLowerInvariant();
            var account = accountList.Items.FirstOrDefault(x => x.Name.ToLowerInvariant().Contains(site));
            var webPropertyListRequest = service.Management.Webproperties.List(account.Id);
            var webPropertyList = webPropertyListRequest.Execute();
            var sitePropertyList = webPropertyList.Items.FirstOrDefault(a => a.Name.ToLowerInvariant().Contains(site));
            var profileListRequest = service.Management.Profiles.List(account.Id, sitePropertyList.Id);
            var profileList = profileListRequest.Execute();
            return profileList.Items.FirstOrDefault(a => a.Name.ToLowerInvariant().Contains(site));
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;

namespace GoogleAnalyticsAPIv3Tests
{
    class Program
    {

        static void Main(string[] args)
        {
            var statistics = new GoogleAnalyticsApiV3
            {
                Authentication = new AnalyticsAuthentication
                {
                    ApplicationName = "My Project",
                    KeyFilePath = "811e1d9976cd516b55-privatekey.p12",
                    ServiceAccountEmail = "10152bng4j3mq@developer.gserviceaccount.com",
                    SiteUrl = new Uri("https://www.dntips.ir/")
                },
                QueryParameters = new AnalyticsQueryParameters
                {
                    Start = DateTime.Now.AddDays(-7),
                    End = DateTime.Now,
                    Dimensions = "ga:date",
                    Filters = null,
                    Metrics = "ga:users,ga:sessions,ga:pageviews"
                }
            }.GetData();


            foreach (var result in statistics.TotalsForAllResults)
            {
                Console.WriteLine(result.Key + " -> total:" + result.Value);
            }
            Console.WriteLine();

            foreach (var row in statistics.ColumnHeaders)
            {
                Console.Write(row.Name + "\t");
            }
            Console.WriteLine();

            foreach (var row in statistics.Rows)
            {
                var rowItems = (List<string>)row;
                Console.WriteLine(rowItems.Aggregate((s1, s2) => s1 + "\t" + s2));
            }

            Console.ReadLine();
        }
    }
}

Data Source=(localdb)\v11.0;Initial Catalog=OldFashionedDB;Integrated Security=True

sqllocaldb share v11.0 IIS_DB

Data Source=(localdb)\.\IIS_DB;Initial Catalog=OldFashionedDB;Integrated Security=True

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin

Data Source=.\SQLEXPRESS;Initial Catalog=OldFashionedDB;Integrated Security=True

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin

Select
{} On Columns ,
{} On Rows
From <Cube_Name>
Where <Condition>

Select
From [Adventure Works]

Select
From [Adventure Works]
Where [Measures].[Reseller Sales Amount]

Select
From [Adventure Works]
where [Measures].[Internet Sales Amount]

Select
[Measures].[Reseller Sales Amount] on columns
From [Adventure Works]

Select
[Date].[Calendar].[Calendar Year] on columns
From [Adventure Works]

Select

{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns

From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
[Product].[Product Categories].[Category] on rows
From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}on rows
From [Adventure Works]

Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns ,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
non empty {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

Select
[Sales Channel] on columns
From [Adventure Works]

Select
[Product] on columns
From [Adventure Works]

Select
[Product].[Category] on columns
From [Adventure Works]

Select
[Product].[Category].[all]   on columns
From [Adventure Works]
--
Select
[Product].[Category].[All] on columns
From [Adventure Works]
--
Select
[Product].[Category].[(all)] on columns
From [Adventure Works]
--
Select
[Product].[Category].[all products] on columns
From [Adventure Works]

Select
{[Product].[Category].members} on columns
From [Adventure Works]

Select
[Product].[Category].[Category].members
-- dimension.hierarchy.level.members
on columns
From [Adventure Works]

Select
[Product].[Category].[Category].members,[Product].[Category].[All Products] on columns
From [Adventure Works]

Select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns
From [Adventure Works]

Select
{[Product].[Category].[Category],[Product].[Category]}
on columns
From [Adventure Works]

Select
[Product].[Category].&[1]
on columns
From [Adventure Works]

Select
[Product].[Category].[Bikes]
on columns
From [Adventure Works]

Select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}
on columns
From [Adventure Works]

Select
[Product].[Product Categories].members
on columns
From [Adventure Works]

Select
[Product].[Product Categories],[Product].[Category]
on columns
From [Adventure Works]

Go

Select
{[Product].[Product Categories],[Product].[Category]}
on columns
From [Adventure Works]

Select
[Product].[Product Categories].members
on rows
From [Adventure Works]

Select
{} on columns,
[Product].[Product Categories].members
on rows
From [Adventure Works]

 record date  actual date  حقوق دریافتی
1392/01/01  1392/01/01  2000/روز
1392/02/01  1392/01/01  2000/روز
...
1392/07/01  1392/07/01  2400/روز
...
1392/17/01  1392/07/01  2400/روز