Save and display images to and from SQL Database C#

Save images on the Database
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.

<asp:FileUpload ID="FileUpload1" runat="server"/>

<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>

write this code inside the button click event:

protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);


// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);

SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";

}
}

Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">

and add a handler class with the below code:

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

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context)
{

try
{
SqlConnection con = new SqlConnection(GetConnectionString());

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;

SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}

catch(Exception ex)
{
ex.Message.ToString();
}
}

public bool IsReusable {
get {
return false;
}
}

}

Mobile Device detection in ASP.NET and use CSS to display

There are basically two ways to detect mobile devices:
-in terms of display (480px/800px)
-in terms of user agent.

My code used to detect using the display but last week Android updated their software (changed the display from 480px to 800px) so all the mobile tweaks I made for our apps are no longer work on Droid Phones.

That's why I want to follow the second method to detect the mobile devices and in future no matter whether they channged the display setting or not.

Let's identify the User Agent(e.g. Android) and switch style sheets depending. (I don't want to redirect to another page, just switch style sheets).

In aspx.cs page:

protected override void OnInit(EventArgs e)
{
string userAgent = Request.UserAgent;
if (userAgent.Contains("BlackBerry")
(userAgent.Contains("iPhone") (userAgent.Contains("Android"))))
{
//add css ref to header from code behind
HtmlLink css = new HtmlLink();
css.Href = ResolveClientUrl("~/mobile.css");
css.Attributes["rel"] = "stylesheet";
css.Attributes["type"] = "text/css";
css.Attributes["media"] = "all";
Page.Header.Controls.Add(css);
}
}
Mobile.cs:

#header {
display: none;
}
#body {
margin: 0;
width: 100%;
min-width: 100%;
font-size: 12px;
background-image: none;
empty-cells: hide;

}
#column_left {
float: none;
width: 100%;
padding-left: 5px;
}
#column_right {
float: none;
width: 100%;
padding-left: 5px;
}
#menuwrapper {
display: none;
}
#sssubsearch {
display: none;
}
h2 {
font-size: 16px;
text-align: center;
}
h3, h4 {
font-size: 1em;
/* border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #666;*/
}
h3 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #E6E6E6;*/
font-size: 16px;
padding-left: 0;
margin: 0;
padding-top: 10px;
padding-bottom: 0;

}
h4 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #FFC;*/
font-size: 16px;
color: #600;
padding-left: 0;
margin-left: 0;
empty-cells: hide;
}
table {
width: 98%;
font-size: 14px;
empty-cells: hide;
}
#navigation {
display: none;
}
.left {
float: left;
vertical-align:top;
}
.right {
float: right;
vertical-align:top;
}

hr {display: none;}

Happy Coding !!

Multiple SQL rows merge into single row if the ID is same

Let's create with a table example:

create table mytable (id int identity(1,1), PersonID int, unit varchar(10))
insert into mytable values (1,'Che YYYY')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy XXXX')

--Replace space in your column with a special character and remove it in your select statement
UPDATE mytable
SET unit=REPLACE(unit,' ','')

SELECT PersonID, REPLACE(Units,'', ' ') as Units
FROM (SELECT t1.PersonID,
Units =REPLACE( (SELECT Unit AS [data()]
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
ORDER BY Unit
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY PersonID)
t0 ;
drop table mytable

Merge SQL tables

Let say, I have two tables:
table1:(Fields:PersonID, FirstName, LastName, Role, Department)
table2:(fields: PersonID, Unit).

and I want to create a new table with these fields:
table: ( Fields:PersonID, FirstName, LastName, Role, Department, Unit).

here is the query:

SELECT table1.*, table2.Unit
INTO new_table_name
FROM table1 inner join table2
on table1.PersonID = table2.PersonID

If table1 and table2 has one-to-many relation then first use this postto make it one-to-one relaion; otherwise it'll create multiple PersonID into new table, which you don't want.