SubbuUSA
asked on
LINQ to Dict from a Datatable
Experts,
I need a help here..in writing two things
1. LINQ query for extracting column names and column values using LINQ
2. Copy them over to the Table 2 using a typed dataset.
I am using VS 2012 / SQL Server 2008
Table1: Query gets executed and fills my datatable
I have a datatable that contains 3 of 60 columns like this
cic total date
GooG 100 4/30/13 7:04:59 AM
Datatypes: cic is string, total is int, and date is datetime and so on
Table 2: I need to copy this to table B through a typed Dataset if it exists otherwise populate DBNull.Value.
cic total date Add1 Add2 Add3
GooG 100 4/30/13 7:04:59 AM DBNull.Value DBNull.Value DBNull.Value
I need a help here..in writing two things
1. LINQ query for extracting column names and column values using LINQ
2. Copy them over to the Table 2 using a typed dataset.
I am using VS 2012 / SQL Server 2008
Table1: Query gets executed and fills my datatable
I have a datatable that contains 3 of 60 columns like this
cic total date
GooG 100 4/30/13 7:04:59 AM
Datatypes: cic is string, total is int, and date is datetime and so on
Table 2: I need to copy this to table B through a typed Dataset if it exists otherwise populate DBNull.Value.
cic total date Add1 Add2 Add3
GooG 100 4/30/13 7:04:59 AM DBNull.Value DBNull.Value DBNull.Value
ASKER
@FernandoSoto
how can I change this code to accomodate this requirement. Initially I use to read these values from the XML file but not I am reading directly from the SQL Table
how can I change this code to accomodate this requirement. Initially I use to read these values from the XML file but not I am reading directly from the SQL Table
try
{
var source = (from m in xdoc.Descendants("ColumnNumber")
select new
{
Key = m.Attribute("id").Value,
SourceColumn = m.Element("SourceColumnDetail").Value,
SourceType = m.Element("SourceColumnDetail").Attribute("type").Value,
SourceLength = m.Element("SourceColumnDetail").Attribute("length").Value,
SourceAllownulls = m.Element("SourceColumnDetail").Attribute("allownulls").Value
}).ToDictionary(s => s.Key, a => new { a.SourceColumn, a.SourceType, a.SourceLength, a.SourceAllownulls });
foreach (var keyMap in source.Keys)
{
var map = source[keyMap];
int sourceIndex = Convert.ToInt32(keyMap);
if (sourceIndex <= source.Keys.Count)
{
dsStage.Tables["dtSecMast"].Columns.Add(map.SourceColumn);
}
}
}
ASKER
Can anybody help me with the above problem? appreciate your help
ASKER
@kaufmed
Can you help me with this problem
Can you help me with this problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@FernandoSoto
Thanks for your response.
Please find my schema of the destination table. Source data table contains only first 60 columns. Rest of them should have DBNull.value
Column Name Data Type Allow Nulls
RowNumber int identity 4
cic varchar 16
SecurityType varchar 15
OwnershipClass varchar 15
InvestmentType varchar 15
Ticker varchar 15
Cusip varchar 15
Sedol varchar 15
Isin varchar 16
BloombergTicker varchar 32
QuoteCcy varchar 15
DomicileCcy varchar 15
SettleCcy varchar 15
UnderlyingId varchar 24
Industry varchar 15
Description varchar 36
Updated datetime 16
UpdatedBy varchar 16
UnitsOfMeasure varchar 15
SettlementDay int 4
ValuationType varchar 15
AccrualType varchar 15
ParValue numeric 20
ConvertInd smallint 2
ConversionRate numeric 20
MaturityDate datetime 16
ExpirationDt datetime 16
FirstPayment datetime 16
Frequency int 4
ContractSize numeric 22
Exchange varchar 15
IssueDate datetime 16
VariableInd smallint 2
Regs144A varchar 15
DirtyPricingInd smallint 2
ExerciseType varchar 15
ExercisePrice numeric 20
WhenIssued varchar 15
TaxRate numeric 20
PricePctFlag varchar 15
SectorCode varchar 15
GroupInvestmentId varchar 24
SPSubIndustry varchar 15
PriceExpInd varchar 15
TotalIssued numeric 20
TotalShares numeric 22
PalomaIssuerRtg varchar 15
OptionType varchar 15
PricingFactor numeric 20
TradingFactor numeric 20
AvatarID varchar 32
CreditSpread varchar 30
GenevaInvType varchar 15
AssetType varchar 20
RecordClass varchar 24
LowLevUnderlying varchar 24
InactiveDate datetime 16
SuperCategory varchar 60
IncludedInOTCExposureFlag tinyint 1
CouponRate numeric 20
NextInterestStartDate datetime 16
Issuer int 4
BloombergIssuer int 4
PaydownDate datetime 16
PaydownFactor numeric 20
CountryOfRisk varchar 15
CountryOfIncorporation varchar 15
BloombergUniqueId varchar 24
IssuerIndustry varchar 16
ConversionFactor varchar 18
FixedRate varchar 16
BBCollateralType varchar 16
CDSCollateralType varchar 24
InitialSpread numeric 20
NextDividendDate datetime 16
NextDividendRate numeric 20
ClientInvRefId varchar 36
AccrualType1 varchar 15
AccrualType2 varchar 15
CCY1 varchar 15
CCY2 varchar 15
EffectiveDate1 varchar 15
EffectiveDate2 varchar 15
FirstPaymentDate1 varchar 15
FirstPaymentDate2 varchar 15
FirstResetDate1 varchar 15
FirstResetDate2 varchar 15
FixedRate1 numeric 20
FixedRate2 numeric 20
FloatMaturity1 numeric 20
FloatMaturity2 numeric 20
FloatSpread1 numeric 20
FloatSpread2 numeric 20
Frequency1 varchar 15
Frequency2 varchar 15
IndexYC1 varchar 15
IndexYC2 varchar 15
Lag varchar 15
LastPaymentDate1 varchar 15
LastPaymentDate2 varchar 15
LatestResetDate1 varchar 15
LatestResetDate2 varchar 15
LatestResetRate1 numeric 20
LatestResetRate2 numeric 20
LegType1 varchar 15
LegType2 varchar 15
ParValue1 numeric 20
ParValue2 numeric 20
PositionSign smallint 2
ResetFrequency1 varchar 15
ResetFrequency2 varchar 15
SwapMaturityDate varchar 15
Term varchar 15
TIPSIndex varchar 15
YC1 varchar 15
YC2 varchar 15
BarrierType varchar 15
BarrierDirection varchar 15
Barrier1 numeric 20
Barrier2 numeric 20
Rebate1 numeric 20
Rebate2 numeric 20
ReferenceFrequency varchar 15
AssetSwapSpread numeric 20
AssetSwapPymtFreq varchar 15
ExercisePxType varchar 15
FinalPayment numeric 20
Strike numeric 20
AnnualizationFactor numeric 20
DeliveryPrice numeric 20
FixedVarianceRate numeric 20
ReferenceIndex varchar 15
ReturnHorizon varchar 15
ZeroMean int 4
NotionalType varchar 15
PaymentType varchar 15
ShortTermBorrow numeric 20
LongTermBorrow numeric 20
OtherShortTermLiabilities numeric 20
OtherLongTermLiabilities numeric 20
MinorityInterest numeric 20
CurrentMarketCap numeric 20
PreferredEquity numeric 20
SwapReferenceIndex1 varchar 100
SwapReferenceTerm1 varchar 100
SwapFloatMaturity1 numeric 20
SwapReferencedIndex2Cic varchar 100
SwapReferenceIndex2 varchar 100
SwapReferenceTerm2 varchar 100
SwapFloatMaturity2 numeric 20
LoanXID varchar 100
LIN varchar 100
RIC varchar 100
OCCSymbol varchar 100
OTCID varchar 100
last_modified_date datetime 16
RowNumber bigint 8
cic varchar 16
SecurityType varchar 8
OwnershipClass varchar 8
InvestmentType varchar 8
Ticker varchar 12
Cusip varchar 12
Sedol varchar 10
Isin varchar 16
BloombergTicker varchar 32
QuoteCcy varchar 4
DomicileCcy varchar 4
SettleCcy varchar 4
UnderlyingId varchar 24
Industry varchar 4
Description varchar 36
Updated datetime 16
UpdatedBy varchar 16
UnitsOfMeasure varchar 8
SettlementDay int 4
ValuationType varchar 4
AccrualType varchar 8
ParValue numeric 20
ConvertInd smallint 2
ConversionRate numeric 20
MaturityDate datetime 16
ExpirationDt datetime 16
FirstPayment datetime 16
Frequency int 4
ContractSize numeric 22
Exchange varchar 8
IssueDate datetime 16
VariableInd smallint 2
Regs144A varchar 12
DirtyPricingInd smallint 2
ExerciseType varchar 1
ExercisePrice numeric 20
WhenIssued varchar 1
TaxRate numeric 20
PricePctFlag varchar 2
SectorCode varchar 4
GroupInvestmentId varchar 24
SPSubIndustry varchar 12
PriceExpInd varchar 3
TotalIssued numeric 20
TotalShares numeric 22
PalomaIssuerRtg varchar 12
OptionType varchar 12
PricingFactor numeric 20
TradingFactor numeric 20
AvatarID varchar 32
CreditSpread varchar 30
GenevaInvType varchar 8
AssetType varchar 20
RecordClass varchar 24
LowLevUnderlying varchar 24
InactiveDate datetime 16
SuperCategory varchar 60
IncludedInOTCExposureFlag tinyint 1
CouponRate numeric 20
NextInterestStartDate datetime 16
Issuer int 4
BloombergIssuer int 4
PaydownDate datetime 16
PaydownFactor numeric 20
CountryOfRisk varchar 2
CountryOfIncorporation varchar 2
BloombergUniqueId varchar 24
IssuerIndustry varchar 16
ConversionFactor varchar 18
FixedRate varchar 16
BBCollateralType varchar 16
CDSCollateralType varchar 24
InitialSpread numeric 20
NextDividendDate datetime 16
NextDividendRate numeric 20
ClientInvRefId varchar 36
AccrualType1 varchar 10
AccrualType2 varchar 10
CCY1 varchar 3
CCY2 varchar 3
EffectiveDate1 varchar 8
EffectiveDate2 varchar 8
FirstPaymentDate1 varchar 8
FirstPaymentDate2 varchar 8
FirstResetDate1 varchar 8
FirstResetDate2 varchar 8
FixedRate1 numeric 20
FixedRate2 numeric 20
FloatMaturity1 numeric 20
FloatMaturity2 numeric 20
FloatSpread1 numeric 20
FloatSpread2 numeric 20
Frequency1 varchar 3
Frequency2 varchar 3
IndexYC1 varchar 10
IndexYC2 varchar 10
Lag varchar 10
LastPaymentDate1 varchar 8
LastPaymentDate2 varchar 8
LatestResetDate1 varchar 8
LatestResetDate2 varchar 8
LatestResetRate1 numeric 20
LatestResetRate2 numeric 20
LegType1 varchar 10
LegType2 varchar 10
ParValue1 numeric 20
ParValue2 numeric 20
PositionSign smallint 2
ResetFrequency1 varchar 3
ResetFrequency2 varchar 3
SwapMaturityDate varchar 8
Term varchar 10
TIPSIndex varchar 12
YC1 varchar 10
YC2 varchar 10
BarrierType varchar 10
BarrierDirection varchar 10
Barrier1 numeric 20
Barrier2 numeric 20
Rebate1 numeric 20
Rebate2 numeric 20
ReferenceFrequency varchar 10
AssetSwapSpread numeric 20
AssetSwapPymtFreq varchar 10
ExercisePxType varchar 1
FinalPayment numeric 20
Strike numeric 20
AnnualizationFactor numeric 20
DeliveryPrice numeric 20
FixedVarianceRate numeric 20
ReferenceIndex varchar 12
ReturnHorizon varchar 12
ZeroMean int 4
NotionalType varchar 12
PaymentType varchar 12
ShortTermBorrow numeric 20
LongTermBorrow numeric 20
OtherShortTermLiabilities numeric 20
OtherLongTermLiabilities numeric 20
MinorityInterest numeric 20
CurrentMarketCap numeric 20
PreferredEquity numeric 20
SwapReferenceIndex1 varchar 100
SwapReferenceTerm1 varchar 100
SwapFloatMaturity1 numeric 20
SwapReferencedIndex2Cic varchar 100
SwapReferenceIndex2 varchar 100
SwapReferenceTerm2 varchar 100
SwapFloatMaturity2 numeric 20
Thanks for your response.
Please find my schema of the destination table. Source data table contains only first 60 columns. Rest of them should have DBNull.value
Column Name Data Type Allow Nulls
RowNumber int identity 4
cic varchar 16
SecurityType varchar 15
OwnershipClass varchar 15
InvestmentType varchar 15
Ticker varchar 15
Cusip varchar 15
Sedol varchar 15
Isin varchar 16
BloombergTicker varchar 32
QuoteCcy varchar 15
DomicileCcy varchar 15
SettleCcy varchar 15
UnderlyingId varchar 24
Industry varchar 15
Description varchar 36
Updated datetime 16
UpdatedBy varchar 16
UnitsOfMeasure varchar 15
SettlementDay int 4
ValuationType varchar 15
AccrualType varchar 15
ParValue numeric 20
ConvertInd smallint 2
ConversionRate numeric 20
MaturityDate datetime 16
ExpirationDt datetime 16
FirstPayment datetime 16
Frequency int 4
ContractSize numeric 22
Exchange varchar 15
IssueDate datetime 16
VariableInd smallint 2
Regs144A varchar 15
DirtyPricingInd smallint 2
ExerciseType varchar 15
ExercisePrice numeric 20
WhenIssued varchar 15
TaxRate numeric 20
PricePctFlag varchar 15
SectorCode varchar 15
GroupInvestmentId varchar 24
SPSubIndustry varchar 15
PriceExpInd varchar 15
TotalIssued numeric 20
TotalShares numeric 22
PalomaIssuerRtg varchar 15
OptionType varchar 15
PricingFactor numeric 20
TradingFactor numeric 20
AvatarID varchar 32
CreditSpread varchar 30
GenevaInvType varchar 15
AssetType varchar 20
RecordClass varchar 24
LowLevUnderlying varchar 24
InactiveDate datetime 16
SuperCategory varchar 60
IncludedInOTCExposureFlag tinyint 1
CouponRate numeric 20
NextInterestStartDate datetime 16
Issuer int 4
BloombergIssuer int 4
PaydownDate datetime 16
PaydownFactor numeric 20
CountryOfRisk varchar 15
CountryOfIncorporation varchar 15
BloombergUniqueId varchar 24
IssuerIndustry varchar 16
ConversionFactor varchar 18
FixedRate varchar 16
BBCollateralType varchar 16
CDSCollateralType varchar 24
InitialSpread numeric 20
NextDividendDate datetime 16
NextDividendRate numeric 20
ClientInvRefId varchar 36
AccrualType1 varchar 15
AccrualType2 varchar 15
CCY1 varchar 15
CCY2 varchar 15
EffectiveDate1 varchar 15
EffectiveDate2 varchar 15
FirstPaymentDate1 varchar 15
FirstPaymentDate2 varchar 15
FirstResetDate1 varchar 15
FirstResetDate2 varchar 15
FixedRate1 numeric 20
FixedRate2 numeric 20
FloatMaturity1 numeric 20
FloatMaturity2 numeric 20
FloatSpread1 numeric 20
FloatSpread2 numeric 20
Frequency1 varchar 15
Frequency2 varchar 15
IndexYC1 varchar 15
IndexYC2 varchar 15
Lag varchar 15
LastPaymentDate1 varchar 15
LastPaymentDate2 varchar 15
LatestResetDate1 varchar 15
LatestResetDate2 varchar 15
LatestResetRate1 numeric 20
LatestResetRate2 numeric 20
LegType1 varchar 15
LegType2 varchar 15
ParValue1 numeric 20
ParValue2 numeric 20
PositionSign smallint 2
ResetFrequency1 varchar 15
ResetFrequency2 varchar 15
SwapMaturityDate varchar 15
Term varchar 15
TIPSIndex varchar 15
YC1 varchar 15
YC2 varchar 15
BarrierType varchar 15
BarrierDirection varchar 15
Barrier1 numeric 20
Barrier2 numeric 20
Rebate1 numeric 20
Rebate2 numeric 20
ReferenceFrequency varchar 15
AssetSwapSpread numeric 20
AssetSwapPymtFreq varchar 15
ExercisePxType varchar 15
FinalPayment numeric 20
Strike numeric 20
AnnualizationFactor numeric 20
DeliveryPrice numeric 20
FixedVarianceRate numeric 20
ReferenceIndex varchar 15
ReturnHorizon varchar 15
ZeroMean int 4
NotionalType varchar 15
PaymentType varchar 15
ShortTermBorrow numeric 20
LongTermBorrow numeric 20
OtherShortTermLiabilities numeric 20
OtherLongTermLiabilities numeric 20
MinorityInterest numeric 20
CurrentMarketCap numeric 20
PreferredEquity numeric 20
SwapReferenceIndex1 varchar 100
SwapReferenceTerm1 varchar 100
SwapFloatMaturity1 numeric 20
SwapReferencedIndex2Cic varchar 100
SwapReferenceIndex2 varchar 100
SwapReferenceTerm2 varchar 100
SwapFloatMaturity2 numeric 20
LoanXID varchar 100
LIN varchar 100
RIC varchar 100
OCCSymbol varchar 100
OTCID varchar 100
last_modified_date datetime 16
RowNumber bigint 8
cic varchar 16
SecurityType varchar 8
OwnershipClass varchar 8
InvestmentType varchar 8
Ticker varchar 12
Cusip varchar 12
Sedol varchar 10
Isin varchar 16
BloombergTicker varchar 32
QuoteCcy varchar 4
DomicileCcy varchar 4
SettleCcy varchar 4
UnderlyingId varchar 24
Industry varchar 4
Description varchar 36
Updated datetime 16
UpdatedBy varchar 16
UnitsOfMeasure varchar 8
SettlementDay int 4
ValuationType varchar 4
AccrualType varchar 8
ParValue numeric 20
ConvertInd smallint 2
ConversionRate numeric 20
MaturityDate datetime 16
ExpirationDt datetime 16
FirstPayment datetime 16
Frequency int 4
ContractSize numeric 22
Exchange varchar 8
IssueDate datetime 16
VariableInd smallint 2
Regs144A varchar 12
DirtyPricingInd smallint 2
ExerciseType varchar 1
ExercisePrice numeric 20
WhenIssued varchar 1
TaxRate numeric 20
PricePctFlag varchar 2
SectorCode varchar 4
GroupInvestmentId varchar 24
SPSubIndustry varchar 12
PriceExpInd varchar 3
TotalIssued numeric 20
TotalShares numeric 22
PalomaIssuerRtg varchar 12
OptionType varchar 12
PricingFactor numeric 20
TradingFactor numeric 20
AvatarID varchar 32
CreditSpread varchar 30
GenevaInvType varchar 8
AssetType varchar 20
RecordClass varchar 24
LowLevUnderlying varchar 24
InactiveDate datetime 16
SuperCategory varchar 60
IncludedInOTCExposureFlag tinyint 1
CouponRate numeric 20
NextInterestStartDate datetime 16
Issuer int 4
BloombergIssuer int 4
PaydownDate datetime 16
PaydownFactor numeric 20
CountryOfRisk varchar 2
CountryOfIncorporation varchar 2
BloombergUniqueId varchar 24
IssuerIndustry varchar 16
ConversionFactor varchar 18
FixedRate varchar 16
BBCollateralType varchar 16
CDSCollateralType varchar 24
InitialSpread numeric 20
NextDividendDate datetime 16
NextDividendRate numeric 20
ClientInvRefId varchar 36
AccrualType1 varchar 10
AccrualType2 varchar 10
CCY1 varchar 3
CCY2 varchar 3
EffectiveDate1 varchar 8
EffectiveDate2 varchar 8
FirstPaymentDate1 varchar 8
FirstPaymentDate2 varchar 8
FirstResetDate1 varchar 8
FirstResetDate2 varchar 8
FixedRate1 numeric 20
FixedRate2 numeric 20
FloatMaturity1 numeric 20
FloatMaturity2 numeric 20
FloatSpread1 numeric 20
FloatSpread2 numeric 20
Frequency1 varchar 3
Frequency2 varchar 3
IndexYC1 varchar 10
IndexYC2 varchar 10
Lag varchar 10
LastPaymentDate1 varchar 8
LastPaymentDate2 varchar 8
LatestResetDate1 varchar 8
LatestResetDate2 varchar 8
LatestResetRate1 numeric 20
LatestResetRate2 numeric 20
LegType1 varchar 10
LegType2 varchar 10
ParValue1 numeric 20
ParValue2 numeric 20
PositionSign smallint 2
ResetFrequency1 varchar 3
ResetFrequency2 varchar 3
SwapMaturityDate varchar 8
Term varchar 10
TIPSIndex varchar 12
YC1 varchar 10
YC2 varchar 10
BarrierType varchar 10
BarrierDirection varchar 10
Barrier1 numeric 20
Barrier2 numeric 20
Rebate1 numeric 20
Rebate2 numeric 20
ReferenceFrequency varchar 10
AssetSwapSpread numeric 20
AssetSwapPymtFreq varchar 10
ExercisePxType varchar 1
FinalPayment numeric 20
Strike numeric 20
AnnualizationFactor numeric 20
DeliveryPrice numeric 20
FixedVarianceRate numeric 20
ReferenceIndex varchar 12
ReturnHorizon varchar 12
ZeroMean int 4
NotionalType varchar 12
PaymentType varchar 12
ShortTermBorrow numeric 20
LongTermBorrow numeric 20
OtherShortTermLiabilities numeric 20
OtherLongTermLiabilities numeric 20
MinorityInterest numeric 20
CurrentMarketCap numeric 20
PreferredEquity numeric 20
SwapReferenceIndex1 varchar 100
SwapReferenceTerm1 varchar 100
SwapFloatMaturity1 numeric 20
SwapReferencedIndex2Cic varchar 100
SwapReferenceIndex2 varchar 100
SwapReferenceTerm2 varchar 100
SwapFloatMaturity2 numeric 20
ASKER
Thanks
ASKER
I need your help again.