AJAX offers users a seamless way to work with your interface, no waiting for whole pages to load. jQuery has a set of tools to make it super simple to implement. We will walk you through how to use jQuery to communicate databse using jquery’s ajax method in asp.net.
Each example will feature the same requirement, and that is to obtain and display the Customer details relating to a specific CustomerID selected from a DropDownList on a page called Customer.aspx.
In this article, I’ll explain how to store and retrieve Image in Database using C# ASP.Net jQuery. There is many other way to do that but here is the easiest way to do it.
Database:
For this tutorial, I am using following database.
CREATE TABLE [dbo].[tblImage]( [ID] [int] IDENTITY(1,1) NOT NULL, [image] [image] NULL, CONSTRAINT [PK_tblImage] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Connection string:
Below is the connection string to connect to the database.
<connectionStrings>
<add name="TempConnectionString" connectionString="Data Source=ASHISH;Initial Catalog=Temp;Persist Security Info=True;User ID=sa;Password=Temp1234567" providerName="System.Data.SqlClient"/>
</connectionStrings>
Page:
In this tutorial, there one aspx page and one handler.
1. UploadImage.aspx – To Store image into database
2. DisplayImage.aspx – To display image from handler
3. DisplayImage.ashx – To read image from database (Handler)
1. UploadImage.aspx
Below HTML Markup of the page, you will notice that I have placed FileUpload to upload image into database.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UploadImge.aspx.cs" Inherits="UploadImge" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Ashish Blog</title>
</head>
<body>
<form id="form1" runat="server">
<strong>Upload Image into database</strong><br />
<br />
Select
Image: <asp:FileUpload ID="FileUpload1" runat="server" /><br />
<br />
<asp:Button ID="butSubmit" runat="server" Text="Submit"
onclick="butSubmit_Click" /><br />
<asp:Label ID="lblStatus" runat="server"></asp:Label>
<br />
<br />
<a href="DisplayImage.aspx">Display Image</a></form>
</body>
</html>
Codebehind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
public partial class UploadImge : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void butSubmit_Click(object sender, EventArgs e)
{
try
{
Byte[] imgByte = null;
if (FileUpload1.HasFile && FileUpload1.PostedFile != null)
{
HttpPostedFile File = FileUpload1.PostedFile;
imgByte = new Byte[File.ContentLength];
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
string cs = ConfigurationManager.ConnectionStrings["TempConnectionString"].ConnectionString;
using( SqlConnection connection = new SqlConnection(cs))
{
connection.Open();
string sql = "INSERT INTO tblImage(image) VALUES(@theImage) SELECT @@IDENTITY";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddWithValue("@theImage", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblStatus.Text = String.Format("Image is Uploaded successfully!! and Image ID is {0}", id);
cmd.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch(Exception ex)
{
lblStatus.Text = "There was an error" + ex.Message ;
}
}
}
2. DisplayImage.aspx
Below HTML Markup of the page, you will notice that I have placed jQuery function to display Image from database. You may also notice that I created Img variable and call ReadImage.ashx handler by passing Image ID in scr attribute of Img then append this Img to div.display.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DisplayImage.aspx.cs" Inherits="DisplayImage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#Button1").click(function (e) {
$('.display').text(''); //clean div display
var ID = $("#txtID").val();
if (ID.length > 0) {
var newImage = $('<img />');
newImage.attr('src', 'ReadImage.ashx?id=' + ID); // call handler with id
$('.display').append(newImage);
}
e.preventDefault();
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
Display Image<br />
<br />
Id
<asp:TextBox ID="txtID" runat="server"></asp:TextBox>
&amp;nbsp;<asp:Button ID="Button1" runat="server" Text="Display" />
<br />
<br />
<div class="display"></div>
<br />
<br />
</form>
</body>
</html>
3. DisplayImage.ashx (Handler)
<%@ WebHandler Language="C#" Class="ReadImage" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
public class ReadImage : IHttpHandler
{
public void ProcessRequest (HttpContext context) {
Int32 theID;
if (context.Request.QueryString["id"] != null)
theID = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");
context.Response.ContentType = "image/jpeg";
Stream strm = DisplayImage(theID);
byte[] buffer = new byte[2048];
int byteSeq = strm.Read(buffer, 0, 2048);
while (byteSeq > 0)
{
context.Response.OutputStream.Write(buffer, 0, byteSeq);
byteSeq = strm.Read(buffer, 0, 2048);
}
}
public Stream DisplayImage(int theID)
{
string cs = ConfigurationManager.ConnectionStrings["TempConnectionString"].ConnectionString;
using( SqlConnection connection = new SqlConnection(cs))
{
string sql = "SELECT image FROM tblImage WHERE id = @ID";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@ID", theID);
connection.Open();
object theImg = cmd.ExecuteScalar();
try
{
return new MemoryStream((byte[])theImg);
}
catch
{
return null;
}
finally
{
cmd.Dispose();
connection.Close();
connection.Dispose();
}
}
}
}
public bool IsReusable {
get {
return false;
}
}
}
Thanks
Download
[wpdm_file id=5]
In this Article, I perform following steps to Import Excel 2007(.xlsx) Sheet into SQL Server 2008.
First: To Import Excel (.xls) to database you need to install Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from here.
Step 1: Select Destination Database where you would like to Import Excel 2007 Sheet.
Right click database > Tasks > Select Import data… as shown in following figure.
Step 2: Select “Microsoft Office 12.0 Access Database Engine OLE DB Provider” from data source selection drop down. Click on “Properties…” button as shown in figure.
Step 3: Properties dialog box appears.
Enter Excel 2007 file path in Data source text box. Then Click on “All Tab”.
Step 4: Double Click on “Extended Properties” appears Edit value dialog box.
Step 5: Enter “Excel 12.0” in Property value textbox and click OK button
Step 6: Press Test Connection button to make sure everything is working good.
Finally press OK button.
After that you need to follow usual procedure to import excel sheet data into sql server.
Thanks.
In This Article, I’ll show you How to solve Resolve the collation conflict in SQL Server.
For Example I have SQL Query:
Query:
SELECT *
FROM categories INNER JOIN
search ON categories.cid = search.cat_id
Above SQL Query Giving me a below error.
Error:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Why:
Because Category.cid column has different collate than search.cat_id. So, we cannot use = operation between them.
Here categories.cid hase collate SQL_Latin1_General_CP1_CI_AS
And search.cat_id hase collate Latin1_General_CI_AS
Solution:
1. Have to make both column with same Collate so we can compare.
1a. here we change collate of search.cat_id to collate SQL_Latin1_General_CP1_CI_AS from Latin1_General_CI_AS
SELECT search.* FROM categories INNER JOIN search ON categories.cid = search.cat_id collate SQL_Latin1_General_CP1_CI_AS
OR
1b. here we change collate of categories.cid to Latin1_General_CI_AS from collate SQL_Latin1_General_CP1_CI_AS
SELECT search.* FROM categories INNER JOIN search ON categories.cid collate Latin1_General_CI_AS = search.cat_id
2. Use COLLATE DATABASE_DEFAULT
SELECT search.* FROM categories INNER JOIN search ON categories.cid COLLATE DATABASE_DEFAULT = search.cat_id COLLATE DATABASE_DEFAULT
How to check Collate:
Go to SQL Server Object Explorer then go to your database table. Expand table & expand Column of table then right click on column which you want to check Collate.
Now Click on Property and you will see following image
In this article i am going to show you how to pass the variable value from codebehind to javascript. It is quite easy and handy.
I have declared a protected type string variable Variable_codebehind in asp.net codebehind, assign a value to that variable on page load .
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
public partial class _Default : System.Web.UI.Page
{
protected string Variable_codebehind;
protected void Page_Load(object sender, EventArgs e)
{
Variable_codebehind = "Ashish Blog";
}
}
Now, we going to diaplay this variable Variable_codebehind into html page using javascript.
Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Ashish's Blog</title>
<script type="text/javascript">
////Getting variable from asp.net code behind
alert("<%=Variable_codebehind %>");
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
Thanks.