Using a temporary table in C #

Question

I read excel sheet in datagrid. Because of this, I was able to read the grid lines in the DataTable. The DataTable object has data because when I make it equal to the network data source for this table object, the grid is filled.

My problem: I want to use a table object and manage its values ​​using a SQL server (i.e. I want to save it as a temporary table and manipulate it using SQL queries from C # code, and I want it to return another the result integrates the grid (I don't know how to work with temporary tables in C #)

Here is the code to execute when the button is clicked ....

SqlConnection conn = new SqlConnection("server = localhost;integrated security = SSPI"); //is connection string incorrect? SqlCommand cmd = new SqlCommand(); //!!The method ConvertFPSheetDataTable Returns a DataTable object// cmd.Parameters.AddWithValue("#table",ConvertFPSheetDataTable(12,false,fpSpread2_Sheet1)); //I am trying to create temporary table //Here , I do a query cmd.CommandText = "Select col1,col2,SUM(col7) From #table group by col1,col2 Drop #table"; SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText,conn); DataTable dt = new DataTable(); da.Fill(dt); ***// I get an error here 'Invalid object name '#table'.'*** fpDataSet_Sheet1.DataSource = dt; //**NOTE:** fpDataSet_Sheet1 is the grid control 

Answer 1

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

Using ## means a global pace table that is around. You will need to remove it after completing your task.

Command = "Unpack Table ##"

Answer 2

Putting data into a database will take some time - since you already have memory, maybe LINQ-to-Objects (with DataSetExtensions) is your friend? Replace <int> etc. with the right 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); } 

Answer 3

I don’t think you can make a temporary table in SQL the way you think, because it exists only within the scope of the query / stored procedure that creates it.

If the spreadsheet is the standard format, it means that you know the columns, and they are always the same, you would like to create a table in SQL to accommodate this file. There is a very quick way to do this called SqlBulkCopy

 // 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); 

But if I understand your problem correctly, you do not need to use a SQL server to modify data in a DataTable. You use the JET mechanism to capture 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(); 

In addition, in the code you ask for the correct connection string. I do not think it is (but I could be wrong). If yours does not work, try this.

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

Answer 4

Forgive me if I do not understand what you definitely want.
If you want to execute an SQL query on an excel sheet, you can do it directly.

Alternatively, you can use SQL Server to query excel (OPENROWSET or a function that I don’t remember right away). Using this, you can join the sql server table with excel sheet

Marc's offer is another way to look at it.

Answer 5

Perhaps you can use a DataView. You create this from the DataTable that you already have.

 dv = new DataView(dataTableName); 

Then you can filter (apply the SQL WHERE clause) or sort the data using the DataView methods. You can also use “Find” to find a matching row or FindRows to find all matching rows.

Some filters:

 dv.RowFilter = "Country = 'USA'"; dv.RowFilter = "EmployeeID >5 AND Birthdate < #1/31/82#" dv.RowFilter = "Description LIKE '*product*'" dv.RowFilter = "employeeID IN (2,4,5)" 

Sorting:

 dv.Sort = "City" 

Search string: Find a customer named "John Smith."

  vals(0)= "John" vals(1) = "Smith" i = dv.Find(vals) 

where I am the index of the row containing the client.

After you apply them to the DataView, you can snap your grid to the DataView.

Answer 6

Change the command text from

 Select col1,col2,SUM(col7) From #table group by col1,col2 

to

 Select col1,col2,SUM(col7) From @#table group by col1,col2