Calling database using jQuery AJAX and ASP.NET

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.

Continue reading

How to store and retrieve Image in Database using C# ASP.Net jQuery

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
Upload image
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 &amp;&amp; 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
display image
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;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]

Import Excel 2007(.xlsx) Sheet into SQL Server 2008 without Query

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.

here is my another article Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server Using SQL Query

Thanks.

How to Resolve the collation conflict and check Collate in SQL Server

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

How to pass the variable value from codebehind to javascript

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.