C#根据条件查询数据库统计百万级记录条数三种方式的优劣

开发过程中，统计记录类别数量时，如果数据量超过百万级，经测试，用第三种方式统计可以将查询时间缩短到毫秒级。

一、使用 DataTable 方法：

string str = "";

DataTable dt = new DataTable();

string strWhere = " 1 = 1 ";

dt = b.GetList("select mainID,autoid,artificialID,titleProper,otherTitle,ethnicGroup,timesProperty,artSchool,keywords,hits,firstName,secondName,thirdName,forthName,fifthName,socialAttribute,formatTypeName from tempyefei where " + strWhere + " order by mainid asc ");

str = dt.Rows.Count.ToString();

dt.Dispose();

dt.Clear();

return str;

二、使用while(read())方法：

SqlConnection conn = new SqlConnection(getConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand("select * from t_main", conn);

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

MessageBox.Show(string.Format("数据库共有{0}个记录", dr[0]), "提示信息");

}

--------------------------------------------------------------------------------------

string str = "";

string strWhere = " 1 = 1 ";

string sql = "select mainID,autoid,artificialID,titleProper,otherTitle,ethnicGroup,timesProperty,artSchool,keywords,hits,firstName,secondName,thirdName,forthName,fifthName,socialAttribute,formatTypeName,publisher,publicationDate from View_qw_main_spatial_people where " + strWhere + " ";

SqlDataReader aa = DbHelperSQL.ExecuteReader("select count(*) from (" + sql+") as bb ");

while (aa.Read())

{

str = aa[0].ToString();

}

aa.Close();

aa.Dispose();

return str;

三、使用cmd.ExecuteScalar()方法

SqlConnection conn = new SqlConnection(getConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand("select count(*) from t_main", conn);

MessageBox.Show("共有"+Convert.ToInt32(cmd.ExecuteScalar())+"条记录","提示信息");

----------------------------------------------------------------------------------------

string str = "";

string strWhere = " 1 = 1 ";

string sql = "select mainID,autoid,artificialID,titleProper,otherTitle,ethnicGroup,timesProperty,artSchool,keywords,hits,firstName,secondName,thirdName,forthName,fifthName,socialAttribute,formatTypeName,publisher,publicationDate from View_qw_main_spatial_people where " + strWhere + " ";

int cc = yfbb.Execint("select count(*) from (" + sql + ") as bb ");

str = cc.ToString();

return str;

综上所述，经实际测试，查询时间消耗时间最少的，也就是最快的方式是第三种 ！