SqlDataAdapter handle error - object is not created

Question

I have this error:

Invalid object name 'sap.AfdelingsrapportACTUAL'.

Which is because this table is not created yet.

I need to implement a check in my SqlDataAdapter - so if is null or doesnt get anything it moves on. But i really doesnt know how to.

I think it should be around my SqlDataAdapter but i could be wrong

Code

 DataTable sqldt = new DataTable();
            string sqlQuery = @"Select * from " + table;
            SqlConnection sqlcon = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sqlQuery, sqlcon);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
             da.Fill(sqldt);
            }

What i think it should look like:

 public static bool CompareDataTables(DataTable dt, string table, string connectionString)
        {
            DataTable sqldt = new DataTable();
            string sqlQuery = @"Select * from " + table;
            SqlConnection sqlcon = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sqlQuery, sqlcon);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {

                if (da == "doesnt have any table")
                {
                    return true;
                }
                else
                {
                    da.Fill(sqldt);
                }
            }

            int sqlCols = sqldt.Columns.Count;
            int excelCols = dt.Columns.Count;

            if (excelCols == sqlCols)
            {
                return false;
            }
            else return true;

        }

you should check if the table exists first: https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server — jazb, Oct 11 '18 at 07:46
Why do you select all rows (`SELECT *` without condition) if you are just going to compare the number of columns in the table? — Philipp Grathwohl, Oct 11 '18 at 07:47
@John Yeah i could do that. I just hoped that i could go around it with a smart C# feature — SqlKindaGuy, Oct 11 '18 at 07:47
@PhilippGrathwohl What should i else do? I guess i need to fill my datatable with something? — SqlKindaGuy, Oct 11 '18 at 07:48
well if you want to play with raw sql instead of using an ORM you're going to have to get your hands dirty :-) — jazb, Oct 11 '18 at 07:51
@Thomas You could use a `SELECT TOP 0 *` so no data is returned for example. Or if you are just going to compare the table schema you could select this information with `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourtablename'` (If you are using sqlserver - which is not clear in your question) — Philipp Grathwohl, Oct 11 '18 at 07:51
@John I dont know what an ORM is :) But I know to mix C# with SQL ;) — SqlKindaGuy, Oct 11 '18 at 07:51
@PhilippGrathwohl Ahh yes of cause - No need to fill my DataTable with data ofc. Good point. But do i really need to check if my table exists first in SQL? I cannot wrap something around my C# script? — SqlKindaGuy, Oct 11 '18 at 07:53
@Thomas Not sure. I guess you could catch the SqlException that occurs when the command fails. But that feels rather hacky. Why not selecting the data you actually want from the information_schema? This way you could compare the rows, and if the table does not exist, yet, you don't get an error, but just an empty datatable. — Philipp Grathwohl, Oct 11 '18 at 07:57
@John Instead of doing something i dont know, im going with your solution. I had thinked of it before, but i was trying hard to see if I could do it in C# instead. Since you were first, you can leave an answer if you want. — SqlKindaGuy, Oct 11 '18 at 07:57
@PhilippGrathwohl I was also thinking that. Oh well, im gonna check on information schema if it exists and return a bool. Thanks for the effort though, and thanks for the advices! — SqlKindaGuy, Oct 11 '18 at 07:58

score 0 · Accepted Answer · answered Oct 11 '18 at 08:00

0

One way would be first check for the existence of the data table.

follow the sample given here: Check if table exists in SQL Server

answered Oct 11 '18 at 08:00

jazb

5,498
6
37
44

SqlDataAdapter handle error - object is not created

1 Answers1