About Data set and Data Reader
Introduction ADO.NET offers two data classes for query results: the DataReader and the DataSet.
Determining which is best for your application involves careful analysis. Consider these factors before making a decision. If you use the SqlDataReader, note the following:
DataReaders all implement the same IDataReader interface, which means that all of the DataReaders should provide the same set of functionality. Each DataReader implementation is optimized for a specific data provider such as SQL Server or Oracle. Version 1.0 of the Microsoft .NET Framework includes a System.Data.SqlClient.SqlDataReader that is specific to SQL Server and a System.Data.OleDb.OleDbDataReader that is more generic for OLEDB providers. Version 1.1 of the .NET Framework introduced the SqlCeDataReader for connecting to the SQL Server edition for Windows CE, OdbcDataReader for ODBC data sources, and OracleDataReader for connecting to Oracle databases. It isn''t required that you use the DataReaders from Microsoft, and each database vendor often has its own .NET provider. In addition, you can implement a DataReader of your own through the IDataReader interface.
The C# sample code in Listing 1 demonstrates how to use a DataReader to retrieve data from the Northwind sample database included with Microsoft SQL Server. A connection is set up, the command object is created to retrieve the data, the connection is opened, and the data is retrieved and printed to the console window. The connection will automatically be closed when the DataReader is closed by using the appropriate CommandBehavior options on the Command object, which is a good habit to follow to ensure connections are not accidentally left open. ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below ExecuteNonQuery - Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null. ExecuteXmlReader - Executes the SQL query against SQL Server only,while returning an XmlReader object. Prepare - Equivalent to ADO's Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once.
Ex. Command.Prepare();
Dispose - Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object's Close method as well.
How do I know which one to use?
You can use the following information to help you decide which data retrieval is right for your purposes.
When to consider using a DataReader:
Retrieving Data, that we can display or process the data in some other way, we have two basic choices. We can use a DataSet object or a DataReader object.
The DataReader approach is generally quicker because it avoids the overhead that is associated with creating a DataSet object. The overhead that is associated with a DataSet object includes creating DataSet subobjects such as DataTables, DataRows, and DataColumns. However, the DataReader provides less flexibility, and is less suited to situations where you have to cache data and pass the data to components in an application that has multiple tiers or we can say if data modification is required, DataReader is not the choice. So DataReader is best choice when we have to display data.
Note The DataAdapter used to fill the DataSet uses a DataReader internally. Use a DataReader when the following conditions are true:
Now Question arises what made me to explain DataReader Again when it is simple object of ADO.NET as other. I agree it is .But fact is that when it comes to using DataReader or returning DataReader, it is bit tricky, where most of the developers get caught. I have seen most of the developers have raised Issue’s regarding returning DataReader from class doesn’t work. Let me make it simple and explain it in simple steps.
Example of Login Control where we need to fetch username, password to compare it with existing record. I shall look into the same concept, but with any data type and without using RETURN anymore. Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns some value. Using “Query Analyzer,” execute the following script
[Note: Don’t forget to create Table [UserLogin_Tbl] within database]
Important Point: Use of Datareader is worth when we just need to Retrieve and display the data.
The above stored procedure simply uses a SELECT statement to retrieve User_ID and User_Password from UserLogin_Tbl based on the “USERID” and “USERPASSWORD” we send through the parameter “@User_ID & @User_Password.” The stored procedure is named “proc_ValidateUser.” Now, we need to go to ADO.NET to access the same in ASP.NET.
Now let me take a simple example where we will retrieve some fields of the table by using datareader Object.
The DataReadThe DataReader provides the fastest but least flexible way to retrieve data from the database. Data is returned as a read-only, forward-only stream of data that is returned one record at a time. If you need to retrieve many records rapidly, using a DataReader would require less memory than a DataSet, which would need to create a large table to hold the results. The following code example, which requires the User table (see below) , shows how to execute a simple query on SQL database and read the results using a DataReader:
By default, the DataReader loads incoming data as a row as soon as an entire row of data is available. Binary large objects (BLOBs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload that will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received.
This is ideal for loading BLOBs or other large data structures. Note that this behavior may depend on your data source. For example, returning a BLOB from Microsoft Access will load the entire BLOB being loaded into memory, rather than sequentially as it is received. When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values are no longer available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data is not available after the DataReader has read past it. When accessing the data in the BLOB field, use the GetBytes or GetChars typed accessors of the DataReader, which fill an array with data.
You can also use GetString for character data; however. to conserve system resources you might not want to load an entire BLOB value into a single string variable. You can instead specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB.
You can optionally specify an index in the array as a starting position for the data being read.
Introduction ADO.NET offers two data classes for query results: the DataReader and the DataSet.
Determining which is best for your application involves careful analysis. Consider these factors before making a decision. If you use the SqlDataReader, note the following:
- The underlying connection to the database remains open and cannot be used for any other purpose while the data reader is active. Call Close on the SqlDataReader as soon as possible.
- There can be only one data reader per connection.
- You can close the connection explicitly when you finish with the data reader, or tie the lifetime of the connection to the SqlDataReader object, by passing the CommandBehavior.CloseConnection enumerated value to the ExecuteReader method. This indicates that the connection should be closed when the SqlDataReader is closed.
- When accessing data by using the reader, use the typed accessor methods (such as GetInt32 and GetString) if you know the column's underlying data type because they reduce the amount of type conversion required when you read column data.
- To avoid unnecessary data being pulled from server to client, if you want to close the reader and discard any remaining results, call the command object's Cancel method before calling Close on the reader. Cancel ensures that the results are discarded on the server and are not pulled unnecessarily to the client. Conversely, calling Close on the data reader causes the reader to unnecessarily pull the remaining results to empty the data stream.
- If you want to obtain output or return values returned from a stored procedure and you are using the ExecuteReader method of the SqlCommand object, you must call the Close method on the reader before the output and return values are available.
What is a DataReader?
DataReaders are different–they provide forward-only, read-only, connected access to data sources, and they don't support data manipulation. So why would you want to use something that limits you so much? Performance, for starters: DataReaders are much faster. Another benefit is a smaller memory footprint–DataReaders allow you to work with data as you get it, a row at a time. So DataReaders are particularly well-suited for working with data that's too large to fit in memory comfortably. A DataReader is a read-only stream of data returned from the database as the query executes. It only contains one row of data in memory at a time and is restricted to navigating forward only in the results one record at a time. The DataReader does support access to multiple result sets, but only one at a time and in the order retrieved. Just as in the original version of ADO, the data is no longer available through the DataReader once the connection to the data source is closed, which means a DataReader requires a connection to the database throughout its usage. Output parameters or return values are only available through the DataReader once the connection is closed.DataReaders all implement the same IDataReader interface, which means that all of the DataReaders should provide the same set of functionality. Each DataReader implementation is optimized for a specific data provider such as SQL Server or Oracle. Version 1.0 of the Microsoft .NET Framework includes a System.Data.SqlClient.SqlDataReader that is specific to SQL Server and a System.Data.OleDb.OleDbDataReader that is more generic for OLEDB providers. Version 1.1 of the .NET Framework introduced the SqlCeDataReader for connecting to the SQL Server edition for Windows CE, OdbcDataReader for ODBC data sources, and OracleDataReader for connecting to Oracle databases. It isn''t required that you use the DataReaders from Microsoft, and each database vendor often has its own .NET provider. In addition, you can implement a DataReader of your own through the IDataReader interface.
Retrieving a Single Item
In this scenario, you want to retrieve a single item of data. For example, you might want to look up a single product name, given its ID, or a single customer credit rating, given the customer's name. In such scenarios, you will generally not want to incur the overhead of a DataSet or even a DataTable when retrieving a single item. You might also want simply to check whether a particular row exists in the database. For example, as a new user registers on a Web site, you need to check whether or not the chosen user name already exists. This is a special case of the single item lookup, but in this case, a simple Boolean return is sufficient.Comparing the Options
Consider the following options when you retrieve a single item of data from a data source:- Use the ExecuteScalar method of a SqlCommand object with a stored procedure.
- Use a stored procedure output or return parameter.
- Use a SqlDataReader object.
Sample DataReader C# code
The C# sample code in Listing 1 demonstrates how to use a DataReader to retrieve data from the Northwind sample database included with Microsoft SQL Server. A connection is set up, the command object is created to retrieve the data, the connection is opened, and the data is retrieved and printed to the console window. The connection will automatically be closed when the DataReader is closed by using the appropriate CommandBehavior options on the Command object, which is a good habit to follow to ensure connections are not accidentally left open. ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below ExecuteNonQuery - Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null. ExecuteXmlReader - Executes the SQL query against SQL Server only,while returning an XmlReader object. Prepare - Equivalent to ADO's Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once.
Ex. Command.Prepare();
Dispose - Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object's Close method as well.
c#
CSharp Source CodeSqlCommand objCmd = new SqlCommand(Sql , objConnect); SqlDataReader ObjDr =objCmd.ExecuteReader(); while (ObjDr.Read() == true) { Response.Write (objDataReader[0].ToString() + ""); } // closing Reader object: objDataReader.Close(); objDataReader = null; objCommand.Dispose(); objCommand = null; objConnect.Close(); objConnect= null;
Vb.net
Vb.net Source CodeDim objCmd As New SqlCommand(Sql, ObjConn) Dim ObjDr As Data.SqlClient.SqlDataReader = objCmd.ExecuteReader() while ObjDr.Read() = True Response.Write(ObjDr(0).ToString() & "") End while ' closing Reader object: ObjDr.Close() ObjDr = Nothing objCmd.Dispose() objCmd = Nothing ObjConn.Close() ObjConn = Nothing
When not to use Sql Data Reader ?
Don’t use Sql Data Reader when you don’t need to unecessarily keep a database connection open. It’s more efficient to use a disconnected data source model, meaning that you fetch all the data that is required at the beginning, and then close the connection. This is already implemented for you in SqlHelper.ExecuteDataSet().. If you are doing a big loop and outside the loop it has while (dr.read()) you will start to use up all your database connections pretty fast if your site gets moderately busy. In most cases you won’t need this, so just use ExecuteDataSet instead. If you do need to grab the data as you are going along and use it, for example when your data source is very large, or when you want to do asynchronous work on the data, then its a good idea to use SqlDataReader so you can process the data as it comes (rather than waiting until the entire data results are available) As well, DON’T FORGET, you need to explicitly close your Database connections, best to have a try { } catch { } and in the finally { } block you can put conn.Close(), otherwise if there is an exception you will have a connection that is going to stay open until .NET decides to dispose of itHow do I know which one to use?
You can use the following information to help you decide which data retrieval is right for your purposes.
When to consider using a DataReader:
- The DataReader is a better choice for applications that require optimised read-only and forward-only access to data such as binding to a DataGrid control. The sooner the data is off-loaded from the DataReader and the connection is closed the better the application performance.
- The DataReader is a better choice when you are planning to make repeated calls to retrieve small amounts of information or the data you are retrieving must be as up to date as possible each time it is used.
FieldName | Datatype |
User_ID | varchar (30) |
User_Password | varchar (30) |
Retrieving Data, that we can display or process the data in some other way, we have two basic choices. We can use a DataSet object or a DataReader object.
The DataReader approach is generally quicker because it avoids the overhead that is associated with creating a DataSet object. The overhead that is associated with a DataSet object includes creating DataSet subobjects such as DataTables, DataRows, and DataColumns. However, the DataReader provides less flexibility, and is less suited to situations where you have to cache data and pass the data to components in an application that has multiple tiers or we can say if data modification is required, DataReader is not the choice. So DataReader is best choice when we have to display data.
Note The DataAdapter used to fill the DataSet uses a DataReader internally. Use a DataReader when the following conditions are true:
Now Question arises what made me to explain DataReader Again when it is simple object of ADO.NET as other. I agree it is .But fact is that when it comes to using DataReader or returning DataReader, it is bit tricky, where most of the developers get caught. I have seen most of the developers have raised Issue’s regarding returning DataReader from class doesn’t work. Let me make it simple and explain it in simple steps.
Example of Login Control where we need to fetch username, password to compare it with existing record. I shall look into the same concept, but with any data type and without using RETURN anymore. Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns some value. Using “Query Analyzer,” execute the following script
[Note: Don’t forget to create Table [UserLogin_Tbl] within database]
Important Point: Use of Datareader is worth when we just need to Retrieve and display the data.
1. Create Stored procedure "proc_ValidateUser"
CREATEPROCEDURE proc_ValidateUser(@UserID int@User_Password varchar(50))AS SELECT User_ID, User_Password FROM UserLogin_Tbl WHERE User_ID = @ User_ID AND User_Password=@User_Password RETURN |
The above stored procedure simply uses a SELECT statement to retrieve User_ID and User_Password from UserLogin_Tbl based on the “USERID” and “USERPASSWORD” we send through the parameter “@User_ID & @User_Password.” The stored procedure is named “proc_ValidateUser.” Now, we need to go to ADO.NET to access the same in ASP.NET.
2. Create Class "ClsLogin"
c#
CSharp Source Codeusing System; using System.Data; using System.Data.SqlClient; using System.Web; //Author:Shabir //Dated: 23/01/2009 public class ClsLogin { //Your SQL Connection string which is already in the webconfig private string DBConstr = ConfigurationManager.ConnectionStrings["FAITHConnStr"].ConnectionString; public string homepageURL= "HomePage.aspx"; public string LoginpageURL= "Login.aspx"; public ClsLogin() { } public string getConnectionString { get { return DBConstr; } } public boolLogin(string DBConstr,string Userid, string password) { try { SqlConnection objConn= new SqlConnection(getConnectionString); SqlCommand objcmd=new SqlCommand("proc_ValidateUser", objConn); objcmd.CommandType=CommandType.StoredProcedure; /// open connection objConn.Open(); objcmd.Parameters.AddWithValue("@User_ID",UserName); objcmd.Parameters.AddWithValue("@User_Password", password); SqlDataReader ObjDr= objcmd.ExecuteReader(); while (ObjDr.Read()) { if (ObjDr[0].ToString() == Userid && ObjDr[1].ToString() == password) { HttpContext.Current.Response.Redirect(homepageURL); } } // Close the reader and the connection. ObjDr.Close(); return true; } catch (Exception ex) { //if exception was raised then redirect to loginpage where he has to relogin HttpContext.Current.Response.Redirect("Login.aspx?error="+ex.Message); } finally { objConn.Close();} } }
vb.net
vb.net Source CodeImports System Imports System.Data Imports System.Data.SqlClient Imports System.Web 'Author:Shabir 'Dated: 23/01/2009 Public Class ClsLogin 'Your SQL Connection string which is already in the webconfig Private DBConstr As String = ConfigurationManager.ConnectionStrings("FAITHConnStr").ConnectionString Public homepageURL As String = "HomePage.aspx" Public LoginpageURL As String = "Login.aspx" Public Sub New() End Sub Public ReadOnly Property getConnectionString() As String Get Return DBConstr End Get End Property Public Function Login(ByVal DBConstr As String, ByVal Userid As String, ByVal password As String) As Boolean Try Dim objConn As New SqlConnection(getConnectionString) Dim objcmd As New SqlCommand("proc_ValidateUser", objConn) objcmd.CommandType = CommandType.StoredProcedure ' open connection. objConn.Open() objcmd.Parameters.AddWithValue("@User_ID", UserName) objcmd.Parameters.AddWithValue("@User_Password", password) Dim ObjDr As SqlDataReader = objcmd.ExecuteReader() While ObjDr.Read() If ObjDr(0).ToString() = Userid AndAlso ObjDr(1).ToString() = password Then HttpContext.Current.Response.Redirect(homepageURL) End If End While ' Close the reader and the connection. ObjDr.Close() Return True Catch ex As Exception 'if exception was raised then redirect to loginpage where he has to relogin HttpContext.Current.Response.Redirect("Login.aspx?error=" & ex.Message) Finally objConn.Close() End Try End Function End Class
3 Call Login Method Login on Login Button Click
protected void btnlogin_Click(object sender,EventArgs e){ ClsLogin objlogin=new ClsLogin(); Objlogin.Login(StrConn, txtUsername.Text, txtPassword.Text);}
Protected sub btnlogin_Click(object sender,EventArgs e)Dim objlogin as new ClsLogin() Objlogin.Login(StrConn, txtUsername.Text, txtPassword.Text) End subImportant thing to notice is that while calling Login Method ,I am passing Class Defined Connection as parameter,if new Connection will be opened and tried to login,you will receive Error ExecuteReader requires an open and available Connection. The connection's current state is closed.. As shown in code , “ExecuteReader” is a method which returns a “DataReader” object to pull the information from the database (in this case it is stored procedure) continuously, in a forward only and read-only manner. In the next article, we shall work with DataReader in Deep, Next we will understand data tables, data sets, and so forth using stored procedures. If possible, we shall also look into stored functions and compare both.
Now let me take a simple example where we will retrieve some fields of the table by using datareader Object.
The DataReadThe DataReader provides the fastest but least flexible way to retrieve data from the database. Data is returned as a read-only, forward-only stream of data that is returned one record at a time. If you need to retrieve many records rapidly, using a DataReader would require less memory than a DataSet, which would need to create a large table to hold the results. The following code example, which requires the User table (see below) , shows how to execute a simple query on SQL database and read the results using a DataReader:
// Create a SQL command ace="" ns="urn:schemas-microsoft-com:office:office" prefix="o" ?> string strSQL = "SELECT UserID,User_FName,User_LName FROM User_Tbl"; SqlConnection objConn = new SqlConnection(getConnectionString); SqlCommand objcmd = new SqlCommand(strSQL , objConn); objcmd.CommandType =CommandType.Text; //open connection. objConn.Open(); SqlDataReader ObjDr; ObjDr = ObjCmd.ExecuteReader(); while (ObjDr.Read()) { Console.WriteLine(ObjDr["UserID"].ToString()); Console.WriteLine(ObjDr["User_FName"].ToString()); Console.WriteLine(ObjDr["User_LName"].ToString()); } ObjDr.Close(); // Close the connection objConn.Close();
Retrieving a Scalar Value
You can use the ExecuteScalar method of the Command object to return a single value, such as a sum or a count, from the database. The ExecuteScalar method returns the value of the first column of the first row of the result set. The following code example, which requires the User table (see below) ,shows how to retrieve the count of a specified group// Retrievethe number of Users who whose age is more than 24pan>// from the user table // Open connection
string strSQL =
"select count(sal) FROM User_Tbl where age >'24' ;
SqlConnection objConn = new SqlConnection(getConnectionString);
SqlCommand objcmd = new SqlCommand(strSQL, objConn);
objcmd.CommandType = CommandType.Text;
objConn.Open();
try
{
int count = (int)objcmd.ExecuteScalar();
}
catch (Exception
ex) { // Display any exceptions in a messagebox
MessageBox.Show (ex.Message);
} //
Close the connection
objConn.Close();
Retrieving Large Data (ADO.NET)
By default, the DataReader loads incoming data as a row as soon as an entire row of data is available. Binary large objects (BLOBs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload that will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received.
This is ideal for loading BLOBs or other large data structures. Note that this behavior may depend on your data source. For example, returning a BLOB from Microsoft Access will load the entire BLOB being loaded into memory, rather than sequentially as it is received. When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values are no longer available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data is not available after the DataReader has read past it. When accessing the data in the BLOB field, use the GetBytes or GetChars typed accessors of the DataReader, which fill an array with data.
You can also use GetString for character data; however. to conserve system resources you might not want to load an entire BLOB value into a single string variable. You can instead specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB.
You can optionally specify an index in the array as a starting position for the data being read.
Example
The following example returns the publisher ID and logo from the Users Table The User ID (userID) is a character field, and the logo is an image, which is a BLOB. Because the logo field is a bitmap, the example returns binary data using GetBytes. Notice that the publisher ID is accessed for the current row of data before the logo, because the fields must be accessed sequentially.// Assumes that connection is a valid SqlConnection object from above defined class. SqlCommand ObjCmd = new SqlCommand("SELECT User_id, User_Photo FROM User_Tbl",ObjConn); // Writes the BLOB to a file (*.bmp). FileStream stream; // Streams the BLOB to the FileStream object. BinaryWriter writer; // Size of the BLOB buffer. int bufferSize = 100; // The BLOB byte[] buffer to be filled by GetBytes. byte[] outByte = new byte[bufferSize]; // The bytes returned from GetBytes. long retval; // The starting position in the BLOB output. long startIndex = 0; // The User ID to use in the file name. string UserID= ""; // Open the connection and read data into the DataReader. ObjConn.Open(); SqlDataReader ObjRd = ObjCmd.ExecuteReader(CommandBehavior.SequentialAccess); while (ObjDr.Read()) { // Get the //publisher id, which must occur before getting the logo. UserID =ObjDr.GetString(0); // Create a file to hold the output. stream = new FileStream("Photo" + UserID +".bmp",FileMode.OpenOrCreate, FileAccess.Write); writer = new BinaryWriter(stream); startIndex = 0; retval = ObjDr.GetBytes(1, startIndex, outByte, 0, bufferSize); while (retval == bufferSize) { writer.Write(outByte); writer.Flush(); // Reposition start index to end of last buffer and fill buffer. startIndex += bufferSize; retval = reader.GetBytes(1,startIndex, outByte, 0, bufferSize); } // Write the remaining buffer. writer.Write(outByte, 0, (int)retval- 1); writer.Flush(); writer.Close(); stream.Close(); } ObjDr.Close(); ObjConn.Close();
' Assumes that connection is a valid SqlConnection object from above defined class. Dim ObjCmd As New SqlCommand("SELECT User_id, User_Photo FROM User_Tbl", ObjConn) ' Writes the BLOB to a file (*.bmp).FileStream stream; ' Streams the BLOB to the FileStream object.BinaryWriter writer; outByte = New Byte(bufferSize - 1) {} ' Open the connection and read data into the DataReader. ObjConn.Open() Dim ObjRd As SqlDataReader = ObjCmd.ExecuteReader(CommandBehavior.SequentialAccess) While ObjDr.Read() 'Get the USER id, which must occur before getting the logo. UserID = ObjDr.GetString(0) ' Create a file to hold the output. stream = New FileStream("Photo" & UserID & ".bmp", FileMode.OpenOrCreate, FileAccess.Write) writer = New BinaryWriter(stream) startIndex = 0 retval = ObjDr.GetBytes(1, startIndex, outByte, 0, bufferSize) While retval = bufferSize writer.Write(outByte) writer.Flush() ' Reposition start index to end of last buffer and fill buffer. startIndex += bufferSize; retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize) End While ' Write the remaining buffer. writer.Write(outByte, 0, (int)retval - 1); writer.Flush(); writer.Close(); stream.Close() End While ObjDr.Close() ObjConnObjConn.Close()The SqlDataReader class defines a lightweight yet powerful object that is used to read information from a SQL database. A SqlDataReader object has a small footprint, because it doesn't contain more than a single record in memory at any time. This makes it ideal for reading large amounts of data from a database.
|