none
How to map my CSV file column name with sql server database column RRS feed

  • Question

  • Hi All,

    I am importing a CSV file in my databse table using following method and it's working fine.

    <!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]-->public System.Data.DataTable GetDataTable(string strFileName)
    {
    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
    conn.Open();
    string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
    System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
    System.Data.DataSet ds = new System.Data.DataSet("CSV File");
    adapter.Fill(ds);
    return ds.Tables[0];
    }


    I am going to update whole data at once so iam using the below method.

    <!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]-->

         public void UpdateDatatable(string ProcedureName, Hashtable Parameters, DataTable Table)

            {

                //Creating instance of sqladapter class to update the datatable in a single command.

                SqlConnection Connection = null ;

                SqlDataAdapter adapter = null ;

                SqlCommandBuilder Bldr = null ;

     

                try

                {

                    Connection = new SqlConnection (ConnectionString);

                    Connection.Open();

                    adapter = new SqlDataAdapter (ProcedureName, Connection);

                    if (Parameters != null && Parameters.Count > 0)

                    {

                        //Add parameters to select command

                        foreach (DictionaryEntry dEntry in Parameters)

                        {

                            SqlParameter spParam = new SqlParameter (dEntry.Key.ToString(), dEntry.Value);

                            adapter.SelectCommand.Parameters.Add(spParam);

                        }

                    }

                    adapter.SelectCommand.CommandType = CommandType .StoredProcedure;

                    Bldr = new SqlCommandBuilder (adapter);

                    //Update the datatable 

                    adapter.Update(Table);

                }

                catch

                {

                    throw ;

                }

                finally

                {

                    //Disposing all the used connection resources

                    if (adapter != null )

                        adapter.Dispose();

                    if (Connection != null )

                    {

                        if (Connection.State == ConnectionState .Open)

                            Connection.Close();

                        Connection.Dispose();

                    }

                    if (Bldr != null )

                        Bldr.Dispose();

                }

            }

    But CSV column and database table column name are different.

    So how i map the column to update all records in table at once using the given method.

    Thanks in advance!!

    • Moved by Arthur Vickers - MSFT Tuesday, December 7, 2010 10:18 PM Not an EF question (From:ADO.NET Entity Framework and LINQ to Entities (Pre-Release))
    Tuesday, December 7, 2010 6:15 PM

All replies