User Defined Table Types (UDT)

Hi Folks,

Today we will discuss importance of Table Types provided in MS SQL Server. This is probably the most under rated feature although extremely powerful and useful while developing an Enterprise application.

Real time scenario:

It is a very common scenario that as a developer we have to pass large amount of data to the database at once. For instance A Data Table having 1000 rows, more or less. So if we have such situation then most of the times as a developer we call our Insert Stored Procedure 100 times in a loop. Classic way !

With UDT very easily we can pass entire object of Data Table to a Stored Procedure as a single parameter. Looping then can be done in Database itself which will ultimately boosts overall performance.

Implementation:

Creating UDT:

In the MS SQL Server Query :


CREATE TYPE [dbo].[MyCustomType] AS TABLE
(
[FieldId] [int] NOT NULL,
[FieldName] [varchar(10)] NOT NULL,
[FieldCreatedDate] [DateTime]
)

It can be viewed here:

SQlTypes1

Then I will create a Stored Procedure which will accept MyCustomType as a parameter.

Creating Procedure:


CREATE PROCEDURE [dbo].[SP_InsertDataDemo] (
@in_UserId INT,
@in_UserName VARCHAR(30),
@in_multipleRecords MyCustomType READONLY
)
AS
BEGIN
//Logic goes here
SELECT * FROM @in_multipleRecords WHERE (your condition)
END

Creating Sample DataTable to pass:

Creating DataTable – Language C#


DataTable dt = new DataTable();
dt.Columns.Add("FieldId", typeof(int));
dt.Columns.Add("FieldName", typeof(string));
dt.Columns.Add("FieldCreatedDate", typeof(DateTime));

for (int i = 0; i < 100; i++)
{
DataRow dr = dt.NewRow();
dr[“FieldId”] = “Field_”+i.ToString();
dr[“FieldName”] = “FieldName_”+i.ToString();
dr[“FieldCreatedDate”] = DateTime.Now;
dt.Rows.Add(dr);
dr = null;
}

Note: Column names and Data Type of the columns in said Data Table must be exact the same as per the [MyCustomType] Type which we had defined in the MS SQL Server.

Passing the DataTable to Stored Procedure:

Now that we have everything set up. Let’s get into action asap.


SqlConnection con = new SqlConnection("Data Source= ; initial
catalog= Northwind ; User Id= ; Password= '");
SqlCommand command = new SqlCommand("SP_InsertDataDemo",con);
command.Parameters.AddWithValue("@in_UserId",123);
command.Parameters.AddWithValue("@in_UserName","user");
command.Parameters.AddWithValue("@in_multipleRecords",dt);
con.Open();
int i=command.ExecuteNonQuery();
con.Close();

That’s it ! Entire Data Table will be passed in one go.

Conclusion:

When you have multiple rows to be passed, UDT is an option available for quickly performing the task from front-end or from your DAL layer.

P.S. –

Ensure that Data validation is performed at Data Table level itself so that invalid data should not reach till your Database.

Thank you.

GauranG

AngularJS Tutorial Series – 4

This is a continued series of tutorials. Please refer to intro part here


Angular Modules

Modules are used for organizing the application. Means what? It means that various parts related to your application are defined in the Modules. Various parts like Controllers , Services etc.

Though we can define the Modules in same page but the recommended practice is to keep it in seperate JS file and refer it wheverever we are going to use it.

app = angular.module("myApplication", []); 

In above example Angular checks for ng-app directive through the DOM. The container which has this directive becomes the host of Angular. The app variable will have full access to angular child objects.

NOTE: It is highly recommended that we declare angular library at the head tag or at the start of the body tag. Once Angular is initialized then it will compile angular.module

Once the module is added then we can add Controllers, Custom Directives, etc.


Conclusion:

Angular modules are more like structuring your code at a common place. Angular Controllers can only be called if module is present.

Next we will see Angular controllers.


Thanks,

GauranG

AngularJS Tutorial Series – 3

This is a continued series of tutorials. Please refer to part -2 Here


Angular Expressions

syntax: {{expression}}
Expressions in Angular are used to bind data directly to HTML tags without using ng-bind anywhere in the code. The simple example to this would be-

<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.4/angular.min.js"></script>
<title>Angular Expression Demo</title>
</head>
<body ng-app="">
<div>
My name is {{ 'John Doe' }}</div>
</body>
</html>

Output
My name is John Doe


Lets go for one step higher.

<div ng-init="name='John Doe'">
     My name is <b>{{ name }} </b></div>

Output
My name is John Doe

ng-init="name='John Doe'" is used to initialize a variable name and assign a value to it ng-init="name='John Doe'". Then we are just using the variable to bind with the DOM. Like this:
My name is {{ name }}


Expressions with Numbers

The following example is quite self explanatory

<div ng-app="">
        <input type="text" ng-init="salary=50000;pf=1000;LTA=2000" />
        <span>Total Salary:{{salary+pf+LTA}}</span></div>

Output
Total Salary: 53,000.00


Mathematical expressions can be resolved like above. The best part is Angular also supports formatting.

Formatting is also referred as Filters in Angular. Following is the same example of above with Currency Filter.

<div ng-app="">
        <input type="text" ng-init="salary=50000;pf=1000;LTA=2000" />
        <span>Total Salary:{{salary+pf+LTA | currency:"₹"}}</span></div>

Output
Total Salary: ₹ 53,000.00


More about filters in later tutorials for now let’s focus back on Expressions. 🙂

Strings with Expression:

The regular string operations are supported, like concatenation for example:

<div ng-app="" ng-init="firstName='Gaurang';lastName='Naik'">

The name is {{ firstName + " " + lastName }}</div>

Output:
The name is Gaurang Naik


The Expressions can also be used with JavaScript objects.

<div ng-app="" ng-init="employee={firstName:'Vishal',lastName:'Dhawan'}"> 
The name is {{ employee.firstName }}
</div>
Output:
The name is Vishal

With Arrays

<div ng-app="" ng-init="numbers[23,06,82,13,01,84]">
The number is {{ numbers[3]  }}
</div>
Output:
The number is 13

Note: {{Expression}} can be replaced with ng-bind attribute.

Example

<div ng-app="" ng-init="numbers[23,06,82,13,01,84]"> 
The number is <span ng-bind="numbers[3]"></span>
</div>
Output:

The number is 13


Conclusion
Angular supports dynamic way of quickly binding methodology using brackets. It can resolve the expression and return the exact result. They support literals, operators, and variables.

In next chapter we will focus on Angular Modules. Stay tuned!
Thank you.


GauranG

AngularJS Tutorial Series – 2

This is a continued series of tutorials. Please refer to intro part here


First thing first. Since we are going to use AngularJS API, it is first important to add reference of the latest stable version to our HTML file.

<script ="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.4/angular.min.js"></script>

 

Introduction to ng-directives

 

1) ng-app  2) ng-model 3) ng-bind

 

ng-app:

This is the root element. All the code inside this element will be valid Angular code. So ng-app can be applied to tags like html , head , body and also to a div tag. Please note that, there can not be nested ng-app directive within the same hierarchy. Following example uses div tag as ng-app root element.


 

ng-model:

This element is used to actually bind values to a variable. This variable can be called as Model. All types of HTML input tags come with value attribute. This attribute is directly bound to the respective variable which later can be used throughout the page. Following example binds value field of txtinput to a variable called name. All you have to do is, this:

<input id="txtinput" type="text"  ng-model="name"/>

 


 

ng-bind:

This attribute is used to bind the model. Once we have associated a value in model to any control then the primary purpose will be to use this value through out the page or may be save this value to database. The database operations we will see later in the course, for now we will bind it to a paragraph tag. Its real time binding.


 

Sample explaining above

<!DOCTYPE html>
<html>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.4/angular.min.js"></script>
    <title>Angular Demo</title>
</head>
 <body >
<div ng-app="">
        <input type="text"  ng-model="name" /></div>
</body>
</html>

Conclusion

Now we know the basic three attributes of Angular, we will go further to understand quick binding expression, modules and controllers.

Thank you.


GauranG


Next chapter is Here

Introduction to Angular JS – Part 1

AngularJS Introduction

angularjs


Hi Guys,

Before we learn any technology we should first understand the primary purpose of inventing it. Angular is not very old and not very new to developers as well. It comes with rich set of API for manipulating of DOM but we still first need to understand why was it invented, isn’t it?

Data Binding

Data binding directly to HTML controls is the primary objective behind finding the angular. We hardly work with any application without the database. Offcourse Similar operations can be achieved by jQuery,etc but we are going towards easier world for developers thus Angular !

Angular uses a structured MVC way to bind data to controls and it also provides two way data binding approach. Means your model bound to any text box on HTML changes immediately as user changes the data and vice versa. Nevertheless to say without any Postbacks.

Framework

Angular is a framework thus it actually can not be compared to any libraries. One can design full fledged Single Page application with Angular. Following are the things for which we can use the Angular framework:

  • Data Binding
  • Routing
  • Unit Testing
  • Dependency Injection
  • Reusable component(Directives, Custom Directives)
  • Deep linking
  • Form Validation
  • Animation support

Now you know the primary purpose of Angular. Lets see some code, especially to two way binding example:

<!DOCTYPE html>
<html data-ng-app>
<head><title>Example</title>
    https://ajax.googleapis.com/ajax/libs/angularjs/1.6.4/angular.min.js
</head>
<body>
Name

{{name}}

</body> </html>

Conclusion

AngularJS developed for data driven applications with data binding is the primary objective.

In next tutorial we will start with basics of Angular.

Thank you,


GauranG


Next chapter is Here

Working with Files through MS-SQL 2012 onwards

Hi Folks,
The primary purpose of this article is to push developers to use modern techniques of working with files. Usually in our applications whenever we work with files, we follow the orthodox way. Say there is requirement where user wants to upload a document what we do is Create a table in our SQL database, something like below:

fileId userId filePath fileCreatedOn
14 45 D:\root\myApp\Files\user001.jpg 2017-02-28 11:39:45

Off course we can add more columns as per the requirements but lets focus on filePath and fileCreatedOn (which is nothing but getdate() at the time of insert). So these are the only two columns which give us information about our file. what if we need more than that ?

The Microsoft SQL 2012 has introduced File Table feature where SQL Server internally uses Windows API to store the file at the physical location as well as it stores all the details (like attributes) of the files in tabular format for our T-SQL querying. Behind the scenes SQL Engine uses the famous File Stream classes to store and access the information. The plus point is we get lot of extra attributes associated with the files which can be later used for extension of any functionality. So lets get started to work with File Tables in MS SQL Server. Here are the steps to follow:

First we will have to enable File Stream and complete some pre-requisites:

  1. Enable File Stream for the current instance.
  2. Assign a File Stream file group to database.
  3. Provide Non-Transnational access to the database.
  4. Specify directory for File Tables.

For above four steps, we will have to execute the following steps:

  1. Go to SQL Server configuration manager. Select SQL Server Services right click on the Instance Name and select Properties Follow the screenshot below:SQLServerProps.PNG
    Restart the SQL Service once you set these properties.


  2. Create a new database and go to its properties. In case if Database is already created then directly select the database and go to its properties and go to Files.Add new Database File. I named my file as FileTableDB_file. In adjacent column select FileType as FILESTREAM Data

  3. Go to Filegroups Add a new Filegroup under FILESTREAM group box. I named it as FilePrimary

  4. Now we will have to map this filegroup to our newly created Filestream Data file. So lets quickly go back to Files tab and go to FileTableDB_file and select the Filegroup from the dropdown as FilePrimary. Refer to the Screenshot below:DBFile.PNG

  5. Next step before we Save and Exit is go to Options and punch in the Directory Name and Non-Transacted Access Refer to the Screenshot below:DBOptions.PNG

We are done configuring the system for File Stream. Now let’s explore the functionality.


Creating a new File Table:

CREATE TABLE MyFileTable AS FileTable
WITH
(FileTable_Directory = 'MyFileTable_Dir');
GO
Now we go ahead and run a SELECT query on this table then we shall see the list of columns which will hold all the information regarding the document. The table is going to be empty for now. So lets fill it up.There are two methods from which we can insert the data.
  1. Move the file to the specified directory. The movement can be done either programmatically or simple copy-paste.
  2. Bulk copy files using T-SQL statements. (INSERT SQL query).

Let’s explore the option 1.

Right click on the table name to click on Explore FileTable Directory. This will browse to a localhost network location where  MyFileTable_Dir is created. Currently its empty. So I will copy two files in this location. Screenshot below:

DbFileTableAndDirectory.png
One is the MS word file and other is PPT file.
Note: MS SQL Server saves file references located on a specified path .It doesn’t actually store the files.
Now Lets go back SQL Server and fire a SELECT query on same table. You will see the data !

Now let’s explore the option 2.

Assume that you have files kept it in some other location which now you would want to import it into SQL tables.So I am going to import file from my F:\testFiles\testfile1.txt

INSERT INTO [dbo].[MyFileTable] ([name],[file_stream])
SELECT
'NewFileFromSQL1.txt', * FROM OPENROWSET(BULK N'C:\testFiles\NewFileFromSQL2.txt', SINGLE_BLOB) AS FileData
GO
If you will see that data is now inserted in the table like below. Also it will create a new file named ‘NewFileFromSQL1.txt’ on the physical location.
insfile


Note:
Before we conclude this article, one more quick topic I would like to cover. The core purpose of File Tables are to work with existing files but in certain scenarios you would also want to create files on the go. The MS SQL Server covers this requirement as well. One can always create a file using INSERT QUERY.

INSERT INTO [dbo].[MyFileTable] ([name],[file_stream])
SELECT
'NewFile.txt', CAST('THIS IS TEST FILE.' AS varbinary(20))
GO
This will create a new txt file named “NewFile.txt” at the physical location. We may use this feature to write simple Exception logs, etc.

Conclusion

This feature of MS SQL Server is useful in applications which required functionalities like Full Text Search, Maintaining files and it’s version controls,  document repositories etc.

There is lot about File Tables than we have covered but I hope I atleast got you interested in the topic.  Thank you very much for reading, All the best and Happy Coding!


– GauranG