Simple beginners question

Steve Geard

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs16y ago

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 ashfaq16y ago
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 Skerjanc16y ago
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 Geard16y ago
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.
Crish16y ago
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();