SQL Query Aggregator

sknake 0 192 Views

This is a query aggregator that stacks up queries and parameters until you hit the hard limit of 2010 set by the SQL Server/Driver, or you call .RunQuery()

Scott Knake
Custom Software
Apex Software

[code=c#]
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace daniweb
{
  /// <summary>
  /// This class is designed to aggregate large upserts in to a single transaction
  /// </summary>
  internal sealed class QueryAggregator
  {
    /// <summary>
    /// Maximum number of params SQL Server allows in a transaction
    /// </summary>
    private const int MaxParametersAllowed = 2010;
    /* -------------------------------------------------------------------- */
    private List<string> _query;
    private List<SqlParameter> _parameters;
    private SqlParameter[] _startupParameters;
    private SqlConnection _connection;
    private bool _executed;
    private UInt64 _pCnt;
    /* -------------------------------------------------------------------- */
    /// <summary>
    /// True if at least one query was ran
    /// </summary>
    public bool Executed { get { return _executed; } }
    /* -------------------------------------------------------------------- */
    public QueryAggregator(SqlConnection Connection)
    {
      if (Connection == null)
        throw new ArgumentNullException("Connection");
      _query = new List<string>();
      _parameters = new List<SqlParameter>();
      _startupParameters = null;
      _executed = false;
      _connection = Connection;
      _pCnt = default(UInt64);
    }
    /* -------------------------------------------------------------------- */
    /// <summary>
    /// Aggregates a query for large upserts
    /// </summary>
    /// <param name="Parameters">Parameters that are common to all queries</param>
    public QueryAggregator(SqlConnection Connection, SqlParameter[] Parameters)
      : this(Connection)
    {
      _startupParameters = Parameters;
      SetDefaults();
    }
    /* -------------------------------------------------------------------- */
    private void SetDefaults()
    {
      if (_query == null)
        _query = new List<string>();
      else
        _query.Clear();

      if (_parameters == null)
        _parameters = new List<SqlParameter>();
      else
        _parameters.Clear();

      if ((_startupParameters != null) && (_startupParameters.Length > 0))
        _parameters.AddRange(_startupParameters);
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query)
    {
      AddQuery(Query, (null as List<SqlParameter>));
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query, SqlParameter Parameter)
    {
      if (Parameter == null)
        throw new ArgumentNullException("Parameter");
      List<SqlParameter> lst = new List<SqlParameter>();
      lst.Add(Parameter);
      AddQuery(Query, lst);
    }
    /* -------------------------------------------------------------------- */
    public void AddQuery(string Query, List<SqlParameter> Parameters)
    {
      if (Parameters == null)
      {
        _query.Add(Query);
      }
      else
      {
        if ((_parameters.Count + Parameters.Count) > MaxParametersAllowed)
          RunQuery();
        _query.Add(Query);
        _parameters.AddRange(Parameters.ToArray());
      }
    }
    /* -------------------------------------------------------------------- */
    public void RunQuery()
    {
      if (_query.Count > 0)
      {
        _executed = true;
        //fix me
        //SQL.Main.Execute(_query.GetText(), _parameters);
        using (SqlCommand cmd = new SqlCommand(GetText(_query), _connection))
        {
          cmd.Parameters.AddRange(_parameters.ToArray());
          cmd.ExecuteNonQuery();
        }
      }

      SetDefaults();
    }
    /* -------------------------------------------------------------------- */
    public string GetParameterName()
    {
      if (_pCnt == UInt64.MaxValue)
      {
        _pCnt = 0;
      }

      return "@P" + _pCnt++.ToString();
    }
    /* -------------------------------------------------------------------- */
    public static string GetText(List<string> sc)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < sc.Count; i1++)
      {
        sb.AppendLine(sc[i1]);
      }
      return sb.ToString();
    }
    /* -------------------------------------------------------------------- */
  }
}
[/code]
Member Avatar for sknake
sknake 1,622 Senior Poster

I forgot to post on how to call it:

private void button1_Click(object sender, EventArgs e)
    {
      //File
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        {
          QueryAggregator agg = new QueryAggregator(conn);
          for (int i1 = 1; i1 <= 5000; i1++)
          {
            const string query = "Insert Into Test (InvNumber, CustNumber) Values ({0}, {1})";
            List<SqlParameter> lst = new List<SqlParameter>();

            SqlParameter pInvNumber = new SqlParameter(agg.GetParameterName(), SqlDbType.Int);
            pInvNumber.Value = i1 * 100;
            
            SqlParameter pCustNumber = new SqlParameter(agg.GetParameterName(), SqlDbType.Int);
            pCustNumber.Value = i1 * 100;
            
            lst.Add(pInvNumber);
            lst.Add(pCustNumber);
            agg.AddQuery(
              string.Format(query, pInvNumber.ParameterName, pCustNumber.ParameterName),
              lst);
          }

          agg.RunQuery();
        }
      }
    }
Member Avatar for serkan sendur
serkan sendur 821 Postaholic

if i were you i would post it as a thread with the project attached to it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview