Create DataTable and sort Programmatically in C#, ASP.NET

Create a DataTable instance

DataTable table = new DataTable();

Create 7 columns for this DataTable

DataColumn col1 = new DataColumn("ID");
DataColumn col2 = new DataColumn("Name");
DataColumn col3 = new DataColumn("Checked");
DataColumn col4 = new DataColumn("Description");
DataColumn col5 = new DataColumn("Price");
DataColumn col6 = new DataColumn("Brand");
DataColumn col7 = new DataColumn("Remarks");

Define DataType of the Columns

col1.DataType = System.Type.GetType("System.Int");
col2.DataType = System.Type.GetType("System.String");
col3.DataType = System.Type.GetType("System.Boolean");
col4.DataType = System.Type.GetType("System.String");
col5.DataType = System.Type.GetType("System.Double");
col6.DataType = System.Type.GetType("System.String");
col7.DataType = System.Type.GetType("System.String");

Add All These Columns into DataTable table

table.Columns.Add(col1);
table.Columns.Add(col2);
table.Columns.Add(col3);
table.Columns.Add(col4);
table.Columns.Add(col5);
table.Columns.Add(col6);
table.Columns.Add(col7);

Create a Row in the DataTable table

DataRow row = table.NewRow();

Fill All Columns with Data

row[col1] = 1100;
row[col2] = "Computer Set";
row[col3] = true;
row[col4] = "New computer set";
row[col5] = 32000.00
row[col6] = "NEW BRAND-1100";
row[col7] = "Purchased on July 30,2008";

Add the Row into DataTable

table.Rows.Add(row);


Sort DataTable

dt_table.DefaultView.Sort = "[" + dt_table.Columns[1].ColumnName + "] DESC";
table.AcceptChanges();
string PIdmax = table.DefaultView[0]["Name"].ToString();

Left Outer Join in LINQ

LINQ Query

var query = (from p in dc.GetTable<Person>()
join pa in dc.GetTable<PersonAddress>() on p.Id equals pa.PersonId into tempAddresses
from addresses in tempAddresses.DefaultIfEmpty()
select new { p.FirstName, p.LastName, addresses.State });


SQL Translation

SELECT [t0].[FirstName], [t0].[LastName], [t1].[State] AS [State]
FROM [dbo].[Person] AS [t0]
LEFT OUTER JOIN [dbo].[PersonAddress] AS [t1] ON [t0].[Id] = [t1].[PersonID]

Fill a DataSet or a DataTable from a LINQ query resultset

MyDataContext db = new MyDataContext();
IEnumerable<DataRow> query = 
         (from order in db.Orders.AsEnumerable()
          select new
          {
           order.Property,
           order.Property2
          }) as IEnumerable<DataRow>;
return query.CopyToDataTable<DataRow>();

Read excel data into dataset

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Data\\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""");
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet4$]", con);
DataSet ds = new DataSet();
da.Fill(ds);

for (Int32 i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Implement your logic here
}

Handle master page click events in content page

Step 1 : Define the property in the Master Page for the LinkButton lnkbtnFromMasterPage

Public LinkButton lnkbFromMasterPage(){

Get {

Return lnkbtnFromMasterPage;

}

}

Step 2 : Reference the Master Page as a casted object in the Content page.This can be done accessing the MasterPage object of the content page and casting it to the class for our Master Page. Here the class defined for the Master Page is CustomMasterPageCls.

CustomMasterPageCls cmp = ((CustomMasterPageCls)(Master));

Step 3 : Now we can access any Public properties, methods and members of our CustomMasterPageCls class. So here was the magic, would I be able to handle the button events in the Content Page? Of course I can!
You simply need to define the event handler in your Content Page, I do it in the PageLoad event handler.

If (!(IsNothing(cmp))) {

cmp.lnkbtnFromMasterPage.Click += New EventHandler(lnkbtnFromMasterPage_Click);

End If

Step 4 : Define the actual method to handle the event.

Protected void lnkbtnFromMasterPage_Click(Object sender, System.EventArgs e){

'Perform Business Logic Here

}

Read html of any web page

Step 1: Create string type variable and assign function "readHtmlPage" to this, this function will take any web page URL of which sourcecode you want to read

string strResult = readHtmlPage("ANY web page url");

Step 2: Create the function "readHtmlPage"


private String readHtmlPage(string url)
{
String result;
WebResponse objResponse;
WebRequest objRequest = System.Net.HttpWebRequest.Create(url);
objResponse = objRequest.GetResponse();
using (StreamReader sr =
new StreamReader(objResponse.GetResponseStream()))
{
result = sr.ReadToEnd();
// Close and clean up the StreamReader
sr.Close();
}
return result;
}

Check uncheck all checkboxes in a gridview using javascript

Step 1:



Put this script below head tag

<script type="text/javascript" language="javascript">

function changeCheckState(chk)

{

var frm = document.forms[0];

for (i=0; i<frm.length; i++)

{

if (frm.elements[i].id.indexOf('checkBox') != -1)

{

frm.elements[i].checked = chk;

}

}

}



</script>

Step :2



Put this checkbox in header template

<asp:CheckBox ID="checkBox" runat="server" />

Step 3:



Put this checkbox in Itemtemplate

<input id="changeCheckStateId" onclick="changeCheckState(this.checked);" runat="server"

type="checkbox" />

Print the contents of a grid on button click

Step 1:

Put the below mentined script below head tag

script language="javascript" type="text/javascript"



function printDiv() {

//open new window set the height and width =0,set windows position at bottom

var a = window.open ('','','location=1,status=1,scrollbars=1,width=600,height=600');

a.moveTo(0,0);//window.open ('','','left =' + screen.width + ',top=' + screen.height + ',width=400,height=400,toolbar=0,scrollbars=0,status=1');

//write gridview data into newly open window

a.document.write(document.getElementById('<%= Printtbl.ClientID %>').innerHTML);

a.document.close();

a.focus();

//call print

a.print();

a.close();

return false;

}

//
script

Step 2:

Put print button

<asp:Button ID="btnPrint" runat="server" OnClientClick="javascript:return printDiv();"

Text="Print" />

Step 3:

Put the Gridview1 on page

Masking Ajax UpdateProgress control during page processing

Step 1:

Create stylesheet which will cover the window during page processing





.LodingDivBackClass

{

position:fixed;

top:0px;

bottom:0px;

left:0px;

right:0px;

overflow:hidden;

padding:0;

margin:0;

background-color:#000;

filter:alpha(opacity=50);

opacity:0.5;

z-index:1000;

}

.LoadingDiv

{

position:fixed;

top:30%;

left:43%;

padding:10px;

width:20%;

z-index:1001;

background-color:#fff;

border:solid 1px #000;

}



Step 2:

Put the scriptmanager on top of the page

Put the Updatepanel on page. Put all the controls of page in this updatepanel on any event of these controls you want to show progress image which will mask the window.



Step 3:

Put the UpdateProgress control out of update panel and also provide the image which you want to show as processing.

asp:UpdateProgress ID="uProgress" runat="server" AssociatedUpdatePanelID="UpdatepnlRecord"
ProgressTemplate
div class="LodingDivBackClass"
div class="LoadingDiv" img runat="server" id="imgLoading" src="~/images/progressbar.gif" /
div
div
ProgressTemplate
asp:UpdateProgress

Masking ajax ModalPopupExtender

Step 1:
Create stylesheet to cover the whole window

style type="text/css"
.modalBackground
{
background-color: black;
opacity: 0.5;
z-index: 1000;
filter: alpha(opacity=50);
}
.processMessage
{
position: fixed;
top: 30%;
left: 43%;
padding: 10px;
width: 14%;
background-color: #fff;
border: solid 1px #000;
}
style

Step 2:
Add your ModalPopupExtender control on page

ajaxToolkit:ModalPopupExtender ID="ModalPopupExtender1" PopupControlID="pnllinkDoctorName"
Drag="false" TargetControlID="lnkbtnConnectedto" CancelControlID="imgbtnCloseWindow"
DropShadow="false" BackgroundCssClass="modalBackground" runat="server">
/ajaxToolkit:ModalPopupExtende

Step 3:
Add your control which will be shown in popup

asp:Panel ID="pnllinkDoctorName" runat="server" CssClass="processMessage" Width="535px"
Style="display: none; background-"

div
Put what you want to show in popup
/div
/asp:panel

Highlighting Rows in a GridView

-------------

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load ( object sender, EventArgs e )
{
bindGrid();
}

protected void GridView1_RowDataBound ( object sender, GridViewRowEventArgs e )
{
if ( e.Row.RowType == DataControlRowType.DataRow )
{
if ( e.Row.RowType == DataControlRowType.DataRow )
{
e.Row.Attributes.Add ( "onmouseover", "this.savedColor=this.style.backgroundColor;this.style.backgroundColor='Yellow'" );
e.Row.Attributes.Add ( "onmouseout", "this.style.backgroundColor=this.savedColor;" );
}
}
}
}

export excel data into sql server using c#

Condition : excel file columns and sql server tables columns should be same and datatype also should be same.

public void Exel2Sql(){OdbcConnection connection;SqlBulkCopy bulkCopy;string ConnectionString = @”server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;”;string connstr = @”Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls”;using (connection = new OdbcConnection(connstr)){OdbcCommand command = new OdbcCommand(”Select * FROM [Sheet1$]“, connection);
//you can change [Sheet1$] with your sheet name
connection.Open();
// Create DbDataReader to Data Worksheet
using (OdbcDataReader dr = command.ExecuteReader()){// Bulk Copy to SQL Server
using (bulkCopy = new SqlBulkCopy(ConnectionString)){bulkCopy.DestinationTableName = “Names”;//”Names” is the sql table where you want to copy all data.
bulkCopy.WriteToServer(dr);}dr.Close();}
}
bulkCopy.Close();connection.Close();}

Show Image from SQL Server in ASP.NET Using C#

SqlConnection objConnection;SqlCommand objCommand;SqlDataAdapter objAdapter;DataSet objDS=new DataSet();
using (objConnection = new SqlConnection(”server=sujitkumar\\sqlexpress;database=livechat;uid=sa;pwd=1234″)){objConnection.Open();using (objCommand = new SqlCommand()){objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.Text;objCommand.CommandText = “select img_online from image_master”;
objAdapter = new SqlDataAdapter(objCommand);objAdapter.Fill(objDS, “CustomerAttributes”);}objConnection.Close();}
MemoryStream stream = new MemoryStream();byte[] image = (byte[])objDS.Tables[0].Rows[0][0];Response.ContentType = “image/jpeg”;Response.Expires = 0; Response.Buffer =true;Response.Clear();Response.BinaryWrite(image);Response.End();


The above code will write image on the page.
You can show this image in any image button or image control like:

A potentially dangerous Request.Form value

This error occurs when we try to post html code from code behind.The reason behind it server always validate the posted data, if it find that its an HTML code, error will occues like ‘A potentially dangerous Request.Form value…’.

if you want to send html data then you hvae to write {validateRequest=”false”} in page directiveex:
AutoEventWireup=”true” ValidateRequest=”false”

Filter table at runtime from dataset and store it in datatable in c#

In this article I am filtering data from dataset and store it in different different table.

//connect to sqlcon = new SqlConnection(”Data Source=firoz\\sqlexpress;Initial Catalog=pubs;Integrated Security=SSPI”);
//Here I have filled up dataset with two table, in first table all records are comming and in second table only unique id are coming from database.

String query = “select * from student;select distinct(id) from student”;
da = new SqlDataAdapter(query, con);da.Fill(ds);
//here I filled up different different table with unique id.
int count = ds.Tables[1].Rows.Count-1;
for (int i = 0; i <= count; i++){DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = “id=” + ds.Tables[1].Rows[i][0];
DataTable dt = new DataTable();dt=dv.ToTable(i.ToString());ds.Tables.Add(dt);}

Reset or Reseed Identity on tables with identity column in sql server

Run this command to reset or reseed Identity columns of a table.

exec sp_MSforeachtable‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’
Once you run this query on a table it will reset Identity column always.

How to get Disticnt rows/value from a DataSet or Datatable

Following line will give you distict result from dataset/datatable .

DataSet ds=new DataSet();
//code to fill dataset from database.
DataTable dt=new DataTable();
dt=ds.Tables["0"].DefaultView.ToTable(true,”columnName”);
//here true means you want distict result.

Get comma separated column vaue in SQL

CREATE PROCEDURE [dbo].[EmailIds] AS Declare @Email VARCHAR(max) Select @Email = COALESCE(@Email + ', ', '') + email from usersnew Select @Email as Email