i want to export excel records to my sql server if not exists else it should show the exitsting records.
here where i code this is not workign properly .
is there any method to export excel records if not exists
here lblfilename constist excel fle and this.ddlSheets.SelectedItem.Text is takes by default excel first sheet
i want to insert those records which are not exists int the sql database?
how could i implement this?
try
{
p = this.lblFileName.Text;
this.ddlSheets.SelectedIndex = 1;
//Create connection string to Excel work book
string excelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(p) + @";Extended Properties=""Excel 8.0;HDR=YES;""";
//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select * from [" + this.ddlSheets.SelectedItem.Text + "]",
excelConnection);
excelConnection.Open();
dReader = cmd.ExecuteReader();
sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "track_new";
bool flag = true;
while (dReader.Read())
{
string s = "";
string excel = "";
SqlConnection obj = new SqlConnection(strConnection);
// SqlCommand objcmd = new SqlCommand("select * from track_new", obj);
SqlDataAdapter da = new SqlDataAdapter("select * from track_new", obj);
DataSet ds = new DataSet();
obj.Open();
da.Fill(ds, "track_new");
excel = dReader[0].ToString();
if (excel != "")
{
int i = 0;
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
//this.lblMessage.Text = objDataSet.Tables[0].TableName.ToString();
s = ds.Tables[0].Rows[i][0].ToString();
if (ds.Tables[0].Rows[i][0].ToString() == excel)
{
s = ds.Tables[0].Rows[i][0].ToString();
flag = true;
break;
}
else
{
flag = false;
continue;
}
}
}
if (flag == true)
{
this.lblMessage.Text = "\n" + "These Records Already Exists in the Database" + "
";
// this.ListBox1.Visible = true;
this.ListBox1.Items.Add(s);
for (int j = 0; j < this.ListBox1.Items.Count; j++)
{
this.ListBox1.Items[j].Selected = true;
if (this.ListBox1.Items[j].Selected)
{
this.lblMessage.Text = this.lblMessage.Text + this.ListBox1.Items[j].Value.ToString() + ",\n";
}
}
}
//else
//{
// sqlBulk.WriteToServer(dReader);
// ClientScriptManager script = Page.ClientScript;
// script.RegisterStartupScript(this.GetType(), "alert", "alert('Records Successfully exported')", true);
// // dReader.Close();
//}
//obj.Close();
}
if (flag == false)
{
sqlBulk.WriteToServer(dReader);
ClientScriptManager script = Page.ClientScript;
script.RegisterStartupScript(this.GetType(), "alert", "alert('Records Successfully exported')", true);
// dReader.Close();
}
//sqlBulk.WriteToServer(dReader);
//ClientScriptManager script = Page.ClientScript;
//script.RegisterStartupScript(this.GetType(), "alert", "alert('Records Successfully exported')", true);
}
catch (SqlException s)
{
this.lblMessage.Visible = true;
this.lblMessage.Text = s.Message.ToString();
}
plesae i really need help and its very urgent i need to build it
Loading
naura paxPosted Oct 30, 2009, 6:12 AM
any progress?... you can easily find "stored procedure" just google it!!
naura paxPosted Oct 30, 2009, 4:31 AM
you'll just have to give a name and write your insert query with if statment in its body.
And in your code in the you will have to use Command object of ado.net as you use for queries but select type:
cmd.CommandType = CommandType.StoredProcedure;]
cmd.CommandText= "storedprocedurename";
gouthami vasalaPosted Oct 30, 2009, 4:00 AM
could provide me code
naura paxPosted Oct 30, 2009, 2:53 AM
you can use a trigger your table which checks for duplicate and gives error and does not allow to add duplicate (trigger definition is easily available).
or you should use Stored Procedure with insert query and an if condition before the query to check for duplicate , in that case you cannot use bulk copy and add rows one by one in a loop.