Valdating data in Dataset

• Validating data is the process of confirming that the values being entered into data objects conform to the constraints within a dataset's schema, as well as the rules established for your application.
• Validating data prior to sending updates to the underlying database is a good practice that reduces errors as well as the potential number of round trips between an application and the database.
• You can confirm that data being written to a dataset is valid by building validation checks into the dataset itself.
• The dataset can check the data no matter how the update is being performed — whether directly by controls in a form, within a component, or in some other way. Because the dataset is part of your application, it is a logical place to build application-specific validation (unlike building the same checks into the database backend).

TableAdapter in Windows Application

TableAdapter Overview:

1. TableAdapters provide communication between your application and a database.
2. TableAdapters are also used to send updated data from your application back to the database.
3. ADO.NET Data Adapters were used for communicating between an application and a       database.
4. TableAdapters typically contain Fill and Update methods to fetch and update data in a database.
5. TableAdapters are created with the Dataset Designer inside of strongly typed datasets. 

Fill a dataset with data

• The phrase "filling a dataset with data" refers to loading data into the individual DataTable objects that make up the dataset.
• You fill the data tables by executing TableAdapter queries or by executing data adapter (for example, SqlDataAdapter) commands.

INTERSECT vs INNER JOIN

 Both gave a same result. But One difference that is Column value is Null. Please see the Examples:
First create a temp Table in the Sql Server.
Example :1

Declare @m_table1 table (id int, firstName varchar(50))
Declare @m_table2 table (id int, firstName varchar(50))

-- Insert the Value

Insert into @m_table1 values (1,NULL)
Insert into @m_table2 values (1,NULL)

-- Inner Join

Select t1.*
from @m_table1 t1
inner join @m_table2 t2
On
t1.id=t2.id and t1.firstName=t2.firstName

-- INTERSECT

Select * from @m_table1

INTERSECT

Select * from @m_table2

Result for Example 1:
  Inner Join -- No data displays.
 INTERSECT -- Row displays.

Example --2:

Declare @m_table1 table (id int, firstName varchar(50))
Declare @m_table2 table (id int, firstName varchar(50))

-- Insert the Value

Insert into @m_table1 values (1,1)
Insert into @m_table2 values (1,NULL)

-- Inner Join

Select t1.*
from @m_table1 t1
inner join @m_table2 t2
On
t1.id=t2.id and t1.firstName=t2.firstName

-- INTERSECT

Select * from @m_table1

INTERSECT

Select * from @m_table2

Result for Example 1:
 Inner Join -- No data displays.
 INTERSECT -- No data displays.

I hope , you understand the difference between INTERSECT vs INNER JOIN

Function Name Format

Introduction:
    Today, we discussed about function name format.

CREATE FUNCTION FNNameformat(@colName varchar(max))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @IntNumbers VARCHAR(MAX)
BEGIN
SET @IntNumbers = UPPER(LEFT(@colName ,1)) + LOWER(SUBSTRING(@colName,2,LEN(@colName)-1))
END
RETURN @IntNumbers
END

Extract Character from string in SQL

          Today, we see the Extract character from String in SQL Server

Alter FUNCTION ExtractCharacter(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) <> '0' AND SUBSTRING(@String,@Count,1) <> '1' AND SUBSTRING(@String,@Count,1) <> '2' AND SUBSTRING(@String,@Count,1) <> '3' AND SUBSTRING(@String,@Count,1) <> '4'AND SUBSTRING(@String,@Count,1) <> '5' AND SUBSTRING(@String,@Count,1) <> '6' AND SUBSTRING(@String,@Count,1) <> '7' AND SUBSTRING(@String,@Count,1) <> '8' AND SUBSTRING(@String,@Count,1) <> '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1) END SET @Count = @Count + 1 END RETURN @IntNumbers END GO

Split Year,Month and Day from DateTime Column in SQL Server

Split Year,Month and Day from DateTime Column in SQL Server
Select YEAR(iddate) as [Year],Month(iddate) as [Month],Day(iddate) as date from Excel_Table where iddate!='' order by date Or Select DATEPART(YEAR,iddate) as [Year],DATEPART(Month,iddate) as [Month],DATEPART(Day,iddate) as date from Excel_Table where iddate!=''

Extract Number from string in SQL

Extract number from string in SQL

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
 RETURNS VARCHAR(1000)
 AS
BEGIN DECLARE
@Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
 END SET @Count = @Count + 1
END RETURN @IntNumbers
END
GO

Display relationship tables in the SQL Database

Query for Display relationship tables in the SQL Database

SELECT f.name AS ForeignKey,SCHEMA_NAME(f.SCHEMA_ID) SchemaName,OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnNameFROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Store Procedure for Beginners

Database is a important thing in the Software Development. I think, without database, we can't do the Software .So it is a Primary one. So every Program should know the Database Concept, Structures and Usages.
Store Procedure (SP):
The following Points are the Store Procedures Definition and usages:
1. Group of T-SQL statements compiled into a single execution plan.
2. Store and Retrieve information from Database.
3. T-SQL task has executed within the Application
4. Increase Performance
5. Storing a code inside in the SQL -Server
Create a Store Procedure
1. Select Query

Create Procedure SP_userlogin As Select username, password from tbl_Login

Run this Query.
2. Insert Query

Create Procedure SP_Insertuserlogin @userid varchar (20), @username varchar (50), @password varchar (50) As Insert into tbl_ Login (userid, username, password) values (@userid, @username,@ password)

3. Update Query

Create Procedure SP_Updateuserlogin @userid varchar (20), @username varchar (50), @password varchar (50) As Update tbl_Login set username=@username, password=@password where userid=@userid

4. Delete Query

Create Procedure SP_Deleteuserlogin @userid varchar (20), As Delete from tbl_Login where userid=@userid

Search the Store Procedure in the SQL SERVER

SP_HELPTEXT SP_Name

For Ex:

SP_Helptext SP_userlogin

Select the Line and Press F5. You view the Store Procedure like this

Create Procedure SP_userlogin As Select username, password from tbl_Login

If you want to Modify in your store procedure:

Alter Procedure SP_userlogin As Select distinct username, password from tbl_Login

DataSet Vs Datareader

Dataset Vs DataReader

DataSet object	DataReader object
Read/Write access.	Read-only access.
Supports multiple tables from different databases.	Supports a single table based on a single SQL query of one database.
Disconnected mode.	Connected mode.
Bind to multiple controls .	Bind to a single control.
Forward and backward scanning of data .	Forward-only scanning of data.
Slower access to data .	Faster access to data.
Greater overhead to enable additional features.	Lightweight object with very little overhead.
Supported by Visual Studio .NET tools.	Must be manually coded.

ExecuteReader,ExcuteNonQuery and ExecuteScaler

ExecuteReader
Use for accessing data. It provides a forward-only, read-only, connected record set.

ExecuteNonQuery
Use for data manipulation, such as Insert, Update, Delete.

ExecuteScalar
Use for retriving 1 row 1 col. value., i.e. Single value. eg: for retriving aggregate function. It is faster than other ways of retriving a single value from DB.