Scripting the table data

1.       Connect to the server from which you want to copy the data from.

2.       Go to the database from which you want to copy the data,

3.       Right click -> Tasks -> Generate scripts

4.       Choose Object -> Check “Select specific database objects” -> select the table you want to script the data

5.       Go to scripting options -> Advanced -> “Type of data to script” (Choose according to your need)

6.       Next -> Next -> Finish.  It’ll script everything to C:\Users\raghu\Documents\script.sql (you can even give the path at  point 5)

Thanks you for reading. please feel free to contact me if you need any more information.

Views

Types of views:

1. General Views:  These views are normal view, which are just return the results of the underlying tables from one server  without saving the data. 
2. Indexed Views(materialized views): These views store the actual results of the view query in the clustered index structure. We use these views to improve the performance if our view query contains multiple joins and aggregations.
3. Partitioned Views: These views used to get the data from joining partitioned data from the tables which are on the multiple databases and multiple servers.
1. If the all underlying tables are in one server then these views are called "Local Partitioned view".
2. If the underlying tables are in multiple servers then the view is called "Distributed Partitioned view".

Creating General View:

Syntax:

CREATE VIEW [VIEW NAME] 

WITH [OPTION]

AS 

       [SELECT STATEMENT]

GO

Example:

      Creating a view using employee table which returns the employee information along with his manager name. Usually we save only employee manager Id in the table, we need to make a join every time when we need the manager name, instead we can create a view and use it when ever we need same as table.

if not exists(select * from information_schema.tables where table_schema = 'dbo' and table_name = 'Employee')

begin

create table dbo.Employee(Id int, Name Varchar(30),ManagerId int, salary INT)

END

GO

--Creating a view to return the employee and hiss manager

CREATE view dbo.EmployeeManager
WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Some points about view:

1. The view creation always start with "CREATE VIEW" key word.
2. The body of the view is general "SELECT" statement, which is subjected to general rules of SELECT.
3. There is no restriction on the columns in the select statement, we can Add new columns(computed or any other) or Remove the columns which we are interested in
4. If the underlying table structure changes, the view will give errors, so we can use "SCHEMABINDING" option while creating a view. so the alter table is not allowed unless we drop the view.
5. Materialized views also created with SCHEMABINDING option.

Creating indexed view:

          Indexed views are SCHEMABINDED views which are indexes defined on it. We can change the above to indexed view by creating a index on it.

CREATE view dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

CREATE CLUSTERED INDEX idx_cl_id  ON  dbo.EmployeeManager(id);
GO

*) Indexed views store the view results and create the index on them.

Altering View:

We can use "ALTER VIEW" to alter the view definition.

Example: Let's alter the view to add "Manager salary"  column.

ALTER VIEW dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',
                mng.salary 'ManagerSalary',

emp.salary

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Dropping View:

        1. All the views will be stored in "sys.views" system table. 

Syntax: 

DROP VIEW [VIEWNAME]

Example:

if exists (select 'x' from sys.views where object_id = OBJECT_ID(N' dbo.EmployeeManager'))
BEGIN

       DROP VIEW dbo.EmployeeManager

GO

Uses Of  View:

1. Hide the complexity of the queries: Let us say if you have a query with complex joins and calculations you can just create a view for that query and use it anywhere. It'll make the query more readable.
2. Security: If you have a table which have some security information and you want to share some of it's data to others. You can just create a view with the data you want to share and give permission on the view to user, instead of the table. In such a way you can protect illegal access to the tables
3. Support code referring to Deleted or changed tables: If you have a code which referring to a table and in the process of clean up you decided to delete the table or change the table name. In that case the code referring to old table will break, Instead you can create a view with the old table name and refer it to the new table. In such a you can make the old code work,

PS : It is best practice to check the existence of the view before creation and altering and dropping to avoid the SQL errors in the script.

Thanks for reading. Please feel free to comment if you have any suggestions.

Adding Constraints

Adding Primary Key:

1. Primary key enforces the uniqueness of the data in table
2. Data in the table is organized using the primary key as it creates clustered index
3. It also improves the performance of the WHERE clause etc

While creating Table:

    Just specify the 'primary key' attribute next to the column which you want to add as the primaryKey.

Example:

       Create Table Test(
                Id int primary Key,
                Name varchar(20)
         )

-- Compound Primary key:

       Create Table Test(
                Id int  Not NULL,
                Name varchar(20),
                LastName varchar(30)
                Primary key (Id,Name)
         )

• It'll add the primary key on both the columns (Id, Name).

After Creating Table:

1. We need to use ALTER TABLE syntax to add the primary key to table.

ALTER TABLE Test ADD constraint PrimaryKey_Id_Test primary key(Id)
GO

Adding New Primary Key Column:

     We can just specify the 'primary key' attribute at the end of the add column statement

ALTER TABLE [TABLE NAME] 

ADD [COLUMNNAME] DATATYPE primary key

GO

Foreign Key Constraint:

1. It'll enforce the rightness in the data, when data in one table is dependent on the other
2. It'll allow us to avoid accidentally delete the data in one table while it has dependents in other tables

While creating Table:

    Just specify the 'foreign key' attribute next to the column which you want to add as the foreign key.

Example:

       Create Table Test(

                Id int foreign key references ForeignTable(Id) On Delete [OPTION],

                Name varchar(20)

         )

After Creating Table:

ALTER TABLE Test 

ADD CONSTRAINT ForeignKey_Id_Test foreign key(id) 

references ForeignTable(Id) ON DELETE [OPTION]

GO

And you can specify 4 different key words for [OPTION], Which will let you set what should happen if you try to delete the row in ForeignTable, if there a row referencing the Id of the row which we are trying to delete in ForeignTable.

• ON DELETE RESTRICT (default ): It will gives the error message saying we should delete child rows referencing the Id should be deleted in Test table.
• ON DELETE NO ACTION: same as ON DELETE RESTRICT 
• ON DELETE CASCADE:If we try to delete row in ForeignTable It'll delete all the rows which are referencing the row. It will do automatic clean up. but we might end up deleting important information.
• ON DELETE SET NULL : If we try to delete row in ForeignTable It'll set the NULL for the foreign key column of all the rows which are referencing the row.

Default Constraint:

    Default constraint ensures to add the data when there is no data is specified to  the column while inserting data.

While Creating Table:

Example:

       Create Table Test(

                Id int primary Key,

                Name varchar(20) default 'NONAME'

         )

After Creating Table:

ALTER TABLE Test

add constraint default_Id_Test default 'NONAME' for Name

GO

Dropping constraints:

ALTER TABLE TEST DROP [Constraint Name]

GO

Example:

ALTER TABLE TEST 

DROP DF__Test__LastName__108B795B

GO

1. We can't delete the rows unless we delete the constraints on the tables. 
2. we can find all the constraints in "information_schema.table_constraints" table

Please free to comment if you need any other information.

Creating, Altering and Inserting data into tables

Creating Table:

1. You can find all the table information in "information_schema.tables" system table.
2. It is best practice to find the existence of the table before creating so that even if we run the create script 2nd time, it won't give errors.

Syntax: 

CREATE TABLE [TABLENAME]([columnname] dataType, [columnName] dataType,...)
GO

    

Example:

 CREATE TABLE Test (Id int, Name Varchar(50));

 GO

Example:

Alter Table:

      Alter statement is used to Add or drop the columns and change the column datatypes and also adding the constraints.

Adding column:

Syntax:

ALTER TABLE [TABLENAME] ADD [COLUMNNAME] DATATYPE

 GO

      

Example:

ALTER TABLE Test ADD LastName Varchar(20)

 GO

      

- It will add LastName column to Test table.

Renaming the column:

Syntax:

SP_Rename 'TABLENAME.ColumnName','NewCOLUMNNAME', 'COLUMN'

 GO

      

Example:

SP_Rename 'Test .Name','FirstName', 'COLUMN'

 GO

      

- It will rename Name column FirstName.

Dropping column:

Syntax:

ALTER TABLE [TABLENAME] DROP COLUMN  [COLUMNNAME] 

 GO              

  

Example:

 ALTER TABLE Test DROP COLUMN LastName

 GO              

        

- It'll Drop LastName column form Test table.

Changing Column Datatype:

Syntax:

 ALTER TABLE [TABLENAME] ALTER COLUMN [COLUMNNAME] DATATYPE

 GO              

        

Example: 

 ALTER TABLE Test ALTER COLUMN LastName Varchar(100)

 GO              

   

- It'll change the datatype from varchar(50) to varchar(100)

Inserting Data:

Syntax: 

 INSERT INTO TABLE [TABLENAME] VALUES(1ColumnValue,2ColumnValue,,...)

 GO               

Example:

    INSERT INTO Test Values(1,'Rajesh')

    GO

    INSERT INTO Test Values(2,'Raju')

    GO        

If you want to know adding constraints to table read Adding Constraints.

Please feel free to comment if you need any other help.

Install , Update Nuget Package from Package Manager Console

Open Package Manager Console:

1. Goto Tools -> Library Package Manager >Package Manager Console

2. It'll open command line. 

Installing Package (Install-Package):

1. Use Install-Package command to install the package.(Installs on all projects of your solution)

     Install-Package Newtonsoft.Json

2. Install package to specific project

     Install-Package -ProjectName SampleWebAppAPI  Newtonsoft.Json

3. Installing specific Version of the package

     Install-Package  Newtonsoft.Json -Version 8.0.2

You can use the combination of the options

Update Package (Update-Package):

1. Update all Packages of all the Projects

    

     Update-Package 

2. Update specific package on all the projects 

     Update-Package Newtonsoft.Json

3. Update  package on all the projects to a specific version

     Update-Package Newtonsoft.Json -Version 8.0.2

4. Update Package of a specific project

     Update-Package -ProjectName SampleWebAppAPI Newtonsoft.Json

5. Reinstall the package and all its dependencies

     Update-Package  Newtonsoft.Json -reinstall

You can also use combination of options

Uninstall Package(Uninstall-Package)

1. Uninstall Package 

     Uninstall-Package  Microsoft.ASPNet.WebApi 

Please feel free to comment or contact me. 

Thank you.

Install,Update Nuget Package

From package Manager:

1. Go to the project for which you want to add the Nuget package
2. Right click on References and select "Manage Nuget Packages"
3. Nuget Package manager will appear. 

There will be 3 options at the left.

1. Installed packages: 

      This contains all the packages. If you want to uninstall , you can just click on uninstall. It'll              remove package from the project.

2. Online:

    It'll list all the packages you can just click on "install" to install the packages

3. Update: 

     It contains all the updates to your installed packages of your project, you can either update single        package or update all the packages.

To know how to Install and Update Packages from Package Manager Console please read - Install and Update Nuget packages from Package Manager Console .

Please feel free to comment or contact me if you need any help. Thanks for reading. 

Error Handling and authentication with Interceptors in angular

About:

             Interceptors are basically service factory which we register with $httpProvider. They are used to intercept $http requests before server call or after server returned the response, so that we can process the config object, request and responses of the $http calls and modify them according to our neeeds. These are mostly used in Global Error Handling and Authentication of the requests.

There is 4 interceptors available:

• request: This method is called before server request is made with "config" object. We can modify config object
• requestError: This will call if the request can't be sent to the server or interceptor rejects the request.
• response: This interceptor will be called after server returns response. arguments contains the response object, we can modify the response.
• responseError: This interceptor will be called if the server returns error. or request is rejected by the interceptor.

Sample script for interceptors:

Creating a service with interceptor definitions:

Here is a service which defines 2 interceptors.

1. In request interceptor we are checking the authorization and if not authorized rejecting the request.
2. In responseError interceptor we are calling a function defined on $rootScope , which will open error model on the UI.

Registering httpInterceptor service with $httpProvider service:

We need to register our Interceptor service with $httpProvider, so that our interceptors called on every $http calls. we just need to push our service to $httpProvider.interceptors array.

We are done! It'll call our interceptor on every $http call. 

Uses:

1. Global error handling of the application. We don't need to write error logic for every $http request
2. Global Authentication handling. We don't need write authorization logic before every request

   Please feel free to comment if you need any information.

Creating service broker sample scripts

About Service Broker:

          Service brokers allows users to send and queue messages using server database engines. These messages processed asynchronously so users can write sophisticated programs. And can improve the performance since we don't need to wait for the message to process. We can send messages between different databases which will help us in inter database communication. We can register Stored procedure with the queue so that they will execute when we get the message in queues which will simplify the programming.

Components of service broker:

1. Message Type: Message Type defines a name for specific kind of messages, and kind of validation for the message. If your message if perfect, setting validation OFF will improve performance. 
2. Contract: Contract defines which message types are used in conversation. And which side can send message of that type. Each conversation follows a contract. 
3. Queue: Queues holds incoming messages for a specific service. We should have different queues for different services. We can register activation procedures for queues which will be executed when message is received. To improve the performance we need to turn off the retention of the messages after processing, unless you need exact message.
4. Service: A service is a name specified for a task which service broker performs. Service broker used service name to deliver messages to specific queue. we need to specify which queue the service should use while creating the service.

Creating Service Broker:

    Let's create service broker to communicate between 2 databases.

1. SenderDB will send messages.
2. RecieverDB will receive and process messages in a stored procedure.

Sender DB :

ReceiverDB:

Once you run both scripts your are done with the set up of all components of service broker.

Communication Using Service broker:

Sending messages:

1.  For sending message we need to create a conversation dialogue on a service.
2. Then we can send multiple message with send command

Receiving messages:

1. All sent messages will be saved in queue which we registered for the receive service.
2. we can read the messages from the service like below.

PS: If service broker is not enabled in database, using following script to enable it.

ALTER DATABASE RecieverDB SET ENABLE_BROKER
GO

Please feel free to comment if you need any clarification. or scripts.

OpenCover Installation

About OpenCover:

            Opencover is a code coverage tool. Which shows % coverage and also the code which is not        covered in the tests. 

Installation Procedure:

1.  Install the OpenCover console https://github.com/opencover/opencover/releases
2. Install the OpenCover VisualStudio extension https://visualstudiogallery.msdn.microsoft.com/6950a046-8919-4935-8542-c6f37956f688/view/Discussions
3.   After restarting Visual Studio click Tools -> Options -> Find OpenCover.UI Options and change OpenCover path to the install location of OpenCover console – my path is C:\Users\raghu\AppData\Local\Apps\OpenCover\OpenCover.Console.exe
4. You should have a menu now in Visual Studio called “OpenCover”, click that menu and click “OpenCover test explorer”
5.    Click the refresh icon in the upper left hand corner of the test explorer, this should show all the tests.                                                     
6.   Highlight all the tests -> right click -> cover with OpenCover (wait for a bit, there is no loading screen it just freezes)
7.  You should now have an “OpenCover Results” window, and your code should have green or red dots next to each line.

Please feel free to comment if you need any more information.