none
Data Cursor in C# in Visual Studio 2008 and Sql Server 2008 RRS feed

  • Question

  • Hi,
    I just want to seperate a TSql Select statement from the handling of the data retrieved by the select statement in C#.
    using System;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public class StoredProcedures
    {
    	[Microsoft.SqlServer.Server.SqlProcedure]
    	public static void PriceSum(out SqlMoney value)
    	{
    		using (SqlConnection connection = new SqlConnection("context connection=true"))
    		{
    			value = 0;
    			connection.Open();
    			SqlCommand command = new SqlCommand<strong>("SELECT IsNull(ListPrice, 0) FROM AdventureWorks.Production.Product"</strong>, connection);
    			SqlDataReader reader = command.ExecuteReader();
    
    			using (reader)
    			{
    				while (reader.Read())
    				{
    					value += reader.GetSqlMoney(0);
    				}
    			}
    		}
    	}
    }
    

    So I want the SELECT IsNull(ListPrice, 0) FROM AdventureWorks.Production.Product in a seperate function, preferably a TSQL table valued function. How do I go about that in the most efficient way?
    I want to be able to write/debug the proc in Visual Studio 2008.
    I have spent serveral hours trying to find a simple example, but no sigar.
    Regards Jos
     

    I'll be back
    • Moved by Andrew.Wu Tuesday, September 27, 2011 6:32 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Monday, September 26, 2011 11:46 AM

Answers

  • Hi Gareth,

    I wanted an example of seperation of C# and TSQL in one project. However I find it is not possible to have 2 languages in one VS project.
    So far I have:

    ALTER				PROCEDURE					dbo.SelectListPrice
    --=================================================================
    --		Datum		26 sep 2011
    --		Auteur		Jos vd Vlis
    --=================================================================
    AS
    BEGIN
    
    	SELECT			ListPrice
    	FROM
    	(
    	SELECT			IsNull(ListPrice, 0)	As	ListPrice
    	FROM			AdventureWorks.Production.Product
    	)										As	lst
    	WHERE			ListPrice				<>	0
    	
    	RETURN
    END
    
    

    and

    using System;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public class StoredProcedures
    {
    	[Microsoft.SqlServer.Server.SqlProcedure]
    	public static void PriceSum(out SqlMoney value)
    	{
    		using (SqlConnection connection = new SqlConnection("context connection=true"))
    		{
    			value = 0;
    			connection.Open();
    			SqlCommand command = new SqlCommand("dbo.SelectListPrice", connection);
    			command.CommandType = System.Data.CommandType.StoredProcedure;
    			SqlDataReader reader = command.ExecuteReader();
    
    			using (reader)
    			{
    				while (reader.Read())
    				{
    					value += reader.GetSqlMoney(0);
    				}
    			}
    		}
    	}
    }
    

    The Sql is not attached to the project, but can be debugged whilest debugging the .cs, what was my aim.


    I'll be back
    • Marked as answer by Josje Tuesday, September 27, 2011 7:54 AM
    Tuesday, September 27, 2011 7:29 AM

All replies

  • You could probably simplify the code in a couple of ways.  I assume this is a contrived example but the following query should give you the same result but only return one row:

    select sum(listprice) from adventureworks.production.product

    In either case, the best way to remove this code from your app would be to wrap it in a stored procedure:

    create procedure PriceSum as 

    select sum(listprice) from adventureworks.production.product

    Then all you need to do is execute the SP in your code.

    An example of executing a stored procedure can be found here: http://msdn.microsoft.com/en-us/library/d7125bke.aspx

    Monday, September 26, 2011 9:55 PM
  • Hi Josje,

    I'm going to help move your post to ADO.NET Managed Providers Forum for better support.

    Thansk for your understanding.

    Best Regards,


    Andrew Wu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 27, 2011 6:32 AM
  • Hi Gareth,

    I wanted an example of seperation of C# and TSQL in one project. However I find it is not possible to have 2 languages in one VS project.
    So far I have:

    ALTER				PROCEDURE					dbo.SelectListPrice
    --=================================================================
    --		Datum		26 sep 2011
    --		Auteur		Jos vd Vlis
    --=================================================================
    AS
    BEGIN
    
    	SELECT			ListPrice
    	FROM
    	(
    	SELECT			IsNull(ListPrice, 0)	As	ListPrice
    	FROM			AdventureWorks.Production.Product
    	)										As	lst
    	WHERE			ListPrice				<>	0
    	
    	RETURN
    END
    
    

    and

    using System;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public class StoredProcedures
    {
    	[Microsoft.SqlServer.Server.SqlProcedure]
    	public static void PriceSum(out SqlMoney value)
    	{
    		using (SqlConnection connection = new SqlConnection("context connection=true"))
    		{
    			value = 0;
    			connection.Open();
    			SqlCommand command = new SqlCommand("dbo.SelectListPrice", connection);
    			command.CommandType = System.Data.CommandType.StoredProcedure;
    			SqlDataReader reader = command.ExecuteReader();
    
    			using (reader)
    			{
    				while (reader.Read())
    				{
    					value += reader.GetSqlMoney(0);
    				}
    			}
    		}
    	}
    }
    

    The Sql is not attached to the project, but can be debugged whilest debugging the .cs, what was my aim.


    I'll be back
    • Marked as answer by Josje Tuesday, September 27, 2011 7:54 AM
    Tuesday, September 27, 2011 7:29 AM