Techniques for In-Clause and SQL Server

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Xml;


namespace InClause
{
    public class TestClassDac
    {
        private const string  DatabaseKey = "InClause";

        public event EventHandler<BuildDataEventArgs> BuildDataEvent;

        public void BuildTestData( int howMany)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO SomeString (Id, SomeString) Values (@Id, @SomeString)";
            SqlParameter parmId = cmd.CreateParameter();
            parmId.DbType = DbType.Int32;
            parmId.ParameterName = "@Id";
            cmd.Parameters.Add(parmId);

            SqlParameter parmSomeString = cmd.CreateParameter();
            parmSomeString.DbType = DbType.String ;
            parmSomeString.ParameterName = "@SomeString";
            parmSomeString.Size = 50;
            cmd.Parameters.Add(parmSomeString);
            const string someString = "monkies ";

            SqlCommand cmdDelete = con.CreateCommand();
            cmdDelete.CommandType = CommandType.Text;
            //cmdDelete.CommandText = String.Format("Delete Top(10000) From SomeString where SomeString like '{0}%'", someString);
            cmdDelete.CommandText = "TRUNCATE TABLE SomeString";
            SqlCommand cmdCheckpoint = con.CreateCommand();

            cmdCheckpoint.CommandText = "Checkpoint";
            cmdCheckpoint.CommandType = CommandType.Text;
            con.Open();

            //long rowCount;
            //do
            //{
            //    rowCount = cmdDelete.ExecuteNonQuery();
            //    cmdCheckpoint.ExecuteNonQuery();

            //} while (rowCount > 0);
            cmdDelete.ExecuteNonQuery();
            cmdCheckpoint.ExecuteNonQuery();
            cmd.Prepare();
            for (int i = 0; i < howMany; i++)
            {
                parmId.Value = i;
                parmSomeString.Value =  someString + i.ToString() ;
                cmd.ExecuteNonQuery();
#if DEBUG
                if (i%10000 == 0)
                {
                    Debug.WriteLine("Now at " + i.ToString());
                }
#endif

                if (i % 10000 == 0)
                {
                    cmdCheckpoint.ExecuteNonQuery();
                    EventHandler<BuildDataEventArgs> temp = BuildDataEvent;
                    if (temp != null)
                        temp(this, new BuildDataEventArgs(i));

                }

            }
            cmdDelete.Dispose();
            cmdCheckpoint.Dispose();
            cmd.Dispose();
            con.Dispose();

        }

        const string sqlGetUsingDynamic = "SELECT * FROM SomeString (NOLOCK) WHERE Id IN ({0})";
        public List<TestClass> GetUsingDynamic(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();

            string sql = String.Format(sqlGetUsingDynamic, ListToDelimitedString(ids, delimDefault));

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);
                    }
                }

            }
            
            return results;
        }

        const string sqlGetUsingParamatized = "SELECT * FROM SomeString (NOLOCK) WHERE ";
        public List<TestClass> GetUsingParamatized(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();
                string sql = sqlGetUsingParamatized + InClauseHelper(ids, fieldId, cmd.Parameters);
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }

            return results;
        }

        
        public string InClauseHelper(List<long> ids, string field, SqlParameterCollection parms)
        {
            List<int> schedule = new List<int>();
            schedule.Add(10);

            schedule.Add ( 40);
            schedule.Add(50);
            schedule.Add(200);
            schedule.Add(1000);

            return InClauseHelper(ids, field, parms, schedule,0);

        }
        /// <summary>
        /// careful if using this, might need some tuning.
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="field"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public string InClauseHelper(List<long> ids, string field, SqlParameterCollection parms, List<int> schedule, int startParameterCount)
        {
            const string formatInitial = " {0} in (@p{1}";
            const string formatParm = "@p{0}";
            const string formatCommaParm = ",@p{0}";
            const string formatCloseOr = ") or";
            const string formatClose = ")";
            if (ids == null || ids.Count == 0)
            {
                return string.Empty;
            }

            //int idPosition = 0; //track where in ids
            int countParms = 0;
            int startParm = parms.Count;
            int  inClauseUniqueNum = startParameterCount;


            StringBuilder sb = new StringBuilder(ids.Count * 5); //rough estimate
            SqlParameter parm = null;
            
            int lastIdPosition = ids.Count-1;
            
            long lastId = ids[lastIdPosition];
            //loop through schedule, make sql and parms per schedule
            for (int iPoint=0 ;  iPoint<schedule.Count; iPoint++)
            {
                //make inclause for each point in schedule
                sb.AppendFormat(formatInitial , field,inClauseUniqueNum);
                parm = parms.Add(String.Format(formatParm, inClauseUniqueNum++), SqlDbType.BigInt);

                countParms = countParms + schedule[iPoint];
                for (int i=1; i<schedule[iPoint];i++)
			    {
                    
                    sb.AppendFormat(formatCommaParm ,inClauseUniqueNum);
                    parm = parms.Add(String.Format(formatParm,inClauseUniqueNum++),SqlDbType.BigInt);
                    
                }
                if (countParms >= ids.Count)
                {
                    //done
                    break;
                }
                if (iPoint < schedule.Count - 1)
                {
                    sb.Append(formatCloseOr);
                }
                if (countParms < lastIdPosition && iPoint + 1 == schedule.Count)
                {
                    schedule.Add(schedule[schedule.Count - 1]);
                }

            }
            sb.Append(formatClose);

            //now set values
            for (int iPos = 0; iPos < ids.Count; iPos++)
            {

                parms[iPos + startParm].Value = ids[iPos];
            }
            if (ids.Count + startParm < parms.Count)
            {
                for (int iRemainder = ids.Count+startParm   ; iRemainder < parms.Count; iRemainder++)

                {
                    parms[iRemainder].Value = lastId;
                }
            }


            //const int breakAt = 1000;
            //int multipleThousand = ids.Count % breakAt;
            //StringBuilder sb = new StringBuilder(ids.Count * 5 + (field.Length + 10) * multipleThousand);

            //sb.AppendFormat("{0} in (@pIn{1}", field, idCount);
            //SqlParameter parm = parms.Add("pIn" + idCount++,SqlDbType.BigInt);
            //parm.Value = ids[0];

            //for (int i = 1; i < ids.Count; i++)
            //{
            //    if (i % breakAt == 0)
            //    {
            //        sb.AppendFormat(") or {0} in (", field);
            //    }
            //    sb.AppendFormat(",@pIn{0}", idCount);
            //    parm = parms.Add("@pIn" + idCount++, SqlDbType.BigInt);
            //    parm.Value = ids[i];
            //}
            //sb.Append(")");

            return sb.ToString();

        }

        /// <summary>
        /// @Ids
        /// </summary>
        const string parmIds = "@Ids";

        const string sqlGetUsingSPDelimited = "GetUsingDelimitedTempTable";
        public List<TestClass> GetUsingSPDelimitedTempTable(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();
            string listString = ListToDelimitedString(ids, delimDefault);

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingSPDelimited;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.VarChar);
                parm.Value = listString;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }

        const string sqlGetUsingSPDelimitedPermTable = "GetUsingDelimitedParmTable";
        public List<TestClass> GetUsingSPDelimitedParmTable(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();
            string listString = ListToDelimitedString(ids, delimDefault);

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingSPDelimitedPermTable;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.VarChar);
                parm.Value = listString;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }


        const string sqlGetUsingDelimitedFunctionTable = "GetUsingDelimitedFunctionTable";
        public List<TestClass> GetUsingSPDelimitedFunctionTable(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();
            string listString = ListToDelimitedString(ids, delimDefault);

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingDelimitedFunctionTable;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.VarChar);
                parm.Value = listString;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }

        const string sqlGetUsingXMLXQuery = "GetUsingXMLXQuery";
        public List<TestClass> GetUsingSPXMLXQuery(List<long> ids)
        {
            
            List<TestClass> results = new List<TestClass>();
            string xml = ListToXML(ids);

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingXMLXQuery;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.Xml);
                parm.Value = xml;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }

        const string sqlGetUsingTypeValue = "GetUsingTypeValue";
        public List<TestClass> GetUsingSPTypeValue(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();

            List<SqlDataRecord> idsDataRecords = ListToSqlDataRecord(ids);

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingTypeValue;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.Structured);
                parm.TypeName ="dbo.IdsTableType";
                parm.SqlValue  = idsDataRecords ;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }


        const string sqlGetUsingDelimitedParmTableUsingUniqueBigInt = "GetUsingDelimitedParmTableUsingUniqueBigInt";
        public List<TestClass> GetUsingSPDelimitedParmTableWithUniqueBigInt(List<long> ids)
        {
            List<TestClass> results = new List<TestClass>();
            string listString = ListToDelimitedString(ids, delimDefault );

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = sqlGetUsingDelimitedParmTableUsingUniqueBigInt;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.VarChar);
                parm.Value = listString;
                con.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TestClass obj = new TestClass();
                        FillObject(dr, obj);
                        results.Add(obj);

                    }
                }

            }
            return results;
        }


        const string fieldId = "ID";
        const string fieldName = "SOMESTRING";
        public void FillObject(IDataReader dr, TestClass obj)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                switch (dr.GetName(i).ToUpper())
                {
                    case fieldId:
                        obj.Id = dr.GetInt32(i);
                        break;
                    case fieldName:
                        obj.Name = dr.GetString(i);
                        break;
                }
            }
        }


        public void ClearSQLCache()
        {
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "dbcc freesystemcache ('All')";
                con.Open();

                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }

        }
        /// <summary>
        /// {0}{1} for formatting
        /// </summary>
        const string  delimItemFormat = "{0}{1}";
        const string delimDefault = ",";

        /// <summary>
        /// takes a List and puts them together with the delimiter.
        /// </summary>
        /// <param name="items"></param>
        /// <param name="delimiter"></param>
        /// <returns></returns>
        public string ListToDelimitedString(List<long> items, string delimiter)
        {

            if (items.Count == 0)
                return string.Empty;
            //take guess at stringbuilder size
            StringBuilder sb = new StringBuilder((delimiter.Length + 5) * items.Count);

            sb.AppendFormat(items[0].ToString());
            
            for (int i = 1; i < items.Count; i++)
            {
                sb.AppendFormat(delimItemFormat, delimiter, items[i]);
            }
            return sb.ToString();
        }

        const string startXML = "<L>";
        const string endXML = "</L>";
        const string itemXML = "<I>{0}</I>";
        const string itemStart ="<I>";
        const string itemEnd = "</I>";
        /// <summary>
        /// changes list to &lt;L&gt;&lt;I&gt;&lt;/I&gt;&lt;/L&gt;
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        public string ListToXML(List<long> items)
        {

            if (items.Count == 0)
                return string.Empty;

            StringBuilder sb = new StringBuilder((10 * items.Count) + 7); //estimate
            sb.Append(startXML);
            for (int i = 0; i < items.Count; i++)
            {
                sb.AppendFormat(itemXML ,items[i]);
            }
            sb.Append(endXML);
            return sb.ToString();
        }


        public string ListToXMLwithXmlTextWriter(List<long> items)
        {
            string xml = string.Empty;
            if (items.Count == 0) return xml;
            const string root = "L";
            const string element = "I";
            Encoding encoding = Encoding.ASCII;
            using (MemoryStream ms = new MemoryStream())
            {
                using (XmlTextWriter writer = new XmlTextWriter(ms,encoding))
                {
                    writer.WriteStartElement(root);
                    foreach (long item in items)
                    {
                        writer.WriteElementString(element, item.ToString());
                    }
                    writer.WriteEndElement();
                    writer.Flush();
                    
                    ms.Position = 0;
                    using (StreamReader sr = new StreamReader(ms,encoding))
                    {
                        
                        return sr.ReadToEnd();
                        
                    }

                } //dispose writer

            } //dispose stream
            
        }

        public string ListToXMLUsingStream(List<long> items)
        {

            if (items.Count == 0)
                return string.Empty;

            using( MemoryStream ms = new MemoryStream(items.Count * 9)) //guess
            {
                using (StreamWriter sw = new StreamWriter(ms,Encoding.ASCII))
                {
                    sw.Write(startXML);
                    for (int i = 0; i < items.Count; i++)
                    {
                        sw.Write(itemStart);
                        sw.Write(items[i]);
                        sw.Write(itemEnd);
                    }
                    sw.Write(endXML);
                    sw.Flush();
                    ms.Position = 0;
                    using (StreamReader sr = new StreamReader(ms,Encoding.ASCII))
                    {                        
                        return sr.ReadToEnd();   
                    }
                }
            }
            
            
        }
        

        public List<SqlDataRecord> ListToSqlDataRecord(List<long> items)
        {
            List<SqlDataRecord> records = new List<SqlDataRecord>();
            SqlDataRecord recordProto;
            //SqlDataRecord record;
            SqlMetaData[] metas = new SqlMetaData[]{new SqlMetaData("Id",SqlDbType.BigInt)};

            foreach (long item in items)
            {
                recordProto = new SqlDataRecord(metas);
                recordProto.SetInt64(0, item);
                records.Add(recordProto);

            }
            return records;
            
        }
    }

    public class BuildDataEventArgs : EventArgs
    {
        private int _currentNumber;

        public BuildDataEventArgs(int currentNumber)
        {
            _currentNumber = currentNumber;
        }
        public int CurrentNumber
        {
            get { return _currentNumber; }
            set { _currentNumber = value; }
        }
    }

}