|
|
Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET |
|
Sometimes, your data is not limited to strings and numbers. You need to store a large amount of data in a SQL server table. Documents, raw files, XML documents and photos are some examples. SQL sever provides special data types for such large volumes of data. In this article, I will discuss how to read and write Binary Large Objects (BLOBs) using SQL Server 2005 and ADO.NET. |
|
Posted On : 04 Aug 2008 |
|
|
Tip: Fetching Random Rows From SQL Server |
|
Recently I needed to fetch random rows from a SQL server table. If you have an integer column then using RAND() function goes well. However in my case there was no number column. |
|
Posted On : 23 Jul 2008 |
|
|
Working with the XML Data Type of SQL Server |
|
With the growing use of XML data, the need for the coexistence of relational data and XML data is also growing. The classic approach of storing XML data as physical disk files is unsuitable and tedious in many situations. No wonder modern database engines are geared to store XML documents right along with the rest of the relational data. To that end, the XML data type introduced in SQL Server 2005 is a great addition to the database engine. Prior to SQL Server 2005, developers often used VARCHAR or TEXT column types to store XML documents and fragments. Although this approach served well as far as data storage is concerned, it proved to be poor in terms of querying and manipulating the XML data. This article will give you a jump start in using the XML data type of SQL server and will teach you how XML data can be manipulated with the help of new XML Data Modification Language (XML DML). |
|
Posted On : 25 Jun 2008 |
|
|
Working with ADO.NET Schema APIs |
|
In majority of data driven applications developers deal with SQL queries that select, insert, update or delete data from the database. However, at times you need to retrieve schema information from the database. Suppose you are building applications that performs data import and export between two or more databases. As a good solution you would want to retrieve table schema at runtime rather than hard coding it. Luckily, ADO.NET provides a set of classes that allow you to query database schema. In this article I will illustrate how these classes work. |
|
Posted On : 25 Feb 2008 |
|
|
ADO.NET Trace Logging |
|
Tracing allows you to keep track of various events happening in the system. Tracing allows you to track the sequence of code execution along with the time taken for each step. Together, tracing can throw light on how your code is being executed and the performance of every step. Many real world applications are database driven and call for performance optimization of your data access code. Luckily, ADO.NET 2.0 provides built-in support for trace logging. The ADO.NET trace logging is tuned for performance and uses Event Tracing for Windows (ETW) providers. This article explains how you can configure the ADO.NET trace logging feature. |
|
Posted On : 05 Feb 2008 |
|
|
Distributed Transactions in .NET 2.0 |
|
Transactions are commonly used to update data as a batch. If any one operation from the batch is failed the entire batch of operations must be cancelled. Thus either all operations in the batch succeed or all of them are cancelled. ADO.NET 1.x provided SqlTransaction class that in conjunction with SqlConnection class provided to commit or rollback transactions. In .NET 2.0 an additional technique can be used and that is what is the topic of this article. |
|
Posted On : 08 Jul 2007 |
|
|
Copying bulk data into SQL Server |
|
Few weeks ago I (Bipin Joshi) released BinaryIntellect Bulk Copy Tool. Many visitors asked me how the tool is developed and hence I decided to write this article. Though the tool uses many other aspects of ADO.NET 2.0 the core thing is the new bulk copy class. Some applications require inserting huge data to SQL server databases. In such case executing independent INSERT queries is not an efficient way. SQL Server data provider allows you to perform such inserts using SqlBulkCopy class. This article explains with an example how this class can be used in your applications. |
|
Posted On : 26 Dec 2005 |
|
|
Improve performance using ADO.NET 2.0 batch update feature |
|
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. |
|
Posted On : 19 Dec 2005 |
|
|
BinaryIntellect Bulk Copy Tool |
|
Many times you need to import data from different databases into your SQL Server database. If you do not have SQL Server Enterprise manager or similar tools then it becomes very tedious to import such data. This is especially true when you are working with SQL Server Express Edition or MSDE.
BinaryIntellect Bulk Copy Tool allows you to import data from SQL Server, Access, Oracle and ODBC databases into your SQL Server database. Developed using new ADO.NET 2.0 features the tool is very easy to use and FREE.
|
|
Posted On : 11 Dec 2005 |
|
|
Developing generic data access layer using ADO.NET 2.0 |
|
Let's accept the real world fact that you need to write applications targeting not only SQL Server but also many other databases including MS-Access, Oracle and other ODBC databases. Classic ADO was a generic object model. You used to use the same Connection class with SQL Server, Access and Oracle. In ADO.NET, however, you need to use different classes depending on the provider you are using. That means before starting the coding phase you need to be aware of target database. If you develop an application using SQL Server data provider and later on decide to use Oracle you must change your code. At first thought using OLEDB data provider or ODBC data provider might come into your mind. However, considering the performance penalty that is not always a good idea. Won't it be nice to have a generic data access layer which will allow you to write provider independent code? That's exactly what this article will talk about. |
|
Posted On : 04 Dec 2005 |
|
|