how to check if all the rows of a table are integer in ado.net ?

Question

0.00/5 (No votes)

See more:

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 ?

C#

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.

Posted 26-May-14 19:09pm

ravikhoda

Updated 26-May-14 20:08pm

v2

Add a Solution

Comments

DamithSL 27-May-14 1:20am

how you get data to DataSet? please update the question with that code

ravikhoda 27-May-14 2:08am

i have update the code.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2014-05-26T20:20:00

Solution 2

C#

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

Posted 26-May-14 20:20pm

DamithSL

Comments

ravikhoda 27-May-14 2:39am

well thanks. i know this will work but this will check for all the records and i think this will affect the performance when i have a very large data from the excel file. that's why i am looking for something other way of doing this. i like the first solution but it also gives me some error at this time. will do some more research and update.

Raul Iloc · Answer 2 · 2014-05-26T19:15:00

Solution 1

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.

Posted 26-May-14 19:15pm

Raul Iloc

Updated 26-May-14 19:16pm

v2

Comments

ravikhoda 27-May-14 2:09am

hi i tried to define structure as per your solution but this gives me error on connection open method that external table is not formatted.