Using temporary table in c#

Question

Hi, I read an excel sheet into a datagrid.From there , I have managed to read the grid's rows into a DataTable object.The DataTable object has data because when I make equal a grid's datasource to that table object , the grid is populated.

My Problem : I want to use the table object and manipulate its values using SQL server,(i.e. I want to store it as a temporary table and manipulate it using SQL queries from within C# code and , I want it to return a different result inte a grid.(I don't know how to work with temporary tables in C#)

Here's code to execute when clicking button....

 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

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

Answer 2

Pardon me, if I have not understood what you exactly want.
If you want to perform SQL query on excel sheet, you could do it directly.

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

Marc's suggestion is one more way to look at it.

Answer 3

Perhaps you could use a DataView. You create that from a DataTable, which you already have.

dv = new DataView(dataTableName);

Then, you can filter (apply a SQL WHERE clause) or sort the data using the DataView's 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"

Finding a row: Find the customer named "John Smith".

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

where i is the index of the row containing the customer.

Once you've applied these to the DataView, you can bind your grid to the DataView.

Answer 4

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 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'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"

Answer 5

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"