Thursday, September 10, 2009

.Net-Insert Excel Data into Database

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Empinfo.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (OleDbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=SCSHYD-067\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "INA_EIS_EMOF_EMEM_OFFL";
bulkCopy.WriteToServer(dr);
}
}
}

SQl-Insert Excel Data into Database

/****** Object: StoredProcedure [dbo].[fs_prog_rt_InsertTransaction] Script Date: 09/10/2009 19:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[fs_prog_rt_InsertTransaction]
@xmlData XML,
@ErrorMessage varchar(500) OUTPUT
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;

DECLARE @SubId VARCHAR(20)
DECLARE @EntryType VARCHAR(50)
DECLARE @ContainerCode VARCHAR(20)
DECLARE @EquipmentId VARCHAR(20)
DECLARE @ProcessCode VARCHAR(20)
DECLARE @TestTypeCode VARCHAR(20)
DECLARE @CreatedByUserId VARCHAR(50)
DECLARE @CreatedTime VARCHAR(50)
DECLARE @idoc INT
DECLARE @LogId INT
DECLARE @return_Value INT
DECLARE @ValidationErrorMessage VARCHAR(500)

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

SELECT * into #temp_table FROM OPENXML (@idoc,'/NewDataSet/RT_Transaction',0)
WITH (LogId int 'LogId',
SubId varchar(50) 'SubId',
EntryType Varchar(50) 'EntryType',
ContainerCode varchar(50) 'ContainerCode',
CreatedByUserId varchar(10) 'CreatedByUserId',
ProcessCode varchar(100) 'ProcessCode',
EquipmentId varchar(50) 'EquipmentId',
TestTypeCode varchar(50) 'TestTypeCode',
CreatedTime varchar(100) 'CreatedTime')

EXEC sp_xml_removedocument @idoc

CREATE TABLE #temp_Errortable (
LogId int,
SubId varchar(50),EntryType Varchar(50),ContainerCode varchar(50),CreatedByUserId varchar(10),ProcessCode varchar(100),EquipmentId varchar(50),TestTypeCode varchar(50),CreatedTime varchar(100),ErrorMessage Varchar(250))




SELECT TOP 1 @LogId = [LOGID] FROM #temp_table

WHILE (ISNULL(@LogId,0) <> 0)
BEGIN
SELECT
@SubId = SubID,
@EntryType = EntryType,
@ContainerCode = ContainerCode,
@EquipmentId = EquipmentId,
@ProcessCode = ProcessCode,
@TestTypeCode = TestTypeCode,
@CreatedByUserId = CreatedByUserId,
@CreatedTime = CreatedTime
FROM #temp_table WHERE [LOGID] = @LogId

IF (@EntryType = 'Cart/Box')
BEGIN
EXEC @return_Value = dbo.RT_ValidateAndInsertCart @SubId,@EntryType,@ContainerCode,@CreatedByUserId,@ValidationErrorMessage OUTPUT,@ErrorMessage OUTPUT
END
ELSE
BEGIN
EXEC @return_Value = dbo.RT_ValidateAndInsertEquipment @SubId,@EntryType,@EquipmentId,@TestTypeCode,@ProcessCode,@CreatedByUserId,@ValidationErrorMessage OUTPUT,@ErrorMessage OUTPUT
END
IF (@ValidationErrorMessage <> '')
BEGIN
-- INSERT INTO #temp_Errortable SELECT LogId,SubId,EntryType,ContainerCode,CreatedByUserId,ProcessCode,EquipmentId,TestTypeCode,CreatedTime,@ValidationErrorMessage AS ErrorMessage FROM #temp_table Where LogId=@LogId
INSERT INTO #temp_Errortable Values(@LogId,@SubId,@EntryType,@ContainerCode,@CreatedByUserId,@ProcessCode,@EquipmentId,@TestTypeCode,@CreatedTime,@ValidationErrorMessage)
END

DELETE #temp_table WHERE [LOGID] = @LogId
set @LogId = 0
SELECT TOP 1 @LogId=[LOGID] FROM #temp_table
END

DROP TABLE #temp_table

Select * From #temp_Errortable
DROP TABLE #temp_Errortable
SET NOCOUNT OFF
END TRY
BEGIN CATCH
BEGIN
SELECT @ErrorMessage=ERROR_MESSAGE()
END
END CATCH
END