Saturday, 10 September 2016

Chapter-6 Handling BLOB and CLOB in ADO.NET



Handling Binary Large Objects in a Database

1)      While working with data, you may need to move large objects (LOB) between the client application and the database server.
2)      LOBs are of different formats:
a)      Binary Large Object (BLOB): If a LOB is stored in a database in a binary format, it is referred to as BLOB.
b)      Character Large Object (CLOB): If a LOB is stored in the database in a textual format, it is referred to as CLOB.
3)      To access the DataReader object in a stream fashion, you can change the DbCommand object’s behavior to a sequential stream when you execute the ExecuteReader() method.
4)      In the stream mode, you must get the bytes from the stream in the order of each column that is being returned.
5)      Refer to following code snippet that retrieves photographs and stores them into a file:

//Declare the BLOB byte[] buffer, which will be filled by GetBytes.  bufferSize refers to the size of the BLOB buffer.
byte[] outbyte = new
byte[bufferSize];
//Open the connection and read data into the DataReader.
connection.Open();
SqlDataReader myReader =
command.ExecuteReader
(CommandBehavior.
SequentialAccess);
while (myReader.Read()){
string fileName = @"C:\demo" + ".bmp";
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
//Stream the BLOB to the FileStream object, fs.
bw is an object of BinaryWriter.
bw = new BinaryWriter(fs);
startIndex = 0;
//Read the bytes into outbyte[] and store the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
//Continue to read and write while bytes are remaining.
while (retval == bufferSize)
{
startIndex +=
                bufferSize; retval =
                myReader.GetBytes(1,
                startIndex, outbyte,
                0, bufferSize);
}           
bw.Write(outbyte, 0, (int)retval - 1);
bw.flush();

Storing BLOB Data
1)      You can write BLOB data to a database by executing INSERT or UPDATE statement.
2)      If the BLOB data is stored in a text format, you can pass the data as a string parameter.
3)      The following code snippet updates a photograph with a new one from the file:
//Retrieve a pointer to the photograph.
//The SQL Server TEXTPTR function is first called to get a pointer to the field of the record to be updated.
command.CommandText = "Select
TEXTPTR(StudentPhoto)
from Students where
StudentId='1004'";
photoPtr =
(byte[])command.ExecuteScalar();
//The SQL Server UPDATETEXT function writes the BLOB data in chunks of a specified size.
using (SqlCommand command = connection.CreateCommand())
command.CommandText = "UPDATETEXT Students.StudentPhoto @Pointer @Offset null @Data"; SqlParameter ptrParm = command.Parameters.Add ("@Pointer", SqlDbType.Binary, 16);
ptrParm.Value = photoPtr;
SqlParameter photoParm = command.Parameters.Add("@Data", SqlDbType.Image);
SqlParameter offsetParm = command.Parameters.Add("@Offset", SqlDbType.Int);
offsetParm.Value = 0;
//Read chunks of the file into the buffer and send the chunks to the database.
using (FileStream file = new FileStream("C:\\newPhoto.gif“, FileMode.Open, FileAccess.Read)){
int count = file.Read (buffer, 0, bufferSize);
while (count != 0){ photoParm.Value = buffer; photoParm.Size = count; command.ExecuteNonQuery(); currentIndex += count; offsetParm.Value = currentIndex;
count = file.Read(buffer, 0, bufferSize);
}
}

Performing Bulk Copy Operations 
1)      There can be occasions when you need to copy large amounts of data from one location to another.
2)      The SqlBulkCopy class allows you to write bulk in SQL Server based tables.
3)      The data is copied from the data source to the destination table, specified by the DestinationTableName property, by using the WritetoServer() method of the SqlBulkCopy class.
4)      Copies all rows from the DataRow array to a destination table
5)      Copies all rows in the DataTable to a destination table
6)      Copies all rows in the IDataReader interface to a destination table
Copies only rows that match the row state in the DataTable to a destination 
1)      A single bulk copy command is executed to perform a single operation against a database.
2)      To perform the bulk copy operation, you need to perform the following steps:
3)      Connect to the source server to get the data to be copied.
using (SqlConnection sourceConnection = new SqlConnection(connectionString)){            sourceConnection.Open();
SqlCommand commandSourceData = new SqlCommand("SELECT * FROM sourceTable;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();

4)      Connect to the destination server.
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{                         destinationConnection.Open();
}

5)      Create a SqlBulkCopy object.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy (destinationConnection))

6)      Set the DestinationTableName property to the name of the target table.
bulkCopy.DestinationTableName = "targetTable";

7)      Call the WriteToServer() method.
bulkCopy.WriteToServer(reader);

Executing Multiple Bulk Copy Operations
1.       Multiple bulk copy command is executed to perform multiple operations against a database.
2.       It can be done by using a single instance of a SqlBulkCopy class.
3.       Performing multiple bulk copy operations by using the same instance of SqlBulkCopy is usually more efficient than by using a separate instance for each operation.

SQL Notification
1.       Data caching improves the performance of applications because it prevents repeated roundtrips to the database server. In this way, database resources are conserved.
2.       However, ensuring that the cache maintains updated records is a challenging task. You need to know when the cache expires, that is, when the data has been modified in the database server.
3.       To provide this functionality, SQL notification, which is based on the Service Broker infrastructure, is used.
4.       To use query notifications, you need to:
5.       Enable Service Broker for the database.
ALTER DATABASE DATABASE_NAME
SET ENABLE_BROKER;
CREATE QUEUE ContactChangeMessages;
CREATE SERVICE ContactChangeNotifications ON QUEUE ContactChangeMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

Ensure that the user ID used to connect to the database has the necessary permissions.
USE DATABASE_NAME
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal
SqlClientPermission permission = new SqlClientPermission(PermissionState.Unrestricted);
try{                               
permission.Demand();
      return true;
   }

       Initialize the SqlDependency class using the Start() method. This method takes a connection string as a parameter.
SqlDependency.Start(connectionString);

Set the Notification property of the SqlCommand object. It is set by passing an object of SqlCommand to the constructor of SqlDependency object.
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
   cn.Open();
   dep = new SqlDependency(cmd);
   dep.OnChange += dep_OnChange;
}
  
   Declare the OnChange event on the SqlDependency object.

a)      While working with large objects (LOB), it is advisable to use streaming techniques. This conserves the system resources.
b)      If a LOB is stored in the database in a binary format, it is referred to as Binary Large Object (BLOB).
c)       The normal operation of the DataReader object is to read one row at a time. To access the DataReader object in a stream fashion, you can change the DbCommand object’s behavior to a sequential stream when you execute the ExecuteReader() method.
d)      You can use the SQL Server UPDATETEXT function to write the BLOB data in chunks of a specified size. The UPDATETEXT function requires a pointer to the BLOB field being updated, so the SQL Server TEXTPTR function is first called to get a pointer to the field of the record to be updated.
e)      Multiple bulk copy command is executed to perform multiple operations against a database.
f)       Multiple bulk copy operation can be done by using a single instance of a  SqlBulkCopy class (for SQL Server).
g)      The SqlBulkCopy class is used to copy large amounts of data to the SQL Server database tables.
h)      A single bulk copy command is executed to perform a single operation against a database.
i)        Query notifications are useful for applications that need to refresh displays or caches.
i)        To use query notifications, you need to:
(1)    Enable query notifications for your database.
(2)    Ensure that the user ID used to connect to the database has the necessary permissions.
ii)       To execute SQL notifications, you need to use the SqlDependency class.

I hope that the functionality of the above attribute is clear to you now. Now you can use this code in your application. If you have further question, just drop a line below and I will try to answer you as soon as possible.

Last but not least, connect with me on Twitter , Facebook , LinkedIn and Google+ for technical updates and articles news. We won’t spam or share your email address as we respect your privacy.





1 comment: