Monday, December 31, 2012

join syntax 

var query =
from order in orders
join detail in details
on order.SalesOrderID
equals detail.SalesOrderID into orderGroup
select new
{
CustomerID = order.SalesOrderID,
OrderCount = orderGroup.Count()
};
Posted by at No comments:

Sunday, December 23, 2012

Import and Export Data from GridView

Introduction

I am not a good article writer. I am glad to have this opportunity to present this article to you. This article will help you understand how to Import and Export the GridView data into Microsoft Excel format.

Using the Code

Use this code in your codebehind file: 
//
// Code Behind
//

using System.Data.OleDb;
using System.IO;

public partial class Forms_WebImportExport : System.Web.UI.Page
{
    public void fillGrid()
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("../xmlTest.xml"));
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {    
            fillGrid();
        }
    }
    protected void btImport_Click(object sender, EventArgs e)
    {
        string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" + 
   ("Data Source=C:\\path\\test.xls;" + 
                        // or use instead of Excel 8.0 - Excel 5.0
   "Extended Properties=\"Excel 8.0;\"")); 
    string SSQL = "SELECT name , dept, salary from [sheet1$]";

        // here use oleDataReader
        OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn); 
     
        DataSet ds = new DataSet();
        //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
        oleDA.Fill(ds);
        GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
        GridView1.DataBind();
    }
    protected void btExport_Click(object sender, EventArgs e)
    {
        ExportGridToExcel(GridView1, "myExcel");
    }
    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.AddHeader("content-disposition",string.Format
   ("attachment;filename={0}.xls",fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* method must be implemented when using RenderControl */
    }
}

HTML CODE

// HTML CODE
<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="WebImportExport.aspx.cs" Inherits="Forms_WebImportExport" %>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btImport" runat="server" OnClick="btImport_Click" Text="Import" /> 
<asp:Button ID="btExport" runat="server" OnClick="btExport_Click" Text="Export" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#E7E7FF"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal">
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
Posted by at No comments:

JQuery UI autocomplete textbox with database in asp.net

JQuery UI autocomplete textbox with database in asp.net

Introduction:

In this article I will explain how to implement Asp.net autocomplete textbox with database using JQuery.

Description:
  
In previous articles I explained Ajax autocomplete extender example and JQuery autocomplete textbox with images in asp.net. Now I will explain another article relating to autocomplete textbox with JQuery UI in asp.net.
To implement this concept first we need to design table in database to save user details in database.

Column Name
Data Type
Allow Nulls
UserId
int(set identity property=true)
No
UserName
varchar(50)
Yes
Location
nvarchar(max)
Yes
After completion table design enter some of user details in database to work for our sample.
Write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>AutoComplete Box with jQuery</title>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script> 
<script type="text/javascript">
$(document).ready(function() {
SearchText();
});
function SearchText() {
$(".autosuggest").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function(data) {
response(data.d);
},
error: function(result) {
alert("Error");
}
});
}
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="demo">
<div class="ui-widget">
<label for="tbAuto">Enter UserName: </label>
<input type="text" id="txtSearch" class="autosuggest" />
</div>
</form>
</body>
</html>
If you observe above code in header section I added some of script and css files by using those files we have a chance to display auto complete text with css style. To get those files just add those urls in in your application.
Another thing here we need to know is script function in header section
$(".autosuggest").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function(data) {
response(data.d);
},
error: function(result) {
alert("Error");
}
});
}
});
This is the function declaration of JSON format we are using this JSON function to call web methods using JQuery $.ajax() whenever we need to make Ajax call with JQuery then we will use JSON functions like as we mentioned in above format. Here type, ContentType and dataType are same for all functions only url, data and success functions will vary based on our requirement.
url: This is the path of our Webmethods
data: we will pass parameters using this parameter if no parameters then we need to use data: "{}"
success: Once our web method execution completed then success function will execute and return username matching’s
(Note: JSON is a case sensitive please be careful before write JSON format of data)
Now open code behind file and add following namespaces
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
After that write the following code
C#.NET Code
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static List<string> GetAutoCompleteData(string username)
{
List<string> result = new List<string>();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT UserName from UserInformation where UserName LIKE '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", username);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["UserName"].ToString());
}
return result;
}
}
}
VB.NET Code:
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Web.Services
Partial Class Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
End Sub
<WebMethod()> _
Public Shared Function GetAutoCompleteData(ByVal username As String) As List(Of String)
Dim result As New List(Of String)()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
Using cmd As New SqlCommand("select DISTINCT UserName from UserInformation where UserName LIKE '%'+@SearchText+'%'", con)
con.Open()
cmd.Parameters.AddWithValue("@SearchText", username)
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
result.Add(dr("UserName").ToString())
End While
Return result
End Using
End Using
End Function
End Class
Now run your application and check the output that would be like this   

 

Posted by at No comments:
Subscribe to: Posts (Atom)