Saturday, April 25, 2015

Monday, January 7, 2013

Comma separated values from the database end..

scenario 1. Type table Contains.. Type ID, Type Name Category table Contains.. Category ID, Category Name TypeCategory Table Contains...Type ID and Category ID.. as composite Primary key.
scenario 2. Show the Type Names with associated comma separated Category Names..
Solution: 1
SELECT RelationshipTypeId,RelationshipTypeName, RelationshipCategoryList = STUFF( ( SELECT ', ' + RelationshipCategoryName FROM ( select rt.RelationshipTypeName,rc.RelationshipCategoryName from Directory.RelationshipTypeCategory rtc inner join Directory.RelationshipType rt on rt.RelationshipTypeId = rtc.RelationshipTypeId inner join Directory.RelationshipCategory rc on rc.RelationshipCategoryId = rtc.RelationshipCategoryId ) b WHERE b.RelationshipTypeName = a.RelationshipTypeName FOR XML PATH('') ) , 1, 2, '') FROM ( select rtc.RelationshipTypeId,rt.RelationshipTypeName,rc.RelationshipCategoryName from Directory.RelationshipTypeCategory rtc inner join Directory.RelationshipType rt on rt.RelationshipTypeId = rtc.RelationshipTypeId inner join Directory.RelationshipCategory rc on rc.RelationshipCategoryId = rtc.RelationshipCategoryId ) a GROUP BY RelationshipTypeName,RelationshipTypeId
Solution 2:
select distinct RelationshipTypeId, (SELECT SUBSTRING((SELECT ',' + cast(s.RelationshipCategoryId as varchar) FROM Directory.RelationshipTypeCategory s where x.RelationshipTypeId=s.RelationshipTypeId ORDER BY s.RelationshipCategoryId FOR XML PATH('')),2,200000) AS CSV) AS CategoryIds from Directory.RelationshipTypeCategory x

Tuesday, March 20, 2012

Remove duplicate rows in datatable

Function is use to remove duplicate row from the data table
    /// <param name="dt">table from which duplicate row to be deleted</param>
    /// <param name="arrCol">columns in the table.</param>
    /// <returns>return distinct row</returns>
    protected DataTable RemoveDuplicateRowFromTable(DataTable dt, string ColOrgID, string[] strarry)
    {
        DataView view = new DataView(dt);
        DataTable dtReturn = view.ToTable(true,  strarry);
        return dtReturn;
    }

Monday, January 16, 2012

Merge two DataTables by eliminating duplicate rows

// dtResumeData is the table used to store previous session data
// dtDomainPersonTypeFinal is the table used to store the current session data

for (int x = 0; x < dtResumeData.Rows.Count; x++)
{
for (int y = 0; y < dtDomainPersonTypeFinal.Rows.Count; y++)
{
//if (dtResumeData.Rows[x]["RowNumber"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["RowNumber"].ToString()) && dtResumeData.Rows[x]["InformationDomainPersonTypeFieldId"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["InformationDomainPersonTypeFieldId"].ToString()))
//{
if (dtResumeData.Rows[x]["PersonTypeID"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["PersonTypeID"].ToString()) && dtResumeData.Rows[x]["InformationDomainPersonTypeFieldId"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["InformationDomainPersonTypeFieldId"].ToString()))
{
dtDomainPersonTypeFinal.Rows[y].Delete();
y = y - 1;
}
}
}
//dtDomainPersonTypeFinal.Merge(dtResumeData);
dtResumeData.Merge(dtDomainPersonTypeFinal);


// dtResumeData contains the merge data of two tables

Multiple Records Insertion using XML

// As we are preparing XML Document pragmatically based on the available Data Table.

public string WriteXmlIdentityInformation(DataTable dataTable)
{
string returnXML = string.Empty;
XmlDocument doc = new XmlDocument();
//XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
//doc.AppendChild(dec);
// Create the root element
XmlElement root = doc.CreateElement("DocumentElement");
doc.AppendChild(root);
foreach (DataRow dr in dataTable.Rows)
{
XmlElement myElement = doc.CreateElement("myDt");
myElement.SetAttribute("Field1", dr[0].ToString());
myElement.SetAttribute("Field2", dr[1].ToString());
myElement.SetAttribute("Field3", dr[2].ToString());
myElement.SetAttribute("Field4", dr[3].ToString());
myElement.SetAttribute("Field5", dr[4].ToString());
root.AppendChild(myElement);
returnXML= doc.OuterXml;
}
return returnXML;
}

//Sending XML document through DB Method..

if (xmlIdentityInformation != string.Empty) cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", xmlIdentityInformation);
else
cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", DBNull.Value);


//Inserting records in the DB Table.

DECLARE @intPersonInfDomainDocHandle int

//Create an internal representation of the document.

EXEC sp_xml_preparedocument @intPersonInfDomainDocHandle OUTPUT, @PersonInformationDomain

INSERT INTO Directory.Table( field1, field2,field3,field4,field5)
SELECT field1, field2,field3,field4,field5
FROM OPENXML(@intPersonInfDomainDocHandle,'/DocumentElement/myDt')
WITH
(
field1 int, field2 INT,field3 INT,field4 INT, field5 int
)
Where some condition

-- Remove the internal representation.
EXEC sp_xml_removedocument @intPersonInfDomainDocHandle

Refeneces..

NO 1
NO 2
NO 3
NO 4

Tuesday, December 21, 2010

Expand-Collapse Gridview

Well I used to display data in the expand/expand gridview

protected void gdvwplansdetails_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{

ClsBO.ClsPlanBO objclsbo = new ClsBO.ClsPlanBO();
DataSet ds = objclsbo.GetCoursePlans(Convert.ToInt64(gdvwplansdetails.DataKeys[e.Row.RowIndex].Value.ToString()));
//string DetailsQuery = "SELECT p.title,p.duration,p.price,c.thumbnail_path FROM [tbl_bt_lesson_plan] as p, [tbl_bt_course] as c where p.plan_id = c.plan_id and p.plan_id = '18'";


//Here I am grabbing the additional data and putting it into mini datagrids...
//If you wish to just use labels, or other controls, just bind the data as you
//wish, and render to html as I did.
GridView NewDg = new GridView();
NewDg.AutoGenerateColumns = false;
NewDg.CellSpacing = 0;
NewDg.CellPadding = 0;
NewDg.Width = 782;
NewDg.ShowHeader = false;
NewDg.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
NewDg.BorderColor = System.Drawing.Color.White;
if (ds.Tables[0].Rows.Count > 0)
{
TemplateField empty = new TemplateField();
empty.ItemStyle.Width = 42;
NewDg.Columns.Add(empty);
ImageField crseimage = new ImageField();
crseimage.DataImageUrlField = "imagepath";
crseimage.DataAlternateTextField = "coursetitle";
crseimage.ItemStyle.Width = 160;
crseimage.ControlStyle.Width = 45;
crseimage.ControlStyle.Height = 45;
NewDg.Columns.Add(crseimage);
BoundField title = new BoundField();
title.ItemStyle.Width = 430;
title.DataField = "coursetitle";
NewDg.Columns.Add(title);
BoundField time = new BoundField();
time.DataField = "duration";
time.ItemStyle.Width = 104;
NewDg.Columns.Add(time);
BoundField price = new BoundField();
price.DataField = "cost";
price.ItemStyle.Width = 100;
price.DataFormatString = "{0:c}";
price.NullDisplayText = "FREE";
NewDg.Columns.Add(price);
}
else
NewDg.EmptyDataText = "No Courses Found";

NewDg.DataSource = ds;
NewDg.DataBind();

System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
NewDg.RenderControl(htw);

string DivStart = "";
string FullDIV = DivStart + DivBody + DivEnd;

int LastCellPosition = e.Row.Cells.Count - 1;
int NewCellPosition = e.Row.Cells.Count - 2;

e.Row.Cells[0].ID = "CellInfo" + e.Row.RowIndex.ToString();

////Match color of div which we will expand base on row
if (e.Row.RowIndex % 2 == 0)
{
//set to regular row style
e.Row.Cells[LastCellPosition].Text = e.Row.Cells[LastCellPosition].Text + "" + FullDIV;
}
else
{
//set to alternative row style
e.Row.Cells[LastCellPosition].Text = e.Row.Cells[LastCellPosition].Text + "" + FullDIV;
}

e.Row.Cells[0].Attributes["onclick"] = "HideShowPanel('uniquename" + e.Row.RowIndex.ToString() + "'); ChangePlusMinusText('" + e.Row.Cells[0].ClientID + "'); SetExpandedDIVInfo('" + e.Row.Cells[0].ClientID + "','" + this.txtExpandedDivs.ClientID + "', 'uniquename" + e.Row.RowIndex.ToString() + "');";
e.Row.Cells[0].Attributes["onmouseover"] = "this.style.cursor='pointer'";
e.Row.Cells[0].Attributes["onmouseout"] = "this.style.cursor='pointer'";

}
}

Please refer below image for your reference

jquery Div tag Scrolling

Well..here i am displaying images in the div tags using jquery scrollable.

the below image is used to displaying the images in the horizontally using jquery horizontal slider.



the asp.net code for horizontal scrolling.

protected void dtlstimages_ItemDataBound(object sender, DataListItemEventArgs e)
{

if (e.Item.DataItem != null)
{
// Thumbview contains courses under every lessonplan, To display courses along with image
// we use the following

Localize lcimgtext = (Localize)e.Item.FindControl("lcimages");
Localize lchoriimages = (Localize)e.Item.FindControl("lchoriimages");

// To get the courses under a lessonplan we use following by passing planid
ClsBO.ClsPlanBO objclsbo = new ClsBO.ClsPlanBO();
DataSet ds = objclsbo.GetCoursePlans(Convert.ToInt64(dlst1.DataKeys[e.Item.ItemIndex]));


// Passing Gridview to get course image paths along with course titles
lcimgtext.Text = GetImages(ds);
lchoriimages.Text = GetHorizontalImages(ds);
}
}


And the methods am using above ...

protected string GetImages(DataSet ds)
{

// This method will be used to get the image path, coursename of the coueses under a plan id.
// and by adding HTML tags to the retrived values make the images,coursetitle displayed under a lesson plan
// All the HTML tags along with image path,coursetitle will be added to string "imgtext"
// finally return this imgtext.

// These images are viewed when we Expand Down (uses jQuery )


string imgtext = "span start here";
if (ds.Tables[0].Rows.Count > 0)
{

// To display 4 horizontal rows we use the following

double total = 0;

total = Math.Floor(Convert.ToDouble(ds.Tables[0].Rows.Count) / 4);

for (int count = 1; count <= ds.Tables[0].Rows.Count; count++)
{

imgtext += "open image tag here width='45px' alt=" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString()
+ " title=" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString()
+ " height='45px' src=" + ds.Tables[0].Rows[count - 1]["imagepath"].ToString()
+ " close image tag and insert break here" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString() + "insert break again.";

if ((count % 4) == 0)
{
imgtext += "span close and very next start span";
}
else
{
imgtext += "break here"
";
}

}
if (total > 0)
{

imgtext += "span close here";
}
}
return imgtext;
}

one more method am using here....


public string GetHorizontalImages(DataSet ds)
{

// This method will be used to get the image path, coursename of the coueses under a plan id.
// and by adding HTML tags to the retrived values make the images,coursetitle displayed under a lesson plan
// All the HTML tags along with image path,coursetitle will be added to string "horiimges"
// finally return this horiimges.

// These images are viewed when we scroll Left / Right (uses jQuery )
string horiimges = "";

for (int count = 1; count <= ds.Tables[0].Rows.Count; count++) {

horiimges += "paragraph start here and very next start image tag here width='45px' alt=" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString()
+ " title=" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString()
+ " height='45px' src=" + ds.Tables[0].Rows[count - 1]["imagepath"].ToString()
+ " end image tag and very next place break" + ds.Tables[0].Rows[count - 1]["coursetitle"].ToString() + "close p tag";

}
return horiimges;
}


And above i have taken a datalist for displaying images to scroll.

here jquery functions to scroll both sides


$(document).ready(function() {

window.api = $("#ctl00_maincontent_dlst1_ctl00_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl00_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl01_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl01_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl02_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl02_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl03_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl03_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl04_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl04_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl05_scroller").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
window.api = $("#ctl00_maincontent_dlst1_ctl05_scroller2").scrollable({ size: 5 }).autoscroll({
autoplay: false,
api: true
});
});


I have taken page size 5 for datalist control. so that i taken 10 functions to scroll image dives for both sides.

The below image used to show images vertically using jquery slider





The jquery files we have to attach the application ..

jquery.js
jquery.min.js

and the concern javascript and css files need to develop by own. or else we can get it in google easy..