What's the difference between accessing data with dataset or data reader?

The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer.
The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application.
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.

Read More

What does it mean by connected data access architecture of ADO.Net?

In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.

Read More

What does it mean by disconnected data access architecture of ADO.Net?

ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
Traditional Data Access Architecture

ADO.Net Disconnected Data Access Architecture

Another important aspect of the disconnected architecture is that it maintains the local repository of data in the dataset object. The dataset object stores the tables, their relationship and different constraints. The user performs operations like update, insert, delete to this dataset locally and finally the changed dataset is stored in actual database as a batch when needed. This greatly reduces the network traffic and results in the better performance.

Read More

Difference between value type and reference type ?

Many programming languages provide built-in data types such as integers and floating-point numbers. These are copied when they are passed in to arguments i.e. they are passed "By Value". In .NET terms, these are called Value Types".
The RunTime supports two kinds of Value Types:
1 Built-in value types
The .NET Framework defines built-in value types such as System.Int32 and System.Boolean which correspond and are identical to primitive data types used in programming languages.
2 User-defined value types
The language you are using will provide functionality to define your own Value Types. These user defined Types derive from System.ValueType. If you want to define a Type representing a value that is a complex number (two floating-point numbers), you might choose to define it as a value type. Why? Because you can pass the Value Type efficiently "By Value". If the Type you are defining could be more efficiently passed "By Reference", you should define it as a class instead. Variables of Reference Types are referred to as objects. These store references to the actual data.
The following are the Reference Types:
• class
• interface
• delegate
This following are the "built-in" Reference Types:
• object
• string

Read More

What is serialization in .NET and what are the ways to control serialization?

Serialization is the process of converting an object into a stream of bytes. On the other hand Deserialization is the process of creating an object from a stream of bytes. Serialization/Deserialization is used to transport or to persist objects. Serialization can be defined as the process of storing the state of an object to a storage medium. During this process, the public and private fields of the object and the name of the class, including the assembly are converted to a stream of bytes. Which is then written to a data stream. Upon the object's subsequent deserialized, an exact clone of the original object is created.
Binary serialization preserves Type fidelity, which is useful for preserving the state of an object between different invocations of an application. For example: An object can be shared between different applications by serializing it to the clipboard.
You can serialize an object to a stream, disk, memory, over a network, and so forth. Remoting uses serialization to pass objects "By Value" from one computer or application domain to another. XML serialization serializes only public properties and fields and does not preserve Type fidelity. This is useful when you want to provide or consume data without restricting the application that uses the data.
As XML is an open standard, it is an attractive choice for sharing data across the Web. SOAP is also an open standard, which makes it an attractive choice too. There are two separate mechanisms provided by the .NET class library - XmlSerializer and SoapFormatter/BinaryFormatter. Microsoft uses XmlSerializer for Web Services, and uses SoapFormatter/BinaryFormatter for remoting. Both are available for use in your own code.

Read More

To store a database from a BAK file only( SQL Server)

RESTORE DATABASE scriptposting
FROM DISK = 'G:\Games\newone\SQL back up\scriptposting_180910.BAK'
WITH REPLACE

Read More

ADD-INS For Microsoft - Excel using .Net

Step:1 :- CREATE THE C# PROJECT(Class lib Application)
Step:2 :- Set the project properties Register for COM Interop to TRUE


#region Using Directives //Using Interop services
using System;
using System.Runtime.InteropServices;

namespace NewAddInFunctionality
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class AddINFunctions
{
public AddINFunctions()
{
}
Add Method
public double Add2(double v1, double v2)
{
return v1 + v2;
}
Multiply Method
public double Multiply(double v1, double v2)
{
return v1 * v2;
}
Divide Method
public double Divide(double v1, double v2)
{
return v1 * v2;
}
Mod Method
public double MOD(double v1, double v2)
{
return v1 % v2;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type funType)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + funType.GUID.ToString().ToUpper() +
"}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type funType)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + funType.GUID.ToString().ToUpper() +
"}\\Programmable");
}
}
}

Step :3 Build the Project Get the DLL
Step :4 GO To Excel and Open a WorkBook
Step : ADDINS select methods and type ADD(3 + 7) then result will be 10

Read More

Send Email using Gmail in ASP.Net

protected void btnSendEmail_Click(object sender, EventArgs e)
{
MailMessage mail = new MailMessage();
mail.To.Add("saurabhjnumca@gmail.com");
mail.To.Add("saurabh_singh_jnu06@yahoo.co.in");
mail.From = new MailAddress("sandeepjnumca@gmail.com");
mail.Subject = "HI this is a test mail for learners using .Net";

string Body = "Hi Friends, this mail is a test mail"+
"sending this mail through Gmail in ASP.NET";
mail.Body = Body;

mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Credentials = new System.Net.NetworkCredential ("sandeepjnumca@gmail.com","GmailPassword");
smtp.EnableSsl = true;
smtp.Send(mail);
}

Read More

SQL :To get comma seperated value from a column

DECLARE @stringCommaSeperated VARCHAR(1000)

--Make comma seperated values from a column
SET @stringCommaSeperated = SUBSTRING((SELECT ', ' + s.ColumnName FROM Table_Name s FOR XML PATH('')),2,200000)

SELECT @stringCommaSeperated

Read More

SQL Tools(DROP , TRUNCATE)

DROP TABLE :-
DROP TABLE table_name

DROP DATABASE :-
DROP DATABASE database_name

TRUNCATE TABLE :-
We use truncate statement if we want to delete the values of the table not the table itself. Like :-

TRUNCATE TABLE table_name

DROP INDEX :-
DROP INDEX table_name.index_no

Read More

SQL Tools(Check , DEFAULT, CREATE INDEX)

Check :-

CREATE TABLE SHIP_Orders
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25),
@ORDER_ADDRESS VARCHAR(45),
CHECK(@O_ID>2)
)

DEFAULT :-

We can provide default values to the table like in the above table :-

CREATE TABLE SHIP_ORDERS
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25) DEFAULT 'SLITTING',
@ORDER_ADDRESS VARCHAR(45) DEFAULT 'DELHI AIRPORT',
CHECK(@O_ID>2)
)

TO ALTER DEFAULT VALUE :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME SET DEFAULT 'SPLIT'

TO DROP DEFAULT VALUES :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME DROP DEFAULT

CREATE INDEX :-

CREATE INDEX index_name
ON table_name (column_name)

LIKE :-
CREATE INDEX index
ON SHIP_ORDERS (@ORDER_NAME,@ORDER_ADDRESS)

Read More

SQL Tools(Create DB, Create Table, UNION, UNION ALL, NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key)

To Create Database :-
CREATE DATABASE Db_Name

To Create Table :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

if a column contains NOT NULL values then it means it does not allow the null values.

Unique :-

CREATE TABLE Person
(
@ID INT NOT NULL UNIQUE,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

Another way to represent is and the way to define primary key :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR,
UNIQUE(@ID),
PRIMARY KEY(@ID)
)

A primary key value can not have null values.

If more than one column has unique value then below way we will handle :-
UNIQUE(@ID, @ADDRESS)

FOREIGN KEY :-

If a table does not have primary key then it points to a primary key to another table then that key calls a foreign key for that table like :-

CREATE TABLE Person_Values
(
@P_ID INT NOT NULL,
@ID INT NOT NULL,
@P_Name VARCHAR(20),
@P_ADDRESS VARCHAR(255) NOT NULL,
@P_PS_NO SMALLINT,
@P_FIRST_CHAR CHAR,
FOREIGN KEY (@Id) REFERENCES Persons(@Id)
)

@ID is a primary key of Persons table and now working as a foreign key for Person_Values table.

UNION AND UNION ALL :-
Union will select the distinct values from both columns. In more general form Union with distinct values
Union All select all the values from selected columns.

UNION :-

SELECT column_name From table1
UNION
SELECT column_name From table2

UNION ALL :-

SELECT column_name From table1
UNION ALL
SELECT column_name From table2

Read More

SQL Tools( Count, MAX, MIN)

COUNT is a method which will count the total no of columns,total no of rows,total no of values.Like :-

SELECT COUNT(Column_Name) FROM Table_Name
This will count total no of rows where First_Name is Saurabh.
SELECT Count(*) FROM Table_Name WHERE First_Name = 'Saurabh'

SELECT COUNT(First_Name) AS NoOfStudents FROM STUDENTS
STUDENTS is a Table_Name

Max :-
SELECT MAX(Column1) From Table1
SELECT MAX(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth > 12202009

Min :-
SELECT MIN(Column1) From Table1
SELECT MIN(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth < 12202009

Read More

SQL Tools(SELECT INTO, INSERT INTO)

SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
The general SQL SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3,
INTO Table2 FROM Table1

This will create a Table2 same as Table1.

SQL INSERT INTO :-

1:- INSERT INTO Table1 VALUES (value1, value2, value3…)
2:- INSERT INTO Table1 (column1,column2,column3) VALUES(value1,value2,value3)

Read More

SQL Tools(JOIN Operator(Inner join, Left join, Right join))

INNER JOIN Operator :-
INNER Join return all rows from the join of left table and right table if there are same data available in both tables if no data will match then it will return null
SELECT * FROM Table_Name_1 INNER JOIN Table_Name_2 WHERE Table_Name_1.Column_Name = Table_Name_2.Column_Name

You can divide your result in group like :-
If a table contains First_Name, Last_Name, Address, Ph_No and there are so many result according to First_Name then you can use Group_By Like this :-

SELECT * FROM Old_Persons As OP INNER JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

LEFT JOIN Operator :-
LEFT join return all rows from the left table even there are no rows in right table.

SELECT * FROM Old_Persons As OP LEFT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

RIGHT JOIN Operator :-
RIGHT JOIN return all rows from the right table even if there are no rows available in left table.

SELECT * FROM Old_Persons As OP RIGHT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

Read More

SQL Tools(Alias Operator & In Operator)

ALIAS Name for Column :-
SELECT Column_Name AS Alias_Name FROM Table_Name

ALIAS Name for table :-
SELECT Column_Name From Table_Name AS Alias_Name

ALIAS Example :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons AS p, Product AS po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

Without using AS
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

IN Operator :-
If you want to select OrderID of between 2,3,4,5 then :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID IN (2,3,4,5)

OR :-
SELECT * FROM Persons
WHERE FirstName IN ('Saurabh','Saanjh', 'vivek')

Read More

SQL - Tools (BETWEEN Operator)

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2

SELECT * FROM COILS WHERE COIL_NAME BETWEEN SomeValue1 AND SomeValue2

Between operator is like It will select a row where COIL_NAME has SomeValue1 . Value depends on database to database . Some database will select rows where COIL_NAME has SomeValue1 and where COIL_NAME has SomeValue2.

We can use NOT BETWEEN operator if you you dont want to select those values like :-
SELECT * FROM COILS WHERE COIL_NAME NOT BETWEEN SomeValue1 AND SomeValue2
This query will select a value where coil_name is not equal to SomeValue1 and SomeValue2.

Read More

SQL - Tools(Top Clause AND Like Operator)

SELECT TOP 1 FROM Table_Name

This will select 1 row from table

SELECT TOP 1* FROM Table_Name (Select all columns of 1st row)
SELECT TOP 2* FROM Table_Name (Select first two rows of table)
SELECT TOP 50 PERCENT * FROM Table_Name (Select 50 % rows from table)

LIKE OPERATOR

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

IF a column name CITY from Persons table contains Kanpur, Delhi , NewDelhi , Kannauj , Nagar values then If you want to select city name starts from K then :-

SELECT * FROM Persons
WHERE CITY LIKE 'k%'

this will retrieve Kanpur , Kannauj.

If you want to select city name ends from i then :-
SELECT * FROM Persons
WHERE CITY LIKE '%i'


IF you want to select a city which has particular format like Select a city which has 'elh' pattern
SELECT * FROM Persons
WHERE City LIKE '%elh%'

this will retrieve Delhi , NewDelhi.

If you dont want to select a city which has particular format like 'elh'

SELECT * FROM Persons
WHERE City NOT LIKE '%elh%'

Read More

SQL - Tools ( INSERT,UPDATE and DELETE Statement)

INSERT INTO table_name
VALUES (value1, value2, value3,...)

IF a table Persons contains P_Id,LastName,FirstName,Address,City columns.If you want to insert values then :-
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')

IF NOT EXISTS (SELECT * FROM Persons WHERE P_Id = 4)
BEGIN
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')
END

By this way it will check firstly, if this p_id exist in the table then do not insert otherwise insert these values in the table.

Update statement :-
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

We use If not exist statement whether there is a need to update or not.

DELETE Statement :-
DELETE From table_name WHERE some_column=some_value
Note :- Never use Delete * use Delete tableName.*

Read More

SQL - Tools(ORDER BY)

ORDER BY keyword is used for sorting suppose in a table there is a column named first_name.
If you want to show the result in ascending order then :-

SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name ASC

If you want to show the result in descending order then :-
SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name DESC

By default it is ascendeng . if we use like GROUP BY first_name then it sorts in ascending order.

Read More