Create Entity Framework Model classes through EF Core with Database First approach.

Today, we will walkthrough about .Net Core with EF Core (Data First approach). In this article, we will learn to create Entity Framework model classes with existing database.

Let's start.

Firstly, we will create a simple .Net Core console app. Currently, this console app has a Main() method under Program.cs class file. If you will run this project, it will print “Hello World!” on console window. This means, everything is running good.

Let us move ahead-

Now, we need to create an Entity Framework model classes and to do this, we need to install Nuget package for the database provider(s). In this demo, we will be using SQL Server 2016 however, in next articles, we will target the other database providers.

To install the Nuget package for SQL Server database provider

• Open package manager console window and install the package

Install-Package Microsoft.EntityFrameworkCore.SqlServer

• We have to install some Entity Framework tool(s) to create model classes from the database and to do so, we have to go in package manager console and run the command.

Install-Package Microsoft.EntityFrameworkCore.Tools

Now, that we have our complete setup with us, we are now ready to create model classes through reverse engineering. I already have a dummy database in SQL Server 2016, So we will directly move on creating model classes. Go into the package manager console and run the below command.

Scaffold-DbContext "ServerDATABASESERVER;Database=DATABASENAME;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer 

The above command will generate the model class in our project root folder. If you have (n) number of model classes then It will become quite difficult to manage all these classes, so in this case all model classes should be in a Model folder but our application does not have any folder as such, however we don’t have to worry because we can create this folder using above command i.e. If we append one parameter “OutputDir”. For example:

Scaffold-DbContext "ServerDATABASESERVER;Database=DATABASENAME;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir 

Now our Entity Framework model classes have been created under the Models folder. Now we have model classes against every table and a derived context based on schema of our database.
Is it possible?  If I want to create model class for specific database table or schema. Yes, it is!!
Microsoft provides the facility to specify the table and schema in Scaffold-DbContext command.

• For tables

Scaffold-DbContext "ServerDATABASESERVER;Database=DATABASENAME;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Hotel

• For schema

Scaffold-DbContext "ServerDATABASESERVER;Database=DATABASENAME;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Schemas Report

Remember, if you run the command with the table or schema which is incorrect (not exists) then command will give only warning and create only context class and If table has any foreign key relationship with other table and you are using only primary key table or foreign key table, in that case also, it will raise only warning not error.

Now, we have model classes and we can perform data operation on these model classes. Now, if someone comes in and changes the schema or add new table in your database, how will we update our context class and create a new model class against newly added table in database? For that:

We will use same command as we were using to create model class. Just add the “-Force” argument end of the command, it is ready to update your context classes.

Scaffold-DbContext "ServerDATABASESERVER;Database=DATABASENAME;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

By the help of these commands you can easily update your EF model classes. Remember, when you run any of the command, at that time there should not be any compile time error in your project. Otherwise, command will not be execute.  

Print multiple pages of SSRS report.

Nowadays we are not able to predict, how many records will be in report and how to break the report in a no of pages. There may be one or more then one page in a report. If we want to print all the pages of SSRS report, developer has to  implement some logical stuff.

Unfortunately, .NET Framework does not keep track of page numbers. Yet, It provides some facility to print multiple pages. Let's start:

Start to get byte[] of first page with the help of Reporting Service's Render() method.

string encoding, extension, mimeType, format = "IMAGE";
string deviceInfo = String.Format(@"<deviceinfo><outputformat><deviceinfo><outputformat>{0}</outputformat></deviceinfo></outputformat></deviceinfo>", "emf");
string[] streamIDs = null;
Warning[] warnings = null;

//Exectute the report and get page count.           
// Renders the first page of the report and returns streamIDs for 
// subsequent pages
firstPage = rs.Render(
    format,
    deviceInfo,
    out extension,
    out mimeType,
    out encoding,
    out warnings,
    out streamIDs);

Now, we have first page content in byte[] and first page is ready to print. In SQL Server 2005, streamIDs provide us the number of pages. Let me share one thing that beyond SQL Server 2008, calculating no of pages is impossible through streamIDs.

So, I have a solution:

we have the byte [] for our first page, but how will we get the byte code for our next pages? Is our question on top. Problem still remains.

 Now let's play with some techniques:

We need to create a dynamic device info, that will increment on the basis of pages. To do this, we will start with initializing two variables, one is integer with value 1 and other one is byte[][]. Assign the first page byte[] value in variable "pages" that is type of byte []. Add one local variable pageIndex and assign the value of m_numberOfPages, which is 1. This pageIndex variable will be number of pages. If we will pass 2 in this page index, it will give second page byte [], or if, we pass 3 then it will return third page byte[]. Do while loop will check the byte [] length for each page. It means device info is playing major role in printing.

Device info contains start page tag followed by passing the pageIndex value in startPage tag. See below snippet.

m_numberOfPages = 1;
pages = new Byte[m_numberOfPages][];

// The first page was already rendered
pages[0] = firstPage;

int pageIndex = m_numberOfPages;
do
{
    deviceInfo =
        String.Format(@"<deviceinfo><outputformat>{0}</outputformat><startpage>{1}</startpage></deviceinfo>",
            "emf", ++pageIndex);
    nextPage = rs.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
    if (nextPage.Length > 0)
    {
        Array.Resize(ref pages, ++m_numberOfPages);
        pages[m_numberOfPages - 1] = nextPage;
    }
}
while (nextPage.Length > 0);

This code snippet will provide the byte [] for all the pages of report. Now we have all the pages byte[] array,  which we will pass to initialize the memory stream object, then send a command on printer to print the records. Complete code of this project is available from the download link :). Download. 

Can we rollback "Truncate", "Drop", and "Delete" command?

Here we talk about the concept of truncate, drop and delete commands and whether they can be rollback, if so, how and when !!

Yesterday I was working on this and see if we use the truncate command in transaction, we can rollback the data. One more thing if we perform delete command outside the transaction, we can’t rollback the data.

Let’s try this…

Suppose I have a table dbo.Employee with three column- Id, Name, and Salary.

CREATE TABLE [dbo].Employee(
      Id [int] NULL,
      Name [varchar](50) NULL,
      Salary [Int] NULL
)

Insert the values into the db.Employee table

INSERT INTO dbo.Employee VALUES (1,'Employee1',10000)
INSERT INTO dbo.Employee VALUES (2,'Employee2',20000)
INSERT INTO dbo.Employee VALUES (3,'Employee3',30000)
INSERT INTO dbo.Employee VALUES (4,'Employee4',10000)
INSERT INTO dbo.Employee VALUES (5,'Employee5',30000)

Now I have a five rows in table

SELECT * FROM dbo.Employee

Now we perform the delete command in Transaction and then select command

BEGIN TRAN
DELETE FROM dbo.Employee WHERE ID = 2
ROLLBACK

SELECT * FROM dbo.Employee

Output:

But again I run the select command then I get the same result as previous select statement (i.e 5).
Now I perform the Truncate command in Transaction, Let see what happen.

BEGIN TRAN
TRUNCATE TABLE dbo.Employee
ROLLBACK

Now again I run the select command and got the same rows and same result. if same thing we do with Drop command that will also give same result.
It means If we use the Delete, Truncate, and Drop command with Transaction, we can rollback the data and if we perform all three command outside the Transaction we can’t rollback.

One more thing I have to add in this article, we can’t rollback the data but we can restore the data from the Transaction_log, if we use the delete command. We can’t restore the data if we use the truncate or drop command. How and Why read in next article.

In summary, 
We can rollback the DDL and DML commands, if all these commands are in transaction. We can only restore the DML commands data. 

 

Difference between Char, Varchar, and nVarchar data type in SQL Server

In real world everyone has different need and requirement, why we use the same data type to store different types of value. What is the actual difference between Char, Varchar and nVarchar data type.

Char DataType

Char datatype which is used to store the fixed length, non-unicode characters data. Suppose if we declared char (50) it will allocates memory for 50 characters. Once we declare char (50) and if we enter fewer than the number of characters defined (i.e. 6), the remaining length will be space filled to the right. it means other 44 characters of memory will be wasted.

Now we look in the query, How will it effect.

 

Output of this Select Statement:

Use this data type when the column data is to be of fixed length, which tends to be the case

for customer IDs and bank account IDs.

Nchar DataType

Nchar type is exactly similar to char. But hold character in Unicode format rather than ANSI. The Unicode format has a larger character set range than ANSI. Unicode datatype take exact double memory space in sql server. So use the nchar when you want to store the Unicode value.

varchar DataType

Varchar datatype, which is used to store the alphanumeric values, just like char datatype. If both are similar than what is the difference between char and Varchar? What is the need to create the new datatype? Later in this article we will discuss this. The maximum size of a Varchar column is 8,000 characters. However, if you define the column with no size—that is, Varchar () — then the length will default to 1.

Difference between the char and Varchar datatype

Char belongs to fixed length characters datatype, Varchar belongs to variable length characters datatype. If you define a column to be 20 characters long, but If you enter fewer than the number of characters defined like 10 characters then it will consume only 10 characters memory. It will not consume the defined memory.

Now we look in the query:

Output of the select statement:

We can see in above image the output of the select statement, the ZipCodelength value are varying according to data in zipCode field.

So we should use the Char for the fixed length data field and Varchar for the variable length data field.

Nvarchar DataType

Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Difference between Varchar and Nvarchar DataType

Varchar(n)

Nvarchar(n)

1.    Non-Unicode Variable Length character data type. Example: Declare @FirstName As Varchar(20) = ‘Developer’ Select @FirstName 2.    It takes 1 byte per character. Example: Declare @FirstName As Varchar(20) = ‘Developer’ Select @FirstName AS FirstName, DATALENGTH(@FirstName) As Length Result: FirstName Length Developer 9 3.    Can store maximum 8000 Non-Unicode characters. 4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1. Example: Declare @FirstName As Varchar =’Developer’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length D                1 5.     If n is not specified in while using Cast/Convert   functions, then it is considered as 30.      Example:      Declare @firstName Varchar(50)= ‘Unicode character sets hold up to 65,536’     Select CAST(@firstName As Varchar) FirstName, DATALENGTH(CAST(@firstName As varchar)) Length        Result: FirstName                                Length Unicode character sets hold up  30

1.    Unicode Variable Length character data type. Nvarchar  can store both the Unicode and non-Unicode (i.e) Chinese, Japanese, Korean etc) character. Example: Declare @FirstName As Nvarchar(20) = ‘Developer’ Select @FirstName 2.    It takes exactly double bytes per character as compare to Varchar. It takes 2 bytes per Unicode/Non-Unicode character. Example: Declare @FirstName As Nvarchar(20)= ‘Developer Select @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length Developer 18 3.    Can store maximum 4000 Unicode/Non-Unicode characters. 4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1. Example: Declare @FirstName As Nvarchar =’Developer’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length D                2 5.    If n is not specified in while using Cast/Convert     functions, then it is considered as 30.       Example:        Declare @firstName Nvarchar(50)=  ‘Unicode character sets hold up to 65,536’      Select CAST(@firstName As Nvarchar) FirstName,  DATALENGTH(CAST(@firstName As Nvarchar)) Length    Result:  FirstName                                 Length  Unicode character sets hold up   60

So if you are not using other languages then it’s better to use varchar datatype instead of nvarchar.