Ado.Net Components, DataSet or DataReader

ADO.Net:
There are two main components of ado.net which are .Net framework Data provider and DataSet.

1. .Net frameword Data provider are very lightweight, used to connect with database, run the command/query by using Command object and get the results. then it depends upon us how to treat that Result set either we directly process the data or we place in DataSet as per requirements. there are different types of Data Providers in .Net Framework like:
a. .NET Framework Data Provider for SQL Server
it gives data access for Microsoft SQL Server. Uses namespace System.Data.SqlClient.
b. .NET Framework Data Provider for OLE DB
Used for data sources discovered by using OLE DB. Uses namespace System.Data.OleDb.
c. .NET Framework Data Provider for ODBC
It is used for data sources discovered by using ODBC. Uses namespace System.Data.Odbc.
d. .NET Framework Data Provider for Oracle
It provides supports for Oracle data sources. namespace System.Data.OracleClient
e. EntityClient Provider
give data access to Entity Data Model (EDM) applications. System.Data.EntityClient
f. .NET Framework Data Provider for SQL Server Compact 4.0.
It is for Microsoft SQL Server Compact 4.0. using System.Data.SqlServerCe.

Mainly .Net Framework Data Providers uses few core Objects which are give below:
a. Connection: Used to establish connection. its base class is DbConnection
b. Command: used to execute a database command. this object base class is DbCommand
c. DataReader: it reads a forward-only(DataReader reads one row at a time then move forward to next row then next, it will not move back thats why it's called Forward only), read-only(it will just read records from data source does not allow any editing). base class for DataReader objects is DbDataReader class.
d. DataAdapter: it is used to populates the DataSet. basically it provides communication between data source and DataSet to enable data access and manipulation of data.

There are some additional Objects also names as Transaction, CommandBuilder, ConnectionStringBuilder, Parameter, Exception, Error and ClientPermission.

2. The DataSet: it is second component of ado.net. it is mainly designed to data access from any data source or we can say independent of data source, it can be used with different data source like XML data or to manage data local. DataSet can have one or more than one DataTable objects which have rows, columns even primary and foreign keys contraints. we can perform within application qurieson DataSet to manipulate the data.

Choose DataReader or DataSet: when you think you want only to read data and there is no requirements to perform editing then DataReader is preferable and fast also but when you require data to cache it locally in application and do some manipulation and perform some other operations then DataSet is best.

SqlDataSource and ObjectDataSource Differences

why we should use SqlDataSource and why ObjectDataSource.
why we do not directly use SqlDatasource and prefer ObjectDataSource even SqlDatasource is less code and fast also.

> Basically ObjectDatasource is business object that calls the stored procedure to get records from database whereas we can do directly this one by using Sqldatasource in very less code.

> Objectdatasource provide a perfect abstraction of our design and code.

> While using objectdatasource our pages does not depend upon the database information used in code behind.

> SqlDataSource is typically two-tier in nature but ObjectDataSource is based on three-tier architecture.

> SqlDataSource requires very less line of code to connect with database and ObjectDataSource needs more coding to build data access classes to communicate with database.

> SqlDataSource does not support data encapsulation whereas ObjectDataSource is fully designed for encapsulation.

> For SqlDataSource we write complete connection string for database connection. ObjectDataSource exposes TypeName attribute as a middle layer class mainly used to perform database operations.

> Sqldatasource uses ADO.NET mainly. we have to write all database queries, connections and commands on the page which looks very raw data.

> Objectdatasource behave like a wrapper which control all crud operations.

> Normally if we have to do same thing for a short time span and for a limited time perioed then it's okay to use SqlDatasource but when we talk about a big application and for long run we prefer to use Objectdatasource because direcltly use sqldatasource in your code behind is very quick but dirty habbit which is not preferable always.

> When we face frequent changes in our system then we have to go for page by page and change all sections of code which are directly accessing that change. so by using objectdatasource it is very professional and pretty to have such changes in practice.

Reference: https://forums.asp.net/t/1876836.aspx?When+do+I+use+SqlDataSource+vs+EntityDataSource+vs+ObjectDataSource

Get Total Count From Second Table - SQL Server

How to get count of a column from second table if there is any entry using SQL Server.

Hi, Today we will check that how we can get count or number of rows in a second table agains a column in first table. for example we have a table of products and we want to check total number of orders against a product. this can be done in various ways but we want to avoid sub query as mostly i don't prefer sub query due to efficiency of execution.

For this problem resolution we can use COMMON TABLE EXPRESSION. basically a common table expression (CTE) is a temporary result set. there are some limitations also but as above mentioned task we can use CTE which is not going to fall us in performance issue. As a comparison to temp table there is no need to worry about it, in CTE, as these have an execution scope within statement. CTE can also be used within Views.

Anyway Let's have a look practically: we want to get count for each product from order table but it should also be shown as column in normal query we don't want to use union or some other technique to combine the result set.

For example: normally we are showing mobile products in table and we get result from below query:

SELECT PROD_ID,PROD_TITLE 
    FROM PRODUCT_TABLE  
    WHERE CATEGORY_NAME = 'MOBILE'

and result show like this

but we want to show the count of each product as third column as shown below:

so we will use a common table expression to get this done very simple. below is shown common table expression:

;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
AS (
     SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
      FROM ORDER_TABLE GROUP BY PROD_ID)

so how we will combine this query with our simple product query to get it done in a single result set. we will use CTE common table expression as table simple table and join it will our original query to get count for each table as below:

;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
  AS (
        SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
          FROM ORDER_TABLE GROUP BY PROD_ID)
    
   SELECT P.PROD_ID,P.PROD_TITLE,CT.TOTAL_COUNT 
   FROM PRODUCT_TABLE P 
   LEFT OUTER JOIN CTE_PROD_COUNT CT ON CT.PRODID1 = P.PROD_ID
    WHERE P.CATEGORY_NAME = 'MOBILE'

With this query you can use your current filter and CTE will simple add a total count of each product in query as a column.
it will look like this:

Hopefully you like this experience. Comments and suggestions are always welcome!!

Constraints in SQL Database

Today we will learn what are constraints in SQL. in our daily routine work we are using many constraints but in actual we don't know what are constrains. basically constraints are rules, such rules which are enforced on data column of a table. we can apply a constraint on a table or a column. these are used to check the accuracy of data and reliability of data in any circumstances.
So, let's have a look on constraints one by one which are mostly used in SQL.
NOT NULL Constraint − will make sure this column cannot be NULL.
For example we have a PRODUCTS table and we want to add NOT NULL constraint on PRICE Column:

ALTER TABLE PRODUCTS 
       MODIFY PRICE DECIMAL (18, 2) NOT NULL;

DEFAULT Constraint − it will set Default value for a column if none is given.
how to add Default Constraints:

ALTER TABLE PRODUCTS 
        MODIFY PRICE DECIMAL (18, 2) DEFAULT 10.00; 

To DROP a default Constraint we will use this query other than normal DROP constraint query:

ALTER TABLE PRODUCTS 
   ALTER COLUMN PRICE DROP DEFAULT;

UNIQUE Constraint − this will check Column Values are unique.
How to Add Unique Constraint:

ALTER TABLE PRODUCTS 
   MODIFY QTY INT NOT NULL UNIQUE;

PRIMARY Key − it will set unique identity for each row/record.
How to add:

ALTER TABLE PRODUCTS 
   ADD CONSTRAINT PK_PRODTID PRIMARY KEY (PROD_ID, PRODUCT_NAME);

FOREIGN Key − it will also Uniquely identifies a row/record for any given database table.
How to add:

ALTER TABLE ORDERS 
   ADD FOREIGN KEY ((PRODUCT_ID) REFERENCES PRODUCTS (PROD_ID);

CHECK Constraint − it used to check that all values of column are according to a specific condition.
How to Add:

ALTER TABLE PRODUCTS
   MODIFY QTY INT NOT NULL CHECK (QTY >= 2);

INDEX − Basically Indexes are used to retreive data from a table quickly.
How to Create Index:

CREATE INDEX idx_price
   ON PRODUCTS (PRICE);

Constraints can also be added while creating a table with CREATE TABLE Statement otherwise you can set later with ALTER TABLE statement.

Dropping Constraints:
We can also remove a constraint by using below query:
For example, to drop the primary key constraint in the PRODUCTS table, you can use the following command.

ALTER TABLE PRODUCTS DROP CONSTRAINT PK_PRODTID;

Comments and Suggestions are always Welcome!

Insert Data from one Database Table to another Database Table on Different Server

Today we are going to learn how to insert data from one database server table to another database server table.
So let’s start writing the query.

Insert into [Server/Machine Name].[Database Name].[Schema].[Table Name]
Select * from [Server/Machine Name]. [Database Name].[Schema].[Table Name]

If you want to pick specific columns then you will write this query like this

Insert into [Server/Machine Name]. [Database Name].[Schema].[Table Name](Column1,Column2)
Select Column1, Column2 from [Server/Machine Name]. [Database Name].[Schema].[Table Name]

Comments and Suggestions are Always Welcome!

Insert Data From one Database Table to another Database Table on Same Server

Today we are going to learn how to insert data from one database table to another database table. I am going to write a separate post in coming days to insert records from different database servers.
So without wasting time let’s have a look at the query to insert records from one DB table to another DB table on a same server.

Insert into [Database Name].[Schema].[Table Name]
Select * from [Database Name].[Schema].[Table Name]

If you want to pick specific columns then you will write this query like this

Insert into [Database Name].[Schema].[Table Name](Column1,Column2)
Select Column1, Column2 from [Database Name].[Schema].[Table Name]

For example you are moving customer table’s data from Northwind database to AdventureWorks database on the same server/machine/computer then your query will be

Insert into [AdventureWorks].[dbo].[Customer]
Select * from [Northwind].[dbo].[Customer]

Comments and Suggestions are Always Welcome!

Import Data in SQL Server Table From Excel

How to import data in sql server table from excel spreadsheet
In this tutorial you will learn how to import data into sql server table from an excel spreadsheet using SQL Import and Export Wizard with easy steps. Let start doing it step by step.
Step 1: Pick your Excel source

Step 2: Pick your SQL Server target database

Step 3: Pick your source worksheet (from Excel) and your target table in your SQL Server database; see the "Edit Mappings" button!

Step 4: Check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table:

Step 5: If you want to use it later on, save your SSIS package to SQL Server:

Step 6: Success! This is on a 64-bit machine, works like a charm - just do it!!

Comments and Suggestions are always Welcome!

Stored Procedures Best Practices

Stored Procedures Best Practices:
Today we will check which performance parameters and other point we should keep in mind while creating a stored procedure.
There are many benefits of using Stored procedures. basically a stored procedure is a set of SQL statements which are stored in database in compiled form with a specific name and most important benefit is, we can use this stored procedure in number of programs, with this feature we are not required to write query again and again.
we can also perform many operations at database level so it will not load our application and application will perform well. we can use many programming concept like IF-ELSE, WHILE loop and other constructs also.
Here i am going to share some good approaches while creating a stored procedure which, a developer should use to enhance the performance of stored procedure. there are some standard rules and signature to create stored procedure but we can add other things as per our requirements. i will not go to details of each benefit, will give a short description. so let’s have a look:
1. SET NOCOUNT ON: we should always use SET NOCOUNT ON statement at the start of your stored procedure code to minimize the unnecessary network round trips.
2. PARAMETER SNIFFING: This is a very good concept to study, we should never use our stored procedure parameters directly in SQL statement because it will cause Parameter Sniffing. first, we should assign the parameter to local variable and then we should pass to SQL statements.
3. Use table variables: Table Variables are very good for small data instead of temp table. it will give your stored procedure a good rise in performance.
4. Use of Temp Tables: Temp tables are not always recommended but if it is required and no other option then always create indexes on temp table. it is for big data like millions of records in this case do not use table variables.
5. Avoid using CURSORS: CURSORS are used to manipulate the data row-by-row but it will make your Stored procedure very slow, always try to avoid CURSORS and check for other SET based approaches.
6. Use TRY-CATCH block: This is a very good approach to use TRY-CATCH in SQL statement so if there is any error you can handle at Database and return a proper flag to application. it will also never let your application do any operation and check data for exception handling.
7. Using Transactions: BEGIB TRANSACTION, COMMIT/ROLLBACK these are good to use when you are going to perform some action on your database but always try to use short transaction with them otherwise it will create blocking in database and deadlocks. but by using Transactions we can keep track of successful/unsuccessful of your transaction.
8. Aliasing objects: although database will allow you to use SQL reserve keyword but try to avoid use reserved keywords otherwise it will affect your statement performance and its good approach to use meaningful naming conventions.
9. Avoid GOTO Statement: Like all other programming language its bad practice to use GOTO statement. it good to use and filter you statements with IF-ELSE without using GOTO.
10. Avoid 'sp_' prefix: your Stored procedure should not start with 'sp_' because in this case compiler will go first to master database and then it will come back to current database so it will slow down your Stored procedure.
11. Use fully qualified objects name: In SQL queries, it will help the compiler to find cached and compiled object quickly.
12. Use WITH ENCRYPTION: if it is required you can use this to hide the Stored Procedure statement.
13. Add Comments: always add comments in your procedure like a good programmer.
14. Use BEGIN-END code block: Whole Stored procedure should be in BEGIN-END block.
Below is the standard template for creating stored procedure which is generated by SQL Management studio.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Comments and Suggestions are Always Welcome!

Difference in ExecuteNonQuery ExecuterReader ExecuteScalar

ExecuteNonQuery() Vs ExecuterReader() Vs ExecuteScalar()
Today we will check the difference between ExecuteNonQuery, ExecuterReader and ExecuteScalar and we will try to understand which one should be used for which type of Database operations:
1. ExecuteNonQuery():
ExecuteNonQuery is always recommended for such operation in which you are not required to return anything from the query. it's used for INSERT,UPDATE and DELETE operations or other Action Queries like Alter, Drop,Create. it's function is same either we use direct SQL Query or Stored Procedure. so becarefull and do not use with SELECT Statements because it will always return total count of affected rows by query or stored procedure. we can not do much with its return because it is only returning int and its assigned to integer variable only.
Example:

        ExecuteNonQuery("DELETE FROM MYTABLE");
    ExecuteNonQuery("UPDATE TABLE SET COLUMNAL = 'Code'");

If we use ExecuteNonQuery with a Select Statement then it will do nothing. it will execute the Query but it's row affected will be negative -1 you can throw any error also.

2. ExecuterReader():
ExecuteReader is used to Read the Data from SQL Statement and its recommended to use with SELECT Operations. ExecuteReader returns IDataReader object when fetch the records from Database and we can perform other operation on this object as per our requirements like we can bind GridView etc. we use .Read() method of SqlDataReader which read the data one by one. it will also work for Action Queries but best to use for SELECT non action queries. it will return a collection of all data on which we can perform operation row by row or column by column also.

       IDataReader dr = ExecuteReader("SELECT * FROM MYTABLE"); 
     while(dr.Read())
     {
             Response.Write("Name: " + dr["ColumnName"]);
    } 

3. ExecuteScalar():
ExecuteScalar is always used for a single column value. it will return first column of first row of Query. it is used when we are required one column or a single value like we want to get SUM of a column,Average of Column or Count and also it's usable when we want to know ID of employee from employees table against a single employee. it will be usefull for Non Action quries. it will return the an object type.

       Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT SUM(SALARY) FROM EMPLOYEES")); 
 Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT AVG(INCENTIVE) FROM EMPLOYEES")); 

it's always used for single value but if we used with multiple column statement then it's behavior will also remain same it will return first row first column value.
Comments and Suggestions are Always Welcome!

Order by clause with Union in Sql Server

Problem: use of order by with union statement.

We will discuss different ways of using order by statement with union statement.

mostly developer tries to add order by in both union statements like below:

SELECT COLUMN1,COLUMN2 FROM TABLE1
    ORDER BY COLUMN1
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2
    ORDER BY COLUMN1

This query is not correct and it will throw an error:

incorrect syntax near order by.

SQL union does not support two order by statement as union is used to combine two queries result as one so how is it possible to have two order by statements for one result set.

However, there are other ways to order both query as per requirements but not with two order by statements. Let's have a look first on simple syntax that how we can use order by with union statement.

SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    ORDER BY COLUMN1

This query will order the union result per Column1 value. this is the most simple and cleaner statement we can use for order by with union statement.

Now another scenario if you have some complex query and it’s difficult for you to handle the order by as mentioned in above query then you can use below query it will give you little more control over the query and you can perform some more operation.

SELECT * FROM (
    SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    )R
    ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()

I share above query because I face on issue on simple order by with union statement so I resolved with this query. maybe you require the union result be joined with some other table so it will be easy for you to handle with this query. however, it is not as much cleaner and its using also subquery so may be some performance factor affect your results.

Now if you want to order you both queries should be order by independently from each other than you can use below query. for example, you want first and second query of union should be sorted differently/independently then have a look:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
    ORDER BY orderbyKey

This query will show first record of top query and then bottom query records accordingly.

If we want to sort the result set that it first shows the top query result and then bottom query result and these should be order by Created Date descending then we can do this below:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
    ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 

Complete Script:

USE tempdb
GO
-- Create table
CREATE TABLE TABLE1 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
CREATE TABLE TABLE2 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
GO
--INSERT Sample Data 
INSERT INTO TABLE1 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 1, 'VAL1_TABLE1',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE1',GETDATE()+1
UNION ALL
SELECT 3, 'VAL3_TABLE1',GETDATE()+2;
INSERT INTO TABLE2 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 3, 'VAL1_TABLE2',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE2',GETDATE()+3
UNION ALL
SELECT 1, 'VAL3_TABLE2',GETDATE()+5;
GO

 --1ST
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 ORDER BY COLUMN1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --2ND
 SELECT COLUMN1,COLUMN2 FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --3RD 
 SELECT * FROM
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 )R
 ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()
 go
 --4TH
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
 ORDER BY orderbyKey
 go
 --5TH<
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
 ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 
go
-- Clean up
DROP TABLE TABLE1;
DROP TABLE TABLE2;
GO

Comments and suggestions are always welcome!