how to insert excel sheet into database and how to retrieve the data from sheet?

hi,
string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=excelfile.xls;Extended Properties=Excel 8.0";
string con_str = @"Data Source=sourcename; Initial Catalog = DBname; Integrated Security=True;";
OleDbConnection oledbConn = new OleDbConnection(conStr);
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "Table1");
DataTable dt = new DataTable();
dt = ds.Tables["Table1"];
SqlConnection con = new SqlConnection(con_str);
foreach (DataRow dr in dt.Rows)
{
//obtain values of the excel
con.open()
SqlCommand cmd1 = new SqlCommand("insert statement" , con);
cmd1.executenonquery();
con.close();
}

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

hi actually am using sql server 2005,then can i change oledb to sqlconnection,is your code work with sql connection?

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

[HttpPost]
        public ActionResult AmazonOrders(HttpPostedFileBase AmazonUpload)
        {
            if (AmazonUpload.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || AmazonUpload.FileName.EndsWith(".xslx", StringComparison.OrdinalIgnoreCase))
            {
                var fileName = Path.GetFileName(AmazonUpload.FileName);
                var path = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
                AmazonUpload.SaveAs(path);
                var excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName) + ";Extended Properties=Excel 12.0;");
                OleDbConnection objOlecon = new OleDbConnection();
                objOlecon.ConnectionString = excelConnectionString;
                objOlecon.Open();
                OleDbDataAdapter objOleDa = new OleDbDataAdapter("Select * from [Amazon-orders$]", objOlecon);
                DataTable objdt = new DataTable();
                objOleDa.Fill(objdt);
                var import = _dbContext.ExecuteStoredProcedure("ImportOrders", objdt);
                ViewData["message"] = "Records Imported Successfully";

                objOlecon.Close();
            }
            return View();
        }

Write the following code in the ObjectContext class:

public int ExecuteStoredProcedure(string commandText, DataTable parameters)
        {
            var context = ((IObjectContextAdapter)(this)).ObjectContext;

            //var connection = context.Connection;
            var connection = this.Database.Connection;
            //Don't close the connection after command execution


            //open the connection for use
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            //create a command object
            using (var cmd = connection.CreateCommand())
            {
                //command to execute
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "AmazonOrdersTemp";
                param.SqlDbType = SqlDbType.Structured;
                param.Value = parameters;
                param.Direction = ParameterDirection.Input;

                // move parameters to command object
                cmd.Parameters.Add(param);
                int result = cmd.ExecuteNonQuery();
                return result;
            }

        }

Import Orders is a stored procedure, below is the proc:

CREATE PROCEDURE ImportOrders    
(    
 @AmazonOrdersTemp AmazonOrderType READONLY     
)    
AS    
BEGIN    
CREATE TABLE AmazonOrders  
(  
   //specify your columns here 
 )  
    
 INSERT INTO AmazonOrders    
 SELECT *    
 FROM @AmazonOrdersTemp    
     
END  
  

This is through codefirst approach

Mark as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Refer these links for import and export
http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html
http://www.codeproject.com/Tips/636719/Import-MS-Excel-data-to-SQL-Server-table-using-Csh
http://www.codeproject.com/Questions/484635/Howplustoplusimportplusexcelplusfileplustoplusgrid

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

hi i tried like this....
protected void btnSend_Click(object sender, EventArgs e)
{

String strConnection = "Data Source=E-3D34;Initial Catalog=Ektha;Integrated Security=True";

string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = "user id=sa;password=e@2013;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
SqlConnection excelConnection =new SqlConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
SqlCommand cmd = new SqlCommand("Select [EmployeeID],[EmployeeNAME],[Designation],[Department],[DOJ],[DOR],[Monthly Salary],[Medical],[HRA],[Incentive],[Other All],[OT],[Gross Total],[PF],[ESI],[Advance],[Other Ded],[PT],[TDS-1],[Total Ded],[Net Amt] from [Sheet1$]",excelConnection);
excelConnection.Open();
SqlDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "PayDescription";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

but am getting the error as....can you please help me

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Source Error:


Line 31: //Create OleDbCommand to fetch data from Excel
Line 32: SqlCommand cmd = new SqlCommand("Select [EmployeeID],[EmployeeNAME],[Designation],[Department],[DOJ],[DOR],[Monthly Salary],[Medical],[HRA],[Incentive],[Other All],[OT],[Gross Total],[PF],[ESI],[Advance],[Other Ded],[PT],[TDS-1],[Total Ded],[Net Amt] from [Sheet1$]",excelConnection);
Line 33: excelConnection.Open(); Line 34: SqlDataReader dReader;
Line 35: dReader = cmd.ExecuteReader();

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Your connection string is not proper try putting @"" before connection string.
Also specify con.Open() and con.Close();

Mark as answer if satisfied...

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

now am getting the error as
An attempt to attach an auto-named database for file Excel 12.0 failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share...
can you please tell me,what should i do,i didn't understand this error

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Did you try using OleDBCommand...?


Mark as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

hi no i didnt use oledb,because i created table in sql server

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator