Entity Framework – AutoDetectChangesEnabled

From MSDN

When using most POCO entities the determination of how an entity has changed (and therefore which updates need to be sent to the database) is handled by the Detect Changes algorithm. Detect Changes works by detecting the differences between the current property values of the entity and the original property values that are stored in a snapshot when the entity was queried or attached. The techniques shown in this topic apply equally to models created with Code First and the EF Designer.

But some times we need to turn off this feature. for example, I need to upload a excel file it contains a bulk data. At this time AutoDetectChangesEnabled is true then it take a lot time for insert all rows. I can do the data validations at db side.

Just check the difference of AutoDetectChangesEnabled ON and OFF while inserting a bulk data.

Normal way:

namespace TestAutoDetectChangesEnabled
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Time Test - AutoDetectChangesEnabled";
            string[] names = { "Arun", "Raj", "Musthaan", "Majeed", "Muzafir",
                                 "Hari", "Dharma", "Kannan", "Imran", "Santo",
                                 "Rahmath", "Jayesh" };
            Stopwatch sw = new Stopwatch();
            sw.Start();
            var db = new AppContext();
            foreach (var i in Enumerable.Range(1, 20000))
            {
                var student = new Student()
                {
                    Name = names[(new Random().Next(0, 10))],
                    ContactNo = "9870002735",
                    RegNo = i.ToString().PadLeft(7, '0')
                };
                db.Students.Add(student);
                Console.Clear();
                Console.WriteLine("{0} records added", i);
            }
            db.SaveChanges();
            sw.Stop();
            Console.WriteLine("Taked time - {0}", sw.ElapsedMilliseconds);
            Console.ReadKey();
        }
    }

    public class AppContext : DbContext
    {
        public AppContext()
            : base("AppConnection")
        {

        }
        public DbSet<Student> Students { get; set; }
    }
    public class Student
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string RegNo { get; set; }
        public string ContactNo { get; set; }

    }
}

AutoDetectChangesEnabled-before

After disable AutoDetectChanges

 class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Time Test - AutoDetectChangesEnabled";
            string[] names = { "Arun", "Raj", "Musthaan", "Majeed", "Muzafir",
                                 "Hari", "Dharma", "Kannan", "Imran", "Santo",
                                 "Rahmath", "Jayesh" };
            Stopwatch sw = new Stopwatch();
            sw.Start();
            var db = new AppContext();
            try
            {
                db.Configuration.AutoDetectChangesEnabled = false;

                foreach (var i in Enumerable.Range(1, 20000))
                {
                    var student = new Student()
                    {
                        Name = names[(new Random().Next(0, 10))],
                        ContactNo = "9870002735",
                        RegNo = i.ToString().PadLeft(7, '0')
                    };
                    db.Students.Add(student);
                    Console.Clear();
                    Console.WriteLine("{0} records added", i);
                }
                db.SaveChanges();
            }
            finally
            {
                db.Configuration.AutoDetectChangesEnabled = true;
            }
            sw.Stop();
            Console.WriteLine("Taked time - {0}", sw.ElapsedMilliseconds);
            Console.ReadKey();
        }
    }

AutoDetectChangesEnabled-after

See the difference, it almost 10x faster. But use it with care. Don’t forget to re-enable detection of changes after the loop — used a try/finally to ensure it is always re-enabled even if code in the loop throws an exception.

CREATE DATABASE PERMISSION DENIED IN DATABASE ‘master’

I have installed SQL SERVER 2008  on my system with Windows 7 operating system installed.
Whenever I try to create a new database it displays an error:
CREATE DATABASE PERMISSION DENIED IN DATABASE 'master'.(Microsoft SQL Server, Error:262).

After a lot of search i am found a solution.

When i start SQL server management studio use ‘sa’ user and not Windows-User. Looks like Windows-user does not have permission.

First, open SQl in administrator mode.
In Security node ,open logins and select users node. In server role tab, tick sysadmin and User Map tab tick master database and select owner in below section.

Click Ok in all windows. Close SQL Management Studio and open it in normal mode.
Now i can create database. No Issues..

 

Musthaan

Seeing the SQL Generated by LINQ to Entity Queries

Sometimes you really, really, really want to see the SQL that LINQ generates when working with the Entity Framework. For instance, in a comment to a recent tip, a reader mentioned that a LINQ to Entities query generated a SQL statement that joined more than 40 tables. The application’s performance was, to say the least, disappointing. If you’re not getting what you expect from your LINQ queries, being able to see the SQL statement can give you real clues in tracking down the problem, as it did with that column’s reader.

There are a couple of free Visual Studio Add-ins that, among their other features, allow you to see the SQL statement for a LINQ query (The LINQ to Entity Query Visualizer, Entity Visualizer). However, developers have had trouble getting those add-ins up and running in some versions of Visual Studio (especially Visual Studio 2010). Fortunately, if all you want to do is see your SQL statement, you can do it in Visual Studio’s debug mode using a little code in the Immediate Window.

The first step, of course, is have a program with a LINQ query as in this example:

Dim en As New northwndEntities

Dim res = From cust In en.Customers

Select cust
If you cast the variable holding the output from the query (res, in this case) to Objects.ObjectQuery then you get access to the ObjectQuery’s ToTraceString property. That property returns the SQL statement that will be issued by your Entity Framework Model. In Visual Basic, you’d type this in the Immediate window:
CType(res, Objects.ObjectQuery).ToTraceString()
In C#, you’d type this:
((System.Data.Objects.ObjectQuery) res).ToTraceString()
For my sample query, the result looked like this in Visual Basic (the C# version includes escape characters for newline and carriage returns):
SELECT [Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax],
[Extent1].[Timestamp] AS [Timestamp]
FROM [dbo].[Customers] AS [Extent1]

How I find a particular column name within all tables of SQL server datbase.

For example, I have a database named Organisation. I have more than one table where tax_id column is present.

Most of the time, we have to find such a column from the whole database. The solution is provided below:
select table_name,column_name from information_schema.columns where column_name like ‘%tax%’

 
Here I am searching for column_name which contains tax within its name. It will return all the tables and respective columns containing tax.

Example:

Table_Name Column_name
t_Customer tax_id
t_Employee tax_number
t_Balance_sheet tax_percentage

 

Get Full Month

ALTER FUNCTION [dbo].[getFullmonth]
(
@date1 date
)
RETURNS @dates TABLE
(
date date not null
)
AS
BEGIN
-- Set first day in month
DECLARE @month int;

SET @month = datepart(MM, @date1);
SET @date1 = convert(datetime, convert(varchar,datepart(yy,@date1)) + ‘.’ + convert(varchar,@month) + ‘.01 00:00:00’);
WHILE datepart(MM,@date1) = @month
BEGIN
INSERT INTO @dates VALUES (@date1);
SET @date1 = dateadd(dd, 1, @date1);
END
RETURN;
END

OVER and PARTITION BY

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost  OrderNum
10.00 345
10.00 346
10.00 347
10.00 348

Quick translation:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break upthat resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo

FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00 345        1
8.00 346        1
8.00 347        1
2.00 348        2

The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.

Ref: http://www.midnightdba.com/Jen

ADO Helper

 

#Region “- Includes”
Imports System.Text
Imports System.Data
Imports System.Xml
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Collections
#End Region

Public Class clsDBA
Implements IDisposable
#Region “Local Variable and Objects”
Private strConnectionString As String
Private objConnection As DbConnection
Private objCommand As DbCommand
Private objFactory As DbProviderFactory = Nothing

#End Region

#Region ” – Constructors”

Public Sub New(ByVal connectionstring As String)
strConnectionString = connectionstring

objFactory = SqlClientFactory.Instance
objConnection = objFactory.CreateConnection()
objCommand = objFactory.CreateCommand()

objConnection.ConnectionString = strConnectionString
objCommand.Connection = objConnection
End Sub

#End Region

#Region ” – Command”

Public ReadOnly Property Command() As DbCommand
Get
Return objCommand
End Get
End Property

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value
Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object, ByVal direction As String) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value

If direction.ToUpper() = “output”.ToUpper() Then
p.Direction = ParameterDirection.Output
End If

Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – Open/CloseConnection”

Public Sub OpenConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
End Sub

Public Sub CloseConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()
End Sub

#End Region

#Region ” – ClearDBConnectionPool”
”’
”’ To clear the DB connection pooling
”’

”’Connection Object Private Sub ClearDBConnectionPool(ByVal objConnection As DbConnection)
Try
SqlConnection.ClearPool(DirectCast(objConnection, SqlConnection))
Catch ex As Exception

End Try
End Sub
#End Region

#Region ” – Dispose”

Public Sub Dispose() Implements IDisposable.Dispose
ClearDBConnectionPool(objConnection)
If objConnection.State = System.Data.ConnectionState.Open Then
objConnection.Close()
End If
objConnection.Dispose()
objCommand.Dispose()
End Sub

#End Region

#Region ” – ExecuteNonQuery”

Public Function ExecuteNonQuery(ByVal query As String) As Integer
Dim intIndex As Integer = -1

Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If

objCommand.ExecuteNonQuery()

For intjIndex As Integer = 0 To objCommand.Parameters.Count – 1
If objCommand.Parameters(intjIndex).Direction.ToString().ToUpper() = “output”.ToUpper() Then
intIndex = Convert.ToInt32(objCommand.Parameters(intjIndex).Value)
End If
Next
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return intIndex
End Function

#End Region

#Region ” – ExecuteScalar”

Public Function ExecuteScalar(ByVal query As String) As Object
Dim o As Object = Nothing
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
o = objCommand.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try

Return o
End Function

#End Region

#Region ” – ExecuteDataSet”

Public Function ExecuteDataSet(ByVal query As String) As DataSet

Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult
End Function

#End Region

#Region ” – ExecuteDataTable”

Public Function ExecuteDataTable(ByVal query As String) As DataTable
Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult.Tables(0)
End Function

#End Region

End Class