.Net Framework, Asp.net, Ado.net, .Net Remoting, .Net Webservice, SQL, XML, XSLT, WCF, WPF, WWF NHibernate, Ajax, Jquery, DHTML

Whenever I think of fine tuning my dataaccess layer, i think of all possible way of doing following staff, now new ado.net 2.0 has really come up with most of our expection to help us to design a fine tuned dataaccess layer.

SQL Cache Dependency

To make this work, you first have to configure the SQL Server instance using the aspnet_regsql.exe command line tool. Then create an instance of the SqlCacheDependency class and add that into the Cache object when adding your data to the Cache

aspnet_regsql.exe -S localhost -U sa -P password -d myDatabase -lt

<system.web>
      <caching>
         <sqlCacheDependency enabled="true">
            <databases>
               <add name="myDatabase" connectionStringName="MyConnectionString1"
                pollTime="100" />
            </databases>
         </sqlCacheDependency>
      </caching>
</system.web>

New features in DataSet and Datatable classes

DataSet Serialization

Conversion of DataReader to a DataSet or a DataTable — ADO.NET 2.0 allows loading a DataReader object into a DataSet or a DataTable,both the classes in ADO.NET 2.0 has the "Load" method that can be used to load a DataReader into a DataSet or a DataTable.

Batch Updates — Reduction in database roundtrips

Asynchronous Data Access

Data Paging

Bulk Copy
Bulk copying of data from one data source to another data source is a new feature added to ADO.NET 2.0. Bulk copy classes provides the fastest way to transfer set of data from once source to the other.

ADO.Net 2.0 Bulk Copy sample

using System.Data ;

1:using System.Data.SqlClient ;

3:namespace Arindam

4:{

6: class BullkCopySample

7: {

9: void CopyMethod()

10: {

11: // Create source connection

12: SqlConnection sourceServer = new SqlConnection("connectionString");

13:

14: // Create destination connection

15: SqlConnection destinationServer = new SqlConnection("connectionString");

16:

17: // Clean up destination table. Your destination database must have the

18: // table in which you are copying data to.

19:

20: SqlCommand cmd = new SqlCommand("delete FROM MyTable1", destinationServer);

21:

22:

23: // Open both connections.

24: sourceServer.Open();

25: destinationServer.Open();

26:

27: cmd.ExecuteNonQuery();

28: // Select data from Products table

29: cmd = new SqlCommand("select * FROM MyTable1", sourceServer);

30:

31: // read the data

32: SqlDataReader reader = cmd.ExecuteReader();

33:

34: // Create SqlBulkCopy

35: SqlBulkCopy bulkData = new SqlBulkCopy(destinationServer);

36:

37: // Set destination table name

38: bulkData.DestinationTableName = "MyDataTable";

39:

40: // Write data to destination

41: bulkData.WriteToServer(reader);

42:

43: // Close objects

44: bulkData.Close();

45:

46:

47: destinationServer.Close();

48: sourceServer.Close();

49: }

50: }

51:}

52:

Batch Update
When you use SqlDataAdapter for performing updates, the SqlDataAdapter propagates the updates one by one. That means if there are 100 rows to be updated the SqlDataAdapter will execute 100 separate operations against the database. As you might have guessed this is not efficient while dealing with large number of rows. Fortunately SqlDataAdapter allows you to execute updates in batches. You can specify the batch size i.e. number of rows to be treated as a single batch via UpdateBatchSize property.

ADO.Net 2.0 Batch Update sample

// Get the DataTable with Rows State as RowState.Added

1: DataTable dtInsertRows = new DataTable(); // GetDataTable();

3: SqlConnection connection = new SqlConnection("connectionString");

4: SqlCommand command = new SqlCommand("sp_BatchInsert", connection);

5: command.CommandType = CommandType.StoredProcedure;

6: command.UpdatedRowSource = UpdateRowSource.None;

8: // Set the Parameter with appropriate Source Column Name

9: command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);

10: command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);

11: SqlDataAdapter adpt = new SqlDataAdapter();

12: adpt.InsertCommand = command;

13:

14: // Specify the number of records to be Inserted/Updated in one go. Default is 1.

15: adpt.UpdateBatchSize = 20;

16: connection.Open();

17: int recordsInserted = adpt.Update(dtInsertRows);

18: connection.Close();

19:

Ado.net	Asp.Net 2.0	SQL	C-Sharp	XML	Framework	IIS	XMLWebService	Architecture
UML	Project Management	SDLC	Methodologies	Design Patteren	OOP	WCF	.Net Remoting	WWF
MVP	MVC	Silver Light	N-Hibernate	Ajax	J-Query	SEO-Marketing	Site Map	Php