How to bind DataGridView with Connected and Dis Connected Mode in Ado.Net

How to bind DataGridView with Connected and Dis Connected  Mode in Ado.Net

Problem : when you bind the GridView in Asp.Net with SqlDataReader dr Its binds with GridView but it doesnot happens in with DataGridView in Windows Application.Now the solution of this problem is given below….

Create table ContactDetails for binding DataGridView…………………………………….

create table ContactDetails(SrNo int identity(1,1) primary key,Name nvarchar(50) unique,Business_MobileNo nvarchar(50),Office_MobileNo nvarchar(50),Home_MobileNo nvarchar(50))

Code for From1.cs ………………………………………………..

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace DataGridViewBind

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

}

//bind DataGridView in Windows Application with Connected Mode..........

private void btnConnectedMode_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=sqltopquery;User ID=sa;Password=tiwari");

SqlCommand cmd = new SqlCommand("Select * from ContactDetails", con);

con.Open();

SqlDataReader dr = cmd.ExecuteReader();

DataTable dt = new DataTable();

dt.Load(dr);

con.Close();

dataGridView1.DataSource = dt;

}

//bind DataGridView in Windows Application with DisConnected Mode..............

private void btnDisconnectedMode_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=sqltopquery;User ID=sa;Password=tiwari");

SqlCommand cmd = new SqlCommand("Select * from ContactDetails", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

}

}

}

Read More

What is Coalesce Function in Sql Server? How it is comfortable with Optional Search

What is Coalesce Function in Sql Server? How it is comfortable with Optional Search

The COALESCE function present in SQL .It use to returns the first non-NULL expression among its arguments. The syntax for COALESCE function is as follows

  Select * from "table name" where
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END

Create table ContactDetails……………………………………..

create table ContactDetails(SrNo int identity(1,1) primary key,Name nvarchar(50) unique,Business_MobileNo nvarchar(50),Office_MobileNo nvarchar(50),Home_MobileNo nvarchar(50))

Note: we want to find out the best way to contact no each Employee according to the following rules:

1. If an employee has a business mobileno, use the business mobileno.

2. If an employee does not have a business mobileno and has a office mobile no, use the office mobile number.

3. If an employee does not have a business mobile no, does not have an office mobile no, and has a home mobile no, use the home mobile no number.

Query for above concept…………………………………….

select SrNo,Name,coalesce(Business_MobileNo,Office_MobileNo,Home_MobileNo)ContactNo from ContactDetails

You can also use above concept without coalesce function…………………………………….

select SrNo,Name as EmployeeName, ISNULL(Business_MobileNo,ISNULL(Office_MobileNo,Home_MobileNo))from ContactDetails

select  SrNo,Name, Business_MobileNo=case when ISNULL(Business_MobileNo,'')='' then case when ISNULL(Office_MobileNo,'')='' then Home_MobileNo else Office_MobileNo end else Business_MobileNo end from ContactDetails

Another similar query with coalesce function

create table IssueBook (Id int ,BookName nvarchar(50))

insert into IssueBook values(1,'Java')

insert into IssueBook values(1,'Html')

Select * from IssueBook

Create function SearchBook with coalesce function

 alter function SearchBook(@id int) returns varchar(max)

 as

 begin

 declare @book varchar(max)

 select @book=coalesce(@book + ',','') + bookname from issuebook where id=@id

 return @book

 end

 select  distinct dbo.SearchBook(1) from IssueBook

Create function SearchBooks without coalesce function

create function SearchBooks(@id int) returns varchar(500)

as

begin

declare @data varchar(500)=''

select @data +=bookname+',' from Issuebook where id=@id

return left(@data,len(@data)-1)

end

select dbo.SearchBooks(1)

Next Topic(How it is comfortable with Optional Search)

Read More

How to create Optional search using coalesce Function in Asp.net

How to create Optional search in ADO.NET with Store Procedure in Asp.net with   coalesce Function in Sql Server

--create database sqltopquery

use sqltopquery

--create table MyFriendList in database sqltopquery

create table MyFriendList(SrNo int identity(1,1) primary key,Name nvarchar(50),Emailid nvarchar(50) unique ,MobileNo nvarchar(50)unique,Address nvarchar(50),Course nvarchar(50))

--insert  into table

insert into MyFriendList values('Mrityunjay Kumar','ritumk.kumar902@gmail.com','9971684069','Bhagalpur','B C A')

--create stored procedure OpSearch…………………………………………

create proc OpSearch @SrNo int=0,@Name nvarchar(50)='',@MobileNo nvarchar(50)='',@Emailid nvarchar(50)='',@Address nvarchar(50)='',@Course nvarchar(50)=''

as

begin

SELECT * FROM MyFriendList where 1=1

and (SrNo=@SrNo or coalesce(@SrNo,'')=0)

and (Name=@Name or coalesce(@Name,'')='')

and (MobileNo=@MobileNo or coalesce(@MobileNo,'')='')

and (Emailid=@Emailid or coalesce(@Emailid,'')='')

and (Address=@Address or coalesce(@Address,'')='')

and (Course=@Course or coalesce(@Course,'')='')

end

--exec  stored procedure OpSearch

exec OpSearch 1,'Kush Tiwari',default,default,default,default

exec OpSearch default,default,default,default,default,'B. Tech'

exec OpSearch 2

Source code  for Search.aspx ………………………………………………..

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

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

<style type="text/css">

.style1

{

width: 70%;

}

</style>

</head>

<body>

<form id="form1" runat="server">

<table class="style1" >

<tr>

<td colspan="2">

<center> <b><font color="Greeen">How to create Optional search in ADO.NET with Store Procedure  with coalesce Function in Sql Server in Asp.net </font></b></center> </td>

</tr>

<tr>

<td>

Search By Name</td>

<td>

Search By EmailId</td>

</tr>

<tr>

<td>

<asp:TextBox ID="txtname" runat="server"></asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtemailid" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Search By Mobile No</td>

<td>

Search By&nbsp; Course</td>

</tr>

<tr>

<td>

<asp:TextBox ID="txtmobileno" runat="server"></asp:TextBox>

</td>

<td>

<asp:DropDownList ID="ddlcourse" runat="server" Height="19px" Width="126px">

<asp:ListItem>--Select--</asp:ListItem>

<asp:ListItem>M C A</asp:ListItem>

<asp:ListItem>B C A</asp:ListItem>

<asp:ListItem>B Tech</asp:ListItem>

<asp:ListItem>M Tech</asp:ListItem>

<asp:ListItem>M A</asp:ListItem>

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

Search&nbsp; By Address</td>

<td>

Search By SrNo</td>

</tr>

<tr>

<td>

<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtsrno" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

&nbsp;</td>

<td>

<asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click"

Text="Search" />

</td>

</tr>

<tr>

<td colspan="2">

<center>   <asp:Label ID="lblerrormessage" runat="server" Text="Label" Visible="False"></asp:Label>

</center>

</td>

</tr>

<tr>

<td colspan="2"><center>

<asp:GridView ID="GridView1" runat="server">

<EmptyDataTemplate>

There is&nbsp; no data for this search

</EmptyDataTemplate>

</asp:GridView>

</center>

</td>

</tr>

</table>

<div>

</div>

</form>

</body>

</html>

Source code  for Search.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.Data;

using System.Drawing;

public partial class Search : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSearch_Click(object sender, EventArgs e)

{

try

{

// using ternary-operator ...

string q = (txtsrno.Text == "") ? "default" + "," : "'" + txtsrno.Text + "'" + ",";

q += (txtname.Text == "") ? "default" + "," : "'" + txtname.Text + "'" + ",";

q += (txtmobileno.Text == "") ? "default" + "," : "'" + txtmobileno.Text + "'" + ",";

q += (txtemailid.Text == "") ? "default" + "," : "'" + txtemailid.Text + "'" + ",";

q += (txtaddress.Text == "") ? "default" + "," : "'" + txtaddress.Text + "'" + ",";

q += (ddlcourse.SelectedIndex == 0) ? "default" : "'" + ddlcourse.SelectedItem.Text + "'";

string query = "exec OpSearch " + q;

SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=SqlTopQuery;Integrated Security=True");

SqlCommand cmd = new SqlCommand(query, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

}

catch (Exception ex)

{

lblerrormessage.Visible = true;

lblerrormessage.ForeColor = Color.Red;

lblerrormessage.Text = ex.Message;

}

 }

}

Read More