Archive for Uncategorized

LINQ to get sorted distinct data from 2 columns

private void LinqQuery() {

  var query = (from c in fsDs.TypedDataTable

                             select c.Column1)

                           .Union(from c in fsDs.TypedDataTable

                           select c.Column2).Distinct().OrderBy(s => s);

}

Put a PDF on a Web Page

This will put a pdf on a web page:

 <object data=”test.pdf” type=”application/pdf” width=”300″ height=”200″>
alt : <a href=”test.pdf”>test.pdf</a></object>

Finding a stored proc across databases on the same Sql Server

I need to look for a stored proc across many databases in the same Sql Server. I was able to get the needed results using the following:

 select ‘select ‘ + char(39) + name + char(39) +
‘ as [DBName], [name] from ‘ + name + ‘.dbo.sysobjects where xtype = ‘
+ char(39) + ‘P’ + char(39) + ‘and name = ‘ + char(39) +
‘{Stored Procedure Name}’
+ char(39) + ‘ UNION ALL’ from sysdatabases

After this is run the results will be as follows:

select ‘master’ as [DBName], [name] from master.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘tempdb’ as [DBName], [name] from tempdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

 

select ‘model’ as [DBName], [name] from model.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘msdb’ as [DBName], [name] from msdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘pubs’ as [DBName], [name] from pubs.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘Northwind’ as [DBName], [name] from Northwind.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

The last UNION ALL must be removed or the follow error will display:

Msg 170, Level 15, State 1, Line 6

Line 6: Incorrect syntax near ‘ALL’.

After removing the UNION ALL for the last select is should look as follows:

select ‘master’ as [DBName], [name] from master.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘tempdb’ as [DBName], [name] from tempdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘model’ as [DBName], [name] from model.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘msdb’ as [DBName], [name] from msdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘pubs’ as [DBName], [name] from pubs.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL

select ‘Northwind’ as [DBName], [name] from Northwind.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’

This will return a select statement that when run will list the name of the server as well as the name of the stored proc that is searched for.

Results will be as follows:

DBName name

——— ———-

master {StoredProcName}

model {StoredProcName}

To order list items by text that is passed for VS2003

private void OrderListItems(System.Web.UI.WebControls.ListControl lc, string[] textValues) {

    ArrayList al = new ArrayList();

    foreach (System.Web.UI.WebControls.ListItem li in lc.Items) {

        al.Add(li);

    }

 

    lc.Items.Clear();

 

    foreach (string s in textValues) {

        foreach (System.Web.UI.WebControls.ListItem li in al) {

            if (li.Text == s) {

                lc.Items.Add(li);

            }

        }

    }

}

To get a comma delimited string of filtered values from a datarow for VS 2003

private string FilterValues(DataRow dr, params string[] parm) {
  ArrayList al = new ArrayList();
  foreach (string s in parm) {
    al.Add(dr[s].ToString());
  }
  string[] sArray = (string[]) al.ToArray(typeof(string));

  return string.Join(“,”, sArray);
}

Ordering Records in a non-standard way with a dataset

I needed to order records but there was no way to order it based upon setting the sort method to order it. I had to create a method that would sort the records based upon the values that were out of order based upon the sort. This is the method that was used:

private DataSet OrderRecords(DataSet ds, string fieldName, string[] values) {

  DataSet retVal = new DataSet();
  DataTable dt = ds.Tables[0].Copy();
  DataRow[] drArray = new DataRow[values.Length];
  dt.Rows.CopyTo(drArray, 0);
  ds.Tables[0].Clear();

  foreach (string s in values) {
    foreach (DataRow dr in drArray) {
      if (dr[fieldName].ToString().IndexOf(s) != -1) {
        ds.Tables[0].ImportRow(dr);
        break;
      }
    }
  }

  retVal = ds;
  return retVal;
}

Get the Item Number of the Checked item of a CheckedBoxList

To get the item number of the item that was checked in a CheckBoxList for Visual Studio 2003 use the following:

int itemNumber = Convert.ToInt32(Request.Form[“__EVENTTARGET”]
                 .Substring(Request.Form[“__EVENTTARGET”].LastIndexOf(“:”) + 1));

To create a scrolling grid that will retain position after postback (only for IE)

To create a scrolling grid that will retain position after postback (only for IE)

  1. Add a div tag around the datagrid as follows:

<div style=”overflow: auto; height: 75px” id=”scrollGrid” runat=”server” onscroll=”updateScroll(this);”>

  <asp:datagrid id=”DataGrid1″ runat=”server” useaccessibleheader=”True” />

</div>

  1. Set the useaccessibleheader to true for the datagrid:


    <asp:datagrid id=”DataGrid1″ runat=”server” useaccessibleheader=”True” />

  2. Add a hidden field to maintain the scroll position. It must have runat attribute set to server to maintain value between postbacks:


    <input type=”hidden” id=”scrollTop” runat=”server” />

  3. In the datagrid ItemDataBound event add the following:


private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) {

  if (e.Item.ItemType == ListItemType.Header) {

    e.Item.Attributes[“style”] = “position:relative; top:expression

         (this.offsetParent.scrollTop);”;

  }

  else if (e.Item.ItemType == ListItemType.Footer) {

  //e.Item.Attributes[“style”] = “position:relative; top:expression(this.offsetParent.clientHeight – this.offsetParent.scrollHeight + this.offsetParent.scrollTop + 40)”;

  }

}

  1. Register client side javascript for 2 things:
    1. Store that top of the item when scrolled
    2. Reset the top to the scroll position after postback


private void RegisterScrollScript() {

System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append(“<script type=\”text/javascript\”>”);

sb.Append(” function updateScroll(gridDiv) {“);

sb.Append(” document.getElementById(\”” + scrollTop.ClientID + “\”).value = gridDiv.scrollTop;”);

sb.Append(” }”);

sb.Append(” function setContentScrollPos(gridDiv) {“);

sb.Append(” document.getElementById(gridDiv).scrollTop = document.getElementById(\”” + scrollTop.ClientID + “\”).value;”);

sb.Append(” }”);

sb.Append(“</script>”);

RegisterClientScriptBlock(“MaintainScrollPos”, sb.ToString());

RegisterClientScriptBlock(“SetScrollPos”, “<script type=’text/javascript’>setContentScrollPos(‘” + scrollGrid.ClientID + “‘);</script>”);

}

  1. The following javascript gets rendered:


<script type=”text/javascript”>

  function updateScroll(gridDiv) {

    document.getElementById(“scrollTop
).value = gridDiv.scrollTop;

  }

  function setContentScrollPos(gridDiv) {

    document.getElementById(gridDiv).scrollTop = document.getElementById(“scrollTop”).value;

  }

</script>

<script type=’text/javascript’>

  setContentScrollPos(‘scrollGrid’);

</script>

Creating a comma delimited list with xslt

I needed to create a comma delimited list from xslt. Here is 2 ways to do it:

<!– Add the comma to each item but the last –>
<xsl:for-each select=TagName>
  <xsl:value-of select=. />
  <xsl:if test=position() != last()>
    <xsl:text>, </xsl:text>
  </xsl:if>
</xsl:for-each>

Class to format a Query String

The following is a class that will format a string as a querystring:

public class QueryStringFormatter {
  private Dictionary<stringstring> _Items = new Dictionary<stringstring>();
  public Dictionary<stringstring> Items {
    get { return _Items; }
    set { _Items = value; }
  }
  public override string ToString() {
    string retVal = string.Empty;
    if (_Items.Count > 0) {
      List<string> list = new List<string>();
      foreach (KeyValuePair<stringstring> kvp in _Items) {
        list.Add(string.Format("{0}={1}", kvp.Key, kvp.Value));
      }
      retVal = "?" + string.Join("&", list.ToArray());
    }
    return retVal;
  }
}