I have a application which take data from CSV and dump to the respective table
using SQLBULKCOPY
I am using the same connection string for truncating data from the same table
and while writing to server
SqlBulkCopy bc = new SqlBulkCopy(connection_string1.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.WriteToServer(dt);//Its giving exception on this line
I am not getting what is wrong
Arjun_Sarankulu -3 Junior Poster
sandeepparekh9 109 Posting Whiz
post a screen shot of the error or some data
Arjun_Sarankulu -3 Junior Poster
namespace Test1
{
public partial class Form1 : Form
{
StreamWriter SW;
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 = "";
string tablename = "";
tablename = Convert.ToString(comboBox1.SelectedItem);
SqlConnection connection_string = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);
try
{
connection_string.Open();
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;
SqlDataReader dtreader = cmd_dtsource.ExecuteReader();//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]);
}
dtreader.Close();
string conn_string = "Data Source = " + servername + "; " + "Integrated Security = false; "
+ "Initial Catalog = " + dbname + "; " + "user id = " + username + "; "
+ "password = " + pwd;
string query1 = "select is_csv_upload from mst_csv_upload where request_name = '" + tablename + "'";
SqlConnection connection_string1 = new SqlConnection(conn_string);
connection_string1.Open();
SqlCommand cmd1 = new SqlCommand(query1, connection_string1);
bool is_csv_upload = (bool)cmd1.ExecuteScalar();
if (is_csv_upload == true)
{
if (openFileDialog1.FileName == "")
{
MessageBox.Show("File Not Selected");
return;
}//if (openFileDialog1.FileName == "")
string filepath = openFileDialog1.FileName;
string query2 = "select is_Flush from mst_csv_upload where request_name = '" + tablename + "'";
SqlCommand cmd = new SqlCommand(query2, connection_string1);
bool flush_flag = (bool)cmd.ExecuteScalar();
string coundata = "SELECT COUNT(*) FROM " + tablename;
SqlCommand rowcount = new SqlCommand(coundata, connection_string1);//to count rows in the table present
if (flush_flag == true)
{
SqlTransaction transaction = connection_string1.BeginTransaction();
string query_trunc = "TRUNCATE TABLE " + tablename;
SqlCommand cmd_delete = new SqlCommand(query_trunc, connection_string1);
cmd_delete.Transaction = transaction;
cmd_delete.CommandType = CommandType.Text;
cmd_delete.ExecuteNonQuery();
transaction.Commit();
}//End of If condition
long countStart = System.Convert.ToInt32(rowcount.ExecuteScalar());
StreamReader sr = new StreamReader(filepath);
SW = File.CreateText(openFileDialog1.FileName + ".err");
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);
}
else
{
SW.WriteLine(value);
}
} //End Of while
SqlBulkCopy bc = new SqlBulkCopy(connection_string1.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = tablename;
bc.BatchSize = dt.Rows.Count;
try
{
bc.WriteToServer(dt);[B]Error:Login failed for User[/B]
bc.Close();
long countEnd = System.Convert.ToInt32(rowcount.ExecuteScalar());
countEnd = countEnd - countStart; //to check no of rows inserted in the table
MessageBox.Show("Data dumped to table : " + tablename + " and " + countEnd + " row(s) affected succesfully and also check the err file for the records which is not inserted");
/*if (MessageBox.Show("Do u want to process another file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
}
else
{
foreach (Form ab in Application.OpenForms)
{
if (ab is Login_Form)
{
ab.Close();
}
}
this.Close();
}*/
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}//if (is_csv_upload == true)
try
{
if (storeproc != "" && storeproc != "NULL") //This is for proc present for selected table
{
connection_string1.Open();
Uploadbutton.Enabled = false;
//MessageBox.Show("Proc-Name is present");
SqlCommand cmdproc = new SqlCommand(storeproc, connection_string1);
cmdproc.CommandType = CommandType.StoredProcedure;
Int32 I = (Int32)cmdproc.ExecuteScalar();
if (I == 1)
{
MessageBox.Show("Executed Successfully");
}
else
{
MessageBox.Show("Execution Failed");
}
}
if (MessageBox.Show("Do u want to process another file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
}
else
{
foreach (Form ab in Application.OpenForms)
{
if (ab is Login_Form)
{
ab.Close();
}
}
this.Close();
}
this.Close();
} // try
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
connection_string1.Close(); //closing the connection
}
}
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
}//public partial class Form1 : Form
}//namespace Test1
Momerath 1,327 Nearly a Senior Poster Featured Poster
Based on the error, I'd say that System.Configuration.ConfigurationManager.AppSettings["database"] doesn't have valid userid/password in it.
Stefan_Lam88 0 Junior Poster
Did you put correct username and password for ur apps setting in web config?
Arjun_Sarankulu -3 Junior Poster
Thnaks Momerath and stefan for giving your valuable time
Ya its working fine because I have 2 form one for login ie authenticating emp code
after valid it goes to next page(which i am taking from config file) and it displays data in the combo-box related to that emp which i am connecting to database through connection string as its display tables in the combo-box, table might be from different database and different server thats why i have created a table whose columns contain server name,
database name ,user name and password(using this i am getting a new connection string which can help to dump data from csv to respective table of the respective data base)
For testing purpose i have created table called test and inserted 2 records
and i have one column called is_flush if its return true then it will truncate the data which is previously contain in the table.
This condition is working fine using new connection string(As i check using select * from test it gives 0 rows selected) and while
bc.WriteToServer(dt);It gives error at this point saying that login failed for user
I am not getting what is wrong in my code
Above is my code
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.