Distinct-DataTable

using System;
using System.Data;
using System.Web.UI;
using System.Text;
using RMA.UIController.Common;
using System.Collections.Generic;
using System.Collections;

namespace RMA.WebInterface
{
public partial class GroupMaintainance : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//FILL ddlGroupLevel from SP
ddlGrouplevel.Items.Clear();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
DataTable dtLevelIndicator = new DataTable();
string[] columnLevelIndicator = new string[] { "LevelIndicator" };
dsGroupLevel.Tables[0].DefaultView.Sort = "LevelIndicator";
dtLevelIndicator = dsGroupLevel.Tables[0].DefaultView.ToTable(true, columnLevelIndicator);
ddlGrouplevel.DataSource = dtLevelIndicator;
ddlGrouplevel.DataValueField = "LevelIndicator";
ddlGrouplevel.DataTextField = "LevelIndicator";
ddlGrouplevel.DataBind();
ddlGrouplevel.Items.Insert(0, "All");


//FILL lstName from SP
lstName.Items.Clear();
DataTable dtGroupLevelName = new DataTable();
string[] columnParentLevelName = new string[] { "ParentLevelName" };
dsGroupLevel.Tables[0].DefaultView.Sort = "ParentLevelName";
dtGroupLevelName = dsGroupLevel.Tables[0].DefaultView.ToTable(true, columnParentLevelName);
lstName.DataSource = dtGroupLevelName;
lstName.DataValueField = "ParentLevelName";
lstName.DataTextField = "ParentLevelName";
lstName.DataBind();
lstName.Items.Remove(string.Empty);
}
}


#region Events
protected void ddlGrouplevel_SelectedIndexChanged(object sender, EventArgs e)
{
lstName.Items.Clear();
lstDependencies.Items.Clear();
if (ddlGrouplevel.SelectedItem.Text == "All")
{
lstName.Items.Clear();
DataTable dtGroupLevelName = new DataTable();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
string[] columnParentLevelName = new string[] { "ParentLevelName" };
dsGroupLevel.Tables[0].DefaultView.Sort = "ParentLevelName";
dtGroupLevelName = dsGroupLevel.Tables[0].DefaultView.ToTable(true, columnParentLevelName);
lstName.DataSource = dtGroupLevelName;
lstName.DataValueField = "ParentLevelName";
lstName.DataTextField = "ParentLevelName";
lstName.DataBind();
lstName.Items.Remove(string.Empty);
}
else
{
lstName.Items.Clear();
DataTable dtGroupLevelName = new DataTable();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
string[] columnParentLevelName = new string[] { "ParentLevelName" };
DataTable dtView = new DataView(dsGroupLevel.Tables[0], "levelindicator='" + ddlGrouplevel.SelectedValue + "'", "ParentLevelName", DataViewRowState.CurrentRows).ToTable();
lstName.DataSource = dtView.DefaultView.ToTable(true, columnParentLevelName);
lstName.DataTextField = "ParentLevelName";
lstName.DataValueField = "ParentLevelName";
lstName.DataBind();
lstName.Items.Remove(string.Empty);
}
}

protected void btnFind_Click(object sender, EventArgs e)
{
lstName.Items.Clear();
lstDependencies.Items.Clear();
if (ddlGrouplevel.SelectedItem.Text == "All")
{
lstName.Items.Clear();
DataTable dtGroupLevelName = new DataTable();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
string[] columnParentLevelName = new string[] { "ParentLevelName" };
DataTable dtView = new DataView(dsGroupLevel.Tables[0], "ParentLevelName like '" + txtFind.Text + "%'", "ParentLevelName", DataViewRowState.CurrentRows).ToTable();
lstName.DataSource = dtView.DefaultView.ToTable(true, columnParentLevelName);
lstName.DataTextField = "ParentLevelName";
lstName.DataValueField = "ParentLevelName";
lstName.DataBind();
lstName.Items.Remove(string.Empty);
}
else
{
lstName.Items.Clear();
DataTable dtGroupLevelName = new DataTable();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
string[] columnParentLevelName = new string[] { "ParentLevelName" };
DataTable dtView = new DataView(dsGroupLevel.Tables[0], "levelindicator='" + ddlGrouplevel.SelectedValue + "' AND ParentLevelName like '" + txtFind.Text + "%'", "ParentLevelName", DataViewRowState.CurrentRows).ToTable();
lstName.DataSource = dtView.DefaultView.ToTable(true, columnParentLevelName);
lstName.DataTextField = "ParentLevelName";
lstName.DataValueField = "ParentLevelName";
lstName.DataBind();
lstName.Items.Remove(string.Empty);
}
txtFind.Text = string.Empty;
}

protected void lstName_SelectedIndexChanged(object sender, EventArgs e)
{
lstDependencies.Items.Clear();
MasterController mastController = new MasterController();
DataSet dsGroupLevel = mastController.GetGroupLevelData(-1, -1);
DataRow[] selectProfile = dsGroupLevel.Tables["Table"].Select("ParentLevelName = '" + lstName.SelectedValue + "'");
foreach (DataRow selectProfiledr in selectProfile)
{
if (!string.IsNullOrEmpty(selectProfiledr["ChildLevelName"].ToString()))
{
lstDependencies.Items.Add("Child : " + selectProfiledr["ChildLevelName"].ToString());
}
}
}



protected void btnNew_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
Page.RegisterStartupScript("scriptviewgroup", sb.ToString());
}

protected void btnEdit_Click(object sender, EventArgs e)
{
if (ddlGrouplevel.SelectedItem.Text == "ALL")
{
string str = ddlGrouplevel.SelectedItem.Text;
string url = "ViewGroup.aspx?Name=" + lstName.SelectedItem.Text + "&grouplevel=" + str + "&Action=Edit";
StringBuilder sb = new StringBuilder();
sb.Append("");
Page.RegisterStartupScript("scripteditgroup", sb.ToString());
}
else
{
int grp = Convert.ToInt16(ddlGrouplevel.SelectedItem.Text.Substring(12));
string url = "ViewGroup.aspx?Name=" + lstName.SelectedItem.Text + "&grouplevel=" + grp + "&Action=Edit";
StringBuilder sb = new StringBuilder();
sb.Append("");
Page.RegisterStartupScript("scripteditgroup", sb.ToString());

}
}


#endregion



}
}



private List GetDistinctIDs(DataTable dtSource, string IDColumnName, string Condition)
{
List arUniqueId = new List();
foreach (DataRow dr in dtSource.Select(Condition))
{
if (dr[IDColumnName] == DBNull.Value || arUniqueId.Contains(dr[IDColumnName].ToString()))
{

continue;
}
arUniqueId.Add(dr[IDColumnName].ToString());
}
return arUniqueId;
}

Ajax - Dynamic Accordion control using xml and sql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml;
using AjaxControlToolkit;
using System.IO;
using System.Data.SqlClient;


public partial class HM28737 : System.Web.UI.UserControl
{

public static string _datasource;
public static string DS_type
{
get { return _datasource; }

set { _datasource = value; }
}

private void accordion_XML()
{
Accordion accordion = new Accordion();
accordion.ID = "AccordionID";
accordion.SelectedIndex = 0;
accordion.FadeTransitions = true;
accordion.FramesPerSecond = 50;
accordion.TransitionDuration = 50;
accordion.SuppressHeaderPostbacks = true;
accordion.HeaderCssClass = "accordionHeader";
accordion.HeaderSelectedCssClass = "accordionHeaderSelected";
accordion.ContentCssClass = "accordionContent";
accordion.RequireOpenedPane = false;


string _path = HttpContext.Current.Server.MapPath(_datasource.ToString());

System.IO.FileStream stream = new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

XmlDocument document = new XmlDocument();

document.Load(stream);

XmlNodeList nodeList = document.GetElementsByTagName("menuitem");


for (int i = 0; i < nodeList.Count; i++)
{

AccordionPane accorPane = new AccordionPane();
Label lbl = new Label();
lbl.ID = "lblControl";
lbl.Text = nodeList[i].Attributes["name"].Value;
accorPane.HeaderContainer.Controls.Add(lbl);

for (int j = 0; j < nodeList[i].ChildNodes.Count; j++)
{



string name = nodeList[i].ChildNodes[j].Attributes["name"].Value;
string url = nodeList[i].ChildNodes[j].Attributes["url"].Value;

LinkButton lbtn_childname = new LinkButton();

lbtn_childname.ID = "lbtn_child";
lbtn_childname.Text = name;
lbtn_childname.PostBackUrl = url;
accorPane.ContentContainer.Controls.Add(lbtn_childname);
accorPane.ContentContainer.Controls.Add(new LiteralControl("
"));
}
accordion.Panes.Add(accorPane);


}
PlaceHolder1.Controls.Add(accordion);
stream.Close();


}

private void accordion_SQL()
{

Accordion accordion1 = new Accordion();
accordion1.ID = "AccordionID1";
accordion1.SelectedIndex = 0;
accordion1.FadeTransitions = true;
accordion1.FramesPerSecond = 50;
accordion1.TransitionDuration = 50;
accordion1.SuppressHeaderPostbacks = true;
accordion1.HeaderCssClass = "accordionHeader";
accordion1.HeaderSelectedCssClass = "accordionHeaderSelected";
accordion1.ContentCssClass = "accordionContent";
accordion1.RequireOpenedPane = false;


SqlConnection con = new SqlConnection("Data Source=rmsw41s0695;Initial Catalog=temp;Integrated Security=True");
con.Open();
SqlCommand cmd_sitetype = new SqlCommand("SELECT DISTINCT sitetype FROM HM28737", con);

cmd_sitetype.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(cmd_sitetype);
DataSet ds = new DataSet();
da.Fill(ds);


for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
AccordionPane accorPane1 = new AccordionPane();
Label lbl = new Label();
lbl.ID = "lblControl";
lbl.Text = ds.Tables[0].Rows[i].ItemArray[0].ToString();

accorPane1.HeaderContainer.Controls.Add(lbl);

SqlCommand cmd_sql = new SqlCommand("SELECT name, url FROM HM28737 WHERE (sitetype = '" + ds.Tables[0].Rows[i].ItemArray[0].ToString() + "')", con);

cmd_sql.ExecuteNonQuery();

SqlDataAdapter da_sql = new SqlDataAdapter(cmd_sql);
DataSet ds_sql = new DataSet();
da_sql.Fill(ds_sql);
cmd_sql.Dispose();

for (int j = 0; j < ds_sql.Tables[0].Rows.Count; j++)
{
string name = ds_sql.Tables[0].Rows[j].ItemArray[0].ToString();
string url = ds_sql.Tables[0].Rows[j].ItemArray[1].ToString();
LinkButton lbtn_childname = new LinkButton();
lbtn_childname.ID = "lbtn_child";
lbtn_childname.Text = name;
lbtn_childname.PostBackUrl = url;
accorPane1.ContentContainer.Controls.Add(lbtn_childname);
accorPane1.ContentContainer.Controls.Add(new LiteralControl("
"));
}
accordion1.Panes.Add(accorPane1);
ds_sql.Clear();
}
PlaceHolder1.Controls.Add(accordion1);
con.Close();
cmd_sitetype.Dispose();
ds.Clear();

}

protected void Page_Load(object sender, EventArgs e)

{

Response.Write("

");
if (_datasource.Contains(".xml"))
{
accordion_XML();
}
else
{
accordion_SQL();
}
Response.Write("

");

}


}

JavaScripts Validating Email,Decimal and Numeric values in Text Box

// EMAIL Validation, Also sets if mandatory to check or not

function validate(obj,mandatory)
{

if(mandatory == "True")
{
if(obj.value=="")
{
alert("Empty");
obj.focus();
return false;
}


var emailPat = /^([0-9a-zA-Z]+[-._+&])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}$/;
// var emailPat = /^[\n <"']*([a-zA-Z0-9._-]@(\[\d{1,3}(\.\d{1,3}){3}]|[A-Za-z]\w*(\.[A-Za-z]\w*)+)$/;
// var emailPat =/^[\n <"']*([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+)/;
var emailid=obj.value;
var matchArray = emailid.match(emailPat);
if (matchArray == null && obj.value!=="")
{
alert("Your email address seems incorrect. Please try again.");
obj.focus();
return false;

}


return true;
}
if(mandatory == "False")
{

var emailPat = /^([0-9a-zA-Z]+[-._+&])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}$/;
// var emailPat = /^[\n <"']*([a-zA-Z0-9._-]@(\[\d{1,3}(\.\d{1,3}){3}]|[A-Za-z]\w*(\.[A-Za-z]\w*)+)$/;
// var emailPat =/^[\n <"']*([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+)/;
var emailid=obj.value;
var matchArray = emailid.match(emailPat);
if (matchArray == null && obj.value!=="")
{
alert("Your email address seems incorrect. Please try again.");
obj.focus();
return false;

}


return true;
}
}


// Decimal Textbox control JavaScript with precision how many decimal places to be allowed

function extractNumber(obj, decimalPlaces, allowNegative)
{
var temp = obj.value;

// avoid changing things if already formatted correctly
var reg0Str = '[0-9]*';
if (decimalPlaces > 0) {
reg0Str += '\\.?[0-9]{0,' + decimalPlaces + '}';
} else if (decimalPlaces < 0) {
reg0Str += '\\.?[0-9]*';
}
reg0Str = allowNegative ? '^-?' + reg0Str : '^' + reg0Str;
reg0Str = reg0Str + '$';
var reg0 = new RegExp(reg0Str);
if (reg0.test(temp)) return true;

// first replace all non numbers
var reg1Str = '[^0-9' + (decimalPlaces != 0 ? '.' : '') + (allowNegative ? '-' : '') + ']';
var reg1 = new RegExp(reg1Str, 'g');
temp = temp.replace(reg1, '');

if (allowNegative) {
// replace extra negative
var hasNegative = temp.length > 0 && temp.charAt(0) == '-';
var reg2 = /-/g;
temp = temp.replace(reg2, '');
if (hasNegative) temp = '-' + temp;
}

if (decimalPlaces != 0) {
var reg3 = /\./g;
var reg3Array = reg3.exec(temp);
if (reg3Array != null) {
// keep only first occurrence of .
// and the number of places specified by decimalPlaces or the entire string if decimalPlaces < 0
var reg3Right = temp.substring(reg3Array.index + reg3Array[0].length);
reg3Right = reg3Right.replace(reg3, '');
reg3Right = decimalPlaces > 0 ? reg3Right.substring(0, decimalPlaces) : reg3Right;
temp = temp.substring(0,reg3Array.index) + '.' + reg3Right;
}
}

obj.value = temp;
}
function blockNonNumbers(obj, e, allowDecimal, allowNegative)
{
var key;
var isCtrl = false;
var keychar;
var reg;

if(window.event) {
key = e.keyCode;
isCtrl = window.event.ctrlKey
}
else if(e.which) {
key = e.which;
isCtrl = e.ctrlKey;
}

if (isNaN(key)) return true;

keychar = String.fromCharCode(key);

// check for backspace or delete, or if Ctrl was pressed
if (key == 8 || isCtrl)
{
return true;
}

reg = /\d/;
var isFirstN = allowNegative ? keychar == '-' && obj.value.indexOf('-') == -1 : false;
var isFirstD = allowDecimal ? keychar == '.' && obj.value.indexOf('.') == -1 : false;

return isFirstN || isFirstD || reg.test(keychar);
}

// Only Numeric Textbox COntrol

function EnsureNumeric()
{var k=window.event.keyCode;if(!((k>47&&k<58))){window.event.returnValue=false;}}

Transfer Excel data to SqlServer without sqlbulkcopy

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\hariharadeep.m\Desktop\C#Testing\VBreports.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{

connection.Open();
OleDbCommand OleDbCmd = new OleDbCommand("Select * INTO [ODBC; Driver={SQL Server};Server=system10;Database=dbname;UID=sa;PWD=sa].[table_name] from [summary$]", connection);
OleDbCmd.ExecuteNonQuery();
connection.Close();
}