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)
        {
            SqlTransaction transaction = null;
            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();

                cmd = connection_string.CreateCommand();
                cmd.CommandText = "select request_server, request_db, request_user, request_pwd, storeProcedure from mst_csv_upload where request_name = '" + tablename + "'";
                
                SqlDataReader dtreader = cmd.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]);
                }

                if (storeproc != "")    //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();
                }


                connection_string.Close();
                string conn_string = "Data Source = " + servername + "; " + "Integrated Security = false; " 
                    + "Initial Catalog = " + dbname + "; " + "user id = " + username + "; "
                    + "password = " + pwd;                  
                //MessageBox.Show("Connection data: " + conn_string);
                //MessageBox.Show("flushflag: " + flush_flag);

                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);

                bc.DestinationTableName = tablename;

                bc.BatchSize = dt.Rows.Count;

                
                connection_string1.Open();

                transaction = connection_string1.BeginTransaction();
                bc.WriteToServer(dt);

                bc.Close();
                transaction.Commit();
                connection_string1.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();
                }
                //this.Close();
            }

            catch (Exception err)
            {

                transaction.Rollback();
                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

Its giving NullReference Exception
Object reference not set to an instance of the object
Its giving exception in catch block
transaction.Rollback();
In upper part of the code i have declared
Transaction transaction = null;

Is it the problem because of this

Recommended Answers

All 3 Replies

hi,
Please try to initialize your sqltransaction object like this and try.

connection.Open();
        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

I hope this thing is not one method Mitja. This is what is commonly referred to as a code smell.

Btw, you could tell us where this error uccur. I wont look not the whole code to find you the error in it.

But i am not doing any thing with SqlCommand and am doing with SQLBulkCopy
to copy the bulk data from csv

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.