Split Function in Sql Server

Input: 

Row Data:  100,200,300,4000

SplitOn : ','

Output as Table

-----------------

Id      Data

-----------

1        100

2        200

3        300

4        4000

ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS 
BEGIN
    Declare @Cnt int
    Set @Cnt = 1


    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))


        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
   
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))


    Return
END

IMultipleResults using LINQ

There may be situation where you want to retrieve multiple result from a single stored procedure, this was easy process using previous dataset and SQLConnection and SQLParameters, but in LINQ we need to do some coding to get it worked.

The LINQ only support SingleResult by default so inorder for your LINQ.dbml to support multipleresults you need to do a manual coding to get it done.

Below are the following steps to get it done..

Step 1:  First of all you need to retrieve the singleresultset class template.. Usually when you drag and drop a particular storedprocedure into the dbml file it generates a singleresultset class template in the form of partial class. Make a copy of each partial resultset. The singleresultset class template usually is available in the SluiceDb.designer.cs.

The sample of singleresultset template

public partial class AssetsProject_SelectByAssetSearchResult
    {

        private int? _ID;

        private int? _AssetsID;

        private string _Title;
       
        private string _FileName;

        private string _URL;

        private string _CreatedBy;

        private string _UploadedDate;

        private string _FileSize;

        public AssetsProject_SelectByAssetSearchResult()
        {
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_ID", DbType = "Int")]
        public int? ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this._ID = value;
                }
            }
        }


        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_AssetsID", DbType = "Int")]
        public int? AssetsID
        {
            get
            {
                return this._AssetsID;
            }
            set
            {
                if ((this._AssetsID != value))
                {
                    this._AssetsID = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Title", DbType = "NVarChar(4000)")]
        public string Title
        {
            get
            {
                return this._Title;
            }
            set
            {
                if ((this._Title != value))
                {
                    this._Title = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_FileName", DbType = "NVarChar(350)")]
        public string FileName
        {
            get
            {
                return this._FileName;
            }
            set
            {
                if ((this._FileName != value))
                {
                    this._FileName = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_URL", DbType = "NVarChar(350)")]
        public string URL
        {
            get
            {
                return this._URL;
            }
            set
            {
                if ((this._URL != value))
                {
                    this._URL = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_CreatedBy", DbType = "NVarChar(101)")]
        public string CreatedBy
        {
            get
            {
                return this._CreatedBy;
            }
            set
            {
                if ((this._CreatedBy != value))
                {
                    this._CreatedBy = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_UploadedDate", DbType = "NVarChar(30)")]
        public string UploadedDate
        {
            get
            {
                return this._UploadedDate;
            }
            set
            {
                if ((this._UploadedDate != value))
                {
                    this._UploadedDate = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_FileSize", DbType = "NVarChar(33)")]
        public string FileSize
        {
            get
            {
                return this._FileSize;
            }
            set
            {
                if ((this._FileSize != value))
                {
                    this._FileSize = value;
                }
            }
        }
    }

Step 2: After you are done with creating the partial resulset for each resultset available in the stored procedure of multiple resultset, delete the stored procedure from the dbml which was helpful for creating the resultset class template. Since you are going to create the class template and the way to manipulate it manually you can always delete those copy of the stored procedure which was helpful to create resultset class template  rrom the dbml which otherwise will create a replicated copy which we dont need.

Step 3:  Copy and paste the copy of each singleresultset class template in SluiceDb.cs.

Step 4: Having known the template of each resultset available in the stored procedure you can create your own function to retrieving those multiple resultsets.

 [FunctionAttribute(Name = "dbo.Requirements_SelectByAssetSearch")]
        [ResultType(typeof(AssetsProject_SelectByAssetSearchResult))]
        [ResultType(typeof(AssetsRequirement_SelectByAssetSearchResult))]
        public IMultipleResults AssetsProjectRequirement(int projectId,int? requirementId, int? Id, string title,int owner)
        {
                IExecuteResult result = ExecuteMethodCall(this, ((MethodInfo)(MethodBase.GetCurrentMethod())), projectId, requirementId, Id, title, owner);
            if (result != null) return (IMultipleResults)(result.ReturnValue);
            return null;
        }

Step 5: Having we are done with manual invocation of Multiple resultset itz time to know how to invoke from the front end aspx page.

  using (SluiceDbDataContext sluiceDc = new SluiceDbDataContext())

                        {

                            IMultipleResults projectRequirement = sluiceDc.AssetsProjectRequirement(searchInfo.ProjectId, searchInfo.RequirementId, searchInfo.AssetId, searchInfo.Title, searchInfo.MemberId);

                            List projects =
                            projectRequirement.GetResult().ToList();

                            List requirements =
                            projectRequirement.GetResult().ToList();


                            rdgListAssetsProject.DataSource = projects;
                        }

For Refrence

http://beyondrelational.com/blogs/ibhadelia/archive/2010/07/17/how-to-get-multiple-result-set-of-procedure-using-linq-to-sql.aspx

For any queries you can very well email me...

Connect your Facebook friends in your application

The Basics:

Facebook application can be developed using the following methods

1.       XFBML.

2.       Javascript with calls to the Javascript client library.

3.       Code in any language, with calls to the Facebook REST API.

I have implemented the facebook with the help of Facebook REST API for selecting the friends from your login and posting the invitation to the wall, the invitation is a url for getting connected to my achieve street site.

FYI: The XFBML is going to be expired in January and will be out of scope, so you think twice before getting it implemented.

Configuring Connect Application

Integrating/developing an application with Facebook requires an API key and a secret key with whom Facebook will authenticate your application on Facebook platform - this is called oauthentication.

FYI:   OAuth: The simplest definition can be OAuth protocol enables users to provide third-party access to their web resources without sharing their passwords

How to Retrieve the API Key and Secret Key

1.       Navigate to Facebook developer center and request for new application which can be obtained from your facebook login.

2. Choose an appropriate application name (My application's name is AchieveStreet).
3. Navigate to basic tab, there you can see API key/ secret key is given.
4. Provide a small description about your application.
5. Navigate to connect tab. Provide the applications full.
6. Navigate to Advanced tab and on Advanced Settings, choose web.
7. Choose sendbox mode enable/disable. Enable allows only the developers of application to see it.
8. Save your settings.

The sample application for the facebook connect can be found in

http://www.codeproject.com/KB/aspnet/fb_connect_asp_net.aspx

How to make connection with the Facebook and Posting into the wall

API’s needed:

Facebook.Web.dll, Facebook.dll

How to establish the connection with Facebook

Api api = new Api(connectAuthentication.CurrentSession);

Posting to the wall

api.Stream.Publish(strbody, null, null, target user id, source user id);

the userid is the unsigned long int.

For posting into the wall you need to give permission for the login user to write the message into wall below is the code for that to do from the application

function setGrandPermission(isRead, isPublish) {

    try {

        FB_RequireFeatures(["Api"], function () {

            //Allow Permission for accessing the friends

            var api = FB.Facebook.apiClient;

            FB.Connect.requireSession(function () {

                var session = api.get_session();

                api.users_hasAppPermission("read_stream", function (hasRead) {

                    api.users_hasAppPermission("publish_stream", function (hasPublish) {

                        var permissions = [];

                        if (isRead)

                            if (!hasRead) permissions.push("read_stream");

                        if (isPublish)

                            if (!hasPublish) permissions.push("publish_stream");

                        if (permissions.length > 0) {                        

                            FB.Connect.showPermissionDialog(permissions.join(","), function (authorized) {                            

                            });

                        }

                    });

                });

            });

        });

    }

    catch (err) {throw err.Message;}

}

Using JQuery facebook Multi-Friend Selection Plugin using Javascript API, Graph API

I would like to thank Thiru and Kumuraguru for helping me to know this topic.

The Javascript API is used to get connected with the Facebook to do more things on Javascript API, to know more on this visit

http://developers.facebook.com/docs/reference/javascript/

The Graph API to retrieve the friends list, to know more about Graph API visit

http://developers.facebook.com/docs/api

To know more about the facebook api's feel free to email me.

Function to convert Html code to proper character.

Input   : it's where ' is a html code of '(oppostrope).

Output : it'z

ALTER FUNCTION [dbo].[HTMLDecode]

(@txt NVARCHAR(4000))

RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @Start INT

DECLARE @HTMLCode NVARCHAR(5)

DECLARE @DecodeTxt NVARCHAR(4000)

SET @DecodeTxt = @txt

SET @Start = 32

WHILE @Start<=126

BEGIN

SET @HTMLCode = '&#' + CAST(@Start AS NVARCHAR(5))+';'

SELECT @DecodeTxt=REPLACE(@DecodeTxt,@HTMLCode,CAST(NCHAR(@Start) AS NVARCHAR(5)))

SET @Start = @Start + 1

END

RETURN @DecodeTxt

END

Note:
From 32  to 126 is the ascii character number so for each there is a equvilent character.
For each the character between 32 to 126 there is html value starting as &#32; to &#126; what we are doing is we are replacing this html value to the equivalent character.

For any questions you can mail me..