Last post May 24, 2013 12:33 AM by markfitzme
May 23, 2013 11:03 PM|sqlservernoob|LINK
I have two related questions: one. how can I create a temporary table using a stored proc in c# and is the way that I'm attempting it is wise
I have the simple stored procedure
create proc spMakeTempTable
create table #temp(columnName varchar(max))
and the c#
protected void Page_Load(object sender, EventArgs e)
string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
using (var cmd = new SqlCommand("spMakeTempTable", con))
cmd.CommandType = CommandType.StoredProcedure;
I'm not sure what I should do because this proc doesn't return any rows so a SqlDataReader wouldn't mean anything, and when I run this the temp table isn't created. My situation is on the form there's a button that will take the text entered into a text
box and insert that into the temporary table I'm trying to create. I can't put the create table proc inside the button click event because the person needs to be able to enter in as many strings as they see fit and the program will fail if the temporary table
creation proc is ran more than once. Firstly, how do I get the temp table to be created, and secondly is it smart to have a sproc fire on the page load event?
May 23, 2013 11:41 PM|ignatandrei|LINK
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted
after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Create a global temporary table( maybe add a column like user id, to take care of what user have what lines)
May 23, 2013 11:47 PM|sqlservernoob|LINK
What do I need to do to execute and create the proc? What I have currently doesn't work and I don't know what I need to execute the proc so I used ExecuteReader and the program crashed.
May 24, 2013 12:33 AM|markfitzme|LINK
To execute it you can do ExecuteNonQuery(). That will execute a query that doesn't return any results.