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
SQL Query Aggregator
[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]
sknake 1,622 Senior Poster Featured 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();
}
}
}
serkan sendur 821 Postaholic Banned Featured Poster
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.