Allow null

[Arjun_Sarankulu]

I am having a desktop application which take the csv file and dump the data to the database.
At the Page Load there is table name in the combo-box
I browse the file and select the table name from combo-box and data get dump to appropriate table
It working fine
But the problem is in csv file, one column doesnot contain data(empty)
and that column in table is type date or datetime
It gives the following error
The given value of type from the datasource cannot be converted to type date of the specified target column

If m writing to one table then it would have not been the problem
But at run time am taking the table name due to which i cannot track which column can be empty
I am not getting
Is there any property related to sqlbulkcopy to allow null
I search but didnt get till now

[Alan81]

Look at the following link for Nullable datetime in c#

http://www.dotnetperls.com/nullable-datetime

[Arjun_Sarankulu]

Following is my code:

namespace Test1
{

    public partial class Form1 : Form
    {
        string emp_code = "";
        public Form1()
        {
            InitializeComponent();
        }
        

        private void Uploadbutton_Click(object sender, EventArgs e)
        {

            openFileDialog1.ShowDialog();

            filenametextBox.Text = openFileDialog1.FileName;


        }//Uploadbutton_Click

        private void save_to_DBbutton_Click(object sender, EventArgs e)
        {

            string servername = "";
            string dbname = "";
            string username = "";
            string pwd = "";
            string storeproc = "";
            
            
            SqlConnection connection_string = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);
            try
            {

                if (openFileDialog1.FileName == "")
                {
                    MessageBox.Show("File Not Selected");
                    return;

                }//if (openFileDialog1.FileName == "")
            
                string filepath = openFileDialog1.FileName;
                string tablename = "";

                tablename = Convert.ToString(comboBox1.SelectedItem);
                string query1 = "select is_Flush from mst_csv_upload where request_name = '" + tablename + "'";
                connection_string.Open();
                
                SqlCommand cmd = new SqlCommand(query1, connection_string);
                
                bool flush_flag = (bool)cmd.ExecuteScalar();
                

                if (flush_flag == true)
                {


                    SqlTransaction transaction = connection_string.BeginTransaction();
                    string query_trunc = "TRUNCATE TABLE " + tablename;
                    SqlCommand cmd_delete = new SqlCommand(query_trunc, connection_string);
                    cmd_delete.Transaction = transaction;
                    cmd_delete.CommandType = CommandType.Text;
                    
                    cmd_delete.ExecuteNonQuery();
                    transaction.Commit();
                
                   
                    //MessageBox.Show("Data Truncate Successfully");
                    
                }

                string query_datasource = "select request_server, request_db, request_user, request_pwd, storeProcedure from mst_csv_upload where request_name = '" + tablename + "'";
                
                SqlCommand cmd_dtsource = new SqlCommand(query_datasource, connection_string);
                cmd_dtsource.CommandType = CommandType.Text;
                //cmd.CommandText = "select request_server, request_db, request_user, request_pwd, storeProcedure from mst_csv_upload where request_name = '" + tablename + "'";
                SqlDataReader dtreader = cmd_dtsource.ExecuteReader();
                while(dtreader.Read())      //this is retrieve the connection string from db to dump the data to the table
                {
                    servername = Convert.ToString(dtreader[0]);
                    dbname = Convert.ToString(dtreader[1]);
                    username = Convert.ToString(dtreader[2]);
                    pwd = Convert.ToString(dtreader[3]);
                    storeproc = Convert.ToString(dtreader[4]);
                }
                
                dtreader.Close();
                

                if (storeproc != "" && storeproc != "NULL")    //This is for proc present for selected table
                {
                    //MessageBox.Show("Proc-Name is present");
                    SqlCommand cmdproc = new SqlCommand(storeproc, connection_string);
                    cmdproc.CommandType = CommandType.StoredProcedure;
                    cmdproc.ExecuteNonQuery();
                }


                
                string conn_string = "Data Source = " + servername + "; " + "Integrated Security = false; " 
                    + "Initial Catalog = " + dbname + "; " + "user id = " + username + "; "
                    + "password = " + pwd;                  

                SqlConnection connection_string1 = new SqlConnection(conn_string);
                
                StreamReader sr = new StreamReader(filepath);

                string line = sr.ReadLine();

                string[] value = line.Split(',');

                DataTable dt = new DataTable();

                DataRow row;
                
                foreach (string dc in value)
                {
                    
                    dt.Columns.Add(new DataColumn(dc));
                }

                while (!sr.EndOfStream)
                {
                    value = sr.ReadLine().Split(',');

                    if (value.Length == dt.Columns.Count)
                    {
                        row = dt.NewRow();

                        row.ItemArray = value;

                        dt.Rows.Add(row);
                    }
                }
                SqlBulkCopy bc = new SqlBulkCopy(connection_string1.ConnectionString, SqlBulkCopyOptions.TableLock);// | SqlBulkCopyOptions.UseInternalTransaction);

                bc.DestinationTableName = tablename;

                bc.BatchSize = dt.Rows.Count;

                try
                {
                    connection_string1.Open();

                                        
                    bc.WriteToServer(dt);

                    bc.Close();

                    
                    MessageBox.Show("Data dumped to table : " + tablename + " succesfully");

                    if (MessageBox.Show("Do u want to process another file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {

                    }
                    else
                    {
                        this.Close();
                    }


                }
                catch (Exception err)
                {
                    MessageBox.Show(err.Message);
                }
                finally
                {
                                        connection_string1.Close();
                }

                

                            }

            catch (Exception err)
            {
               MessageBox.Show(err.Message);
            }

            finally
            {
                connection_string.Close();
                
                
            }   //If any exception occurs connection will always close   

            

    
        }//private void save_to_DBbutton_Click

        private void Form1_Load(object sender, EventArgs e)
        {
            Login_Form log_form = new Login_Form();
            emp_code = log_form.Empcode();
            SqlConnection connection_string;
            
            string sqlquery = "select mcu.Request_Name from mst_csv_upload mcu inner join trn_emp_code_table tect on tect.mst_csv_upload_id = mcu.mst_csv_upload_id and tect.emp_code = '" + emp_code + "'";
            
            
            DataTable dt = new DataTable();
            comboBox1.BeginUpdate();
            try
            {
                connection_string = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);
                connection_string.Open();
                using (SqlCommand cmd = new SqlCommand(sqlquery, connection_string))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        dt = new DataTable();
                        dt.Load(dr);
                    }
                }
                connection_string.Close();
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    comboBox1.Items.Add(dt.Rows[i]["Request_Name"]);
                }

                comboBox1.SelectedIndex = 0;
                
                
            }   //try block

            finally
            {
                if (dt != null)
                {
                    dt.Dispose();
                    dt = null;
                }
                comboBox1.EndUpdate();
            }   //finally block
            
            
        }

    }//public partial class Form1 : Form
}//namespace Test1

Code is working fine
The problem is in csv file, when one column doesnot contain data(empty)
and that column in table is type date or datetime or any type
It gives the following error
The given value of type from the datasource cannot be converted to type date of the specified target column

[Knvn]

Try using ColumnMappings

bc.ColumnMappings.Add(yourSourceColumnName,yourDestinationCoulmnName);

In the above code, replace yourSourceColumnName with exact ColumnName of the DataTable and replace yourDestinationCoulmnName with exact ColumnName of the database.

Before doing that, when you create the datatable you need to add the columns with the corresponding datatype:

yourDataTable.Columns.Add(new DataColumn("yourColumnName", Type.GetType("System.DateTime")));

//Then you need to add individual rows like:
DataRow datarow = dtFileData.NewRow();
datarow["yourColumnName"]=yourSplittedValue[correspondingColumnIndex].Trim();

[Arjun_Sarankulu]

Thanks for your reply
I cant predict the column name or data type of the column
As my program is dumping data to different table at runtime by selecting the table from combo-box

[Knvn]

Ok, then you can put a loop & check for the datatype. If the type is of DateTime then you can handle it.

if(datarow[0].GetType() == typeof(DateTime))
    datarow[0] = DBNull.Value;

[Arjun_Sarankulu]

Thanks for your reply.
This is not the case
For example.
Suppose any column of any type contain 10 rows
But in between there can be empty row data
then that empty to be dump in table which is selected
Please go through my above code

[Knvn]

Try this:

DateTime dtm;
                while (!sr.EndOfStream)
                {
                    value = sr.ReadLine().Split(',');

                    if (value.Length == dt.Columns.Count)
                    {
                        row = dt.NewRow();

                        for (int i = 0; i < value.Length; i++)
                        {
                            if (row[i].GetType() == typeof(DateTime))
                            {
                                if (DateTime.TryParse(value[i], out dtm))
                                    row[0] = dtm;
                                else
                                    row[0] = DBNull.Value;
                            }
                            else
                                row[0] = value[i];
                        }
                        dt.Rows.Add(row);
                    }
                }