Kishor Makwana: 2012

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()
};

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:

Collapse | Copy Code

//
// 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

Collapse | Copy 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>

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

<title>AutoComplete Box with jQuery</title>

$(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>

<label for="tbAuto">Enter UserName: </label>

</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 Function

End Class

Now run your application and check the output that would be like this

Saturday, September 22, 2012

ASP.NET QUIZ-2

1. Which of the following methods is ideally suited for retrieving a single row and single column from the database?

By using fill method of the data adapter
By using ExecuteReader method of the command object
By using ExecuteScalar method of the command object
All of the above

2. Which of the following statements are correct?

A class is an instance factory
A class is a template for creating an entity
A class is a set of all the instances of a pattern
None of the above

3. Which of the following is not a valid ASP.NET Web form control?

<ASP:Panel>
<ASP:Div>
<ASP:TableCell>
<ASP:ImageButton>

4. What does Managed Data refer to?

The data stored by CLR
The data allocated by CLR GC
The data de-allocated by CLR GC
The data allocated and de-allocated by CLR GC

5. You want to create a cookie object named “CustomerAddress” of type “CustomerInfo” to maintain address information of customers who buy products online. Which of the following code snippets should you use to create this cookie?

Dim CustomerAddress As New Cookie(“CustomerInfo”)
Dim CustomerAddress As Cookie(“CustomerInfo”)
Dim CustomerAddress As HttpCookie(“CustomerInfo”)
Dim CustomerAddress As New HttpCookie(“CustomerInfo”)

6. Which of the following is not a service provided by Common Language Runtime (CLR)?

Garbage collection
Multiple platform support
Code verification
Code access security

7. How many times can classes be nested within a class?

5
8
4
Any number of times

8. ______ namespace is not defined in the base class library.

System
System.CodeDom
System.IO
System.Thread
System.Text

9. Which of the following authorization and authentication methods is not provided by ASP.NET?

Windows built-in authentication
Passport-based authentication
Forms-based authentication
IIS authentication

10. You are developing an application to take orders over the internet. When the user posts back the order form, you first check to see whether he is a registered customer of your company. If not, you must transfer control to the Register.html page. Which method should you use to effect this transfer?

Response.Redirect()
Server.Transfer()
Server.Execute()
Page.ProcessRequest()

Answer: 1.3, 2.2, 3.2, 4.4, 5.4, 6.2, 7.4, 8.2, 9.4, 10.1

Kishor Makwana

SQL

Monday, December 31, 2012

join syntax

Sunday, December 23, 2012

Import and Export Data from GridView

Introduction

Using the Code

HTML CODE

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.

Saturday, September 22, 2012

ASP.NET QUIZ-2

Blog Archive

ABOUT ME

Visitors

SQL

Monday, December 31, 2012

join syntax

Sunday, December 23, 2012

Import and Export Data from GridView

Introduction

Using the Code

HTML CODE

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.

Saturday, September 22, 2012

ASP.NET QUIZ-2

Introduction:

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