Joseph Michael Pesch
VP Programming

LDAP Queries

by 28. May 2011 09:03
DirectoryEntry deUser = new DirectoryEntry("LDAP://cn=John Doe,cn=Users,dc=YourCorp,dc=com");

if(deUser != null)
{
   
// check if the manager property is set - it could be NULL (no manager defined)
   
if(deUser.Properties["manager"] != null)
   
{
     
string managerDN = deUser.Properties["manager"][0].ToString();
   
}
}

Properties:
[0]: "homemdb"

    [1]: "countrycode"

    [2]: "cn"

    [3]: "msexchuseraccountcontrol"

    [4]: "mailnickname"

    [5]: "msexchhomeservername"

    [6]: "msexchhidefromaddresslists"

    [7]: "msexchalobjectversion"

    [8]: "usncreated"

    [9]: "objectguid"

    [10]: "msexchrequireauthtosendto"

    [11]: "whenchanged"

    [12]: "memberof"

    [13]: "accountexpires"

    [14]: "displayname"

    [15]: "primarygroupid"

    [16]: "badpwdcount"

    [17]: "objectclass"

    [18]: "instancetype"

    [19]: "msmqdigests"

    [20]: "objectcategory"

    [21]: "samaccounttype"

    [22]: "whencreated"

    [23]: "lastlogon"

    [24]: "useraccountcontrol"

    [25]: "msmqsigncertificates"

    [26]: "samaccountname"

    [27]: "userparameters"

    [28]: "mail"

    [29]: "msexchmailboxsecuritydescriptor"

    [30]: "adspath"

    [31]: "lockouttime"

    [32]: "homemta"

    [33]: "description"

    [34]: "msexchmailboxguid"

    [35]: "pwdlastset"

    [36]: "logoncount"

    [37]: "codepage"

    [38]: "name"

    [39]: "usnchanged"

    [40]: "legacyexchangedn"

    [41]: "proxyaddresses"

    [42]: "userprincipalname"

    [43]: "admincount"

    [44]: "badpasswordtime"

    [45]: "objectsid"

    [46]: "msexchpoliciesincluded"

    [47]: "mdbusedefaults"

    [48]: "distinguishedname"

    [49]: "showinaddressbook"

    [50]: "givenname"

    [51]: "textencodedoraddress"

    [52]: "lastlogontimestamp"

Tags:

Sharepoint Get User Information from within Workflow/Application Page

by 27. May 2011 14:39

// Workflow member variable declarations...
public Guid workflowId = default(System.Guid);
public SPWorkflowActivationProperties workflowProperties = new SPWorkflowActivationProperties();

private void SomeMethod()
{
  // This method failed: SPList userList = SPContext.Current.Web.SiteUserInfoList;
  SPList userList = workflowProperties.Site.RootWeb.SiteUserInfoList;
  SPUser user = SPContext.Current.Web.EnsureUser(@"domain\userid");
  SPListItem userItem = userList.Items.GetItemById(user.ID);
  foreach(SPField fld in userItem.Fields) 
  {
    System.Diagnostics.Debug.WriteLine(string.Format("{0} >>> {1}", fld.Title, fld.InternalName)); 
  }
}

OUTPUT FROM MY SAMPLE TEST:
ID >>> ID
Content Type ID >>> ContentTypeId
Content Type >>> ContentType
Name >>> Title
Modified >>> Modified
Created >>> Created
Created By >>> Author
Modified By >>> Editor
Has Copy Destinations >>> _HasCopyDestinations
Copy Source >>> _CopySource
owshiddenversion >>> owshiddenversion
Workflow Version >>> WorkflowVersion
UI Version >>> _UIVersion
Version >>> _UIVersionString
Attachments >>> Attachments
Approval Status >>> _ModerationStatus
Approver Comments >>> _ModerationComments
Edit >>> Edit
Name >>> LinkTitleNoMenu
Title >>> LinkTitle
Title >>> LinkTitle2
Select >>> SelectTitle
Instance ID >>> InstanceID
Order >>> Order
GUID >>> GUID
Workflow Instance ID >>> WorkflowInstanceID
URL Path >>> FileRef
Path >>> FileDirRef
Modified >>> Last_x0020_Modified
Created >>> Created_x0020_Date
Item Type >>> FSObjType
Sort Type >>> SortBehavior
Effective Permissions Mask >>> PermMask
Name >>> FileLeafRef
Unique Id >>> UniqueId
Client Id >>> SyncClientId
ProgId >>> ProgId
ScopeId >>> ScopeId
File Type >>> File_x0020_Type
HTML File Type >>> HTML_x0020_File_x0020_Type
Edit Menu Table Start >>> _EditMenuTableStart
Edit Menu Table Start >>> _EditMenuTableStart2
Edit Menu Table End >>> _EditMenuTableEnd
Name >>> LinkFilenameNoMenu
Name >>> LinkFilename
Name >>> LinkFilename2
Type >>> DocIcon
Server Relative URL >>> ServerUrl
Encoded Absolute URL >>> EncodedAbsUrl
File Name >>> BaseName
Property Bag >>> MetaInfo
Level >>> _Level
Is Current Version >>> _IsCurrentVersion
Item Child Count >>> ItemChildCount
Folder Child Count >>> FolderChildCount
Account >>> Name
E-Mail >>> EMail
Mobile Number >>> MobilePhone
About Me >>> Notes
SIP Address >>> SipAddress
Locale >>> Locale
Calendar Type >>> CalendarType
AdjustHijriDays >>> AdjustHijriDays
TimeZone >>> TimeZone
Time Format >>> Time24
Alternate Calendar Type >>> AltCalendarType
CalendarView Options >>> CalendarViewOptions
WorkDays >>> WorkDays
WorkDay StartHour >>> WorkDayStartHour
WorkDay EndHour >>> WorkDayEndHour
Is Site Admin >>> IsSiteAdmin
Deleted >>> Deleted
Picture >>> Picture
Department >>> Department
Job Title >>> JobTitle
Is Active >>> IsActive
Group >>> GroupLink
Edit >>> GroupEdit
Name >>> ImnName
Picture >>> PictureDisp
Name >>> NameWithPicture
Name >>> NameWithPictureAndDetails
Edit >>> EditUser
Selection Checkbox >>> UserSelection
Content Type >>> ContentTypeDisp
 
 

Tags:

C# | Sharepoint

Sharepoint Get User Information from within Workflow/Application Page

by 27. May 2011 13:51

SPList userList = SPContext.Current.Web.SiteUserInfoList;

SPUser user = SPContext.Current.Web.EnsureUser(@"domain\userid");

SPListItem userItem = userList.Items.GetItemById(user.ID);

 

 

Tags:

C# | Sharepoint

Linq to Sharepoint DataContext Binding

by 27. May 2011 07:32

// Get DataContext from application page

SPMetalDataContext data = new SPMetalDataContext(SPContext.GetContext(this.Context).Web.Url);

 

// Get DataContext from workflow

public SPWorkflowActivationProperties workflowProperties = new SPWorkflowActivationProperties();

// The workflowProperties variable would be declared in the workflow class

SPMetalDataContext data = new SPMetalDataContext(workflowProperties.Item.Web.Url); 

 

OR

SPMetalDataContext data = new SPMetalDataContext(SPContext.Current.Web.Url);

 

Tags:

C# | Sharepoint

Linq Child/Nested Entity Join to Master Table Entity

by 26. May 2011 10:13
    private void LoadList_AssignedResources(int RequestId)
    {
      using (SPMetalDataContext dc = new SPMetalDataContext(this.Web.Url))
      {

        ResourceRequestsItem req = dc.ResourceRequests
          .Where(x => x.Id.Equals(RequestId)).FirstOrDefault();
        
        var qry = from rs in dc.Resources
                  join rx in req.Resources on rs.Id equals rx.Id
                  select new
                  {
                    Title = rs.Title,
                    Id = rs.Id
                  };

        AssignedResourcesList.DataTextField = "Title";
        AssignedResourcesList.DataValueField = "Id";
        AssignedResourcesList.DataSource = qry;
        AssignedResourcesList.DataBind();
      }
    }

Tags:

C# | LINQ to SQL

Add Author, Created, Editor and Modified Fields to SPMetal Generated C# Class for Sharepoint

by 26. May 2011 09:29
public partial class Item : Microsoft.SharePoint.Linq.ITrackEntityState
  , Microsoft.SharePoint.Linq.ITrackOriginalValues
  , System.ComponentModel.INotifyPropertyChanged
  , System.ComponentModel.INotifyPropertyChanging
{
  // Add the following to the Item class in the SPMetal generated class...
  string _CreatedBy;
  [Microsoft.SharePoint.Linq.ColumnAttribute(Name = "Author", Storage = "_CreatedBy"
    , ReadOnly = true, FieldType = "User", IsLookupValue = true)]
  public string CreatedBy
  {
    get
    { return this._CreatedBy; }
    set
    {
      if ((value != this._CreatedBy))
      {
        this.OnPropertyChanging("CreatedBy"this._CreatedBy);
        this._CreatedBy = value;
        this.OnPropertyChanged("CreatedBy");
      }
    }
  }

  DateTime _CreatedOn;
  [Microsoft.SharePoint.Linq.ColumnAttribute(Name = "Created", Storage = "_CreatedOn"
    , ReadOnly = true, FieldType = "DateTime", IsLookupValue = false)]
  public DateTime CreatedOn
  {
    get
    { return this._CreatedOn; }
    set
    {
      if ((value != this._CreatedOn))
      {
        this.OnPropertyChanging("CreatedOn"this._CreatedOn);
        this._CreatedOn = value;
        this.OnPropertyChanged("CreatedOn");
      }
    }
  }

  string _ModifiedBy;
  [Microsoft.SharePoint.Linq.ColumnAttribute(Name = "Editor", Storage = "_ModifiedBy"
    , ReadOnly = true, FieldType = "User", IsLookupValue = true)]
  public string ModifiedBy
  {
    get
    {
      return this._ModifiedBy;
    }
    set
    {
      if ((value != this._ModifiedBy))
      {
        this.OnPropertyChanging("ModifiedBy"this._ModifiedBy);
        this._ModifiedBy = value;
        this.OnPropertyChanged("ModifiedBy");
      }
    }
  }

  DateTime _ModifiedOn;
  [Microsoft.SharePoint.Linq.ColumnAttribute(Name = "Modified", Storage = "_ModifiedOn"
    , ReadOnly = true, FieldType = "DateTime", IsLookupValue = false)]
  public DateTime ModifiedOn
  {
    get
    {
      return this._ModifiedOn;
    }
    set
    {
      if ((value != this._ModifiedOn))
      {
        this.OnPropertyChanging("ModifiedOn"this._ModifiedOn);
        this._ModifiedOn = value;
        this.OnPropertyChanged("ModifiedOn");
      }
    }
  }

Tags:

C# | Sharepoint

Hide/Remove Title Column from Sharepoint List

by 25. May 2011 10:08

Tags:

Sharepoint

Simple Linq In Clause Example

by 25. May 2011 09:43
      // Sample using array and string converted to array...
      string[] ValueArray = new string[] { "11""13" };
      string ValueList = "1;3;5;9;15";
      using (SPMetalDataContext dc = new SPMetalDataContext(this.Web.Url))
      {
        var query =
          from res in dc.Resources
          where ValueList.Split(';').Contains(res.Id.ToString())
             || ValueArray.Contains(res.Id.ToString())
          select new
          {
            res.Title,
            res.Id
          };

Tags:

C#

C# Application to Toggle Between Multiple Windows for Specified Time Interval

by 19. May 2011 08:12

This application allows you to define windows with intervals and then when started it will toggle between those windows at the specified interval.

 

ToggleWindows.zipx (70.15 kb)

Tags:

C# | Windows

JavaScript Drop Down Menu Example

by 18. May 2011 08:31

Tags:

CSS | HTML | JavaScript

MS CRM Diagnostics

by 24. February 2011 16:33

 Create a platform trace by doing the following steps

·         Run the CRM diag tool on the CRM server.

·         In the Platform Tracing Section click to change the button to Enabled.

·         Recreate the issue by editing the attribute Requirement level and saving it.

·         In the Platform Tracing Section click to change the button to Disabled.

·        

To get the crm_server_report_information.

·         Download the new tool on to the crm servers

·         Select the Dynamics CRM Server from the dropdown

·         On the section Troubleshooting file for support

·         Click on Create file

 

NewCrmDiagTool4.zip (397.56 kb)

Tags:

MS CRM

ExecuteScalar() Truncates Xml Using SQL For Xml

by 24. February 2011 07:51

Use this style as the Sql server actually returns multiple rows of the Xml in 2,033 byte chunks:

System.Xml.XmlReader rdr = cmd.ExecuteXmlReader();
rdr.Read();
while (rdr.ReadState != System.Xml.ReadState.EndOfFile)
  xml.Append(rdr.ReadOuterXml());

Tags:

ASP.Net | SQL Server

SQL Database Summary Query

by 23. February 2011 15:03

SELECT

  vfs.database_id,

  DBName = DB_NAME(vfs.database_id),

  DB_Reads = SUM(vfs.num_of_reads),

  DB_Writes = SUM(vfs.num_of_writes),

  DB_BytesRead = SUM(num_of_bytes_read),

  DB_IOStallReadMS = SUM(io_stall_read_ms),

  DB_BytesWritten = SUM(num_of_bytes_written),

  DB_IOStallWriteMS = SUM(io_stall_write_ms),

  DB_IOStall = SUM(io_stall),

  DB_BytesSize = SUM(size_on_disk_bytes),

  DB_Files = dt.file_list

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN(

  SELECT

    vfs2.database_id,

    STUFF((

    SELECT ',' + mf3.name AS [text()]

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs3

    JOIN sys.master_files mf3 ON mf3.database_id = vfs3.database_id

      AND mf3.file_id = vfs3.file_id

    WHERE vfs2.database_id = vfs3.database_id

    FOR XML PATH('')

  ), 1, 1, '') AS file_list

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs2

  JOIN sys.master_files mf2 ON mf2.database_id = vfs2.database_id

  GROUP BY vfs2.database_id, DB_NAME(vfs2.database_id)

) dt ON dt.database_id = vfs.database_id

GROUP BY vfs.database_id, DB_NAME(vfs.database_id), dt.file_list

ORDER BY DB_NAME(vfs.database_id)

 

Excerpt from: http://www.sqlmag.com/article/tsql3/nifty-ways-to-use-for-xml-path-concatenation.aspx

Tags:

SQL Server

Default and Disable Zoom in Handheld Device Browsers

by 29. January 2011 20:34

<meta content='True' name='HandheldFriendly' />

<meta content='width=device-width; initial-scale=1.0; maximum-scale=1.0; user-scalable=0;' name='viewport' />

<meta name="viewport" content="width=device-width" />

Tags:

HTML

C# Map Network Drive

by 19. January 2011 09:09
/* == Calling code ================================ */
Utilities.Network.DriveMapping.DelDrive("i:", 0, true);
Utilities.Network.DriveMapping.MapDrive(@"\\server\folder""i:"@"domain\user""password");
/* == Class ======================================= */
using System;
using System.Runtime.InteropServices;
using System.ComponentModel;


namespace Utilities.Network
{
  public static class DriveMapping
  {
    public enum ResourceScope
    {
      RESOURCE_CONNECTED = 1,
      RESOURCE_GLOBALNET,
      RESOURCE_REMEMBERED,
      RESOURCE_RECENT,
      RESOURCE_CONTEXT
    }
 
    public enum ResourceType
    {
      RESOURCETYPE_ANY,
      RESOURCETYPE_DISK,
      RESOURCETYPE_PRINT,
      RESOURCETYPE_RESERVED
    }
 
    public enum ResourceUsage
    {
      RESOURCEUSAGE_CONNECTABLE = 0x00000001,
      RESOURCEUSAGE_CONTAINER = 0x00000002,
      RESOURCEUSAGE_NOLOCALDEVICE = 0x00000004,
      RESOURCEUSAGE_SIBLING = 0x00000008,
      RESOURCEUSAGE_ATTACHED = 0x00000010,
      RESOURCEUSAGE_ALL = (RESOURCEUSAGE_CONNECTABLE | RESOURCEUSAGE_CONTAINER | RESOURCEUSAGE_ATTACHED),
    }
 
    public enum ResourceDisplayType
    {
      RESOURCEDISPLAYTYPE_GENERIC,
      RESOURCEDISPLAYTYPE_DOMAIN,
      RESOURCEDISPLAYTYPE_SERVER,
      RESOURCEDISPLAYTYPE_SHARE,
      RESOURCEDISPLAYTYPE_FILE,
      RESOURCEDISPLAYTYPE_GROUP,
      RESOURCEDISPLAYTYPE_NETWORK,
      RESOURCEDISPLAYTYPE_ROOT,
      RESOURCEDISPLAYTYPE_SHAREADMIN,
      RESOURCEDISPLAYTYPE_DIRECTORY,
      RESOURCEDISPLAYTYPE_TREE,
      RESOURCEDISPLAYTYPE_NDSCONTAINER
    }
 
    [StructLayout(LayoutKind.Sequential)]
    private class NETRESOURCE
    {
      public ResourceScope dwScope = 0;
      public ResourceType dwType = 0;
      public ResourceDisplayType dwDisplayType = 0;
      public ResourceUsage dwUsage = 0;
      public string lpLocalName = null;
      public string lpRemoteName = null;
      public string lpComment = null;
      public string lpProvider = null;
    }
 
    [DllImport("mpr.dll")]
    private static extern int WNetAddConnection2(NETRESOURCE lpNetResource, string lpPassword, string lpUsername, int dwFlags);
 
    [DllImport("mpr.dll")]
    private static extern int WNetCancelConnection2(string name, int flags, bool force);
 
    public static bool DelDrive(string name, int flags, bool force)
    {
      int result = WNetCancelConnection2(name, flags, force);
      if (!result.Equals(0))
      {
        throw new Win32Exception((int)result);
      }
      return true;
    }
 
    public static bool MapDrive(string unc, string drive, string user, string password)
    {
      /*
       NOTE: If you receive the following error message try connecting by IP address rather then server name:
             Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. 
             Disconnect all previous connections to the server or shared resource and try again
      */
      NETRESOURCE myNetResource = new NETRESOURCE();
      myNetResource.lpLocalName = drive;
      myNetResource.lpRemoteName = unc;
      myNetResource.lpProvider = null;
      int result = WNetAddConnection2(myNetResource, password, user, 0);
      if (!result.Equals(0))
      {
        throw new Win32Exception((int)result);
      }
      return true;
    }
  }
}

 

Tags:

C#

Asp.Net Url Rewriting Alternative .Net Framework 4.0 RouteTable

by 13. January 2011 11:50

/* Global.asax.cs */

using System;
using System.Collections;
using System.Configuration;
using System.Linq;
using System.Web.Routing;
 
namespace JMP.UrlReWrite
{
  public class Global_ASAX : System.Web.HttpApplication
  {
    protected void Application_Start()
    {
      RegisterRoutes(RouteTable.Routes);
    }
    public static void RegisterRoutes(RouteCollection routes)
    {
      Hashtable tbl = (Hashtable)ConfigurationManager.GetSection("RouteTable");
      foreach (DictionaryEntry de in tbl)
        routes.MapPageRoute((de.Key.ToString().Split('/').Count() == 1) 
          ? de.Key.ToString() : Guid.NewGuid().ToString()
          , de.Key.ToString(), de.Value.ToString());
    }
  }
}

/* Default.aspx.cs */

using System;

namespace JMP.UrlRewrite
{
  public partial class _Default : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      if (Page.RouteData.Values.ContainsKey("module"))
        switch (Page.RouteData.Values["module"].ToString())
        {
          case "aboutus": Response.RedirectToRoute("about"); break;
          defaultreturn;
        }
    }
  }
}

/* web.config */

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <configSections>
    <section name="RouteTable" type="System.Configuration.DictionarySectionHandler" />
  </configSections>
  <RouteTable>
    <add key="home" value="~/default.aspx" />
    <add key="home/{module}" value="~/default.aspx" />
    <add key="about" value="~/aboutus.aspx" />
    <!-- aboutus is being mapped in default.aspx.cs just to illustrate reading the parameters -->
    <!-- test url: ~/home/aboutus -->
    <add key="contact" value="~/contactus.aspx" />
    <add key="contactus" value="~/contactus.aspx" />
  </RouteTable>
  <system.webServer>
    <handlers>
      <add
        name="UrlRoutingHandler"
        preCondition="integratedMode"
        verb="*" path="UrlRouting.axd"
        type="System.Web.HttpForbiddenHandler, System.Web,  
          Version=2.0.0.0, Culture=neutral,  
          PublicKeyToken=b03f5f7f11d50a3a"/>
    </handlers>
    <validation validateIntegratedModeConfiguration="false"></validation>
    <modules runAllManagedModulesForAllRequests="true">
     <remove name="UrlRoutingModule"/>
      <add name="UrlRoutingModule" type="System.Web.Routing.UrlRoutingModule, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </modules>
  </system.webServer>
  <connectionStrings>
  </connectionStrings>
  <system.web>
    <customErrors mode="Off" />
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
      </assemblies>
      <buildProviders>
      </buildProviders>
    </compilation>
    <httpRuntime maxRequestLength="2000000" />
    <pages controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID">
      <controls>
      </controls>
      <namespaces>
        <add namespace="System.Collections.Generic" />
        <add namespace="System.IO" />
      </namespaces>
    </pages>
  </system.web>
  <appSettings>
  </appSettings>
  <system.net>
  </system.net>
</configuration>

 

Tags:

ASP.Net

SQL Read XML to Generate Table Inserts

by 12. January 2011 10:41

set nocount on

 

-- Sample XML

declare @xmlData XML, @xpath varchar(250)

set @xmlData =

'

<xml>

  <schema>

    <table id="Wholesale.EmailSignUp" schema="Wholesale" name="EmailSignUp">

      <col id="EmailAddress" type="varchar" length="150" nullable="false" quote="''" />

      <col id="CompanyName" type="varchar" length="150" nullable="true" quote="''" />

      <col id="FirstName" type="varchar" length="25" nullable="true" quote="''" />

      <col id="LastName" type="varchar" length="25" nullable="true" quote="''" />

      <col id="PositionAtCompany" type="varchar" length="100" nullable="true" quote="''" />

      <col id="EntryDate" type="datetime" length="25" nullable="false" quote="''" />

      <col id="UpdateDate" type="datetime" length="25" nullable="false" quote="''" />

    </table>

    <table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">

      <col id="ProductInfoId" type="uniquidentifier" length="" nullable="false" quote="''" />

      <col id="FieldName" type="varchar" length="50" nullable="true" quote="''" />

      <col id="DisplayText" type="varchar" length="100" nullable="true" quote="''" />

      <col id="Value" type="int" length="" nullable="true" quote="" />

    </table>

  </schema>

  <data>

    <table id="Wholesale.EmailSignUp">

      <row id="1">

        <col id="EmailAddress" value="joe.pesch@impaccompanies.com" />

        <col id="CompanyName" value="Impac" />

        <col id="FirstName" value="Joe" />

        <col id="LastName" value="Pesch" />

        <col id="PositionAtCompany" value="VP Software Development" />

        <col id="EntryDate" value="1/1/2011 7:00AM" />

        <col id="UpdateDate" value="1/1/2011 7:00AM" />

      </row>   

      <row id="2">

        <col id="EmailAddress" value="jpesch@impaccompanies.com" />

        <col id="CompanyName" value="Impac" />

        <col id="FirstName" value="Joe" />

        <col id="LastName" value="Pesch" />

        <col id="PositionAtCompany" value="VP Software Development" />

        <col id="EntryDate" value="1/1/2011 8:00AM" />

        <col id="UpdateDate" value="1/1/2011 8:00AM" />

      </row>   

    </table>

    <table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">

      <row id="1">

        <col id="ProductInfoId" value="EEC326F3-C445-4B44-8C42-4E4FF84FDE1E" />

        <col id="FieldName" value="TESTING" />

        <col id="DisplayText" value="This is a test" />

        <col id="Value" value="100" />

      </row>   

      <row id="2">

        <col id="ProductInfoId" value="3F5F1FB8-5023-4FCD-ACF4-2B7CD25E6043" />

        <col id="FieldName" value="TESTING_ANOTHER" />

        <col id="DisplayText" value="This is another test" />

        <col id="Value" value="200" />

      </row>   

    </table>

  </data>

</xml>

'

 

-- Temp table for inserts

create table #inserts(id varchar(100), data text)

declare @handle int, @datalen int, @txtptr binary(16)

 

-- Variables for processing

declare @tbl varchar(150), @col varchar(50), @comma varchar(1), @value varchar(max)

 

-- Get handle to XML document

exec sp_xml_PrepareDocument@handle output, @xmlData

 

-- Iterate each table in the XML

declare cur cursor for select id from openxml(@handle, '/xml/schema/table', 1) with (id varchar(50))

open cur

while 'For each table in XML' <> '' begin

  fetch next from cur into @tbl

  if @@fetch_status <> 0 break

  if 'Generate the template record' <> '' begin

    set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'

    insert into #inserts values(@tbl + '.0', 'insert into ' + @tbl + '(')

    set @comma = ''

    set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col'

    declare @id varchar(50), @type varchar(50), @length varchar(50), @nullable varchar(50), @quote varchar(50)

    declare curX cursor for

      select [id], [type], [length], [nullable], [quote]

      from openxml(@handle, @xpath, 1)

      with ([id] varchar(50), [type] varchar(50), [length] varchar(50), [nullable] varchar(50), [quote] varchar(50))

    open curX

    while 'Process each column' <> '' begin

      fetch next from curX into @id, @type, @length, @nullable, @quote

      if @@fetch_status <> 0 break

      set @col = @comma + @id

      select @datalen = datalength(data) from #inserts where id = @tbl + '.0'

      select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'

      updatetext #inserts.data @txtptr @datalen 0 @col

      set @comma = ', '

    end

    select @datalen = datalength(data) from #inserts where id = @tbl + '.0'

    select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'

    updatetext #inserts.data @txtptr @datalen 0 ') values('

    close curX

    deallocate curX

  end

  if 'Generate the values using template' <> '' begin

    set @comma = ''

    set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'

    declare curX cursor for

      select [id] from openxml(@handle, @xpath, 1) with ([id] varchar(50))

    open curX

    while 'Process each row' <> '' begin

      fetch next from curX into @id

      if @@fetch_status <> 0 break

      insert #inserts select @tbl + '.' + @id, data from #inserts where id = @tbl + '.0'

      set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row[@id=' + @id + ']/col'

      declare curY cursor for select [id], [value] from openxml(@handle, @xpath, 1) with ([id] varchar(50), [value] varchar(max))

      open curY

      set @comma = ''

      while 'Process each column' <> '' begin

        fetch next from curY into @col, @value

        if @@fetch_status <> 0 break

        set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col[@id=''' + @col + ''']'

        select @quote = [quote]

        from openxml(@handle, @xpath, 1) with ([quote] varchar(10))

        set @value = @comma + @quote + replace(@value, '''', '''''') + @quote

        select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id

        select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id

        updatetext #inserts.data @txtptr @datalen 0 @value

        set @comma = ','

      end

      close curY

      deallocate curY

      select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id

      select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id

      updatetext #inserts.data @txtptr @datalen 0 ')'

    end

    close curX

    deallocate curX

    delete from #inserts where id = @tbl + '.0'

  end

end

close cur

deallocate cur

 

select * from #inserts

drop table #inserts

 

Tags:

Append to Text Column in SQL Server

by 12. January 2011 08:49

declare @datalen int

create table #inserts(id int, data text)

insert #inserts values(1, 'testing')

select @datalen = datalength(data) from #inserts where id = 1

declare @txtptr binary(16);

select @txtptr = textptr(data) from #inserts where id = 1

updatetext #inserts.data @txtptr @datalen 0 '... more data ...'

select * from #inserts

drop table #inserts

 

Tags:

Asp.Net Url Manipulation

by 10. January 2011 16:53

public static string ToAbsoluteUri(string sPath)
{
  return HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + VirtualPathUtility.ToAbsolute(sPath);
}
 
[Obsolete("Consider using ToAbsoluteUri(sPath) instead.")]
public static string RootUrl
{
  get
  {
return HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
  }
}

Tags:

Windows Installation Cleaner - Remove Broker Applications

by 9. January 2011 19:35

Tags: