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 Test1Its 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
