SQL Tree down hierarchical query using Common Table Expression (CTE)

October 20, 2017 Leave a comment

To make SQL Tree down hierarchical query we can use Common Table Expression. This tutorial ( https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/) is good for starting up. But to make tree order, we must add ordering condition. Here is the complete sample query for table Product that has self-referencing field(ParentID):

;with cte as (
select ID,Name,1 as Level ,
CAST(RIGHT('0000000000'+CAST(Product.ID AS VARCHAR(10)),10) AS varchar(255)) AS Paths
 from Product where ParentID is null
UNION ALL
select sub.ID,sub.Name,cte.Level+1 as Level, 
CAST(cte.Paths + '/'+ RIGHT('0000000000'+CAST(sub.ID AS VARCHAR(10)),10) AS varchar(255)) 
AS Paths
 from Product sub
inner join cte ON sub.ParentID= cte.ID
)
select * from cte ORDER BY cte.Paths

 

Advertisements
Categories: SQL Tags:

Add Start and End/Finish date in TFS Tasks and synchronize to Project Tasks

October 17, 2017 Leave a comment

Add Start and End date in TFS Tasks:

  • Goto C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\ (adjust according to your environment).
  • Then download/export Task xml configuration using witadmin:
witadmin exportwitd /collection:http://tfs.my.com/tfs/mycols /p:"my project" /f:Task.xml /n:Task
  • Add Start and End date in Task configuration :
 <FIELDS> 
...
 <FIELD name="Start Date" refname="my.StartDate" type="DateTime" />
 <FIELD name="End Date" refname="my.EndDate" type="DateTime" />
...
</FIELDS>
 <FORM>
...
 <Control FieldName="my.StartDate" Type="DateTimeControl" Label="Start Date" LabelPosition="Left" Format="Short"/>
 <Control FieldName="my.EndDate" Type="DateTimeControl" Label="End Date" LabelPosition="Left" Format="Short"/>
...
 </FORM>
  • Upload/import edited Task xml configuration using witadmin :
witadmin importwitd /collection:http://tfs.my.com/tfs/mycols /p:"my project" /f:Task.xml
  • Result:

Synchronize Start and End date to Project Tasks:

  • Goto C:\Program Files\Common Files\microsoft shared\Team Foundation Server\14.0\ (adjust according to your environment)
  • Download mappingfile using TFSFieldMapping:
TFSFieldMapping download /collection:http://tfs.my.com/tfs/mycols /teamproject:"my project" /mappingfile:MapFile
  • Add Start and End Date definition and map to Start & Finish Date in MapFile:
 <Mapping WorkItemTrackingFieldReferenceName="My.StartDate" ProjectField="pjTaskStart" ProjectName="Start Date" /> 
 <Mapping WorkItemTrackingFieldReferenceName="My.EndDate" ProjectField="pjTaskFinish" ProjectName="Finish Date" />
  • Upload edited MapFile using TFSFieldMapping:
TFSFieldMapping upload /collection:http://tfs.my.com/tfs/mycols /teamproject:"my project" /mappingfile:MapFile
  • Result:

Categories: Project, TFS Tags: ,

Use Boolean Logic for simplifying SQL

May 23, 2017 Leave a comment

Using Boolean Logic instead of Case in writing WHERE clause as described in http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx  , is really useful trick, it simplify SQL Query very much. And I’m sure every SQL programmer will fell quite the same way if the try it. I’ve already used it for several times.  So, Thanks for that article.

Categories: SQL Tags:

SQL Helper

March 21, 2017 Leave a comment

I’ve create simple tool for us to ease doing Database coding using ADO.Net. That tool wraps ADO.Net functions and provides helper to map SQL Field to Property of Entity Object. It also possible for mapping SQL Field that resulted from Join Query to Property of Composite Object. And I’ve add an example to make it easier to follow.

It is actually based on http://aapl.codeplex.com/, but I’ve change its mapping function completely so can map SQL join field to property of composite object, cause the original version only cope with simple DTO (Data Transfer Object).

So please have a look and enjoy : https://github.com/pakdanan/sqlhelper . And I’m very pleased for any responses and critics.

Categories: .Net, SQL

Beware with Culture setting when Converting Value

January 11, 2016 Leave a comment

Not only affect in converting or parsing Datetime, but Culture setting also affect in converting value, e.g. from double to integer. I’ve experienced it myself (you can try it if you don’t trust me 🙂 ).

So it’s always safer if you add System.Globalization.CultureInfo.InvariantCulture parameter when Converting or Parsing any type of values

Or

Define CultureInfo that we used in web.config

Have a nice coding 🙂

 

Categories: .Net

Utilizing standard Sharepoint dialog and application pages, case: add capability to upload picture from computer in List form

December 18, 2015 Leave a comment

Standard Sharepoint dialog and application pages can be useful for making Sharepoint customization easier.

Here, I want to customize add and edit List forms to add capability to upload picture from computer to picture library and put the uploaded picture in picture field in the add or edit List form.

There are two standard Sharepoint application pages for uploading picture that I can use, i.e., Upload.aspx and RteUploadDialog.aspx, different is RteUploadDialog.aspx have upload destination option.

Lets get started:

1). I’ve List with picture field called ‘Foto’. The add and edit forms were built using Infopath. To add upload picture from computer capability, I add button called ‘Upload Foto’ to load Sharepoint upload file dialog. I also add two ‘Foto’ field controls with different type, i.e., picture and text type.

image

2). Then write javascript to load Sharepoint upload dialog and put the uploaded picture  in picture field. As usual, put the javascript code in content editor webpart or script editor webpart (in Sharepoint 2013). Cause I want to put the picture in the specified picture library, i use Upload.aspx. Here’s the code (don’t forget to put JQuery reference) :


$(‘document’).ready(function () {
$(‘input[value=”Upload Foto”]’).on(‘click’, function () {
var  picturesLibraryGuid =  ‘{2F2F4107-D023-419F-8290-ECF7F1374A2F}’;
var  options =
{
url: L_Menu_BaseUrl + “/_layouts/RteUploadDialog.aspx?LCID=1033&Dialog=UploadImage&UseDivDialog=true”,
title: “Upload a picture”,
dialogReturnValueCallback: function (result, value) {
if (result == SP.UI.DialogResult.OK) {
var srcval= window.location.protocol + “//” + window.location.host+$(value).attr(‘src’);
$(‘input[originalid=”V1_I1_T3″]’).val(srcval);
$(‘input[originalid=”V1_I1_T3″]’).trigger( “blur” ); // to trigger refresh as happened in manual action
}
}
};
SP.UI.ModalDialog.showModalDialog (options);
});
});

If you want to use RteUploadDialog.aspx instead, just change the options like this :

        var  options =
{
url: L_Menu_BaseUrl + “/_layouts/RteUploadDialog.aspx?LCID=1033&Dialog=UploadImage&UseDivDialog=true”,
title: “Upload a picture”,
dialogReturnValueCallback: function (result, value) {
if (result == SP.UI.DialogResult.OK) {
var srcval= window.location.protocol + “//” + window.location.host+$(value).attr(‘src’);
$(‘input[originalid=”V1_I1_T3″]’).val(srcval);
$(‘input[originalid=”V1_I1_T3″]’).trigger( “blur” ); // to trigger refresh as happened in manual action
}
}
};

3). And here the result:

image

image

 

References: http://omlin.blogspot.co.id/2011/07/spuimodaldialog-sharepoint.html and http://jeffywang.blogspot.co.id/2013/10/upload-images-to-sharepoint-library.html

Categories: Sharepoint Tags:

Processing multiple data in a single call SQL Query

September 22, 2015 Leave a comment

Say you want to insert multiple rows of data in single shot query, how you accomplished that ?

There are several ways. Two of them I’ve used, they are:

1. Via XML parameter.

Bundle multiple data values into XML then pass to a procedure or statement. In a procedure or statement, use Nodes method to process that data in a single call. Here’s one of the example I’ve found in the Net : http://www.thinkingcog.com/post/2014/02/15/Inserting-multiple-rows-in-SQL-Server-database.aspx

2. Via Table-Valued parameter.

Here you have to create table type first before we can use table-value parameter in procedure. Completed how-to and sample can be found in MSDN link here : https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx . But remember it only available at SQL 2008 or higher.

Categories: SQL Tags: