6 Replies - 20583 Views - Last Post: 15 April 2009 - 02:18 PM Rate Topic: -----

#1 getajob1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 03-February 09

return multiple rows (cursor) from stored procedure

Post icon  Posted 13 April 2009 - 07:53 AM


I am trying to retrieve cursor data from a stored procedure. I was successful with returning anything but a cursor.
The method I am using for getting the data is System.Data.SqlClient.SqlConnection adn for example
cmdEmployee.Parameters.Add("@LastName", SqlDbType.NVarChar, 20); the SqlDbType can't be a cursor (table).

How do I return a cursor from the stored procedure? Do I use ADODB.Recordset? I am connecting to a SQl Server DB locally.

private string connectionString = @"server = DONNAGARY\SQLEXPRESS;integrated security = true;User Instance=True;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2\MSSQL\DATA\AdventureWorksDW_Data.mdf";

Your help in how to return a cursor would be appreciated. Below I made an effort but to no avail. Am I on the right track?


			THE FOLLOWING CODE WORKS BECAUSE IT'S A NON-CURSOR RETURN:
public static int GetEmployeeCountByLastName(int sqlcnt, string connectionString)
			 {
				 string lastname = "Trojan%";
				 System.Data.SqlClient.SqlConnection dataConn = new SqlConnection();
				 dataConn.ConnectionString = connectionString;

				 System.Data.SqlClient.SqlCommand cmdEmployee = new SqlCommand("GetEmployeeCountByLstName", dataConn);
				 cmdEmployee.CommandType = CommandType.StoredProcedure;
				 cmdEmployee.Connection = dataConn;
				 cmdEmployee.Parameters.Add("@LastName", SqlDbType.NVarChar, 20);
				 cmdEmployee.Parameters["@LastName"].Value = lastname;
				 cmdEmployee.Parameters.Add("@Count", SqlDbType.Int);
				 cmdEmployee.Parameters["@Count"].Direction = ParameterDirection.Output;

								 
				 dataConn.Open();
				 int iCount;
				 cmdEmployee.ExecuteNonQuery();
				 iCount = (int)cmdEmployee.Parameters["@Count"].Value;
				 sqlcnt = iCount;
				 dataConn.Close();
				
				 return sqlcnt;

	   (TRIED ADODB.RECORDSET OPTION BUT COULDN'T CONNECT)

  ADODB.Connection cn = new ADODB.Connection();
			ADODB.Recordset rs = new ADODB.Recordset();
			string query = "Select * From DimScenario";
		  //  connectionString = @"Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=DONNAGARY\SQLEXPRESS;integrated security = true;User Instance=True;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2\MSSQL\DATA\AdventureWorksDW_Data.mdf";
			query = "Select * From Authors";

			rs.Open(query, connectionString, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
			rs.Close();

			cn.ConnectionString = connectionString;
			cn.Open(null, null, null, 0);
			cn.Close(); 

		   (  STORED PROCEDURE )
ALTER PROCEDURE dbo.GetEmployeeCountByLstName	
	@LastName varchar(20),
	@Count int OUTPUT

AS
	SELECT @Count = count(*)
	FROM DimEmployee
	WHERE LastName like @LastName;
RETURN


		(TRY AT STORED PROCEDURE TO RETURN A CURSOR)

ALTER PROCEDURE  dbo.GetEmployeeAllCursor
	@EmployeeCursor CURSOR VARYING OUTPUT
AS
	SET @EmployeeCursor = CURSOR
	FORWARD_ONLY STATIC FOR
	  SELECT EmployeeKey,
			 ParentEmployeeKey
	  FROM   DimEmployee;
	OPEN @EmployeeCursor;
	
WHILE (@@FETCH_STATUS = 0)
BEGIN;
	 FETCH NEXT FROM @EmployeeCursor;
END;
CLOSE @EmployeeCursor;
DEALLOCATE @EmployeeCursor;

	RETURN



Mod Edit: Please use code tags when posting your code. Code tags are used like so => :code:

Thanks,
PsychoCoder :)

Is This A Good Question/Topic? 0
  • +

Replies To: return multiple rows (cursor) from stored procedure

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: return multiple rows (cursor) from stored procedure

Posted 13 April 2009 - 08:05 AM

I guess I'm not understanding your question here. A cursor doesn't return data so you cannot return from it
Was This Post Helpful? 0
  • +
  • -

#3 getajob1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 03-February 09

Re: return multiple rows (cursor) from stored procedure

Posted 13 April 2009 - 08:17 AM


I am trying to write a stored procedure that returns data to the form so I can load it into a table for processing.
I have already done this using static SQL but I thought it would be safer and more efficient to duplicate this effort in a stored procedure.


		(THIS CODE WORKS, I JUST WANTED TO TAKE THIS CODE AND RETIEVE IT FROM A STORED PROCEDURE. Any suggestions?)
 public class LoadDataCls
		{
			public static DataTable LoadData(DataTable tbl, string selTbl, string connectionString)
			{
				string sqlSel = @"Select * From " + selTbl;

				DataTable table = new DataTable();
				SqlConnection conndb = new SqlConnection(connectionString);

				try
				{
					SqlDataAdapter myAdapter = new SqlDataAdapter(sqlSel, conndb);
					myAdapter.Fill(table);
					myAdapter.Dispose();
					tbl = table;
				}
				catch (Exception e)
				{
					MessageBox.Show("Error in connecting! " + e.ToString(), "Error");
				}
				finally
				{
					conndb.Close();
					conndb.Dispose();
				}
				return tbl;
			}
		}



Was This Post Helpful? 0
  • +
  • -

#4 getajob1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 03-February 09

Re: return multiple rows (cursor) from stored procedure

Post icon  Posted 13 April 2009 - 12:42 PM


I am able to return a single value such as an int or string or result value but I can't return cursor values. I have a routine in Visual C#.Net that works fine and loads a table which then can be processed but I don't know how to perform the same in a stored procedure and return the cursor to C# for table processing.
Below is a try at it. What do you suggest? Thanks in advance.


						(SHOULD I USE ADODB.RECORDSETS?)
		 private ADODB.Connection cn = new ADODB.Connection();
		 private ADODB.Recordset rs = new ADODB.Recordset();
					 rs.Open(query, cnStr, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);		
					 rs.Close();


		 (THIS CODE WORKS IF I SEND BACK AN INT OR STRING BUT HOW CAN IT WORK FOR A CURSOR?)

System.Data.SqlClient.SqlCommand cmdEmployee = new SqlCommand("GetEmployeeAllCursor", dataConn);
				 cmdEmployee.CommandType = CommandType.StoredProcedure;
				 cmdEmployee.Connection = dataConn;
				 cmdEmployee.Parameters.Add("@LastName", SqlDbType. .NVarChar, 20);
				 cmdEmployee.Parameters["@LastName"].Value = lastname;
				 cmdEmployee.Parameters.Add("@Count", SqlDbType.Int);
				 cmdEmployee.Parameters["@Count"].Direction = ParameterDirection.Output;


				 dataConn.Open();
				 int iCount;
				 cmdEmployee.ExecuteNonQuery();
				 iCount = (int)cmdEmployee.Parameters["@Count"].Value;
				 sqlcnt = iCount;
				 dataConn.Close();	



Was This Post Helpful? 0
  • +
  • -

#5 itlee  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 55
  • Joined: 16-July 08

Re: return multiple rows (cursor) from stored procedure

Posted 15 April 2009 - 01:56 PM

Cursors are an internal mechanism for SQL Server to enable working with one row at a time, you can't pass them out of SQL Server.

You need to return a result set by using a SELECT statement or a stored procedure that returns one.

Maybe the stored procedure name in your code is misleading and it is really returning a result set, if thats the case then you should use a SqlDataAdaptor to fill a DataSet then you can process the data.

Check out MSDN for examples.

itlee.
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: return multiple rows (cursor) from stored procedure

Posted 15 April 2009 - 01:58 PM

Topics merged, please don't create duplicate topics :)
Was This Post Helpful? 0
  • +
  • -

#7 itlee  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 55
  • Joined: 16-July 08

Re: return multiple rows (cursor) from stored procedure

Posted 15 April 2009 - 02:18 PM

Thought I was going mad for a minute, having missed the first 3 posts ! but its a merged topic.

getajob1, are u wanting to write a stored procedure or some C# code to use the stored procedure?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1