Link to home
Create AccountLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

How to loop through a sqldatareader and execute a stored procedure with parameters?

I'm looping through a table of values that has a find / replace columns and calling a stored procedure SearchandReplace - but as I do so I get an error - System.InvalidOperationException was unhandled
  Message=There is already an open DataReader associated with this Command which must be closed first.  with the highlight on cmdSP.ExecuteNonQuery()

How may I change the below syntax to allow me to loop through the data reader and then execute the stored procedure to find/ replace values in all tables?     Please note the SearchandReplace Stored procedure can be found here -
http://vyaskn.tripod.com/sql_server_search_and_replace.htm 

Private Sub Find_Replace()

    'must add code here
    Dim conn As SqlConnection = New SqlConnection("Initial Catalog=" + txtDbName.Text + ";Server=" + txtServer.Text + ";UID=" + txtusername.Text + ";PWD=" + txtpassword.Text + ";")
    Dim sFrom As String
    Dim sTo As String
    Dim cmdSP As New SqlCommand
    Dim cmd As New SqlCommand("SELECT * FROM [FindReplace]", conn)

    Dim reader As SqlDataReader

    conn.Open()
    reader = cmd.ExecuteReader()


    While (reader.Read())

      txtStatus.Text = "replacing this value: " + reader.Item(0) + " with value: " + reader.Item(1)
      'Call run stored procedure with two values...
      '
      cmdSP.Connection = conn
      cmdSP.CommandText = "SearchandReplace"
      cmdSP.CommandType = CommandType.StoredProcedure

      sFrom = reader.Item(0)
      sTo = reader.Item(1)

      cmdSP.Parameters.Add("@SearchStr", sFrom)
      cmdSP.Parameters.Add("@ReplaceStr", sTo)
      cmdSP.ExecuteNonQuery()


    End While

    cmdSP = Nothing

    reader.Close()

    conn.Close()



  End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scubago
Scubago

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of stephenlecomptejr

ASKER

Scubago,

I'm getting a timeout expired message with the highlight on cmdSP.ExecuteNonQuery().
This stored procedure can take up to 2 - 10 minutes to run completely.

How may I change the above to account for this situation?
Why not encapsulate all of that in a single Stored Procedure?
Avatar of Scubago
Scubago

You would have to raise the commandtimeout of the sqlCommand object. The default is 30 seconds.
for 10 minutes (= 600 seconds) do the following:

cmdSP.CommandTimeout = 600

But keep in mind that you'll have to raise the timeout for your cmd that you use for your reader.
If cmdSp takes max 600 seconds and you have 100 records to process, then the cmd.CommandTimeout should be 60000.



acperkins,

How then would the stored procedure be rewritten to loop through the values found at FindReplace?
CREATE PROC SearchAndReplace
(
	@SearchStr nvarchar(100),
	@ReplaceStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string and replace it with another string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 2nd November 2002 13:50 GMT

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	SET @RCTR = 0

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				SET @SQL=	'UPDATE ' + @TableName + 
						' SET ' + @ColumnName 
						+ ' =  REPLACE(' + @ColumnName + ', ' 
						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				EXEC (@SQL)
				SET @RCTR = @RCTR + @@ROWCOUNT
			END
		END	
	END

	SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Open in new window

Something like the code below.  I am making the assumption that the two columns are called FindString and ReplaceString.  Change as appropriate.

Since your table FindReplace is in the same database, make sure you exclude it in the SearchAndReplace Stored Procedure or your will replace all the Find values in that table.
CREATE PROCEDURE usp_SearchAndReplaceAll

AS

DECLARE	@SearchStr nvarchar(100),
	@ReplaceStr nvarchar(100)

SET NOCOUNT ON
		
DECLARE	FindReplaceCursor CURSOR FAST_FORWARD FOR
SELECT	FindString, 
	ReplaceString 
FROM	FindReplace

OPEN FindReplaceCursor

FETCH NEXT FROM FindReplaceCursor 
INTO @SearchStr, @ReplaceStr

WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC SearchAndReplace @SearchStr, @ReplaceStr

		FETCH NEXT FROM FindReplaceCursor
		INTO @SearchStr, @ReplaceStr
	END 

CLOSE FindReplaceCursor
DEALLOCATE FindReplaceCursor

Open in new window

>>If cmdSp takes max 600 seconds and you have 100 records to process, then the cmd.CommandTimeout should be 60000.<<
You are not serious, right?
acperkins,

going back to the SearchandReplace - how may I rewrite it to where it excludes the FindReplace table?  Like at least write what the one line should be and I'll try to figure out placement instead of using the whole code...
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for the comments.

I"m still learning about SQL Server.