Simple winform, that has two buttons: Start and Cancel a a Label component
for record count display.
TODO
- set your connection string and stored proc to execute; stored proc should
just return a larg (~100 000 records) recordset;
- press Start button and then Cancel one;
- press Start button again.
---------------------------------CUT ---------------------------------------
------
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Net;
namespace WindowsApplication8
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnStart; // Start button
private System.Windows.Forms.Button btnCancel; // Cancel button
private System.Windows.Forms.Label lbRecCount; // Record count label
private System.ComponentModel.Container components = null;
SqlCommand _localCmd;
SqlConnection _con;
bool _canceled = false;
DataSet _dataSet;
public Form1()
{
InitializeComponent();
_dataSet = new DataSet();
_con = new SqlConnection("data source=YOUR_SOURCE;initial
catalog=YOUR_CATALOG;password=PWD;persist security info=True;user
id=LOGIN;workstation id=" + Dns.GetHostName() + ";application name=" +
Application.ProductName + ";packet size=4096;Pooling=false");
_con.Open();
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
_con.Close();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnStart = new System.Windows.Forms.Button();
this.lbRecCount = new System.Windows.Forms.Label();
this.btnCancel = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// btnStart
//
this.btnStart.Location = new System.Drawing.Point(20, 48);
this.btnStart.Name = "btnStart";
this.btnStart.TabIndex = 0;
this.btnStart.Text = "Start";
this.btnStart.Click += new System.EventHandler(this.btnStart_Click);
//
// lbRecCount
//
this.lbRecCount.AutoSize = true;
this.lbRecCount.Location = new System.Drawing.Point(87, 12);
this.lbRecCount.Name = "lbRecCount";
this.lbRecCount.Size = new System.Drawing.Size(35, 16);
this.lbRecCount.TabIndex = 1;
this.lbRecCount.Text = "label1";
//
// btnCancel
//
this.btnCancel.Location = new System.Drawing.Point(113, 48);
this.btnCancel.Name = "btnCancel";
this.btnCancel.TabIndex = 2;
this.btnCancel.Text = "Cancel";
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(208, 93);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.lbRecCount);
this.Controls.Add(this.btnStart);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void btnStart_Click(object sender, System.EventArgs e)
{
Start();
}
void Start()
{
SqlDataReader reader = null;
Exception exc = null;
lock(this)
{
try
{
// set some store proc to execute
// this proc should return a record set
_localCmd = new SqlCommand("YOUR_STORED_PROC", _con);
_localCmd.CommandType = CommandType.StoredProcedure;
_localCmd.CommandTimeout = 120;
SqlCommandBuilder.DeriveParameters(_localCmd);
_dataSet.Tables.Clear();
// executing a command
reader = _localCmd.ExecuteReader();
int tableIndex = 0;
bool has_rows = true;
DataTable table = null;
object[] row = null;
// starting from here (see much code) is just simple reading of
// rows with data reader and adding then to _dataSet.Tables
while(has_rows && !_canceled)
{
has_rows = reader.Read();
if(_dataSet.Tables.Count <= tableIndex)
{
table = _dataSet.Tables.Add();
for(int i = 0; i < reader.FieldCount; i++)
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
else
table = _dataSet.Tables[tableIndex];
if(has_rows)
{
row = new object[reader.FieldCount];
reader.GetValues(row);
table.Rows.Add(row);
}
else
{
has_rows = reader.NextResult();
if(has_rows)
tableIndex++;
}
if(TotalRecCount % 1000 == 0)
DisplayRecCount(TotalRecCount);
} // while
}
catch(Exception err)
{
exc = err;
}
if(reader != null)
{
try
{
if(_canceled)
CancelCommand();
reader.Close();
}
catch(Exception)
{}
}
_canceled = false;
if(exc != null)
MessageBox.Show(exc.Message, "Error");
}
}
public void CancelSelect()
{
_canceled = true;
}
/// <summary>
/// Cancels data reading
/// </summary>
void CancelCommand()
{
_localCmd.Cancel();
MessageBox.Show("Command canceled!", "");
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
CancelSelect();
}
void DisplayRecCount(int recCount)
{
lbRecCount.Text = recCount.ToString();
lbRecCount.Refresh();
Application.DoEvents();
}
/// <summary>
/// Total record count.
/// </summary>
public int TotalRecCount
{
get
{
int recCount = 0;
foreach(DataTable table in _dataSet.Tables)
recCount += table.Rows.Count;
return recCount;
}
}
}
}
---------------------------------CUT ---------------------------------------
------
