-
June 18th, 2009, 05:07 AM
#1
show sql-statement with parms
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
-
June 18th, 2009, 08:08 AM
#2
Re: show sql-statement with parms
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.
-
June 18th, 2009, 08:54 AM
#3
Re: show sql-statement with parms
you could also put something like a Debug.WriteLine(sqlins.commandText);
-zd
-
June 18th, 2009, 07:25 PM
#4
Re: show sql-statement with parms
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):
Code:
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#...
Code:
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.
Code:
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 ) );
}
}
-
June 19th, 2009, 09:43 AM
#5
Re: show sql-statement with parms
Originally Posted by 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):
Code:
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.
-
June 19th, 2009, 12:50 PM
#6
Re: show sql-statement with parms
Originally Posted by eclipsed4utoo
@@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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|