Select, Insert, Update and Delete using SqlDataAdapter

We can do the select, insert, update and delete to dataset by using SqlDataAdapter. See the following code to do that:-

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;uid=sa;pwd=sql;");

SqlDataAdapter adapter = new SqlDataAdapter("select * from Customer", con);

DataSet ds = new DataSet();
//fill the dataset
adapter.Fill(ds, "emp");

//CREATE select command and build with adapter

SqlCommand cmdSelect = new SqlCommand("select * from Customer", con);
adapter.SelectCommand = cmdSelect;

//CREATE the insert command and build with adapter

SqlCommand cmdInsert = new SqlCommand("insert into emp (cusotmerName, location) values(@customerName, @location)", con);
cmdInsert.Parameters.AddWithValue("@customerName", "SENTHILNATHAN");
cmdInsert.Parameters.AddWithValue("@location", "CHENNAI");
adapter.InsertCommand = cmdInsert;

//Create update command and build with adapter

SqlCommand cmdUpdate = new SqlCommand("update emp set cusotmerName=@customerName, location=@location where CustomerNo=@CustomerNo", con);
cmdUpdate.Parameters.AddWithValue("@customerName", "SENTHILNATHAN");
cmdUpdate.Parameters.AddWithValue("@location", "CHENNAI");
cmdUpdate.Parameters.AddWithValue("@CustomerNo", "100");
adapter.UpdateCommand = cmdUpdate;

//build delete command and build with adapter

SqlCommand cmdDelete = new SqlCommand("delete from emp where CustomerNo=@CustomerNo", con);
cmdDelete.Parameters.AddWithValue("@CustomerNo", 104);
adapter.DeleteCommand = cmdDelete;

//now update the data adapter with dataset.

adapter.Update(ds, "emp");




-----------------------------------------------------------------------------

SqlBulkCopy

SqlBulkCopy used to store the data from one data source to another data source efficiently. See the following article, it will explain SqlBulkCopy with neat manner.

Click here to see the article

What is Design Patterns?

Design pattern is a description or template for how to solve a problem.
A design pattern is a general repeatable solution to a commonly occurring problem in software design.
A design pattern isn't a finished design that can be transformed directly into code.


http://sourcemaking.com

Cross-Thread operation in c#

example for cross thread operation

GAC

Global Assembly Cache

How to install an assembly in the Global Assembly Cache

How to install an assembly in the Global Assembly Cache

SCOPE_IDENTITY() SQL SERVER

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.