IsNull Column

Question

User-1499457942 posted

hi

  I want to check For IsNull in Criteria Column. If it is Null then it should be replaced by ''

using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT Category,Name,Item,Criteria,,Name+Criteria as Id FROM [" + sheet1 + "]", cnnExcel))

SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);

objbulkInsert.DestinationTableName = "[Test]";
objbulkInsert.WriteToServer(dtExcelData);

Thanks

Answer 1

User1080785583 posted

Here is an example site to do such, https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017.

ISNULL(Criteria, '')

Answer 2

User-893317190 posted

Hi JagjitSingh,

Since you are using ado to retrieve data from excel, syntax of your query may differ from  sql query.

I suggest you could operate on the datatable.

Below is my code.

 DataTable dtExcelData = new DataTable();


               
                dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });
               
                dtExcelData.Columns["Id"].AutoIncrement = true;
                dtExcelData.Columns["Id"].AutoIncrementSeed = 1;
                dtExcelData.Columns["Id"].AutoIncrementStep = 1;

                //connection.Close();
                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT    Name,salary  FROM [" + sheet2 + "]", connection))
                {
                    oda.Fill(dtExcelData);

                    foreach (DataRow item in dtExcelData.Rows)
                    {
                        if(item["Name"] == DBNull.Value)
                        {
                            item["Name"] = ""; // to column of type string
                        }
                        if(item["Salary"]== DBNull.Value)
                        {
                            item["Salary"] = 0;// to column of type int
                        }
                        
                    }
                   

                }
                string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        
                        sqlBulkCopy.DestinationTableName = "dbo.salary";
                         table
                        sqlBulkCopy.ColumnMappings.Add(0, 0);

                        sqlBulkCopy.ColumnMappings.Add(1, 1);
                        sqlBulkCopy.ColumnMappings.Add(2, 2);
                       
                        con.Open();
                       
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();
                    }
                }

My excel

Id		Salary		name
1				
3				abc
5		1500		abd
6		1200		ouuo
7		1000		luv

The result.

id	name	salary
1		0
2	abc	0
3	abd	1500
4	ouuo	1200
5	luv	1000

Best regards,

Ackerly Xu