Getting Started with Web Form and WebAPI using Entity Framework 5

ASP.NET Web API is a framework that makes it easy to build HTTP services that reach a broad range of clients, including browsers and mobile devices. ASP.NET Web API is an ideal platform for building RESTful applications on the .NET Framework.

In this article, we will use Web API with Empty Web Forms application for CURD on list of Customer Information. We will be using Visual Studio 2012 and .Net 4.5.

Create Empty Web Forms application

Open Visual Studio 2012, Go To New -> Project. Select Visual C# -> Web from the left hand navigation and select project type as ASP.Net Web Forms Application. Enter the project name as AB_CustomerWebAPI

1

Model

Right Click the Project in the Solution Explorer, Select Add -> New Folder from the Context Menu. The name of the class will be “Model”. Then do it again to add class “Customer” under Model Folder
2_

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
namespace AB_CustomerWebAPI.Model
{
    public class Customer
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int CustomerID { get; set; }
        [StringLength(100)]
        public string Name { get; set; }
        [StringLength(100)]
        public string Email { get; set; }
    }
}

DbContext

Right Click the Model Folder in the Solution Explorer, Select Add -> Class from the Context Menu. The name of the class will be “CustomerContext”.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace AB_CustomerWebAPI_1.Model
{
    public class CustomerContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }
    }
}

Database Connection string

add following line to web.config file of project

<add name="CustomerContext" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;" providerName="System.Data.SqlClient" />

The Repository

Right Click the Model Folder in the Solution Explorer, Select Add -> Class from the Context Menu. The name of the class will be “CustomerRepository”.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace AB_CustomerWebAPI.Model
{
    public interface ICustomerRepository
    {
        IEnumerable<Customer> GetAll();
        Customer Get(int id);
        Customer Add(Customer item);
        void Remove(int id);
        bool Update(Customer item);
    }
    public class CustomerRepository  : ICustomerRepository
    {
        private readonly CustomerContext _db;
        public CustomerRepository()
        {
            _db = new CustomerContext();
        }
        public IEnumerable<Customer> GetAll()
        {
            return _db.Customers;
        }
        public Customer Get(int id)
        {
            return _db.Customers.Find(id);
        }
        public Customer Add(Customer customer)
        {
            _db.Customers.Add(customer);
            _db.SaveChanges();
            return customer;
        }
        public void Remove(int id)
        {
            Customer customer = _db.Customers.Find(id);
            _db.Customers.Remove(customer);
            _db.SaveChanges();
        }
        public bool Update(Customer item)
        {
            Customer newCustomer =_db.Customers.Find(item.CustomerID);
            if (newCustomer == null)
                return false;
            newCustomer.Email = item.Email;
            newCustomer.Name = item.Name;
            _db.SaveChanges();
            return true;
        }
    }
}

The WebAPI Controller

Right Click the Project in the Solution Explorer, Select Add -> New Folder from the Context Menu. The name of the class will be “Api”. Then do it again to add class “CustomersController” under Api Folder
web api

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using AB_CustomerWebAPI.Model;
namespace AB_CustomerWebAPI.Api
{
    public class CustomersController : ApiController
    {
        private static readonly ICustomerRepository _customers = new CustomerRepository();
        // GET api/<controller>
        public IEnumerable<Customer> Get()
        {
            return _customers.GetAll();
        }
        // GET api/<controller>/5
        public Customer Get(int id)
        {
            Customer c = _customers.Get(id);
            if (c == null)
                throw new HttpResponseException(HttpStatusCode.NotFound);

            return c;
        }
        // POST api/<controller>
        public Customer Post(Customer customer)
        {
            return _customers.Add(customer);
        }
        // PUT api/<controller>/5
        public Customer Put(Customer customer)
        {
            if (!_customers.Update(customer))
                throw new HttpResponseException(HttpStatusCode.NotFound);
            return customer;
        }
        // DELETE api/<controller>/5
        public Customer Delete(int id)
        {
            Customer c = _customers.Get(id);
            _customers.Remove(id);
            return c;
        }
    }
}

Adding Web API Route in Global.asax

For the controller to handle requests of a particular type, we will have to define a route in the Global.asax file.
Open global.asax file and add the following code in the “Application_Start” method

void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            AuthConfig.RegisterOpenAuth();
            RouteConfig.RegisterRoutes(RouteTable.Routes);

            RouteTable.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = System.Web.Http.RouteParameter.Optional }
            );
        }

Now run Web Form Application (F5)

The URL will be something like http://localhost:xxxxx/Default.aspx where xxxxx will be port number.

To Test the WebAPI, we will have to use the URL as defined in the Route. Enter the following URL in the browser http://localhost:xxxxx/api/Customers/ (Replace xxxxx with your port number)

Now database created in your Solution Explorer (App_Data/Database.mdf)
web api

Add some data to Customer table
web api

WebAPI with jQuery (CURD)

Get all Customers:

Web Api URL: api/Customers

web api

    <h3>Get All Customers</h3>
    <input type="button" id="GetallCustomer" value="Display All Customer" />
    <ul id="Customers">
    </ul>
    <hr />

 

 $('#GetallCustomer').click(function (e) {
            e.preventDefault();
            GetAllCustomers();
        });
function GetAllCustomers() {
            jQuery.support.cors = true;
            $.ajax({
                url: '/api/Customers',
                type: 'GET',
                dataType: 'json',
                success: function (customers) {
                    $('#Customers').html('');
                    $.each(customers, function (index, customer) {
                        $('#Customers').append('<li>(' + customer.CustomerID + ') Name=' + customer.Name + ' Email=' + customer.Email + '</li>');
                    });

                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

Search Customer:

Web Api URL: api/Customers/id

web api

    <h3>Get by CustomerID</h3>
    <input id="txtCustomerID" type="text" /><input id="getCustomer" type="button" value="Get Customer by ID" />
    <ul id="Customer">
    </ul>
    <hr />

 

 $('#getCustomer').click(function (e) {
            e.preventDefault();
            GetCustomer();
        });
  function GetCustomer() {
            jQuery.support.cors = true;
            var id = $('#txtCustomerID').val();
            if (id.length) {
                $('#Customer').html('');
                $.ajax({
                    url: 'api/Customers/' + id,
                    type: 'GET',
                    dataType: 'json',
                    success: function (customer) {
                        $('#Customer').html('<li>(' + customer.CustomerID + ') Name=' + customer.Name + ' Email=' + customer.Email + '</li>');
                    },
                    error: function (x, y, z) {
                        $('#Customer').html(z);
                        alert(x + '\n' + y + '\n' + z);
                    }
                });
            }
            else { $('#Customer').html('enter CustomerID'); }
        }

Add Customer:
web api

   <h3>New Customer</h3>
    <br />
    <input id="txtName" type="text" placeholder="Name" /><br />
    <input id="txtEmail" type="text" placeholder="Email" /><br />
    <input id="Btn_Add" type="button" value="Add" />
    <ul id="NewCustomer">
    </ul>
    <hr />

 

  $('#Btn_Add').click(function (e) {
            e.preventDefault();
            AddCustomer();
        });
 function AddCustomer() {
            jQuery.support.cors = true;
            var customer = {
                Name: $('#txtName').val(),
                Email: $('#txtEmail').val()
            };
            $.ajax({
                url: 'api/Customers',
                type: 'POST',
                data: JSON.stringify(customer),
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    $('#NewCustomer').html('<li>(' + data.CustomerID + ') Name=' + data.Name + ' Email=' + data.Email + '</li>');
                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

Delete Customer:

Web Api URL: api/Customers

web api

    <h3>Delete Customer</h3>
    <input id="txtCustID" type="text" /><input id="deleteCustomer" type="button" value="Delete Customer by ID" />
    <ul id=" deleteCustomerDetail">
    </ul>
    <hr />

 

 $('#deleteCustomer').click(function (e) {
            e.preventDefault();
            DeleteEmployee();
        });

  function DeleteEmployee() {
            jQuery.support.cors = true;
            var id = $('#txtCustID').val()
            $.ajax({
                url: 'api/Customers/' + id,
                type: 'DELETE',
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    if (data.CustomerID == id) $('#deleteCustomerDetail').html("Customer ID " + id + " deleted!!");
                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

Update Customer:
web api

   <h3>Update Customer</h3>
    <br />
    <input id="CID" type="text" placeholder="Customer ID" /><br />
    <input id="Name" type="text" placeholder="Name" /><br />
    <input id="Email" type="text" placeholder="Email" /><br />
    <input id="updateCustomer" type="button" value="update" />
    <ul id="UpdateCustomerDetail">
    </ul>
    <hr />

 

$('#updateCustomer').click(function (e) {
            e.preventDefault();
            updateCustomer();
        });
 function updateCustomer() {
            jQuery.support.cors = true;
            var customer = {
                CustomerID: $('#CID').val(),
                Name: $('#Name').val(),
                Email: $('#Email').val()
            };

            $.ajax({
                url: 'api/Customers',
                type: 'PUT',
                data: JSON.stringify(customer),
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    $('#UpdateCustomerDetail').html('<li>(' + data.CustomerID + ') Name=' + data.Name + ' Email=' + data.Email + '</li>');
                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

In the “Default.aspx” file, we will use the following code for the body section to display the data


    <h3>Get All Customers</h3>
    <input type="button" id="GetallCustomer" value="Display All Customer" />
    <ul id="Customers">
    </ul>
    <hr />
    <h3>Get by CustomerID</h3>
    <input id="txtCustomerID" type="text" /><input id="getCustomer" type="button" value="Get Customer by ID" />
    <ul id="Customer">
    </ul>
    <hr /></pre>
<h3>New Customer</h3>
<pre>
    <input id="txtName" type="text" placeholder="Name" />
    <input id="txtEmail" type="text" placeholder="Email" />
    <input id="Btn_Add" type="button" value="Add" /></pre>
<ul id="NewCustomer"></ul>

<hr />

<h3>Delete Customer</h3>
<pre>
    <input id="txtCustID" type="text" /><input id="deleteCustomer" type="button" value="Delete Customer by ID" /></pre>
<ul id="deleteCustomerDetail"></ul>

<hr />

<h3>Update Customer</h3>
<pre>
    <input id="CID" type="text" placeholder="Customer ID" />
    <input id="Name" type="text" placeholder="Name" />
    <input id="Email" type="text" placeholder="Email" />
    <input id="updateCustomer" type="button" value="update" /></pre>
<ul id="UpdateCustomerDetail"></ul>

<hr />

<pre><script type="text/javascript">// <![CDATA[
        $('#GetallCustomer').click(function (e) {
            e.preventDefault();
            GetAllCustomers();
        });
        $('#getCustomer').click(function (e) {
            e.preventDefault();
            GetCustomer();
        });
        $('#Btn_Add').click(function (e) {
            e.preventDefault();
            AddCustomer();
        });
        $('#deleteCustomer').click(function (e) {
            e.preventDefault();
            DeleteEmployee();
        });
        $('#updateCustomer').click(function (e) {
            e.preventDefault();
            updateCustomer();
        });
        function GetAllCustomers() {
            jQuery.support.cors = true;
            $.ajax({
                url: '/api/Customers',
                type: 'GET',
                dataType: 'json',
                success: function (customers) {
                    $('#Customers').html('');
                    $.each(customers, function (index, customer) {
                        $('#Customers').append('
	<li>(' + customer.CustomerID + ') Name=' + customer.Name + ' Email=' + customer.Email + '</li>

');
                    });

                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }
        function GetCustomer() {

            jQuery.support.cors = true;
            var id = $('#txtCustomerID').val();
            if (id.length) {
                $('#Customer').html('');
                $.ajax({
                    url: 'api/Customers/' + id,
                    type: 'GET',
                    dataType: 'json',
                    success: function (customer) {
                        $('#Customer').html('
	<li>(' + customer.CustomerID + ') Name=' + customer.Name + ' Email=' + customer.Email + '</li>

');
                    },
                    error: function (x, y, z) {
                        $('#Customer').html(z);
                        alert(x + '\n' + y + '\n' + z);
                    }
                });
            }
            else { $('#Customer').html('enter CustomerID'); }
        }
        function AddCustomer() {
            jQuery.support.cors = true;
            var customer = {
                Name: $('#txtName').val(),
                Email: $('#txtEmail').val()
            };

            $.ajax({
                url: 'api/Customers',
                type: 'POST',
                data: JSON.stringify(customer),
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    $('#NewCustomer').html('
	<li>(' + data.CustomerID + ') Name=' + data.Name + ' Email=' + data.Email + '</li>

');
                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

        function DeleteEmployee() {
            jQuery.support.cors = true;
            var id = $('#txtCustID').val()

            $.ajax({
                url: 'api/Customers/' + id,
                type: 'DELETE',
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    if (data.CustomerID == id) $('#deleteCustomerDetail').html("Customer ID " + id + " deleted!!");

                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }
        function updateCustomer() {
            jQuery.support.cors = true;
            var customer = {
                CustomerID: $('#CID').val(),
                Name: $('#Name').val(),
                Email: $('#Email').val()
            };

            $.ajax({
                url: 'api/Customers',
                type: 'PUT',
                data: JSON.stringify(customer),
                contentType: "application/json;charset=utf-8",
                success: function (data) {
                    $('#UpdateCustomerDetail').html('
	<li>(' + data.CustomerID + ') Name=' + data.Name + ' Email=' + data.Email + '</li>

');
                },
                error: function (x, y, z) {
                    alert(x + '\n' + y + '\n' + z);
                }
            });
        }

// ]]></script>

Thanks

Create Multi-Layer XML file from SQL Database Table

In this article, I talked about how to create multilayer xml from SQL Server Database table.
Following is the XML which we want to create:
[ad#post]

<Orders>
  <Order OrderNumber="99503" OrderDate="2013-10-20">
    <Address Type="Shipping">
      <Name>Ellen Adams</Name>
      <Street>123 Maple Street</Street>
      <City>Mill Valley</City>
      <State>CA</State>
      <Zip>CA</Zip>
      <Country>USA</Country>
    </Address>
    <Address Type="Billing">
      <Name>Tai Yee</Name>
      <Street>8 Oak Avenue</Street>
      <City>Old Town</City>
      <State>PA</State>
      <Zip>PA</Zip>
      <Country>USA</Country>
    </Address>
    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
    <Items>
      <Item PartNumber="872-AA">
        <ProductName>Lawnmower</ProductName>
        <Quantity>1</Quantity>
        <USPrice>148.95</USPrice>
        <Comment>Confirm this is electric</Comment>
      </Item>
      <Item PartNumber="926-AA">
        <ProductName>Baby Monitor</ProductName>
        <Quantity>2</Quantity>
        <USPrice>39.98</USPrice>
        <ShipDate>2013-05-21</ShipDate>
      </Item>
    </Items>
  </Order>
</Orders>

We have two table called Orders and Order_Products

XML_To_DB

SQL Script to generate above XML:

SELECT [Order] as [email protected]' , OrderDate as [email protected]',
(
     SELECT 'Shipping' as [email protected]' 
     , Shipping_Name as 'Name'
     ,Shipping_Street as 'Street'
     ,Shipping_City as 'City'
     ,Shipping_State as 'State'
     ,Shipping_State as 'Zip'
     ,Shipping_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),
  (
     SELECT 'Billing' as [email protected]' 
     , Billing_Name as 'Name'
     ,Billing_Street as 'Street'
     ,Billing_City as 'City'
     ,Billing_State as 'State'
     ,Billing_State as 'Zip'
     ,Billing_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),DeliveryNotes,
  (
    SELECT PartNumber as [email protected]', ProductName, Quantity,convert(numeric(18,2), USPrice)as USPrice,Comment,ShipDate 			
		From [temp].[dbo].Order_Products P where 	P.[Order] =Ord.[Order]
		FOR XML PATH('Item'),ROOT('Items'),Type		
   )   
FROM [Orders] Ord
FOR XML PATH('Order'),ROOT('Orders')

Thanks

Create a SiteMap with ASP.NET

What are Sitemaps?

Sitemaps are an easy way for webmasters to inform search engines about pages on their sites that are available for crawling. In its simplest form, a Sitemap is an XML file that lists URLs for a site along with additional metadata about each URL (when it was last updated, how often it usually changes, and how important it is, relative to other URLs in the site) so that search engines can more intelligently crawl the site.
Sitemap tag definitions

Tag Required? Description
<urlset> Required Encloses all information about the set of URLs included in the Sitemap.
<url> Required Encloses all information about a specific URL.
<loc> Required Specifies the URL. For images and video, specifies the landing page (aka play page, referrer page). Must be a unique URL.
<lastmod> Optional The date the URL was last modifed, in YYYY-MM-DDThh:mmTZD format (time value is optional).
<changefreq> Optional Provides a hint about how frequently the page is likely to change. Valid values are:

  • always. Use for pages that change every time they are accessed.
  • hourly
  • daily
  • weekly
  • monthly
  • yearly
  • never. Use this value for archived URLs.
<priority> Optional Describes the priority of a URL relative to all the other URLs on the site. This priority can range from 1.0 (extremely important) to 0.1 (not important at all).

Does not affect your site’s ranking in Google search results. Because this value is relative to other pages on your site, assigning a high priority (or specifying the same priority for all URLs) will not help your site’s search ranking. In addition, setting all pages to the same priority will have no effect.

Sitemap Example

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>http://www.ashishblog.com/</loc>
    <lastmod>2012-01-01</lastmod>
    <changefreq>monthly</changefreq>
    <priority>0.8</priority>
  </url>
  <url>
    <loc>http://www.ashishblog.com/about-me/</loc>
    <priority>0.5</priority>
  </url>
</urlset>

First, We need to create table or store procedure to get url from sites.
Here I created on table named GoogleIndexProduct with two column : URl and UpdatedDate
Now I used Following code to generate SiteMap xml file from that created table.
SiteMap.aspx.cs:

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.Xml;
using System.Text;
 
public partial class SiteMap: System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Response.Clear();
            Response.ContentType = "text/xml";
            using (XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.UTF8))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement("urlset");
                writer.WriteAttributeString("xmlns", "http://www.sitemaps.org/schemas/sitemap/0.9");
                
                string connect = WebConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connect))
                    {
                        con.Open();
                        string query = "SELECT [URL],Date FROM [GoogleIndexProduct]";
                        SqlCommand cmd = new SqlCommand(query, con);
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            writer.WriteStartElement("url");
                            writer.WriteElementString("loc", dr[0].ToString());
                            writer.WriteElementString("lastmod", String.Format("{0:yyyy-MM-dd}", dr[1]));
                            writer.WriteElementString("changefreq", "weekly");
                            writer.WriteElementString("priority", "1.0");
                            writer.WriteEndElement();
                        }
                        con.Close();
                        con.Dispose();
                    }
             
                
                writer.WriteEndElement();
                writer.WriteEndDocument();
                writer.Flush();
            }
            Response.End();
           
        }
    }

SiteMap.aspx:
The first thing I did when adding the page to the site was to remove all but the top line of the aspx file. This prevents any html being added to the response and invalidating the xml of the sitemap. All that appears, therefore is:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SiteMap.aspx.cs" Inherits="SiteMap" %>

I then logged into my Google account, and submitted the URL (http://www.ashishblog.com/SiteMap.aspx) for my site map, having tested that it validates against the protocol.

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.