在 C# 中使用临时表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/204933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-03 17:57:25  来源:igfitidea点击:

Using temporary table in c#

c#sqltemp-tables

提问by

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.

我将 Excel 工作表读入数据网格。从那里,我设法将网格的行读入 DataTable 对象。DataTable 对象具有数据,因为当我使网格的数据源与该表对象相等时,网格被填充。

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#)

我的问题:我想使用表对象并使用 SQL 服务器操作它的值,(即我想将它存储为一个临时表并使用 C# 代码中的 SQL 查询操作它,我希望它返回一个不同的结果 inte一个网格。(我不知道如何在 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  

回答by Marc Gravell

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...

将数据放入数据库需要时间 - 因为您已经在内存中拥有它,也许 LINQ-to-Objects(带有 DataSetExtensions)是您的朋友?用正确的类型替换 <int> 等...

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

回答by shahkalpesh

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.

对不起,如果我不明白你到底想要什么。
如果你想在excel表上执行SQL查询,你可以直接进行。

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

或者,您可以使用 SQL Server 查询 excel(OPENROWSET 或我不记得的函数)。使用这个,你可以用excel表加入一个sql server表

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

马克的建议是另一种看待它的方式。

回答by DOK

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

也许您可以使用 DataView。您可以从已有的 DataTable 创建它。

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.

然后,您可以使用 DataView 的方法过滤(应用 SQL WHERE 子句)或对数据进行排序。您还可以使用 Find 查找匹配的行,或使用 FindRows 查找所有匹配的行。

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

查找一行:查找名为“John Smith”的客户。

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

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

其中 i 是包含客户的行的索引。

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

将这些应用到 DataView 后,您可以将网格绑定到 DataView。

回答by Ryan Abbott

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.

我不认为您可以按照您的想法在 SQL 中创建临时表,因为它只存在于创建它的查询/存储过程的范围内。

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

如果电子表格是标准格式——这意味着你知道列并且它们总是相同的,你会想要在 SQL 中创建一个表来放入这个文件。有一种非常快速的方法可以做到这一点,称为 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.

但是,如果我正确理解您的问题,您不需要使用 SQL 服务器来修改 DataTable 中的数据。您可以使用 JET 引擎为您获取数据。

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

回答by user32957

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

在两个地方将临时表从 #table 更改为 ##table。

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"

命令=“删除表##table”

回答by Al Option

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