Reading and writing images from/to database


Hi,
This post will show you how to save/load images to/from a database. This approach lets you do it without having to save the image on to disk. On a real hosting computer, you probably will not have write permissions. Therefore, it is sometimes vital to do image saving/loading on the fly.

In this post we will work on a database named Personnel, in which there is a table called Personnel:

create table Personnel
(
id int identity(1,1) not null primary key,
Fillname varchar(100) not null,
Picture image null
)

We are going to store out personnel’s name and picture into this table. On the application side, create a .aspx page with the following four controls:
1-a TextBox
2-a RequiredFieldValidator
3-a FileUpload control
4-a Button
5-a DataList control

Your page will look like this:

As the above image indicates, we will save each person’s full name and picture to database and the personnel information will be viewd in a DetailList control.

Behind the AddRecord button, we read the image into a Byte[] object. Then transmit the bytes to database. I strongly recommend you to use a stored procedure (if you are working with ADO.NET) because you won’t face any problems with converting bytes to string.

In Asp.NET there is a class claeed HttpPostedFile. This class lets us get full information about the file that is about to upload.  The code bellow show how we read the file:

if (FileUpload1.HasFile)
{
HttpPostedFile myFile = FileUpload1.PostedFile;
int Length = myFile.ContentLength;
string ContentType = myFile.ContentType.ToUpper();

if (Length == 0)
throw new Exception(“File size must be greater than zero!”);
if (ContentType.CompareTo(“IMAGE/PJPEG”) != 0 && ContentType.CompareTo(“IMAGE/JPEG”) != 0)
throw new Exception(“Only JPEG files are welcome!”);

Byte[] myFileBytes = new byte[Length];
myFile.InputStream.Read(myFileBytes, 0, Length);

In the above code I just allow JPEG files to be uploaded. Actually, I wanted to show you how to control the image type and infact, you can upload any file type.

As the last line of this code shows, HttpPostedFile class contains an inner stream from which we can read the image bytes. After reading the image file, we simply save it into db. I have created a stored procedure for doing this and I call this procedure from my C# code:

ALTER PROCEDURE dbo.SavePersonnel
@FullName Varchar(100),
@Picture Image

AS
Insert into Personnel (FullName,Picture) Values (@FullName,@Picture )
Return

C#:

string cnStr = ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “dbo.SavePersonnel”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“FullName”, txtFullName.Text.Trim());
cmd.Parameters.AddWithValue(“Picture”, myFileBytes);
connection.Open();
cmd.ExecuteNonQuery();
}

Well, now how to read the image and show it using a System.Web.UI.WebControls.Image control. Unfortunately, despite System.Drawing.Image class, ASP.NET Image class does not provide a public stream. Therefore we can not create the image content withouth an image url.

Thanks to ASP.NET Generic Handlers, we can overcome this problem easily. We may develop a .aspx or .ashx (generic handler) file and use it to read the file from database and write the image bytes into the response output stream. A generic handler (.ashx) file is much more light weight than a .aspx file. Therefore, I will develop a .ashx file. We will pass the ID of each person to this .ashx file. The generic handler then finds the person at DB, reads the image and writes it to the current HttpContext instance :

Here is the full source code of the .ashx file:

<%@ WebHandler Language=”C#” Class=”GetPersonnelImage” %>

using System;
using System.Web;
using System.Data.SqlClient;

public class GetPersonnelImage : IHttpHandler {

public void ProcessRequest (HttpContext context) {
context.Response.ContentType = “image/jpeg”;
if (context.Request.QueryString[“id”] == null || context.Request.QueryString[“id”] == “”)
return;
else
{
string cnStr = System.Configuration.ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “Select Picture from dbo.Personnel Where Id=” + context.Request.QueryString[“id”];
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
byte[] image = reader.GetValue(0) as byte[];
System.IO.MemoryStream ms = new System.IO.MemoryStream(image);
ms.WriteTo(context.Response.OutputStream);
ms.Flush();
context.Response.OutputStream.Flush();
}
}
}
}

public bool IsReusable {
get {
return true;
}
}
}

As is clear, we read the person’s record with a SqlDataReader and then get the image bytes using it’s GetValue method.

To let a Image control show the image we have to set it’s ImageUrl’s property in ths way:

Image img = new Image();

img.ImageUrl = “~/GetPersonnelImage.ashx?id=10″;

Inside a GridView or DataList, we can create a Template field with an Image control and bind the Image control’s ImageUrl property to the mentioned generic handler. For example:

<asp:Image ID=”Image1″ runat=”server” ImageUrl='<%#Eval(“id”,”GetPersonnelImage.ashx?id={0}”)%>’

You can download a full sample program from here. To run the project, you must have Sql Server 2005 Express edition on your machine. If you don’t have SQL Server 2005 Express, change the connection string existing in Web.config file to your own database.

Querying a database using Linq and Reflection


Hi,

With Insert, Update and Delete methods but without a Select method, LinqDataSource control looks just like a chair with only three legs! Such a powerful and flexible control is used to get connected to a DataBound control like GridView, while it could excel in many scenarios if it had a Select method! For example, it was great if we could write such a code:

LinqDataSource1.TableName=’students’;

LinqDatasource1.Where = ‘Grade<5’;

var x= LinqDataSource1.Select();

Anyway, in order to develop a special component, that I am gonna describe it in a separate post later, I needed to query a Table using Linq to Sql and the table’s string name. In ADO.NET territory, we may create a dynamic string query and execute it over the database:

const string TableName = ‘students’;

string Query = String.Format(‘Select * from {0} ‘, TableName);

But, how to query the Student table with Linq? Better to say, how does LinqDataSource probably work? The question is fairly easy, but before explaining it, I invite you to be informed that Linq has come to let us query in a strongly typed fashion! Thus, only in rare situations you might need to query a database in an untyped manner.

Anyway, first we have to get to know which Assembly is already running. In a Console or Windows application we may simply find this by calling Assembly.GetExecutingAssembly() method. But, in a Web application, things are different. In an ASP.NET application classes are compiled into App_config.dll assembly. ASP.NET uses shadow copy mechanism to let an ASP.NET application get updated while it is executing. In fact, ASP.NET copies every .DLL file in the bin directory to ” Temporary ASP.NET Files “ folder and name them with strange , long and unreadable (!) names. For example App_Code.Dll file might be named “App_Code.7vkubplh.dll” ! Therefore, we have to search all loaded assemblies for the required type.

As a result, we need the following code to get all assemblies loaded inside the current Application Domain:

List<Assembly> asmList =  AppDomain.CurrentDomain.GetAssemblies().ToList();

Since DataContext class provides required methods, we actually don’t need to find concrete DataContext classes. But if for any reason you need to find them, the best way is to find for classes that have been decorated with DatabaseAttribute attribute:

var AllDataContext = from C in asmList

from P in C.GetTypes()

from J in P.GetCustomAttributes(true)

where J.GetType().Name == “DatabaseAttribute”

select P;

Well, In order to query a table, you have to Add a DataContext to your application and drag your desired table on to  it. Luckily, since each Linq To Sql entity is a class, we can not have two different tables with the same name. Therefore,  the only job to do is to query a type with your table name:

Type table = (from C in asmList

from P in C.GetTypes()

where P.Name == “user”

select P).Single();

This code looks up all loaded assemblies and checks to see which one is named “user”. “user” is actually the table name. Since we are sure only one “user” class exists, we have used Single extension method.

Well, now that we have found the class, we should create an instance of DataContext class and call it’s non-generic GetTable method:

DataContext dc = new DataContext(” a connection string comes here”);

List<object> Records = dc.GetTable(table).Cast<object>().ToList();

The above code converts the resulting table to List<object> but it is not necessary indeed.

Well, the records are ready. You can assign “Records” variable to a GridView to see the results :

GridView1.DataSource = Records;

GridView1.DataBind();

If you would like to get the field values individually, you should obtain all public property names, loop over them and call GetValue on each one:

Table tbl = new Table();

// Loop over records

foreach (var s in dc.GetTable(table))

{

TableRow row = new TableRow();

// Retrieve all instance (non-static) and public properties.

PropertyInfo[] pList = s.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

// Loop over properties

foreach (PropertyInfo p in pList)

{

TableCell cell = new TableCell();

object temp = p.GetValue(s, null);

cell.Text = temp != null ? p.GetValue(s, null).ToString() : “-“;

row.Cells.Add(cell);

}

tbl.Rows.Add(row);

}

this.Controls.Add(tbl);

Well, in the above code I have converted all the values to String but be reminded that this method won’t work for Boolean fields.

Hope this article be a bit of help 🙂

Aspguy

Speed up Linq to Sql with compiled queries


There is no doubt that tehre are some drawbacks to Linq to Sql. One of them is that the Sql statement is built dynamically so it is needed to be parsed and compiled each time you run it. Fortunately .Net 3.5 has a solution for this problem. System.Data.Linq namespace includes a class named CompiledQuery which is responsible for caching the compiled version of a Linq to Sql query. This class has a static method called Compile which takes a Func<T,S,R> delegate. In this signature, T is the type of a DataContext (i.e. HRMDataContext) , S is the type of a predicate to filter the query and R is the type of returned result. Needless to say that it must be IQueryable<T>.

In this article we will see how to pre-compile a query, its limitations and how it really improves the speed of a Linq query.

To pre-compile a query we must define a public static field of type Func<T,S,R> . What we assign to this field is the result of CompiledQuery.Compile method:

public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult …

In the above line, testDataContex is the type of a DataContext inside the project, SearchCriteria is type of a class or struct that is designed for passing search criteria to .Compile method. For example, suppose that in testDataContext, we have a Table named Person. We have also defined a class (or struct) named SearchCriteria as bellow:

public class SearchCriteria
{
public int id { set; get; }
public string FirstName { set; get; }
public string LastName { set; get; }
}

Now to get these definitions to work with a precompiled query we can write such a statement:

public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);

That’s it. At this point, FilteredResult contains a pre-compiled query and can be used this way:

testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
List<Person> p = FilteredResult(dc, criteria).ToList();

The above code creates instances of testDataContext (dc) and SearchCriteria (criteria) and passes them to FilteredResult as arguments. The result of FilteredResult is IQueryable<Person> we have called .ToList() extension method to get a List<Person> series.

One upsetting point about pre-compiled queries is that you can not use a stored-procedure to make a compiled query. In the above Linq to Sql code, if you write “from C in usp_GetPerson() …” you will get an error indicating that stored procedures are not allowed to be used.

Now let’s see how much precompilation can be helpful. I have written a small Console application that runs two version (one is compiled and one is not) of a query over a database for 1000 times. The time needed to run each query is as follows:

Compiled query takes 0 minutes, 1 seconds and 62 milliseconds.

Regular query takes 0 minutes, 13 seconds and 328 milliseconds.

As it is clear, the compiled query is greatly faster than a regular query. Notice that in a Linq model, nothing will really happen unless we iterate over the result of the query. Therefore, I have written a foreach statement to iterate over the result of queries. I also have written a small query at the beginning of the program to make Linq manager open a connection to Sql Server. If we do not do this, the compiled query will surprisingly takes longer!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace CompiledQuery
{
class Program
{
public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);

static void Main(string[] args)
{
testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
IQueryable<Person> Q = null;

// The following code makes Linq manager to open a connection to Sql Server
var init = from p in dc.Persons select p;
foreach (Person person in init) ;

criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
DateTime BeginTime = DateTime.Now;

for (int i = 0; i < 1000; i++)
{
Q = FilteredResult(dc, criteria);
foreach (Person person in Q) ;
}

DateTime EndTime = DateTime.Now;
TimeSpan Diff1 = EndTime – BeginTime;

BeginTime = DateTime.Now;

for (int i = 0; i < 1000; i++)
{
Q = from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p;
foreach (Person person in Q) ;
}

EndTime = DateTime.Now;
TimeSpan Diff2 = EndTime – BeginTime;

Console.WriteLine(“Compiled query takes : {0}:{1}:{2}”, Diff1.Minutes, Diff1.Seconds, Diff1.Milliseconds);
Console.WriteLine(“Regular query takes {0}:{1}:{2}”, Diff2.Minutes, Diff2.Seconds, Diff2.Milliseconds);

Console.ReadKey();
}
}
}

You can download the source of of a full sample project from here.

Implementing a 3-Tier architecture with C# – Part 3



The Fill method we have developed so far, executes the Select command on the underlaying server and picks up RowCount records from startRowIndex position. For example, if Select * from Person returnS 20 rows, and startRowIndex is 0, only the first RowCount records will be fetched. This means that the complete result set will be generated on server and will be fetched into an IDataReader, but ADO.NET transmits only RowCount number of records into the DataTable. If the results are too many, that will not be helpful that much, so we have to do something about it.

For doing this, we may define the Fill method virtual and each DAL class may override it to fetch records based on startRowIndex and RowCount from the data base. The 2nd way is to assume that every SelectCommand has two paramerers names startRowIndex and RowCount! This means that we always must supply two int parameters to Fill method. I propose you to have several overloaded Fill methods to use a proper one in different occasions.

Anyway, if we suppose that every SelectCommand executes a stored procedure having two int parameters, we might change the Fill method (or develop an overloaded one) this way:

public void Fill(DataTable table, int startRowIndex, int RowCount)
{
DbParameter p;
innerAdapter.SelectCommand.Parameters.Clear();

p = ProviderFactory.CreateParameter();
p.ParameterName = “StartIndex”;
p.Value = startRowIndex;
innerAdapter.SelectCommand.Parameters.Add(p);

p = ProviderFactory.CreateParameter();
p.ParameterName = “RowCount”;
p.Value = RowCount;
innerAdapter.SelectCommand.Parameters.Add(p);
if (table.Rows.Count > 0)
table.Rows.Clear();
innerAdapter.Fill(table);
}

This Fill method adds two parameters to SelectCommand property of the innerAdapter, fills the DataTable by calling the innerAdapter.Fill method. Notice that we must clear up the DataTable because the innerAdapter.Fill method will append new records to the DataTable if any records exists in it.

But how to implement the paging? The answer might differ fro different data bases. However, I will provide a simple method for SQL Server 2005. SQL Server 2005 provides Ranking funtions, i.e. Row_Number() funtion, so that we may easily rank the resulting records of each table. For more information about Row_number function, please check out the SQL Server 2005 Books Online or MSDN.

Anyway, for our sample database, I have written a stored procedure named FetchAllPerson which has two int parameters, called @StartIndex and @RowCount:

create procedure FetchAllPerson
@StartIndex int,
@RowCount int
As

with NoFilterRecords
as
(
Select Row_Number() over (order by Serial) as RIndex , Serial, FirstName, LastName from Person
)
Select * from NoFilterRecords where RIndex>=@StartIndex and RIndex <@StartIndex+@RowCount
order by 1
Go

This procedure first ranks all records, then fetch only those records positioned in the specific range. Therefore, the innerAdapter.SelectCommand.CommandType must be set to CommandType .StoredProcedure .

innerAdapter.SelectCommand.CommandText = “FetchAllPerson”;
innerAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

Working with identity fields

The value of Identity fields is generated automatically. Therefore, a new row in a DataTable, may not have a value for its Identity fields or their value may not be identical to the mapping record in database after the record is inserted. Thus, we have to reflect the new value of Identity fields to the related row inside the DataTable. For doing this, add a Select statement to the end of Insert statements and seperate them with a semi colon :

innerAdapter.InsertCommand.CommandText = “Insert into Person (Serial, FirstName, LastName) \n” +
“Values (@Serial, @FirstName, @LastName); \n” +
“Select * from Person where Serial = SCOPE_IDENTITY()”;

The Select statement will fetch the new record from database and reflect any changes to the inserted row. To make this method work, we must set the UpdatedRowSource property to FirstReturnedRecord:

innerAdapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.FirstReturnedRecord;

This may be done in the base DAL class. This setting will fetch one record and will synchronize the inserted row with the data base. The SCOPE_IDENTITY() funtion returns the last identity value assigned to a field. This works in SQL SERVER only and you must perform a similar task for other data bases.

Ok this is all about the Data Access Layer. We will discuss about the business layer on the next post.