Thursday, 18 October 2012

excel to sqlserver database in asp.net c#


private void LoadExcelIntoDB()
    {
        // Connection String to Excel Workbook
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Develop\Customer Site Designs\All Events Directory\Site Design\uk_postcodes.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
        // Create Connection to Excel Workbook
        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
        {
            OleDbCommand command = new OleDbCommand("Select PostCode, County, Latitude, longitude FROM [Data$]", connection);

            connection.Open();

            // Create DbDataReader to Data Worksheet
            using (DbDataReader dr = command.ExecuteReader())
            {
                // SQL Server Connection String
                //string sqlConnectionString = "Data Source=AllEventsDBConnectionString1; Initial Catalog=PostCodeLookup;Integrated Security=True";
                string sqlConnectionString = @"Data Source=(local)\SQLEXPRESS;Database=AllEventsDB;uid=event;pwd=event";

                // Bulk Copy to SQL Server
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "PostCodeLookup";
                    bulkCopy.WriteToServer(dr);
                }
            }
        }
    }

No comments:

Post a Comment