.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> C# >> Post New QuestionBookmark and Share Subscribe to Forum

DotNet Window Application Question

Posted By: Amar     Posted Date: August 01, 2011    Points:5   Category :C#
Sir i am using this code to read data from excel spread sheet and try to insert in sql server using sqlbulkcopy class, but it is showing this error

(writetoserver requires open and available connection)

here is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.ProviderBase;

namespace SecurAxDataUploader
{
public partial class Uploader : Form
{
public Uploader()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{


try
{

//String for SqlConnection
string strsqlcon = "Data Source=ICARD-PC;Initial Catalog=SecuraxDB;Persist Security Info=True;User ID=sa";
//String for OLedbConnection For Excel Sheet
string stroledbcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ExcelDataFile.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

//string stroledbcon = @"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+ System.IO.Path.GetDirectoryName(excelFileName) + @"\" +System.IO.Path.GetFileName(ExcelDataFile.xls)+ @";Extended Properties='Excel 8.0;HDR=YES'";
// SQL connection To Fetch Data from Excel

SqlConnection sqlConnection = new SqlConnection(strsqlcon);

// Oledb connection To Fetch Data from Excel

OleDbConnection excelConnection = new OleDbConnection(stroledbcon);

// Oledb command To Fetch Data from Excel
//string ol="select [Emp_ID],[Emp_FirstName],[Emp_LastName],[Card_ID],[Comp_ID],[Tally_No],[Cat_Code],[DOB],[DOJ],[Trade_Code],[Dept_Code],[Desig_Code],[Job_No],[Address],[Phone],[Photo_Image],[Status],[CreatedDate],[CreatedBy],[ModifiedDate],[ModifiedBy],[Version],[PH_Hrs],[RH_Hrs],[SentDate],[IsManager],[Managerid],[Emp_Email],[Shift_Code] from [Emploee$]";

OleDbCommand cmd = new OleDbCommand("select * from [emp$]", excelConnection);
excelConnection.Open();


OleDbDataReader odr = cmd.ExecuteReader();


//fetching data from Sheet so provide sheet name here






if (odr.HasRows == true)
MessageBox.Show("data is there");

//Now using Sql Bulk Copy class
SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnection);
sqlBulk.DestinationTableName = "Employee";

//Mapping Column from excel to sql server

sqlBulk.ColumnMappings.Add("Emp_ID", "Emp_ID");
sqlBulk.ColumnMappings.Add("Emp_FirstName", "Emp_FirstName");
sqlBulk.ColumnMappings.Add("Emp_LastName", "Emp_LastName");
sqlBulk.ColumnMappings.Add("Card_ID", "Card_ID");
sqlBulk.ColumnMappings.Add("Comp_ID", "Comp_ID");
sqlBulk.ColumnMappings.Add("Tally_No", "Tally_No");
sqlBulk.ColumnMappings.Add("Cat_Code", "Cat_Code");
sqlBulk.ColumnMappings.Add("DOB", "DOB");
sqlBulk.ColumnMappings.Add("DOJ", "DOJ");
sqlBulk.ColumnMappings.Add("Trade_Code", "Trade_Code");
sqlBulk.ColumnMappings.Add("Dept_Code", "Dept_Code");
sqlBulk.ColumnMappings.Add("Desig_Code", "Desig_Code");
sqlBulk.ColumnMappings.Add("Job_No", "Job_No");
sqlBulk.ColumnMappings.Add("Address", "Address");
sqlBulk.ColumnMappings.Add("Phone", "Phone");
sqlBulk.ColumnMappings.Add("Photo_Image", "Photo_Image");
sqlBulk.ColumnMappings.Add("Status", "Status");
sqlBulk.ColumnMappings.Add("CreatedDate", "CreatedDate");
sqlBulk.ColumnMappings.Add("CreatedBy", "CreatedBy");
sqlBulk.ColumnMappings.Add("ModifiedDate", "ModifiedDate");
sqlBulk.ColumnMappings.Add("ModifiedBy", "ModifiedBy");
sqlBulk.ColumnMappings.Add("Version", "Version");
sqlBulk.ColumnMappings.Add("PH_Hrs", "PH_Hrs");
sqlBulk.ColumnMappings.Add("RH_Hrs", "RH_Hrs");
sqlBulk.ColumnMappings.Add("SentDate", "SentDate");
sqlBulk.ColumnMappings.Add("IsManager", "IsManager");
sqlBulk.ColumnMappings.Add("Managerid", "Managerid");
sqlBulk.ColumnMappings.Add("Emp_Email", "Emp_Email");
sqlBulk.ColumnMappings.Add("Shift_Code", "Shift_Code");

// write it to server

sqlBulk.WriteToServer(odr);
excelConnection.Close();

}
catch (Exception EX)
{
MessageBox.Show(EX.ToString());

}

finally
{
MessageBox.Show("Sucessfully Inserted");
}

}


}
}







Responses
Author: Sasi Prabhu             
Posted Date: August 01, 2011     Points: 20   
Author: Gowthammanju             
Posted Date: August 02, 2011     Points: 20   


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend