在C＃中创建临时表，我试图在其中复制tabl，一个sqlexception（无效对象名'#xyz'。）是创建临时表的正确方法 [英] created temp table in C#,i tried to copy tabls in it, an sqlexception(Invalid object name '#xyz'.)is it right way to create temp table

问题描述

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.;AttachDbFilename=C:\Users\Amit\Documents\ghf.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

            SqlDataAdapter sda = new SqlDataAdapter(("create table  #xyz(pid int, pname nvarchar(50),pamount nvarchar(50),cid int,cname nvarchar(50)"), con);
            dt = new DataTable();
            sda.Fill(dt);   

           dataGridView1.DataSource = dt;
        }


SqlConnection con = new SqlConnection(@"Data Source=.;AttachDbFilename=C:\Users\Amit\Documents\ghf.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
            SqlCommand cmd = new SqlCommand("insert into #xyz (pid, pname, pcost , cid ,cname) select  product.pid,product.pname,product.pcost ,category.cid ,category.cname  from product inner join category on product.cid = category.cid", con);
             
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;

推荐答案

代码没有多大意义。在第一部分中，您将创建一个临时表，但使用该命令作为选择。 create table命令没有返回任何内容。



然后在第二个中你再次使用select命令添加到临时表中，然后再次像选择一样使用它。 br />


这些命令中的任何一个都会向客户端返回任何内容，因此使用SqlCommand.ExecuteNonQuery并忘记数据表会更有意义。

The code doesn't quite make sense. In the first part you create a temporary table but use the command as a select. The create table command isn't returning anything.

Then again in the second you add to the temporary table using a select command and again use it like a select.

NOne of those commands will return anything to the client so it would make more sense to use SqlCommand.ExecuteNonQuery and forget the data tables.

临时表就是：临时表。

所以当你在一个SqlConnection上创建它时，它不会在另一个上面创建它！

实际上，它仅限于会话 - 所以我怀疑它只会在当前命令的持续时间内存在。我从来没有试过在一个命令中创建一个临时表并在一秒钟内使用它，但我知道它会在创建它的SP结束时被销毁，所以我怀疑SQL服务器会在命令结束时销毁它 - 即使它没有，这也不是我希望未来版本所依赖的行为！



就个人而言，我会写一个存储过程来创建和然后使用该表使范围清晰明显。

A temporary table is just that: temporary.
So when you create it on one SqlConnection, it isn't going to be available on a different one!
In fact, it's limited to the Session - so I suspect that it's only going to exist for the duration of the current Command. I've never tried to create a temporary table in one command and use it in a second, but I know it will be destroyed at the end of the SP that created it so I would suspect SQL server will destroy it when the command ends - even if it doesn't, that isn't behaviour I would want to rely on for future versions!

Personally, I'd write a stored procedure to create and then use the table so that the scope is clear and obvious.

是的，我完全同意上述2个解决方案中的建议。如果要解决问题，请使用相同的连接对象来处理临时表，因为它是临时的一个连接。当你创建了另一个连接时，你的临时表将不再存在。



不要再次初始化连接对象并将相同的con对象传递给第二个查询插入到select。

Yes, I completely agree with the suggestion in above 2 solutions. If you want to solve your problem then use the same connection object to deal with the temporary table as it is temporary with one connection. When you have created another connection then your temporary table will not longer be exist.

Do not initialize the connection object again and pass the same con object to second query where you doing insert into select.

这篇关于在C＃中创建临时表，我试图在其中复制tabl，一个sqlexception（无效对象名'#xyz'。）是创建临时表的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！