Free C# Entity Generator or C# Class Generator

C# Entity Generator or Class Generator Tool

A few years back, I created a tool “C# Entity Generator”. Though it was created for an older version of visual studio, still it can be useful. I am sharing this tool here so that anyone can download and use it for free. This tool does not require any installation and is a copy paste utility.

C# Entity Generator is a tool which can be used to generate C# Entity Layer classes without writing a single line of code. If we are using three-tier architecture in our applications and need to create entities frequently to map the output of the relational queries, this tool can be very useful. It increases the developer’s productivity especially if the entity contains a large set of properties. Only once would we need to define the data types, their prefixes as per the naming convention guidelines, and their … More


Python use case – Get employees for given skill set – SQL Server 2017

In the previous blog post “Python use case – Map unequal comma separated values from two columns – SQL Server 2017“, we demonstrated a use case example of Python in SQL Server 2017. Now, in this post, “Python use case – Get employees for given skill set – SQL Server 2017”, we are going to explore another use case example of Python in SQL Server 2017.

As Microsoft has integrated Python in SQL Server 2017 for advanced data analytics and machine learning purpose, it can also be used to ease the complex data transformation and analysis which might be tedious and a bit complex while doing the same using T-SQL.

Let’s create the required table with the sample data to demonstrate the use case example.

Python use case – Get employees for given skill set

Assume that, we have a table employee master named as tbl_EmpMaster which has … More


Python use case – Map unequal comma separated values from two columns – SQL Server 2017

We know that Microsoft has integrated Python in SQL Server 2017 to enable rich data analytics capabilities within the database itself. Python is one of the most powerful languages which provides lots of built-in libraries for advanced data analytics and transformations. We can use Python for almost everything from website development to robotics and Data Science. In SQL Server 2017, Python can be used primarily for Machine learning purposes but it is not limited to that only. We can also use Python for complex data transformations and analysis which might be a bit tedious and complex while doing the same using T-SQL in SQL Server.

In this post, we will be exploring an use case example of Python for data transformation in SQL Server 2017. If you want to read more about Python and how to use it in SQL Server, you can visit my previous blog post “Why More


Why Python and how to use it in SQL Server 2017

Microsoft has integrated Python in SQL Server 2017 which can be used for in-database analysis purpose. In this post, we are going to explore “Why Python and how to use it in SQL Server 2017”, and then we will explore that how we can use it in SQL Server 2017.

Why Python

Python is a general purpose object oriented programming language which can be used to develop applications for a variety of domains. We can use Python for almost everything from desktop and website development, gaming, robotics, scientific and numeric computing to spacecraft control and much more. Python is a high-level programming language which is an interpreted language (execute line by line) instead of compiled language. The Python has gained popularity because of its user friendliness. The developers fall in love with Python because it is easy to learn, but still very powerful. The technology giants like Google, YouTube, Dropbox, … More


SQL Server 2017 New Features

Microsoft has launched the most recent SQL Server 2017 release candidate (RC1, July 2017). It can be downloaded from this link. SQL Server 2017 will run on both Windows as well as on Linux OS. It also supports macOS via Docker too. In this post, we will discuss the new features of SQL Server 2017.

SQL Server 2017 New Features (SQL Server vNext)

Though the SQL Server 2017 has many new features, in this post, we are going to highlight the features which can be mostly used by SQL Server Developers.

1. SQL Server Machine Learning Services – R and Python

SQL Server 2016 integrated the R programming which can be run within the database server and can be embedded into T-SQL script too. Now, in SQL Server 2017, we can execute the Python script within the database server itself. Both, R and Python are most popular programming language … More


Work offline with SSIS package

Sometimes it happens that we need to open an SSIS package which contains connections which are not accessible or not allowed to be accessed due to some security reasons. In this post “Work offline with SSIS package”, we are going to learn that how we can open, read, and modify the components of an SSIS package when its connection managers are not able to connect to the underlying data source. This feature can be highly useful when it comes that there is a developer who is not authorized to access the underlying data source being used in the package but he needs to read some column mappings information from the source and destinations being used in the package.

Whenever we open an SSIS package in Visual Studio designer, the designer tries to connect to the data sources being used by the package to verify the metadata. And if it fails … More


Specific row at the top then sort the rest result set in SQL Server

In this post, “Specific row at the top then sort the rest result set in SQL Server“, we are going to learn that how we can order a result set in a customized way which cannot be achieved using ORDER by Clause in a simple way.

To demonstrate this, let’s create a sample table named as “tbl_Department” and insert some dummy rows in it. Below is the code to create the sample table:

IF(OBJECT_ID('dbo.tbl_Department') IS NOT NULL)
DROP TABLE tbl_Department
GO
CREATE TABLE tbl_Department
(
DeptId INT IDENTITY(1,1) NOT NULL,
DeptName VARCHAR(256) NOT NULL,
DeptAddress VARCHAR(512),
DeptContact VARCHAR(15)
)
GO

Now, add some sample rows into the table:

INSERT INTO tbl_Department
(DeptName, DeptAddress, DeptContact)
VALUES
('Engineering', 'New Delhi', '1234567890'),
('Human Resources', 'Chennai', '9876543210'),
('Admin', 'Kolkata', '2763282789'),
('Human Resources', 'New Delhi', '1928272727'),
('Not Assigned', 'Mumbai', '1839288976')
GO

Let’s have a look at the table data now:

Sample table

Sample table

From … More


Use NEWID() inside function in SQL Server

In SQL Server, if we use the NEWID() function inside an user-defined function, it throws an error as “Invalid use of side-effecting or time-dependent operator in ‘newid()’ within a function.

For any reason, if we need to use the NEWID() function inside an user-defined function, we need to do some work around. In this post, we will discuss few tips which can be used to deal with such kind of scenarios.

To demonstrate this problem, first, let’s create a user defined function which uses NEWID() function inside. Though in production environment we might be using more complex logic with the NEWID value, but in this demo, to keep it simple, we will be using the LOWER() function which converts the given value to the lower case.

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID()
RETURNS NVARCHAR(50)
AS
BEGIN
--do some stuff with NEWID and 
More

Extract the first number from an alphanumeric string in sql server 4

In this post, we are going to learn how to extract the first numeric value from an alphanumeric string in SQL Server. To demonstrate this, we will create a dummy table and then we will insert some dummy rows into that table. Finally, we will use a SELECT statement to extract the first numeric value from the given alphanumeric string for each row of the table.

Let’s create the dummy table as below:

--Create dummy table named testTable
IF(OBJECT_ID('dbo.testTable')) IS NOT NULL
DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
(
val VARCHAR(100)
)
GO

Now, let’s insert some alphanumeric values in this table as below:

--Insert some dummy rows in testTable
INSERT INTO dbo.testTable
(val)
VALUES
('1846855445-DB-FC-D-B-FEDACAE'),
('912898124-B--EB-CBD-EEC'),
('AFAA---E-EDDEC-1080731029'),
('FFDE-DA-B-A-BCE-1807799782'),
('DDFAD-DDE-D-EB-FFDFBB-1683269209'),
('522822568-C-EFC--ACCA-EDB'),
('BDDAC-C-C-A-EC-450054394'),
('DBCD-D-AA-AD-EFA-385921800'),
('EF-B-ED-BBC-FEBC-599561669'),
('BED--F-BC-FD')

We have below sample records in the testTable:

--Get data from testTable
SELECT * FROM dbo.testTable
Sample records in testTable

Sample records in testTable

Now, to extract the … More


Checkpoints with containers in SSIS

In the previous post “Using checkpoints in an SSIS package“, we discussed how to configure an SSIS package in order to enable the checkpoints. Now, In this post “Checkpoints with containers in SSIS“, we are going learn how to configure a package to restart from the first task of the container in which it fails instead of the beginning of the package.

Most of the time, in an SSIS package, containers are used to group a set of tasks in a single unit of work and if the package gets failed, we need to restart the package from the beginning of the container instead of restarting it from the beginning of the package. Let’s create a dummy package and demonstrate it.

Using checkpoints with containers in an SSIS package – Demo

In this demonstration, we are going to use sequence container to group multiple tasks in … More