StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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</p> <pre><code>// Load the reports in bulk SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString); // Map the columns foreach(DataColumn col in dataTable.Columns) bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); bulkCopy.DestinationTableName = "SQLTempTable"; bulkCopy.WriteToServer(dataTable); </code></pre> <p>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.</p> <pre><code> // 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(); </code></pre> <p>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.</p> <pre><code>connectionString="Data Source=localhost\<instance>;database=<yourDataBase>;Integrated Security=SSPI" providerName="System.Data.SqlClient" </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload