ListView hasn't exactly stellar performance characteristics, you will probably be better off using the DataGridView control.
Here is a nice example on populating the DataGridView:
How to populate DataGridView, GridView with SQL statement in C#[
^]
Possible easy solution:
Try altering the isolation level
http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx[
^] for the transactions. See:
SqlConnection.BeginTransaction[
^]
It has severe impact on performance, but check the implications carefully.
If you like John's solution better - check up on the active object pattern, here is a nice explanation:
Applied Long-Running Active Object Pattern[
^], I haven't tried the code though - it is a thorough and good explanation.
If you don't mind using C++/CLI you can create a high quality solution using
ACE[
^], and here is a nice
tutorial[
^] by Umar Syyid showing how to create Active Objects and more using ACE.
This is what I would recommend for a production quality solution.
Here is a little component, a sort of minimalistic approach/hack at John’s idea. SqlCommand objects can be enqued using the Enqueue method.
Update
The answer now includes a little bit of test functionality - showing how SqlCommand objects can be enqueued.
The component maintains a simple pool of connections and executes the ExecuteNonQuery method on the queued commands.
Remember to use the InvokeRequired/Invoke pattern for the ExceptionEvent event.
ConnectionPool.cs:
using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Threading;
namespace QueuedDatabaseCommands.Harlinn.Com
{
public class ExceptionEventArgs : EventArgs
{
Exception exception;
public ExceptionEventArgs(Exception exc)
{
exception = exc;
}
public Exception Exception
{
get
{
return exception;
}
}
}
public delegate void ExceptionEventHandler(object sender,
ExceptionEventArgs eventArgs);
public class SqlCommandQueue : Component,INotifyPropertyChanged
{
private string connectionStringName = string.Empty;
private string connectionString = string.Empty;
private ushort maxFreeConnections = 10;
private LinkedList<SqlConnection> freeConnections = new
LinkedList<SqlConnection>();
private LinkedList<SqlConnection> busyConnections = new
LinkedList<SqlConnection>();
private object synchObject = new object();
public event PropertyChangedEventHandler PropertyChanged;
public event ExceptionEventHandler ExceptionEvent;
public SqlCommandQueue()
{
}
private void DisposeConnections(LinkedList<SqlConnection>
connections)
{
foreach (SqlConnection sqlConnection in connections)
{
try
{
sqlConnection.Dispose();
}
catch (Exception exc)
{
OnException(exc);
}
}
}
protected override void Dispose(bool disposing)
{
lock (synchObject)
{
if (freeConnections != null)
{
LinkedList<SqlConnection> freeConnections_ =
freeConnections;
freeConnections = null;
DisposeConnections(freeConnections_);
}
if (busyConnections != null)
{
LinkedList<SqlConnection> busyConnections_ =
busyConnections;
busyConnections = null;
DisposeConnections(busyConnections_);
}
}
base.Dispose(disposing);
}
protected virtual void OnException(Exception exc)
{
if ((ExceptionEvent != null)&&(exc != null))
{
ExceptionEventArgs eventArgs =
new ExceptionEventArgs(exc);
ExceptionEvent(this, eventArgs);
}
}
protected void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChangedEventArgs eventArgs =
new PropertyChangedEventArgs(propertyName);
PropertyChanged(this, eventArgs);
}
}
[DefaultValue("")]
public string ConnectionStringName
{
get
{
string result;
lock (synchObject)
{
if (connectionStringName != null)
{
result = (string)connectionStringName.Clone();
}
else
{
result = string.Empty;
}
}
return result;
}
set
{
bool connectionStringChanged = false;
lock (synchObject)
{
if (connectionStringName == value)
return;
connectionStringName =
string.IsNullOrWhiteSpace(value) ?
string.Empty : (string)value.Clone();
if (string.IsNullOrWhiteSpace(connectionString) ==
false)
{
connectionString = string.Empty;
connectionStringChanged = true;
}
}
OnPropertyChanged("ConnectionStringName");
if (connectionStringChanged)
{
OnPropertyChanged("ConnectionString");
}
}
}
[DefaultValue("")]
public string ConnectionString
{
get
{
bool connectionStringChanged = false;
string result;
lock (synchObject)
{
if (DesignMode == false)
{
if (string.IsNullOrWhiteSpace(connectionString)
== false)
{
if (string.IsNullOrWhiteSpace
(connectionStringName))
{
ConnectionStringSettings
connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
if (connectionStringSettings != null)
{
string cs = connectionStringSettings.ConnectionString;
if (cs != connectionString)
{
connectionString = cs;
connectionStringChanged = true;
}
}
}
}
}
if (string.IsNullOrWhiteSpace(connectionString) == false)
{
result = (string)connectionString.Clone();
}
else
{
result = string.Empty;
}
}
if (connectionStringChanged)
{
OnPropertyChanged("ConnectionString");
}
return result;
}
set
{
lock (synchObject)
{
if (connectionString == value)
return;
connectionString = value;
}
OnPropertyChanged("ConnectionString");
}
}
public ushort MaxFreeConnections
{
get
{
lock (synchObject)
{
return maxFreeConnections;
}
}
set
{
try
{
if (value == 0)
{
throw new ArgumentException(
"0 is not allowed for MaxFreeConnections", "value");
}
lock (synchObject)
{
if (maxFreeConnections == value)
return;
maxFreeConnections = value;
}
OnPropertyChanged("MaxFreeConnections");
}
catch (Exception exc)
{
OnException(exc);
throw;
}
}
}
private SqlConnection GetConnection()
{
try
{
SqlConnection result = null;
lock (synchObject)
{
if (freeConnections.Count > 0)
{
result = freeConnections.Last.Value;
freeConnections.RemoveLast();
busyConnections.AddLast(result);
}
else
{
string conStr = ConnectionString;
result = new SqlConnection(conStr);
result.Open();
busyConnections.AddLast(result);
}
return result;
}
}
catch (Exception exc)
{
OnException(exc);
throw;
}
}
private void ReleaseConnection(SqlConnection sqlConnection)
{
try
{
lock (synchObject)
{
busyConnections.Remove(sqlConnection);
if (freeConnections.Count >= maxFreeConnections)
{
sqlConnection.Dispose();
}
else
{
freeConnections.AddLast(sqlConnection);
}
}
}
catch (Exception exc)
{
OnException(exc);
throw;
}
}
class QueuedExecuteInfo
{
WeakReference sqlCommandQueueReference;
SqlCommand sqlCommand;
public QueuedExecuteInfo(SqlCommandQueue sqlCommandQueue, SqlCommand sqlCommand)
{
sqlCommandQueueReference = new WeakReference( sqlCommandQueue );
this.sqlCommand = sqlCommand;
}
public SqlCommandQueue SqlCommandQueue
{
get
{
return sqlCommandQueueReference.Target as SqlCommandQueue;
}
}
public SqlCommand SqlCommand
{
get
{
return sqlCommand;
}
}
}
private static void Execute(object param)
{
QueuedExecuteInfo queuedExecuteInfo = (QueuedExecuteInfo)param;
SqlCommand sqlCommand = queuedExecuteInfo.SqlCommand;
if (sqlCommand != null)
{
using (sqlCommand)
{
SqlCommandQueue sqlCommandQueue = queuedExecuteInfo.SqlCommandQueue;
if (sqlCommandQueue != null)
{
sqlCommandQueue.ExecuteCommand(sqlCommand);
}
}
}
}
private void ExecuteCommand(SqlCommand sqlCommand)
{
try
{
SqlConnection sqlConnection = GetConnection();
try
{
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
}
finally
{
ReleaseConnection(sqlConnection);
}
}
catch (Exception exc)
{
OnException(exc);
}
}
public bool Enqueue(SqlCommand sqlCommand)
{
try
{
QueuedExecuteInfo queuedExecuteInfo = new QueuedExecuteInfo(this, sqlCommand);
bool result = ThreadPool.QueueUserWorkItem(new WaitCallback(Execute), queuedExecuteInfo);
return result;
}
catch (Exception exc)
{
OnException(exc);
throw;
}
}
}
}
Small test:
Table:
CREATE TABLE QueuedData
(
ID bigint identity NOT NULL PRIMARY KEY,
CreatedTime datetime2 NOT NULL default SYSDATETIME(),
MessageText nvarchar(2048)
)
go
MainForm.Designer.cs:
namespace QueuedDatabaseCommands
{
partial class MainForm
{
private System.ComponentModel.IContainer components = null;
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.enqueueTimer = new System.Windows.Forms.Timer(this.components);
this.stopButton = new System.Windows.Forms.Button();
this.startButton = new System.Windows.Forms.Button();
this.sqlCommandQueue = new QueuedDatabaseCommands.Harlinn.Com.SqlCommandQueue();
this.enqueueButton = new System.Windows.Forms.Button();
this.SuspendLayout();
this.enqueueTimer.Interval = 50;
this.enqueueTimer.Tick += new System.EventHandler(this.enqueueTimer_Tick);
this.stopButton.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
this.stopButton.Enabled = false;
this.stopButton.Location = new System.Drawing.Point(720, 408);
this.stopButton.Name = "stopButton";
this.stopButton.Size = new System.Drawing.Size(75, 23);
this.stopButton.TabIndex = 0;
this.stopButton.Text = "Stop";
this.stopButton.UseVisualStyleBackColor = true;
this.stopButton.Click += new System.EventHandler(this.stopButton_Click);
this.startButton.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
this.startButton.Location = new System.Drawing.Point(639, 408);
this.startButton.Name = "startButton";
this.startButton.Size = new System.Drawing.Size(75, 23);
this.startButton.TabIndex = 1;
this.startButton.Text = "Start";
this.startButton.UseVisualStyleBackColor = true;
this.startButton.Click += new System.EventHandler(this.startButton_Click);
this.sqlCommandQueue.ConnectionStringName = "QueuedDatabaseName";
this.sqlCommandQueue.MaxFreeConnections = ((ushort)(10));
this.enqueueButton.Location = new System.Drawing.Point(558, 408);
this.enqueueButton.Name = "enqueueButton";
this.enqueueButton.Size = new System.Drawing.Size(75, 23);
this.enqueueButton.TabIndex = 2;
this.enqueueButton.Text = "Manual";
this.enqueueButton.UseVisualStyleBackColor = true;
this.enqueueButton.Click += new System.EventHandler(this.enqueueButton_Click);
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(807, 434);
this.Controls.Add(this.enqueueButton);
this.Controls.Add(this.startButton);
this.Controls.Add(this.stopButton);
this.Name = "MainForm";
this.Text = "Queued Database Commands by Espen Harlinn";
this.ResumeLayout(false);
}
#endregion
private Harlinn.Com.SqlCommandQueue sqlCommandQueue;
private System.Windows.Forms.Timer enqueueTimer;
private System.Windows.Forms.Button stopButton;
private System.Windows.Forms.Button startButton;
private System.Windows.Forms.Button enqueueButton;
}
}
MainForm.cs:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace QueuedDatabaseCommands
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void Enqueue()
{
SqlCommand sqlCommand = new SqlCommand();
DateTime dt = DateTime.Now;
string sql =
"INSERT INTO QueuedData(MessageText) VALUES(@msg)";
string message = "Message created:" + dt.ToString("o");
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@msg",
SqlDbType.NVarChar).Value = message;
sqlCommandQueue.Enqueue(sqlCommand);
}
private void enqueueTimer_Tick(object sender, EventArgs e)
{
Enqueue();
Enqueue();
Enqueue();
}
private void startButton_Click(object sender, EventArgs e)
{
startButton.Enabled = false;
stopButton.Enabled = true;
enqueueTimer.Start();
}
private void stopButton_Click(object sender, EventArgs e)
{
startButton.Enabled = true;
stopButton.Enabled = false;
enqueueTimer.Stop();
}
protected override void OnFormClosing(FormClosingEventArgs e)
{
if (enqueueTimer.Enabled)
{
enqueueTimer.Stop();
}
base.OnFormClosing(e);
}
private void enqueueButton_Click(object sender, EventArgs e)
{
enqueueTimer_Tick(sender, e);
}
}
}
App.config:
="1.0"="utf-8"
<configuration>
<connectionStrings>
<add name="QueuedDatabaseName" connectionString="Data Source=(local);Initial Catalog=QueuedDatabase;Persist Security Info=False;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Program.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace QueuedDatabaseCommands
{
static class Program
{
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new MainForm());
}
}
}
Regards
Espen Harlinn
Submit an article or tip