Using temporary table in c#

Change your temp table from #table to ##table in both places.

Using ## means a global temp table that stays around. You'll need to Drop it after you have completed your task.

Command = " Drop Table ##table"

Putting the data into a database will take time - since you already have it in memory, perhaps LINQ-to-Objects (with DataSetExtensions) is your friend? Replace <int> etc with the correct types...

        var query = from row in table.Rows.Cast<DataRow>()                  group row by new                  {                      Col1 = row.Field<int>(1),                      Col2 = row.Field<int>(2)                  } into grp                  select new                  {                      Col1 = grp.Key.Col1,                      Col2 = grp.Key.Col2,                      SumCol7 = grp.Sum(x => x.Field<int>(7))                  };        foreach (var item in query)        {            Console.WriteLine("{0},{1}: {2}",                item.Col1, item.Col2, item.SumCol7);        }

I don't think you can make a temp table in SQL the way you are thinking, since it only exists within the scope of the query/stored procedure that creates it.

If the spreadsheet is a standard format - meaning you know the columns and they are always the same, you would want to create a Table in SQL to put this file into. There is a very fast way to do this called SqlBulkCopy

// Load the reports in bulkSqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);// Map the columnsforeach(DataColumn col in dataTable.Columns)   bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);bulkCopy.DestinationTableName = "SQLTempTable";bulkCopy.WriteToServer(dataTable);

But, if I'm understanding your problem correctly, you don't need to use SQL server to modify the data in the DataTable. You c an use the JET engine to grab the data for you.

    // For CSV    connStr = string.Format("Provider=Microsoft.JET.OLEDB.4.0;Data Source={0};Extended Properties='Text;HDR=Yes;FMT=Delimited;IMEX=1'", Folder);    cmdStr = string.Format("SELECT * FROM [{0}]", FileName);    // For XLS    connStr = string.Format("Provider=Microsoft.JET.OLEDB.4.0;Data Source={0}{1};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", Folder, FileName);    cmdStr = "select * from [Sheet1$]";OleDbConnection oConn = new OleDbConnection(connStr);            OleDbCommand cmd = new OleDbCommand(cmdStr, oConn);            OleDbDataAdapter da = new OleDbDataAdapter(cmd);            oConn.Open();            da.Fill(dataTable);            oConn.Close();

Also, in your code you ask if your connection string is correct. I don't think it is (but I could be wrong). If yours isn't working try this.

connectionString="Data Source=localhost\<instance>;database=<yourDataBase>;Integrated Security=SSPI" providerName="System.Data.SqlClient"