.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

String Array

strEmployeeName="Tester(Test)";
///

/// It will return Full name and FQN
///

///
///
public string[] SplitEmployeeNameFQN(string strEmployeeName)
{
string[] strInputValue = strEmployeeName.Split(';');
string[] arrReturn = new string[2];
//string employeeName = string.Empty;
string strFQN = string.Empty;
for (int i = 0; i < strInputValue.Length; i++)
{
if (!(strInputValue[i] == ""))
{
int startIndex = strInputValue[i].IndexOf("(");
strFQN = strInputValue[i].Substring(startIndex + 1);
strFQN = strFQN.Replace(")", "");
arrReturn[0] += strFQN.Trim() + ";";
arrReturn[1] += strInputValue[i].Remove(startIndex).Trim() + ";";
}
}

return arrReturn;
}

SQL Search

CREATE PROCEDURE [dbo].[GetSparePartsECRETS]
(
@pnEcretsNum INT = NULL
,@pnvcEcretsTitle NVARCHAR(100) = NULL
,@pnvcOriginatorID NVARCHAR(50) = NULL
)
AS

BEGIN

/*SET NOCOUNT TO ON AND NO LONGER DISPLAY THE COUNT MESSAGE*/
SET NOCOUNT ON

/*START THE TRY BLOCK*/
BEGIN TRY
IF @pnEcretsNum = '' SET @pnEcretsNum = NULL
IF @pnvcEcretsTitle = '' SET @pnvcEcretsTitle = NULL
IF @pnvcOriginatorID = '' SET @pnvcOriginatorID = NULL

SELECT
TBLE.ECRETSID,
TBLE.[Title],
[TBEI].[FirstName] +' '+COALESCE([TBEI].[LastName],'') AS Originator
FROM dbo.tblECRETS TBLE,
dbo.TblEmployeeInfo TBEI
WHERE (TBLE.ECRETSID = @pnEcretsNum OR @pnEcretsNum IS NULL)
AND (TBLE.Title = @pnvcEcretsTitle OR @pnvcEcretsTitle IS NULL)
AND (TBLE.OriginatorID = @pnvcOriginatorID OR @pnvcOriginatorID IS NULL)
AND TBLE.OriginatorID = TBEI.FQN

END TRY
BEGIN CATCH
IF @@ERROR != 0
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END
END CATCH

/*Reset SET NOCOUNT to OFF*/
SET NOCOUNT OFF
END

Web Services

1. Which attribute is used in order that the method can be used as WebService ?
WebMethod attribute has to be specified in order that the method and property can be treated as WebService.
2. Do webservice have state ?
Twist :- How can we maintain State in Webservices ?
Webservices as such do not have any mechanism by which they can maintain state.
3. How to Authentication for Web Services
using SOAP headers
4. HOW TO: Pass Current Credentials to an ASP.NET Web Service
myProxy.Credentials = System.Net.CredentialCache.DefaultCredentials
OR
localhost.MyWebService myService = new localhost.MyWebService();
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("UserName", "Password");
myCredentials.Add(new Uri(myService.Url), "Basic", netCred);
myService.Credentials = myCredentials;
---------
DataSet dsPartDetails = new DataSet();
Item[] arrPartDetails = null;
arrPartDetails = objSrchPartsCommonMethods.GetPartService(vendorPartNum, manPartNum);
dsPartDetails = objSrchPartsCommonMethods.ConvertArrayToDataSet(arrPartDetails);
----------------------------------


public FirstSolarMasterDataServices.Item[] GetPartService(string partNumber, string manPartNum)
{
try
{
FirstSolarMasterDataServices.MasterData objFirstSolarERPWebService = new FirstSolarMasterDataServices.MasterData();
FirstSolarMasterDataServices.Item[] arrParts;

objFirstSolarERPWebService = ImpersonateDevUser();

//Calling Web services menthods
arrParts = objFirstSolarERPWebService.GetItems(ConfigurationManager.AppSettings["CompanyId"].ToString(), "", partNumber, "","",manPartNum);
return arrParts;
}
catch
{
throw;
}
//code used at Onsite
//comment this code at ODC

//finally
//{
// if (m_Ctx != null)
// {
// m_Ctx.Undo();
// }
//}
}

private FirstSolarMasterDataServices.MasterData ImpersonateDevUser()
{
FirstSolarMasterDataServices.MasterData objFirstSolarErpModifyWebService = new FirstSolarMasterDataServices.MasterData();

try
{
//code used at ODC
//comment this code at Onsite
//*************************************

NetworkCredential nwCredentials = new NetworkCredential();

nwCredentials.UserName = ConfigurationManager.AppSettings["NWUserName"].ToString();
nwCredentials.Password = ConfigurationManager.AppSettings["NWPassword"].ToString();
nwCredentials.Domain = ConfigurationManager.AppSettings["NWDomain"].ToString();

//Assignign credentials to web services
objFirstSolarErpModifyWebService.Credentials = new NetworkCredential(nwCredentials.UserName, nwCredentials.Password, nwCredentials.Domain);

//*************************************


//code used at Onsite
//comment this code at ODC
//*************************************

//m_Ctx = WindowsIdentity.GetCurrent().Impersonate();
//RevertToSelf();

//objFirstSolarERPModifyWebService.Url = ModifyWebService;

// //We have to authenticate ourselves with the web service.
//objFirstSolarERPModifyWebService.PreAuthenticate = true;
//objFirstSolarERPModifyWebService.Credentials = System.Net.CredentialCache.DefaultCredentials;

//*************************************
}
catch
{
throw;
}

return objFirstSolarErpModifyWebService;
}
------------------
Covert Arrat to Dataset
---
public DataSet ConvertArrayToDataSet(Object[] objArray)
{
XmlTextReader reader;
DataSet arrayDataSet = new DataSet();
MemoryStream memStream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(memStream, System.Text.Encoding.UTF8);
XmlSerializer serializer = new XmlSerializer(objArray.GetType());
try
{
serializer.Serialize(writer, objArray);
memStream.Position = 0;
reader = new XmlTextReader(memStream);
arrayDataSet.ReadXml(reader);

return arrayDataSet;
}
catch (Exception ex)
{
throw;
}
}

Use Data View

protected void BindEmployee()
{
ConnectDB objConnectDB = null;
SqlConnection objSqlConnection = null;
SqlCommand objSqlCommand = null;
SqlDataAdapter objSqlDataAdapter = null;
DataSet objDataSet = null;
try
{
objConnectDB = new ConnectDB();
objSqlConnection = objConnectDB.getConnection();
objDataSet = new DataSet();
//string strSQL = "SELECT GUID,FirstName+' '+coalesce(LastName,'') as FullName from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";
string strSQL = "SELECT GUID, FirstName+' '+coalesce(LastName,'') as FullName, REPLACE(FirstName+' '+coalesce(LastName,''),'''','\\''') as FullNameRead from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";

objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);

objSqlDataAdapter.Fill(objDataSet);

if (txtFilter.Text == "")
{
grdvwEmployee.DataSource = objDataSet.Tables[0];
}
else
{
//DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim() + "%'", "", DataViewRowState.CurrentRows);
DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim().Replace("'", "") + "%'", "", DataViewRowState.CurrentRows);
grdvwEmployee.DataSource = dv;
}


grdvwEmployee.DataBind();


}
catch (Exception ex)
{
lblMsg.Text = "Error: " + ex.Message;
}
finally
{
objSqlCommand.Dispose();
objSqlDataAdapter.Dispose();
objDataSet.Dispose();
}
}

Get Selected result with Comma separate

CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO



Insert into dbo.tblItems (ItemName) Values ('Item1')
Insert into dbo.tblItems (ItemName) Values ('Item2')
Insert into dbo.tblItems (ItemName) Values ('Item3')
Insert into dbo.tblItems (ItemName) Values ('Item4')
Go


Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=''
Select @m_ItemsList=@m_ItemsList+ItemName+',' from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)