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)
{
SqlTransaction transaction = null;
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);
string flush_flag = (string)cmd.ExecuteScalar();
MessageBox.Show("flushflag: " + flush_flag);
if (flush_flag == "1")
{
//here i will be truncating the table
}
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_string.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = tablename;
bc.BatchSize = dt.Rows.Count;
connection_string.Open();
transaction = connection_string.BeginTransaction();
bc.WriteToServer(dt);
bc.Close();
connection_string.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 Test1Actually i have created a table in which column contain a table name and another column contain flag
If the flag is 1 then first i have to truncate the records of the table before inserting
string query1 = "select is_Flush from mst_csv_upload where request_name = '" + tablename + "'";
here is_Flush if returns 1 then m truccating table
But i m getting the following error:
Execute scalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.
The transaction property of the command has not been initialised
I am not getting where i am wrong
According to me my flow of program is proper
