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 ---------------------------------------------
Sergey, I am sorry for security reasons we do not look at post attachments,
could you post it as text.
Thanks
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
Here is the repro code (see attach).
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.
can you post some code? I will look into this.
Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.
I open an SqlConnection.
Then:
1. execute a reader on SqlCommand and get data through that reader;
2. in a certain time I call Cancel() method on SqlCommand and
SqlDataReader.Close().
If I perform steps 1 and 2 several times (sometimes it happens even
from
the
first time)
I get the error which says that SqlConnection is closed. And it is.
So,
seems that SqlCommand.Cancel()
in combination with SqlDataReader.Close() forces the connection to
close.
I can't determine the reason why it happens.
Can I control somehow this behaviour, because I need the
connection
to
be
opened.
Thank you in advance,
Sergey Ivasenko.
