SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Passing a recordset to sproc (from ASP.net)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/07/2005 :  19:15:18  Show Profile  Reply with Quote
In another thread, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58702, I was told that a table parameter can't be passed to a sProc or UDF as a parameter.
Several people suggested using techniques such as passing a string and parsing values out of it.

If varchar wasn't limited to 8000 chars in SS2k, I wouldn't hesitate to use the pass-everything-as-a-string method.
Unfortunately, in our app data of just one field could be a memo, and we could have 200 fields to pass, so this isn't an answer until ss2005.

I worked with a colleague today on a hunch that creating a temporary table in asp.net may provide an answer. We built a prototype, which I would like you to comment on.
My questions are below the code.

What are the advantages of this technique? First, you can pass a string longer than 8000 characters this way (as ntext, for example).
Second, you can pass a complete record including BLOBs such as ntext. And third, you can pass a complete multirow recordset with unlimited no of fields and rows.

Why do we need this? Our form object in asp.net can have any number of linkboxes, which list linked records via a linking table (we manage a lot of many to many links).
These linkbox values (IDs of linked records) are stored in session variables. When the user clicks Save, we want to insert or delete records in all linking tables as needed in SS, not in the middle tier.
The motivation for this is that we intend to perform operations in SS that will require the same functionality, perhaps as triggers that manage link table data, perhaps a relational import utility or something else.
Also it seems that keeping this functionality close to data will give us the greatest flexibility in improving performance down the road.

Our application supports a fully customizable schema and dynamic forms defined in our meta data. The whole app is meta data-driven.
Any solution we build has to be generic. That's why I use WITH RECOMPILE in the sProc.

If you want to run this, replace 'OurDBName' in all code below with the name of the database in which you will run this.
You will need to define a connection string in function TempTable() in ConfigurationManager.ConnectionStrings("OurConnectionString").ConnectionString.

Here is the SQL code. I create the procedure and then test it by creating a temp table and reading it through my sproc.

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'spTestTempTable' 
	   AND 	  type = 'P')
    DROP PROCEDURE spTestTempTable
GO

------------------ spTestTempTable ----------------------

CREATE PROCEDURE spTestTempTable 
	@sTableName varchar(200)

-- AUTHOR: MI 12/7/05
-- PURPOSE: 
--	Testing the scope of local and global temporary tables.
--	This sProc captures the temp table and returns it back
--	as a recordset. This is just a proof of concept.
--	The sp could process the data and write to real tables or 
--	return a recordset with formatted data, new columns, etc.
--	In this example I use global temp tables, but in the
--	asp.net code below we use local tables.
-- PARAMETERS:
--	
-- RETURNS:
--	
-- EXAMPLE:
	
WITH RECOMPILE
AS
	-- Here we can perform formatting, populate linking tables,
	-- perform validation, etc, then either INSERT/UPDATE
	-- the data in the real tables or return the recordset.
	EXEC('SELECT * FROM ['+@sTableName+']')
GO

-- =============================================
-- Example to execute the store procedure
-- =============================================
-- Delete the temp table if it exists
USE tempdb
GO
PRINT object_id(N'[##table]')
if exists (select * from dbo.sysobjects where id = object_id(N'[##table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Table being dropped'
	drop table [##table]
END
else
BEGIN
	PRINT 'Table not dropped'
END
GO
USE OurDBName
GO

CREATE TABLE ##table 
	(ID int,
	Text varchar(20),
	MMO_Memo ntext)
INSERT INTO ##table SELECT 1, 'some text 1', 'text for the memo field 1'
INSERT INTO ##table SELECT 2, 'some text 2', 'text for the memo field 2'
INSERT INTO ##table SELECT 3, 'some text 3', 'text for the memo field 3'

EXEC spTestTempTable
	@sTableName = '##table'
GO

-------------------------
RESULT:

1	some text 1	text for the memo field 1
2	some text 2	text for the memo field 2
3	some text 3	text for the memo field 3


Here is the asp.net code (VS2005). Here we create a temp table named with a hyphenless GUID, populate it and run our sProc.
We send the table name via a parameter. Then we count the rows in the data the reader gets from the sProc to prove that the sproc returned them.
Finally we drop the table.

Public Function TempTable() As String

        Dim settings As String = ConfigurationManager.ConnectionStrings("OurConnectionString").ConnectionString
	'OurConnectionString is:
	'Data Source=SERVERNAME;Initial Catalog=OurDBName;User ID=someuser;Password=somepassword
        Dim sqlConnection1 As New Data.SqlClient.SqlConnection(settings)

        Dim cmd As New SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader
        Dim mColl As New Collection

        Dim lRet As Long
        Dim TempTableName As String = "#" & Replace(GenGuid(), "-", "")

        Dim sBigString As String
        Dim i As Long

        sBigString = ""

        For i = 1 To 10000
            sBigString = sBigString & "BigString "
        Next


        cmd.Connection = sqlConnection1
        sqlConnection1.Open()
        cmd.CommandType = CommandType.Text

        'cmd.CommandText = "USE tempdb if exists (select * from dbo.sysobjects where id = object_id(N'[" & TempTableName & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [" & TempTableName & "]"
        'lRet = cmd.ExecuteNonQuery

        cmd.CommandText = "USE OurDBName " & _
                        "CREATE TABLE " & TempTableName & " (ID int, Text varchar(20), MMO_Memo ntext) " & _
                        "INSERT INTO " & TempTableName & _
                            " SELECT 1, 'some text 1', '1 " & sBigString & "' " & _
                        "INSERT INTO " & TempTableName & _
                            " SELECT 2, 'some text 2', '2 " & sBigString & "' " & _
                        "INSERT INTO " & TempTableName & _
                            " SELECT 3, 'some text 3', '3 " & sBigString & "' "
        lRet = cmd.ExecuteNonQuery

        cmd.CommandText = "USE OurDBName EXEC spTestTempTable  @sTableName = '" & TempTableName & "'"
        reader = cmd.ExecuteReader

        lRet = 0
        If reader.HasRows Then
            Do While reader.Read()
                lRet = lRet + 1
            Loop
        Else
            TempTable = 0
        End If
        reader.Close()

        cmd.CommandText = "USE TempDB DROP TABLE  " & TempTableName
        cmd.ExecuteNonQuery()

        TempTable = lRet.ToString

        sqlConnection1.Close()


    End Function


    Private Function GenGuid() As String

        Dim guidValue As Guid = Guid.NewGuid
        Return guidValue.ToString

    End Function





I have the following questions:

Will it be a problem to run this a lot? A form in which the user has modified 20 linkboxes for example will require writing a temp table from asp.net 21 times
(1 time for the main fields and 20 times for linking tables). The sProc would read the first table and perform an INSERT or UPDATE
in the main real table, then it would read the other 20 temp tables and perform INSERT/DELETEs in the linking tables as needed.
Typically only a few values are modified in linkboxes, but someone can add 10,000 Contacts to a marketing mailing activity, for ex.
This will require performing 10000 inserts. But this is no different than doing the same thing directly except that our sProc
is not compiled. Can we do some kind of a bulk insert instead?

How well will this scale? We typically have 50-100 users and up to 1M records in main tables. Some linking tables can have millions of records.

What are the implications of this technique on security? How are permissions managed for temp tables? We tested this under sa logon.
Can non-admin users create a temp table and read from it? If not, what are the implications of asp.net creating temp tables under a logon with adequate permissions?

We've been able to use local temp tables (#tablename). I think this is preferred to global (##tablename) because they get cleaned up after the connection ends. Is there an advantage to using global temp tables instead?

Anything else? TIA.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 12/07/2005 :  19:31:13  Show Profile  Reply with Quote
You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.
Go to Top of Page

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/07/2005 :  19:43:15  Show Profile  Reply with Quote
quote:
Originally posted by DustinMichaels

You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.



How can I overcome the 8000 byte limit?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/08/2005 :  00:21:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>How can I overcome the 8000 byte limit?

In this topic search for 8000
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 12/08/2005 :  12:08:41  Show Profile  Reply with Quote
quote:
Originally posted by Marioi

quote:
Originally posted by DustinMichaels

You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.



How can I overcome the 8000 byte limit?



Text fields can contain up to 2 gigabytes of space. You would have plenty of room if you tried using the XML text parameter.
Go to Top of Page

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/12/2005 :  10:12:45  Show Profile  Reply with Quote
quote:
Originally posted by DustinMichaels
Text fields can contain up to 2 gigabytes of space. You would have plenty of room if you tried using the XML text parameter.



I can't declare a parameter of ntext type, so how can I pass 2GB to the proc? The largest parameter type that I see available is 8000 chars. I am in SS2k.
Go to Top of Page

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/12/2005 :  10:21:37  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan
In this topic search for 8000
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210


I saw your suggestion about declaring multiple varchars(8000) and splitting a big string between them. However, my proc is being called by asp.net, and I don't know how big the string might be. To support only 10MB, I would have to declare 1250 parameters. Using a temp table, I can pass a recordset of any size.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000