We help IT Professionals succeed at work.

Add List<newFiles> to a dt, save to table_1

Mike Eghtebas
Mike Eghtebas asked
on
In this code, I need help with two items:

Q1: How to code this:        // define a datatable (dt) and add newFiles List to it , see the code below.

Q2: Given cs, cn, table_1, how to do ...       // add dt to SQL Server table_1

   protected void Button1_Click(object sender, EventArgs e)
    {
        List<ExcelFiles> newFiles = new List<ExcelFiles>();
        ExcelFiles xlFile = new ExcelFiles();

        // Put file names in the directory into array.
        string dirPath ="C:\\Users\\Mike\\Data\\"; 
        var files = Directory
                        .EnumerateFiles(dirPath, "*", SearchOption.TopDirectoryOnly)
                        .Select(Path.GetFileName);

        // store file names in xlFile object
        foreach (string name in files)
        {
            string file =name;
            string fileExtention = name.Substring(name.Length - 5);
            if (fileExtention == ".xlsx")
            {
                xlFile.filename = file;

                // add file name to xlFile object 
                newFiles.Add(xlFile);
            }
        }
        // define a datatable (dt) and add newFiles List to it (I will need this method elsewhere as well)

       // add dt to SQL Server table_1
    }

Open in new window

Comment
Watch Question

View Solution Only

Top Expert 2014
Commented:
If table_1 is the name of the table, cs the connection string, and cn the column name:

Q1
DataTable dt = new DataTable
{
    Columns = { { cn, typeof(string) } }
};

foreach(string file in newFiles)
{
    dt.Rows.Add(file);
}

Open in new window

Q2
SqlConnection connection = new SqlConnection(cs);
SqlDataAdapter adapter = new SqlDataAdapter("select * from table_1", connection);
new SqlCommandBuilder(adapter);
adapter.Update(dt);

Open in new window

Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank you.