How to create Aggregate Function of SQL Server using .NET CLR

1. C# coding 

using System;

using System.Text;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

namespace hrn2k1.SqlServer

{

    [Serializable]

    [SqlUserDefinedAggregate(

        Format.UserDefined, //use clr serialization to serialize the intermediate result

        IsInvariantToNulls = true, //optimizer property

        IsInvariantToDuplicates = false, //optimizer property

        IsInvariantToOrder = false, //optimizer property

        MaxByteSize = 8000) //maximum size in bytes of persisted value

    ]

    public class Concat : IBinarySerialize

    {

        /// <summary>

        /// The variable that holds the intermediate result of the concatenation

        /// </summary>

        private StringBuilder intermediateResult;

        /// <summary>

        /// Initialize the internal data structures

        /// </summary>

        public void Init()

        {

            this.intermediateResult = new StringBuilder();

        }

        /// <summary>

        /// Accumulate the next value, not if the value is null

        /// </summary>

        /// <param name="value"></param>

        public void Accumulate(SqlString value, string Separator)

        {

            if (value.IsNull)

            {

                return;

            }

            if (this.intermediateResult.Length > 0)

                this.intermediateResult.Append(Separator).Append(value.Value);

            else

                this.intermediateResult.Append(value.Value);

        }

        /// <summary>

        /// Merge the partially computed aggregate with this aggregate.

        /// </summary>

        /// <param name="other"></param>

        public void Merge(Concat other)

        {

            this.intermediateResult.Append(other.intermediateResult);

        }

        /// <summary>

        /// Called at the end of aggregation, to return the results of the aggregation.

        /// </summary>

        /// <returns></returns>

        public SqlString Terminate()

        {

            string output = string.Empty;

            //delete the trailing comma, if any

            if (this.intermediateResult != null

                && this.intermediateResult.Length > 0)

            {

                output = this.intermediateResult.ToString();

            }

            return new SqlString(output);

        }

        public void Read(BinaryReader r)

        {

            intermediateResult = new StringBuilder(r.ReadString());

        }

        public void Write(BinaryWriter w)

        {

            w.Write(this.intermediateResult.ToString());

        }

    }

}

 2. Integrate .NET DLL into SQL Server

3. SQL Coding

CREATE AGGREGATE Concat (@input nvarchar(200),@Separator nvarchar(50)) RETURNS nvarchar(max)

EXTERNAL NAME [hrn2k1].[hrn2k1.SqlServer.Concat]

3. Use in Query

SELECT UserID,

       COUNT(InvoiceNumber) AS InvoiceCount,

       dbo.Concat(InvoiceNumber,',') AS InvoiceNumbers 

FROM Invoices

GROUP BY UserID

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


 public class SearchData
    {
        public SearchData() { }
        public int GetRowCount(string Where)
        {
            int Count = 0;
            try
            {
                using (SqlConnection con = new SqlConnection("Data Source=192.168.0.1;Initial Catalog=hrm;User ID=sa;Password=sa1234"))
                {
                    using (SqlCommand cmd = new SqlCommand("SP_GET_ROWCOUNT", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@SelectedColumns", SqlDbType.NVarChar, 500).Value = "*";
                        cmd.Parameters.Add("@FromTables", SqlDbType.NVarChar, 500).Value = "Invoices";
                        cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = Where == "" ? "1=1" : Where;
                        cmd.Parameters.Add("@Return_Value", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        Count = Convert.ToInt32(cmd.Parameters["@Return_Value"].Value);

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return Count;
        }
        public DataTable GetData(string Where, string OrderBy, int StartIndex, int PageSize)
        {
            DataTable dtData = new DataTable();
            try
            {
                int pageIndex = StartIndex / PageSize ;
                //using (SqlConnection con = new SqlConnection("Data Source=192.168.0.1;Initial Catalog=hrm;User ID=sa;Password=sa1234"))
                //{
                //    using (SqlCommand cmd = new SqlCommand("SP_GET_DATA", con))
                //    {
                //        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                //        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                //        cmd.Parameters.Add("@SelectedColumns", SqlDbType.NVarChar, 500).Value = "*";
                //        cmd.Parameters.Add("@FromTables", SqlDbType.NVarChar, 500).Value = "Invoices";
                //        cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = "1=1";
                //        cmd.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 500).Value = " Id";
                //        con.Open();
                //        cmd.ExecuteNonQuery();
                //        con.Close();


                //    }
                //}

                using (SqlDataAdapter da = new SqlDataAdapter("SP_GET_DATA", "Data Source=192.168.0.1;Initial Catalog=hrm;User ID=sa;Password=sa1234"))
                {
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                    da.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                    da.SelectCommand.Parameters.Add("@SelectedColumns", SqlDbType.NVarChar, 500).Value = "*";
                    da.SelectCommand.Parameters.Add("@FromTables", SqlDbType.NVarChar, 500).Value = "Invoices";
                    da.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = Where == "" ? "1=1" : Where;
                    da.SelectCommand.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 500).Value = OrderBy==""?"Id":OrderBy;
                    da.Fill(dtData);
                    da.Dispose();

                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dtData;
        }
    }

    public class Entity
    {
    }
    public interface IDataAccess<T>
    {

        T Map(DataRow datarow);
        T Map(SqlDataReader datareader);

        T Get(string Where);
        DataTable GetRows(string Where, string OrderBy);
        List<T> GetList(string Where, string OrderBy);
        int GetRowCount(string Where);
        DataTable GetDataRows(string Where, string OrderBy, int StartRowIndex, int PageSize);
        List<T> GetDataList(string Where, string OrderBy, int StartRowIndex, int PageSize);

        int Save(T obj);
        int Save(T obj, SqlConnection objCon, SqlTransaction objTrans);
        void Delete(string Where);
        void Delete(string Where, SqlConnection objCon, SqlTransaction objTrans);


    }


    public class DalEntity : IDataAccess<Entity>
    {

        public Entity Map(DataRow datarow)
        {
            Entity obj  = new Entity();
            try
            {
                //obj.

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return obj;
        }

        public Entity Map(SqlDataReader datareader)
        {
            Entity obj = new Entity();
            try
            {
             
                //obj.

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return obj;
        }

        public Entity Get(string Where)
        {
            throw new NotImplementedException();
        }

        public DataTable GetRows(string Where, string OrderBy)
        {
            throw new NotImplementedException();
        }

        public List<Entity> GetList(string Where, string OrderBy)
        {
            throw new NotImplementedException();
        }

        public int GetRowCount(string Where)
        {
            throw new NotImplementedException();
        }

        public DataTable GetDataRows(string Where, string OrderBy, int StartRowIndex, int PageSize)
        {
            throw new NotImplementedException();
        }

        public List<Entity> GetDataList(string Where, string OrderBy, int StartRowIndex, int PageSize)
        {
            throw new NotImplementedException();
        }

        public int Save(Entity obj)
        {
            throw new NotImplementedException();
        }

        public int Save(Entity obj, SqlConnection objCon, SqlTransaction objTrans)
        {
            throw new NotImplementedException();
        }
    }

    public class CodeHelper
    {
        public string PackedIntoTryCatch(string Code)
        {

            return string.Format(@"try
            {0}
            {2}
            {1}
            catch (Exception ex)
            {0}
                throw ex;
            {1}", "{", "}", Code);


        }
    }