如何检查表的所有行在ado.net中是否都是整数? [英] how to check if all the rows of a table are integer in ado.net ?

问题描述

大家好，
我正在导入一个excel文件，我需要将excel文件的所有数据插入sql server.但是我不能使用批量插入，因为我需要对数据做一些操作+需要在Excel中插入数据之前添加除Excel以外的一些额外列.现在在excel中有一些列只能是整数.我将所有数据从excel获取到c#中的数据集.现在我需要检查这些列是否这些列的所有行都是整数.即使单个值不是数字，我也会拒绝该文件并要求用户上传适当的文件.我可以通过for循环来检查它，但是可能会花一些时间，因为我的excel将拥有大量数据，请问有没有更快的方法来进行此验证?

Hi all,
i am importing an excel file and i need to insert all the data of excel file to sql server. however i can not use bulk insert as i need to do some operation on data + need to add some extra columns apart from excel before i insert data into sql server. now in the excel there are some columns which should be only integer. i get all the data from excel to a dataset in the c#. now i need to check for these columns whether all the rows of these columns are integer or not. even if a single value is non numeric i will reject the file and ask user to upload a proper file. i can check this by for loop but it may take some time as my excel will have large number of data, is there any faster way to do this validation ?

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/XML/" + Filename) + ";Extended Properties=Excel 8.0");

               
                OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
               
                oconn.Open();
                da.Fill(ds);
                oconn.Close();

where ds is a datatable.

推荐答案

"，oconn)； OleDbDataAdapter da = 新 OleDbDataAdapter(cmd); oconn.Open(); da.Fill(ds); oconn.Close();

", oconn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); oconn.Open(); da.Fill(ds); oconn.Close();

where ds is a datatable.

DataTable dt=  ds.Table[0];
int numberOfColumns = dt.Columns.Count;
bool IsAllInt =true;
// go through each row
foreach (DataRow dr in dt.Rows)
{
    // go through each column in the row
    for (int i = 0; i < numberOfColumns; i++)
    {
        int val;
        if(!int.TryParse(dr[i].ToString(), out val))
        {
           IsAllInt =false;
           break;
        }
    }
}
//check the value of IsAllInt

从XLS文件加载数据之前，您应先将Column对象的类型指定为int，然后将DataTable及其列创建为try-catch-finally阻止，您应该从XLS文件中的数据中加载数据，如果XLS中的数据中存在错误，则会引发某些异常.

Before to load the data from XLS file you should create your DataTable and its columns by specifying the type of the Column objects as int then, into a try-catch-finally block you should load your data from your data from XLS file and if there errors in the data from XLS some exception will be thrown.

这篇关于如何检查表的所有行在ado.net中是否都是整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！