Link to home
Start Free TrialLog in
Avatar of SubbuUSA
SubbuUSAFlag for United States of America

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
Avatar of SubbuUSA
SubbuUSA
Flag of United States of America image

ASKER

@Fernandosoto


I need your help again.
@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

 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);

                    }

                }
            }

Open in new window

Can anybody help me with the above problem? appreciate your help
@kaufmed

Can you help me with this problem
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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