Sukumar Raju's Blog

This article explains ‘how to store and retrieve BLOB (Binary Large Objects) type data into BLOB data type column in database table.

BLOB is collection of ‘binary data’ stored as a single entity in database. BLOBs are session objects or images or audio files or multi media objects.

Note that when session object is stored into BLOB type column, you need to Serialize the session object and insert into database as shown in below source code. Serialization is the process of converting an object or data structure into sequence of bytes.

Retrieve BLOB type data

Note that in this example session object retrieval from BLOB type column is explained.

Below is the source code to read BLOB type data from database column. Note that ORACLE is the database management system in this example.

// <summary>Reading blob Type from Database example</summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings
[Your ConnString key].ConnectionString;

//Connection object
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

//Command 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM YourTable";

//Error Handler
try
{
//OracleDataReader object
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
//Get your blob type column which is Index 4 
OracleBlob blob = dr.GetOracleBlob(4);
//Test 
Response.Write(blob.Value);
}

catch( Exception ex )
{
throw new Exception("error Reading data from blob Types Table", ex);
}

//Close connection
conn.Dispose();
}

Insert into BLOB type column

Session object should be serialized before inserting into BLOB type column. In the source code below how to Serialize a session object and then inserting  into BLOB type column is shown.

// <summary>
//on button click event to insert values 
//into BLOB Type column </summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void button2_Click(object sender, EventArgs e)
{
//Connection string
string oradb = Read from web.config
//Connection 
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

// Start a transaction
OracleTransaction txn = conn.BeginTransaction();
//create Command object 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

// Lock the result set using the "FOR UPDATE" clause
// Running this SQL Statement in SQL editor locks the row, 
causing code Step out

cmd.CommandText = "SELECT object FROM your Table 
WHERE <condition> FOR UPDATE ";

//Error Handler
try
{
//Reader
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
		
//Read BLOB from DataReader
OracleBlob blob = dr.GetOracleBlob(0);

//byte array
byte[] bytes;

	
//get the byte array
MemoryStream memStream1 = new MemoryStream();
BinaryFormatter formatter = new BinaryFormatter();

//Serializing the object
formatter.Serialize(memStream1, Your object);
bytes = memStream1.GetBuffer();
memStream1.Close();
//write blob object to Database 
blob.Write(bytes, 0, bytes.Length);

//Commit data and close connection
txn.Commit();
Response.Write("updated to new data");
}

catch( Exception ex )
{
throw new Exception("error writing session data to Databse", ex);
}
conn.Close();
}

NB:- To improve performance it is highly advisable to use ODP.NET (Oracle Data Provider for .NET)

//ODP.NET namespce
using Oracle.DataAccess.Types;

References

Reading blob Type from Database example
http://msdn2.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx

Transaction Lock with Blobs example http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/rlock/index.html

Serialization Example:
http://progtutorials.tripod.com/C_Sharp.htm#_Toc65227888

In my article Install ASP.NET Application Services database step by step guide is provided to install ASP.NET Application Services database regard less of SQL Server version. Installing / configuring Application services database allows multiple applications to utilise the database to implement application authentication and authorization.

After installation of services database, it is required to configure ASP.NET application to utilise the ASP.NET Services database(called as Membership database as well).

I come across multiple number of repeating postings at www.asp.net/forums with exceptions when post holders try to utilise ASP.NET application services database to authenticate or authorise end users. Common exceptions noted are ‘sql exceptions’ or ‘Invalid end user credentials when tried to use Login control’ or similar exception.

The reason behind most of these exceptions are not configuring web application to utilise SQL Server installed ASP.NET Application Services database. When responding to those repeating postings on asp.net forums i decided to write an article explaining this process instead of repeating the same info multiple times.

This article explains step by step process of configuring ASP.NET Web application to utilise SQL Server installed ASP.NET Application Services database.

1. Copying and configuring connection String
2. Copying and configuring Membership, Roles and Profile sections
3. Using ASP.NET Web application Configuration tool to choose providers

Hope you find it helpful.

Step 1

Copying and configuring connection String

Note that configuration settings in Web.config file are inherited from machine.config file on your machine. In order to configure web application to utilise Application Services database it is required to copy related sections from machine.config file, which is located at

C:\windows\Microsoft.NET\Framework\v2.0.50727\CONFIG

NB:- Make sure that you are not making any changes to your machine.config file.

To Do:- Copy connection string from machine.config file as shown below.

<connectionStrings>
	<add name="LocalSqlServer" 
	connectionString="data source=.\SQLEXPRESS;
	Integrated Security=SSPI;
	AttachDBFilename=|DataDirectory|aspnetdb.mdf;
	User Instance=true" 
	providerName="System.Data.SqlClient"/>
</connectionStrings>

To Do:- Paste above connection string from machine.config into Web.config and 

change required properties as shown below.

<add name="<Connection String Name>" 
connectionString="Server=<SQL Server NAME>;
Database=<ASP.NET Application services Database NAME>;
User ID=<user ID>;
Password=<password>"
providerName="System.Data.SqlClient" 
/>

Step 2

Copying and configuring Membership, Roles and Profile sections

Note that depending on your application requirements you need either or combination 

or all of these three sections.

1. Membership
2. Roles
3. Profile

To Do:- Copy Membership, Roles and Profile sections from machine.config into Web.config and configure required properties.

<membership>
	<providers>

	<add name="AspNetSqlMembershipProvider" 

        type="System.Web.Security.SqlMembershipProvider,
	System.Web, Version=2.0.0.0, Culture=neutral, 
	PublicKeyToken=b03f5f7f11d50a3a" 
	connectionStringName="LocalSqlServer" 
	enablePasswordRetrieval="false" 
	enablePasswordReset="true" 
	requiresQuestionAndAnswer="true" 
	applicationName="/" 
	requiresUniqueEmail="false" 
	passwordFormat="Hashed" 
	maxInvalidPasswordAttempts="5" 
	minRequiredPasswordLength="7" 
	minRequiredNonalphanumericCharacters="1" 
	passwordAttemptWindow="10" 
	passwordStrengthRegularExpression=""/>
	
	</providers>
</membership>

<profile>
	<providers>
	
	<add name="AspNetSqlProfileProvider" 
	connectionStringName="LocalSqlServer" 
	applicationName="/" 
	type="System.Web.Profile.SqlProfileProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral, 		

        PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</profile>

<roleManager>
	<providers>

	<add name="AspNetSqlRoleProvider" 
	connectionStringName="LocalSqlServer" 
	applicationName="/" 
	type="System.Web.Security.SqlRoleProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

	PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</roleManager>

To Do:- After copying above sections into Web.config, make sure you modify 

minimum required attributes such as name, connectionStringName and

ApplicationName

• Note that depending on your application requirements you may modify other attributes mostly in Membership section.

After modifying minimum attributes in Membership, Roles and Profile sections, these sections looks similar as shown below.

<membership defaultProvider="AspNetMembershipProvider">
	<providers>
	<add connectionStringName="<AspNetServices 
	Connectionstring Name from connectionstrings
	section" 
	enablePasswordRetrieval="false" 
	enablePasswordReset="true" 
	requiresQuestionAndAnswer="true" 
	applicationName="<WEB APP NAME>" 
	requiresUniqueEmail="false" 
	passwordFormat="Clear" 
	maxInvalidPasswordAttempts="5" 
	minRequiredPasswordLength="7" 
	minRequiredNonalphanumericCharacters="0" 
	passwordAttemptWindow="10" 
	passwordStrengthRegularExpression="" 
	name="AspNetMembershipProvider"

        type="System.Web.Security.SqlMembershipProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	</providers>
</membership>

<roleManager enabled="true" defaultProvider="AspNetRoleProvider">
	<providers>
	<add connectionStringName="<AspNetServices 
	Connectionstring Name from connectionstrings
	section" 
	applicationName="<ASP.NET application NAME>" 
	name="AspNetRoleProvider" 
	type="System.Web.Security.SqlRoleProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	</providers>
</roleManager>

<profile>
	<providers>
	
	<add name="<ProfileProvider Name>" 
	connectionStringName="<From above connectionStirngs
	section that is pointing to Servies database>" 
	applicationName="/<Your ASP.NET app Name>" 
	type="System.Web.Profile.SqlProfileProvider, 
	System.Web, Version=2.0.0.0, Culture=neutral,

        PublicKeyToken=b03f5f7f11d50a3a"/>
	
	</providers>
</profile>

NB:- Make sure that application Name property is set at all the time as

suggested by Scott Guthrie here

Step 3

Using ASP.NET Web application Configuration tool to choose providers

After adding and modifying required sections as explained above, save your Web.config and configure your web application to utilise ASP.NET Application Services Database as explained below.

To Do:- Start ASP.NET Configuration tool from Website menu (shown below). Note that ASP.NET configuration tool can be initiated from Solution Explorer menu as well. 

Selecting ASP.NET Configuration opens Web Site Administration Tool in browser.

To Do:-Select Provider Configuration hyperlink as shown below.

Selecting Provider Configuration navigates to Provider page where you can choose either Single provider or different provider for each feature as shown below.

• To Do:- You can choose either of the above options available. For this tutorial choosing ‘Select a different provider each feature’ option. By doing so each providercan choose different data sources.

Selecting Select a different provider for each feature (advanced) hyperlink navigates to next page where you can select a provider for each feature as shown below.

On this screen you can see provider name(from provider section), choose a provider

for each feature i.e., Membership, Roles and Profile and select Ok.

Thats it. You are ready to fly! Configuration is done.

• Testing:- In order to make sure your ASP.NET application services database is configured to be used by web application, you can create a new user by selecting Security tab with in Web Site Administration tool, then Create User hyperlink. After creating the user make sure that you see the same information in ASP.NET Application Services database aspnet_Users

References

• http://www.asp.net/learn/security-videos/video-388.aspx
• http://www.asp.net/(S(sf10gzjodvrpce55el2p5cnk))/learn/hosting/tutorial-09-cs.aspx