How can I insert each element in a 1D array into a new row in an SQL Database using C#?

I have a开发者_开发百科n array containing a list of file paths that I want to insert into an SQL database.

string[] filePaths = Directory.GetFiles(@"C:\Test Folder");

I am confident in setting up and connecting to the database, I just can't figure out how to take each element and place it in a new row in the database.

Thanks, Matt

It depends on which technology you are using (Please note that when inserting lots of rows the use of SqlBulkCopy is recommended).

ADO.NET

foreach (var path in filePaths)
{
   var command = new SqlCommand("INSERT INTO mytable(col1) VALUES(@param1)", connection);
   command.Parameters.AddWithValue("@param1", path);
   command.ExecuteNonQuery();
}

LINQ-to-SQL

var projection = filePaths.Select(a => new MyTable() { Col1 = a });
myContext.InsertAllOnSubmit(projection);
myContext.SubmitChanges();

LINQ-to-Entities

foreach (var path in filePaths)
{
   myModel.MyTable.AddObject(new MyTable() { Col1 = path });
}
myModel.SaveChanges();

foreach(string fp in filePaths)
{
   InsertIntoDb(fp);
}

//Method
public void InsertIntoDb(string insert)
{
   SqlConnection con = //Setup DB connection
   SqlCommand command = new SqlCommand();
   command.Connection = con;
   command.CommandText = "Insert @insert into Table";
   command.Parameters.AddWithValue("@insert", insert);
   command.ExecuteNonQuery();
}

This leaves out a lot, but it should point you in the right direction. Essentially, you want to set up your connection, instantiate a SqlCommand object where the command text is the SQL Text to insert your value (Better would be a stored procedure or some other sanitized way to insert the data to avoid Sql Injection), and then call ExecuteNonQuery() to insert the actual data.

The most efficient way is to use SqlBulkCopy

you will have to project your data into a DataTable, DataRow[], etc. OR IDataReader (which is more efficient - refer this discussion and example) in order to use SqlBulkCopy