using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

using Microsoft.SqlServer.Server;

using PremierSoftware.SqlUtilities;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void up_CLR_PopulateCollectionFromIDs(SqlBytes TableName, SqlBytes SelectOrdinals, SqlBytes DisplayMemberOrdinals, SqlBytes IDs, SqlBoolean UseNotIn)
    {
        using (SqlConnection con = new SqlConnection("Context connection=true"))
        {
            int[] selectOrdinals = Converters.ConvertSqlBytesToInts(SelectOrdinals);
            int[] displayMemberOrdinals = Converters.ConvertSqlBytesToInts(DisplayMemberOrdinals);
            int[] ids = Converters.ConvertSqlBytesToInts(IDs);

            StringBuilder idsString = new StringBuilder();

            #region Generate IDs string
            for (int i = 0; i < ids.Length; i++)
            {
                idsString.Append(ids[i]);

                if (i != ids.Length - 1)
                    idsString.Append(", ");
            }
            #endregion

            StringBuilder command = new StringBuilder("SELECT ");

            #region Generate Select Fields
            for (int i = 0; i < selectOrdinals.Length; i++)
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "up_CLR_GetColumnName";
                    cmd.Parameters.Add(new SqlParameter("TableName", TableName));
                    cmd.Parameters.Add(new SqlParameter("Ordinal", selectOrdinals[i]));

                    con.Open();

                    command.Append(cmd.ExecuteScalar().ToString());

                    con.Close();
                }

                if (i != selectOrdinals.Length - 1)
                    command.Append(", ");
            }
            #endregion

            #region Generate Select DisplayMember
            if (displayMemberOrdinals.Length > 0)
            {
                command.Append(", ");

                for (int i = 0; i < displayMemberOrdinals.Length; i++)
                {
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "up_CLR_GetColumnName";
                        cmd.Parameters.Add(new SqlParameter("TableName", TableName));
                        cmd.Parameters.Add(new SqlParameter("Ordinal", displayMemberOrdinals[i]));

                        con.Open();

                        command.Append(cmd.ExecuteScalar().ToString());

                        con.Close();
                    }

                    if (i != selectOrdinals.Length - 1)
                        command.Append(" + ' ' + ");
                }

                command.Append("as DisplayMember");
            }
            #endregion

            #region Generate Where
            if (ids.Length > 0)
            {
                command.Append(" WHERE T_");
                command.Append(TableName);
                command.Append(".");
                command.Append(TableName);
                command.Append("ID ");

                if (UseNotIn)
                    command = command + "not ";

                command.Append("in ( @IDs )");
            }
            #endregion

            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = command.ToString();
                cmd.Parameters.Add(new SqlParameter("@IDs", idsString));

                con.Open();

                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
        }
    }
};