show sql-statement with parms

[jogisarge]

Hi,

i use VS2008 and SQLServer 2005.

How can i debug a sql-statement, that includes parameters like this ?

string sqlstatement = "INSERT INTO artikel (nr,text1,text2)" +
"VALUES" +
"( @parm1, " +
" @parm2, " +
" @parm3 " +
" " +
" )";
SqlCommand sqlins = new SqlCommand(sqlstatement, con);
sqlins.Parameters.Add("@parm1", SqlDbType.NVarChar).Value = var1;
sqlins.Parameters.Add("@parm2", SqlDbType.Int).Value = var2;
sqlins.Parameters.Add("@parm3", SqlDbType.Int).Value = var3;
sqlins.ExecuteNonQuery();

so, how can i see the statement, that will be sent to the sql-server ?

bye jogi

[eclipsed4utoo]

breakpoint on "sqlins.ExecuteNonQuery();" . then in the Immediate Window, enter "sqlins.CommandText" then hit ENTER. That should show you the SQL query that ADO is going to use.

[zdavis]

you could also put something like a Debug.WriteLine(sqlins.commandText);

-zd

[Arjay]

I'm one of those guys that don't like to put T-SQL into the C# code and instead prefer using stored procedures.

I do this because I like the extra layer of abstraction that the stored procedure provides (as it becomes another 'Interface' layer). Plus I like the fact that I can change the underlying stored procedure code at any time without having to recompile the app. In fact the whole database schema could change and the as long as the stored procedure 'interfaces' didn't change, the app would be none the wiser.

For example, here's a simple stored procedure that would insert a new record and return its ID (of course there's no error handling or checking for duplicates in the code below):

CREATE PROCEDURE [dbo].[Artikel.Insert]
	@Param1 NVARCHAR( 50 )
	, @Param2 INT
	, @Param2 INT
	, @ArtikelID INT OUTPUT
AS
BEGIN
	INSERT INTO [dbo].[Artikel]
		([Param1, Param2, Param3])
	VALUES
		( @Param1, @Param2, @Param3 )

	SELECT @ArtikelID = @@IDENTITY
END

To call this in C#...

using( SqlConnection cn = new SqlConnection( ... ) )
{
	using( SqlCommand cmd = new SqlCommand( "[dbo].[Artikel.Insert]", cn ) )
	{
		SqlParameterCollection sqlParams = cmd.Parameters;

		sqlParams.AddWithValue( "Param1", "value1" );
		sqlParams.AddWithValue( "Param2", value2 );
		sqlParams.AddWithValue( "Param3", value3 );

		SqlParameter outputParam = new SqlParameter( "ArtikelID", System.Data.SqlDbType.Int );
		outputParam.Direction = System.Data.ParameterDirection.Output;
		sqlParams.Add( outputParam );

		cmd.CommandType = System.Data.CommandType.StoredProcedure;

		cmd.ExecuteNonQuery( );

		int artikelID = Convert.ToInt32( sqlParams[ "ArtikelID" ] );
	}
}

Most folks write a simple Data Access Layer (DAL) which makes db calls even easier and further simplifies the code. The following is an example of a DAL and also using constant classes for the string constants.

SqlParamCollection sqlParams = SqlParamCollection.Create( );

sqlParams.Add( Constants.DB.Params.Param1, value1 );
sqlParams.Add( Constants.DB.Params.Param2, value2 );
sqlParams.Add( Constants.DB.Params.Param3, value3 );
sqlParams.AddOutput( Constants.DB.Params.ArtikelID, SqlDbType.Int );
			
using( DAL dal = DAL.Create( Constants.DB.Connections.ArtikelDB ) )
{
	dal.ExecuteSproc( Constants.DB.Sprocs.Artikel.Insert, sqlParams );

	int artikelId = Convert.ToInt32( sqlParams.Parameters[ 3 ].Value ) );
}
}

[eclipsed4utoo]

I'm one of those guys that don't like to put T-SQL into the C# code and instead prefer using stored procedures.

I do this because I like the extra layer of abstraction that the stored procedure provides (as it becomes another 'Interface' layer). Plus I like the fact that I can change the underlying stored procedure code at any time without having to recompile the app. In fact the whole database schema could change and the as long as the stored procedure 'interfaces' didn't change, the app would be none the wiser.

For example, here's a simple stored procedure that would insert a new record and return its ID (of course there's no error handling or checking for duplicates in the code below):

CREATE PROCEDURE [dbo].[Artikel.Insert]
	@Param1 NVARCHAR( 50 )
	, @Param2 INT
	, @Param2 INT
	, @ArtikelID INT OUTPUT
AS
BEGIN
	INSERT INTO [dbo].[Artikel]
		([Param1, Param2, Param3])
	VALUES
		( @Param1, @Param2, @Param3 )

	SELECT @ArtikelID = @@IDENTITY
END

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. Always use SCOPE_IDENTITY() to return the identity of the recently added row.

[Arjay]

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. Always use SCOPE_IDENTITY() to return the identity of the recently added row.

Yep, that's the latest and greatest way to do it.