public void RefreshRowCounts()
{
List <TableSizeInfo> tableSizes = GetAllTableRowCounts();
foreach (TableSizeEntry tableEntry in Tables)
{
TableSizeInfo tableSizeInfo = (from ti in tableSizes
where ti.Name == tableEntry.TableName
select ti).FirstOrDefault();
if (tableSizeInfo == null)
{
tableEntry.RowCount = -1;
tableEntry.ErrorStr = "Table not found!";
}
else
{
tableEntry.RowCount = tableSizeInfo.Rows;
tableEntry.ErrorStr = "";
}
}
}
public static List <TableSizeInfo> GetAllTableRowCounts(string sqlServer, string database, bool integratedSecurity, string userName, string password, int sqlCmndTimeOutSec)
{
List <TableSizeInfo> list = new List <TableSizeInfo>();
string sql = "select t.name, i.[rows] from sys.sysindexes i inner join sys.tables t on t.object_id = i.id where i.indid in (0, 1, 255) order by t.name";
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = sqlServer;
scsb.InitialCatalog = database;
scsb.IntegratedSecurity = integratedSecurity;
if (!integratedSecurity)
{
scsb.UserID = userName;
scsb.Password = password;
}
using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))
{
conn.Open();
using (SqlCommand cmnd = new SqlCommand(sql, conn))
{
cmnd.CommandType = CommandType.Text;
cmnd.CommandTimeout = sqlCmndTimeOutSec;
using (SqlDataReader r = cmnd.ExecuteReader())
{
while (r.Read())
{
TableSizeInfo tableSizeInfo = new TableSizeInfo();
tableSizeInfo.Name = r[0].ToString();
tableSizeInfo.Rows = long.Parse(r[1].ToString());
list.Add(tableSizeInfo);
}
}
}
}
return(list);
}