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

N-tier Architecture in Asp.net

Here we will talk generally about what n-Tier architecture is, and then we will have a look at different n-Tier architectures you can use to develop ASP.NET applications and issues that arise relating to performance, scalability and future development issues for each one.

Firstly, what is n-Tier architecture? N-Tier architecture refers to the architecture of an application that has at least 3 "logical" layers -- or parts -- that are separate. Each layer interacts with only the layer directly below, and has specific function that it is responsible for.

Why use n-Tier architecture? Because each layer can be located on physically different servers with only minor code changes, hence they scale out and handle more server load. Also, what each layer does internally is completely hidden to other layers and this makes it possible to change or update one layer without recompiling or modifying other layers.

This is a very powerful feature of n-Tier architecture, as additional features or change to a layer can be done without redeploying the whole application. For example, by separating data access code from the business logic code, when the database servers change you only needs to change the data access code. Because business logic code stays the same, the business logic code does not need to be modified or recompiled.
[Note] tier and layer mean the same thing [End Note]

An n-Tier application usually has three tiers, and they are called the presentation tier, the business tier and the data tier. Let's have a look at what each tier is responsible for.

Presentation Layer
Presentation Layer is the layer responsible for displaying user interface and "driving" that interface using business tier classes and objects. In ASP.NET it includes ASPX pages, user controls, server controls and sometimes security related classes and objects.

Business Tier
Business Tier is the layer responsible for accessing the data tier to retrieve, modify and delete data to and from the data tier and send the results to the presentation tier. This layer is also responsible for processing the data retrieved and sent to the presentation layer.

In ASP.NET it includes using SqlClient or OleDb objects to retrieve, update and delete data from SQL Server or Access databases, and also passing the data retrieved to the presentation layer in a DataReader or DataSet object, or a custom collection object. It might also include the sending of just an integer, but the integer would have been calculated using the data in the data tier such as the number of records a table has.

BLL and DAL
Often this layer is divided into two sub layers: the Business Logic Layer (BLL), and the Data Access Layers (DAL). Business Logic Layers are above Data Access Layers, meaning BLL uses DAL classes and objects. DAL is responsible for accessing data and forwarding it to BLL.

In ASP.NET it might be using SqlClient or OleDb to retrieve the data and sending it to BLL in the form of a DataSet or DataReader. BLL is responsible for preparing or processing the data retrieved and sends it to the presentation layer. In ASP.NET it might be using the DataSet and DataReader objects to fill up a custom collection or process it to come up with a value, and then sending it to Presentation Layer. BLL sometimes works as just transparent layer. For example, if you want to pass a DataSet or DataReader object directly to the presentation layer.

Data Tier
Data tier is the database or the source of the data itself. Often in .NET it's an SQL Server or Access database, however it's not limited to just those. It could also be Oracle, mySQL or even XML. In this article we will focus on SQL Server, as it has been proven to be the fastest database within a .NET Application.

Logical Layers vs. Physical Layers (Distributed)
Logical Layers and Physical Layers are the ones that confuse people. Firstly, a logical layer means that layers are separate in terms of assembly or sets of classes, but are still hosted on the same server. Physical layer means that those assemblies or sets of classes are hosted on different servers with some additional code to handle the communication between the layers. E.g. remoting and web services.

Deciding to separate the layers physically or not is very important. It really depends on the load your application expects to get. I think it's worth mentioning some of the facts that might affect your decision.

Please DO note that separating the layers physically WILL slow your application down due to the delay in communicating between the servers throughout the network, so if you are using the physical layer approach, make sure the performance gain is worth the performance loss from this.

Hopefully you would have designed your application using the n-Tier approach. If this is the case, then note that you can separate the layers in the future.

Cost for deploying and maintaining physically separated applications is much greater. First of all, you will need more servers. You also need network hardware connecting them. At this point, deploying the application becomes more complex too! So decide if these things will be worth it or not.

Another fact that might affect your decision is how each of the tiers in the application are going to be used. You will probably want to host a tier on a separate server if more than 1 service is dependent on it, e.g. You might want to host business logic somewhere else if you have multiple presentation layers for different clients. You might also want a separate SQL server if you have other applications using the same data.