在使用sql server查询大量数据,并显示到界面dataGridView中发现界面出现了几十秒的假死现象,为了解决假死问题,开始百度解决方案。
经过计时发现,sql server查出数据花了2.1s,DGV.DataSource = dt;这个语句花了40.2s.
查阅资料后,主要有以下几种优化思路:
1…将类集合作为数据源绑定
2.这是系统控件的通病,进行dataGridView分页处理
3.使用SqlDataReader查询更快 - 0.6s
1.将类集合作为数据源绑定基本没啥效果
//新建一个类
public class DATASOURCE
{
public string header1 { get; set; } //get set似乎是必须的,不然不显示
public string header2 { get; set; }
public string header3 { get; set; }
public string header4 { get; set; }
public string header5 { get; set; }
public string header6 { get; set; }
}
结果如下,时间基本没少几秒
private void DisplayDataOnDGV(SqlDataReader dr)
{
List<DATASOURCE> da = new List<DATASOURCE>();
BindingSource bs = new BindingSource();
BindingList<DATASOURCE> DL = new BindingList<DATASOURCE>();
while (dr.Read())
{
DL.Add(new DATASOURCE() { header1 = dr[0].ToString(), header2 = dr[1].ToString(), header3 = dr[2].ToString(), header4 = dr[3].ToString(), header5 = dr[4].ToString(), header6 = dr[5].ToString() });
}
//G_DGV.DataSource = bs; // 39500 - 39.5s
G_DGV.DataSource = DL; // 40607 - 40.6s //37.815
}
2.分页 也就是给别人看一部分,按下一页再显示另一部分,copy一下别人的代码,这个有效。代码中使用的是DataTable,在查询过程中依旧会有2.1s的卡顿。
原链接:https://wenku.baidu.com/view/c06e21f0cd2f0066f5335a8102d276a2002960c5.html
copy在主窗体类
#region datagridview sort
/// <summary>
/// 每页记录数
/// </summary>
private int pageSize = 100;
/// <summary>
/// 总记录数
/// </summary>
private int recordCount = 0;
/// <summary>
/// 总页数
/// </summary>
private int pageCount = 0;
/// <summary>
/// 当前页
/// </summary>
private int currentPage = 0;
DataTable l_dt = new DataTable();
/// <summary>
/// 分页的方法
/// </summary>
/// <param name="str"></param>
private void PageSorter()
{
foreach (DataColumn col in l_dt.Columns)
{
DataGridViewTextBoxColumn dgvc = new DataGridViewTextBoxColumn();
dgvc.Name = "";
dgvc.DataPropertyName = "";
dgvc.HeaderText = col.Caption;//col.ToString();
dgvc.SortMode = DataGridViewColumnSortMode.NotSortable; //并无卵用
SQLDGV.Columns.Add(dgvc);
}
recordCount = l_dt.Rows.Count; //记录总行数
pageCount = (recordCount / pageSize);
if ((recordCount % pageSize) > 0)
{
pageCount++;
}
//默认第一页
currentPage = 1;
LoadPage();//调用加载数据的方法
}
/// <summary>
/// LoadPage方法
/// </summary>
private void LoadPage()
{
if (currentPage < 1) currentPage = 1;
if (currentPage > pageCount) currentPage = pageCount;
int beginRecord; //开始指针
int endRecord; //结束指针
DataTable dtTemp;
dtTemp = l_dt.Clone();
beginRecord = pageSize * (currentPage - 1);
if (currentPage == 1) beginRecord = 0;
endRecord = pageSize * currentPage;
if (currentPage == pageCount) endRecord = recordCount;
for (int i = beginRecord; i < endRecord; i++)
{
dtTemp.ImportRow(l_dt.Rows[i]);
}
SQLDGV.Rows.Clear();
this.lb_CurrentRow.Text = "当前页: " + currentPage.ToString() + " / " + pageCount.ToString();//当前页
this.lb_TotalRows.Text = "总记录数: " + recordCount.ToString() + " 条";//总记录数
//把临时table的数据插入到datagridview控件里面。
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
SQLDGV.Rows.Add();
for (int j = 0; j < dtTemp.Columns.Count; j++)
SQLDGV.Rows[i].Cells[j].Value = dtTemp.Rows[i][j].ToString();
}
}
#region 初始化窗体
private void FormMain_Load(object sender, EventArgs e)
{
InitialFormMain();
this.btn_EndPage.Click += Ctrl_Click;
this.btn_FirstPage.Click += Ctrl_Click;
this.btn_LastPage.Click += Ctrl_Click;
this.btn_NextPage.Click += Ctrl_Click;
}
#endregion
private void Ctrl_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
if (btn.Text == this.btn_EndPage.Text)
{
if (currentPage == pageCount)
{ return; }
currentPage = pageCount;
LoadPage();
}
else if (btn.Text == this.btn_FirstPage.Text)
{
if (currentPage == 1)
{ return; }
currentPage = 1;
LoadPage();
}
else if (btn.Text == this.btn_LastPage.Text)
{
if (currentPage == 1)
{ return; }
currentPage--;
LoadPage();
}
else if (btn.Text == this.btn_NextPage.Text)
{
if (currentPage == pageCount)
{ return; }
currentPage++;
LoadPage();
}
else
{
MessageBox.Show("error");
}
}
使用时调用PageSorter即可
l_dt = l_OptSQL.DTSelectSQLData("EIPInfoData", strAddSql);//查询(表,sql语句)
if (l_dt.Rows.Count == 0 && l_dt.Columns.Count == 0)
{
MessageBox.Show(l_OptSQL.SearchErrMsg(), "查询数据库错误", MessageBoxButtons.OK);
btnEnable();
return;
}
PageSorter();
至此就基本解决问题了,由于使用的是DataTable进行处理所以卡顿仍然有一些。查询22万条记录结果如下:
优化:
1.分页的代码增加一条判断
beginRecord = pageSize * (currentPage - 1);
if (currentPage == 1) beginRecord = 0;
endRecord = pageSize * currentPage;
if(beginRecord < 0) beginRecord = 0;//新增,否则会出错
if (currentPage == pageCount) endRecord = recordCount;
for (int i = beginRecord; i < endRecord; i++)
{
dtTemp.ImportRow(l_dt.Rows[i]);
}
SQLDGV.Rows.Clear();
this.lb_CurrentRow.Text = "当前页: " + currentPage.ToString() + " / " + pageCount.ToString();//当前页
this.lb_TotalRows.Text = "总记录数: " + recordCount.ToString() + " 条";//总记录数
2.允许跳转页
this.lb_当前页.Visible = true;
this.txt_CurrentRow.Visible = true;
this.txt_CurrentRow.Text = currentPage.ToString();//当前页
this.lb_CurrentRow.Text = " / " + pageCount.ToString();
this.lb_TotalRows.Text = "总记录数: " + recordCount.ToString() + " 条";//总记录数
3.改用SqlDataReader完成分表,加快查询
private SqlDataReader l_dr = null; //主窗体类字段
//查询按钮事件中
l_dr = l_OptSQL.DTSelectSQLData("EIPInfoData", strAddSql);//查询(表,sql语句)
if (l_dr.HasRows == false && l_dr.FieldCount == 0)
{
MessageBox.Show(l_OptSQL.SearchErrMsg(), "查询数据库错误", MessageBoxButtons.OK);
btnEnable();
return;
}
PageSorter();
PageSorter修改
private void PageSorter()
{
//foreach (DataColumn col in l_dt.Columns)
//{
// DataGridViewTextBoxColumn dgvc = new DataGridViewTextBoxColumn();
// dgvc.Name = "";
// dgvc.DataPropertyName = "";
// dgvc.HeaderText = col.Caption;//col.ToString();
// SQLDGV.Columns.Add(dgvc);
//}
for (int icol = 0; icol < l_dr.FieldCount; icol++)
{
DataGridViewTextBoxColumn dgvc = new DataGridViewTextBoxColumn();
dgvc.Name = "";
dgvc.DataPropertyName = "";
dgvc.HeaderText = l_dr.GetName(icol);
SQLDGV.Columns.Add(dgvc);
}
l_dt = ConvertDataReaderToDataTable(l_dr);
recordCount = l_dt.Rows.Count; //记录总行数
pageCount = (recordCount / pageSize);
if ((recordCount % pageSize) > 0)
{
pageCount++;
}
//默认第一页
currentPage = 1;
LoadPage();//调用加载数据的方法
}
ConvertDataReaderToDataTable将dataReader数据转dataTable,源代码链接:http://t.zoukankan.com/sdd53home-p-5481125.html
/// <summary>
/// SqlDataReader 转成 DataTable
/// 源需要是结果集
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
private static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
///定义DataTable
DataTable datatable = new DataTable();
try
{ ///动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
///添加表的数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
Application.DoEvents();
}
///关闭数据读取器
dataReader.Close();
return datatable;
}
catch (Exception ex)
{
///抛出类型转换错误
//SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
结果如下:
补充:解决假死问题还有一种方法,就是使用多线程,让另一条线程去处理查询数据库并显示的操作,还需要一条线程检查停止查询标志位,使用标志位实现停止查询的功能。当然也可以放在停止按钮点击事件之中死循环检查标志位,并abort停止 。