Simple beginners question

Question

I have created a string array by parsing a long CSV string (read from a .csv file) into its elements (i.e. the bits that are actually seperated by commas). I now wish to add those elements to an SQL Server 2005 database table that I have already created for that purpose.
Can someone please point me at a simple example of how to loop thru the array and add the elements to the appropriate fields of a database.
I am using Visual Studio 2008, C#.NET.
Thanks.

Sam Hobbs · Answer

There are many ways to do that. One alternative is to use parameters. The following uses SQL CE but if you are using SQL Server and not the CE version then just change SqlCe___ to Sql___. The main portion that connects to the database and creates the insert command is: SqlCeConnection Connection = new SqlCeConnection(ConnectionString); try { Connection.Open(); } catch (Exception ex) { Console.WriteLine(Open error: {0} ex.ToString()); return; } const String CommandText = Insert Into People (First Last) Values (@First @Last); SqlCeCommand InsertCommand = new SqlCeCommand(CommandText Connection); SqlCeParameter p = null; p = InsertCommand.Parameters.Add(@First SqlDbType.NVarChar 50 First); p.SourceVersion = DataRowVersion.Original; p = InsertCommand.Parameters.Add(@Last SqlDbType.NVarChar 50 Last); p.SourceVersion = DataRowVersion.Original; InsertPerson(InsertCommand John Smith); InsertPerson(InsertCommand Bill Johnson); InsertPerson(InsertCommand Brooke Dudley); Connection.Close(); And then my InsertPerson function is: static void InsertPerson(SqlCeCommand InsertCommand String First String Last) { InsertCommand.Parameters[First].Value = First; InsertCommand.Parameters[Last].Value = Last; try { InsertCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(Insertion error: {0} ex.ToString()); } }

ahsan ashfaq · Answer

Hello ------------------------------- string query query=insert into table values(' foreach(string x in stringarray[]) { query=query+x+' } query=left(query(query.length-2)) //Removing last extra comma please check the indexes query=query+) //here you have your query to run

Matej Skerjanc · Answer

hmm not sure if i understand you correctly but something in lines of : foreach(string x in stringarray[]) { then you can create data row and fill x into it and add row to datatable... really depending on your database organisation }

Steve Geard · Answer

Thanks but I have already parsed the CSV string into a string array (I did it differently to you I used a regular expression and regex.split which I am told is a bit more robust than .split if there are commas inside quoted strings and other similar nasties). Where I need the help is looping thru the string array and adding each element to a row in a previously created SQL Server database.

Crish · Answer

hi Get data from CSV file use below code. StreamReader sr = new StreamReader( file.csv ); string inputLine =  ; String[] values = null ; while ((inputLine = input.ReadLine()) != null ) { values = inputLine.Split( ; ); } input.Close();