CODEWITHSUNDEEP
c#sql
Travis
Travis

Reputation: 687

C# Exception printing out SQL Row that breaks

I have a function that inserts SQL data based on a spreadsheet. I have had the issue of certain rows triggering the exception due to being truncated, and I am trying to figure out the rows that are causing the issue. (I have to query 3 different tables so I am using a function passing in SQL/command parameters/values instead of writing the same function 3 times)

The function works to insert the SQL data, except for the few rows that throws the ex message:

String or Binary data would be truncated. The statement has been terminated

My question is how do I print out the row number that causes the above message to troubleshoot the data in the excel sheet. The size of the sheet is 100+ thousand rows, so I don't want to go through it row by row.

The function I have:

public static void insert_data(string[] cols, string[] vals, string sql)
        {
            int exception_count = 0;
            List<string> rows = new List<string>();
            string connectionString = "Server = ; Database = ; User Id = ; Password = ";
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                connection.Open();
                for(int x = 0; x<cols.Length; x++)
                    command.Parameters.AddWithValue(cols[x], vals[x]);
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    exception_count =+ 1;
                    Console.WriteLine(ex.Message);
                    //rows.Add(--rows number--);
                }
            }

        }

Upvotes: 0

Views: 132

Answers (1)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Like I can see, the rows come from array, so first use a lambda expression to find the rows that have more than the length that you want, or viceverse find rows than have less or equal length, depends of you.

public void test2()
{
        //ensure that there is no empty rows in the array... of will thrown an exception
        string[] vals = new string[7];
        int myMaxColumnDatabaseLenght = 7;

        vals[0] = "length7";
        vals[1] = "length7";
        vals[2] = "length7";
        vals[3] = "length7";
        vals[4] = "length_8";
        vals[5] = "length__9";
        vals[6] = "length__10";

        Debug.WriteLine(vals.Count());

        vals = vals.Where(x => x.Length <= myMaxColumnDatabaseLenght).ToArray();

        Debug.WriteLine(vals.Count());
}

If you want make this dynamic, maybe you need query the length definition of the specific columns in SQL, if you want this part I will try to find for add information about that... .

Upvotes: 1

Related Questions