Pages

Query XML Data in Sql 2005

que
  1. Sql Server 2005 includes subset of the XQuery language
  2. Xquery is a language for query data stored using Xml Data Type
  3. DDL operation on xml column
  4. Syntax to retrieve a date from a column of xml data type< Select columnname .query (‘/nodename’) from TableName Select columname .query (‘/nodename/childnodename’) from TableName Note: other clause of select such as ’ where’ clause can be use in above statements and column should be xml type nodename refer to element name
  5. DML operation on Xml
  • INSERT
  • Syntax : Insert into tablename (columname,Xmlcolumname) values (1 ,”Xquery”) Note :xmlcolumn name can be second or third column according to table definition and valid xml data should be inserted in xml column
  • UPDATE
  • I)as first II) as last III)into Iv)after V)before VI)Delete VII)Replace value of As Last & As First Syntax:Update table name set columnname.modify(‘insert microsoft as last into (/book[1]) ’) Note: as last and as first same syntax .column should be xml type After & before Syntax:Update table name set columname.modify(‘insert james after into (book /pubs[1]) ’) Note: after and before same syntax .column should be xml type Delete Syntax:Update table name set columname.modify(‘ delete /book/pubs/auother ’) Note:Enter element name with path colum should be xml type Replace value of particular element Syntax:Update table name set columname.modify(‘ replace value of (book /pubs/text())[1] with ‘‘sql’’ ’) Note:Enter element name with path colum & text() colum should be xml type
Refered links http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx http://msdn.microsoft.com/en-us/library/ms971534.aspx http://msdn.microsoft.com/en-us/library/ms345117.aspx http://blogs.msdn.com/mrorke/archive/2005/04/13/407921.aspx

Accessing a Xml File on Server using JavaScript

  1. Create a XML file static or dynamic in Server
  2. Write a javascript function as shown JavscriptFunctionCode shown below .
  3. Call a method loadXMLDoc("XML filename")
  4. Parse XMl as shown Pseduo Codefor futher ways to parse XML document visit http://www.w3schools.com/dom/

Javasript Function Code (ref:w3schools)

function loadXMLDoc(dname) { try //Internet Explorer { xmlDoc=new ActiveXObject("Microsoft.XMLDOM"); } catch(e) { try //Firefox, Mozilla, Opera, etc. { xmlDoc=document.implementation.createDocument("","",null); } catch(e) {alert(e.message)} } try { xmlDoc.async=false; xmlDoc.load(dname); return(xmlDoc); } catch(e) {alert(e.message)} return(null); }

Pseduo Code

/////Xml File <?xml version="1.0" encoding="iso-8859-1" ?> <shop> <product> <category>Electronics</category > <pname>LapTop</pname> <cost>50</cost> </product> <product> <category>Entertainment</category > <pname>Lg Tv</pname> <cost>500</cost> </product> </shop> //////////////////////////////// ///javscript calling code xmlDoc=loadXMLDoc("Products.xml"); //one of the way of parsing xml eleement document.write(xmlDoc.getElementsByTagName("category")[0].childNodes[0].nodeValue); ////////////////////////////////

Sorting & Filtering Data Using DataView In Asp.net 2.0

Steps:

  1. Create Dataview Object pass DataTable object to constructer of Dataview class (Assume datatable object is Created)
  2. To sort Data use property called 'Sort' in Dataview object as peseduoCode shown below .
  3. To Filter Data use RowFilter or RowStateFilter.as peseduoCode shown below
  4. RowFilter is use as where clause in sql statment use all operaters use in where clause
  5. RowState Filter provide base on DataRow object RowState property.It is use to retrive version information with DataTable using one of the DataViewRowState enermuation

DataViewRowState Enermuation

  • Added:Retervies the current DataRowVersion of DataRow Object that row state of Added
  • CurrentRows:Reterive all rows that current DataRowVersion
  • Deleted:Reterive the original DataRowVersion of DataRow object that have a RowState of deleted.
  • ModifiedCurrent:Reterives the Current DataRow Version of DateRow object that have RowState of modified
  • ModifiedOriginal:Reterives the Original DataRow Version of DateRow object that have RowState of modified
  • None:Clears Row state filter property
  • OriginalRows:Reterive the DataRow object that have original DataRowVersion.
  • Unchanged: Reterive DataRow object that have row state of unchanged

PseduoCode

////Dataview Sort
DataView view = new DataView("datableobject"); view.Sort= "columname1 ASC , columnname2 DESC"; ///Binding datasource gird; gridobject.DataSource=view; gridobject.DataBind();
////////////////// ////Dataview RowFilter
view.RowFilter ="columname1 like '%A' and columname2 < 26 "; //Do Some Thing
////////////////////// ////DateView RowState property
view.RowStateFilter = DataRowState.Added //DataRowState.Enumerationnames //Do Some Thing

Create a Custom colums in GridView

  1. Add CustomBoundField file to your project (c# libaray project for creation of dll or in web application directly access it)
  2. To acces file in your code behind follow the Pseudo code below .If you are using as dll add refence then access in your application

Pseduo code

DataSet ds = new DataSet(); ds.Tables.Add(); ds.Tables[0].Columns.Add("UserName"); // ds.Tables[0].Columns.Add("xyz"); ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); // ds.Tables[0].NewRow(); ds.Tables[0].Rows[0][0] = "sdfg1"; //ds.Tables[0].Rows[0][1] = "0"; CustomBoundField cbound = new CustomBoundField(); cbound.HeaderText = "Name"; cbound.DataField = "UserName"; cbound.Editable = true ; CustomBoundField cbound1 = new CustomBoundField(); cbound1.HeaderText = "tempcolum1"; cbound1.ShowCheckBox = true; CustomBoundField cbound2 = new CustomBoundField(); cbound2.HeaderText = "tempcolum2"; cbound2.ShowRadioButton = true; CustomBoundField cbound3 = new CustomBoundField(); cbound3.HeaderText = "tempcolum3"; cbound3.ShowTextBox = true; CustomBoundField cbound4 = new CustomBoundField(); cbound4.HeaderText = "tempcolum4"; cbound4.ShowDropDown = true; GridView1.Columns.Add(cbound); GridView1.Columns.Add(cbound1); GridView1.Columns.Add(cbound2); GridView1.Columns.Add(cbound3); GridView1.Columns.Add(cbound4); GridView1.AutoGenerateColumns = false; GridView1.DataSource = ds; GridView1.DataBind() ;

CustomBound Field class (ref:Codeproject)

public class CustomBoundField : DataControlField { public CustomBoundField() { // // TODO: Add constructor logic here // } #region Public Properties /// /// This property describe weather the column should be an editable column or non editable column. /// public bool Editable { get { object value = base.ViewState["Editable"]; if (value != null) { return Convert.ToBoolean(value); } else { return true; } } set { base.ViewState["Editable"] = value; this.OnFieldChanged(); } } /// /// This property is to describe weather to display a check box or not. /// This property works in association with Editable. /// public bool ShowCheckBox { get { object value = base.ViewState["ShowCheckBox"]; if (value != null) { return Convert.ToBoolean(value); } else { return false; } } set { base.ViewState["ShowCheckBox"] = value; this.OnFieldChanged(); } } public bool ShowRadioButton { get { object value = base.ViewState["ShowRadioButton"]; if (value != null) { return Convert.ToBoolean(value); } else { return false; } } set { base.ViewState["ShowRadioButton"] = value; this.OnFieldChanged(); } } public bool ShowTextBox { get { object value = base.ViewState["ShowTextBox"]; if (value != null) { return Convert.ToBoolean(value); } else { return false; } } set { base.ViewState["ShowTextBox"] = value; this.OnFieldChanged(); } } public bool ShowDropDown { get { object value = base.ViewState["ShowDropDown"]; if (value != null) { return Convert.ToBoolean(value); } else { return false; } } set { base.ViewState["ShowDropDown"] = value; this.OnFieldChanged(); } } /// /// This property describe column name, which acts as the primary data source for the column. /// The data that is displayed in the column will be retreived from the given column name. /// public string DataField { get { object value = base.ViewState["DataField"]; if (value != null) { return value.ToString(); } else { return string.Empty; } } set { base.ViewState["DataField"] = value; this.OnFieldChanged(); } } #endregion #region Overriden Life Cycle Methods /// /// Overriding the CreateField method is mandatory if you derive from the DataControlField. /// /// protected override DataControlField CreateField() { return new BoundField(); } /// /// Adds text controls to a cell's controls collection. Base method of DataControlField is /// called to import much of the logic that deals with header and footer rendering. /// /// A reference to the cell /// The type of the cell /// State of the row being rendered /// Index of the row being rendered public override void InitializeCell(DataControlFieldCell cell, DataControlCellType cellType, DataControlRowState rowState, int rowIndex) { //Call the base method. base.InitializeCell(cell, cellType, rowState, rowIndex); switch (cellType) { case DataControlCellType.DataCell: this.InitializeDataCell(cell, rowState); break; case DataControlCellType.Footer: this.InitializeFooterCell(cell, rowState); break; case DataControlCellType.Header: this.InitializeHeaderCell(cell, rowState); break; } } #endregion #region Custom Protected Methods /// /// Determines which control to bind to data. In this a hyperlink control is bound regardless /// of the row state. The hyperlink control is then attached to a DataBinding event handler /// to actually retrieve and display data. /// /// Note: This control was built with the assumption that it will not be used in a gridview /// control that uses inline editing. If you are building a custom data control field and /// using this code for reference purposes key in mind that if your control needs to support /// inline editing you must determine which control to bind to data based on the row state. /// /// A reference to the cell /// State of the row being rendered protected void InitializeDataCell(DataControlFieldCell cell, DataControlRowState rowState) { //Check to see if the column is a editable and does not show the checkboxes. if (Editable & !ShowCheckBox & !ShowRadioButton & !ShowTextBox & !ShowDropDown) { string ID = Guid.NewGuid().ToString(); TextBox txtBox = new TextBox(); txtBox.Columns = 5; txtBox.ID = ID; txtBox.DataBinding += new EventHandler(txtBox_DataBinding); cell.Controls.Add(txtBox); } else { if (ShowCheckBox) { CheckBox chkBox = new CheckBox(); cell.Controls.Add(chkBox); } else if (ShowRadioButton) { RadioButton rdBttn1 = new RadioButton(); rdBttn1.GroupName = "grp1"; rdBttn1.Text = "Yes"; RadioButton rdBttn2 = new RadioButton(); rdBttn2.GroupName = "grp1"; rdBttn2.Text = "No"; cell.Controls.Add(rdBttn1); cell.Controls.Add(rdBttn2); } else if (ShowTextBox) { TextBox txtBox = new TextBox(); cell.Controls.Add(txtBox); txtBox.Columns = 5; } else if (ShowDropDown) { DropDownList ddl = new DropDownList(); cell.Controls.Add(ddl); // txtBox.Columns = 5; } else { Label lblText = new Label(); lblText.DataBinding += new EventHandler(lblText_DataBinding); cell.Controls.Add(lblText); } } } void lblText_DataBinding(object sender, EventArgs e) { // get a reference to the control that raised the event Label target = (Label)sender; Control container = target.NamingContainer; // get a reference to the row object object dataItem = DataBinder.GetDataItem(container); // get the row's value for the named data field only use Eval when it is neccessary // to access child object values, otherwise use GetPropertyValue. GetPropertyValue // is faster because it does not use reflection object dataFieldValue = null; if (this.DataField.Contains(".")) { dataFieldValue = DataBinder.Eval(dataItem, this.DataField); } else { dataFieldValue = DataBinder.GetPropertyValue(dataItem, this.DataField); } // set the table cell's text. check for null values to prevent ToString errors if (dataFieldValue != null) { target.Text = dataFieldValue.ToString(); } } protected void InitializeFooterCell(DataControlFieldCell cell, DataControlRowState rowState) { CheckBox chkBox = new CheckBox(); cell.Controls.Add(chkBox); } protected void InitializeHeaderCell(DataControlFieldCell cell, DataControlRowState rowState) { // TODO:if wnat add label to headet text then use it else comment it //Label lbl = new Label(); //lbl.Text = "Name";//this.DataField; //cell.Controls.Add(lbl); } void txtBox_DataBinding(object sender, EventArgs e) { // get a reference to the control that raised the event TextBox target = (TextBox)sender; Control container = target.NamingContainer; // get a reference to the row object object dataItem = DataBinder.GetDataItem(container); // get the row's value for the named data field only use Eval when it is neccessary // to access child object values, otherwise use GetPropertyValue. GetPropertyValue // is faster because it does not use reflection object dataFieldValue = null; if (this.DataField.Contains(".")) { dataFieldValue = DataBinder.Eval(dataItem, this.DataField); } else { dataFieldValue = DataBinder.GetPropertyValue(dataItem, this.DataField); } // set the table cell's text. check for null values to prevent ToString errors if (dataFieldValue != null) { target.Text = dataFieldValue.ToString(); } } #endregion }

Converting rows into colums In Sql Server for Cross Tab Report

Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data. visit : http://www.sqlservercentral.com/articles/T-SQL/63681/ .It require registration .Enjoy learning

Creating Document library in Moss2007

1)Click on site setting select libirary setting 2)Enter Name ,Description ,Navigation,Document version history 3)Select Document tempelate as per requirment 4)Once document libirary created At top you find option (New ,Upload,Action,Setting) 5)New ->to create a document and save it 6)Upload -> To upload single doc or multiple docments 7)Action->Edit Datasheet ,Open window explore to drag drop document NOTE:If using office 2007 save in word 97-2003 format only

Different Custom Controls in Asp.net2.0

They are 3 primary custom control in Asp.net2.o 1)User control : A user Control is a template Control that provide extra behaviour to allow consitent control added to the user control in GUI .This control is added to the user control template file i.e ascx 2)Custom Web control : A custom web control is control that inherits from a web control.Write all the code for Rendering the control or inherit from existing web control and provide extra behaviour. 3)Composite control : A custom web control that can contain constiuent controls.The constiuent control are added to composite control via code to class file that defines the control.The class file compiled to a .dll