Devart Forums

dbembic

I have problem with SqlBulkCopy class. Here is code sample:

public static void ExportToTableDevart(Devart.Data.SqlServer.SqlConnection connection, string tableName, DataTable dataTable)
{
  // create table
  Devart.Data.SqlServer.SqlBulkCopy bcp = new Devart.Data.SqlServer.SqlBulkCopy(connection);
  bcp.DestinationTableName = tableName;
  bcp.WriteToServer(dataTable);
}

public static void ExportToTable(System.Data.SqlClient.SqlConnection connection, string tableName, DataTable dataTable)
{
  // create table
  System.Data.SqlClient.SqlBulkCopy bcp = new Devart.Data.SqlServer.SqlBulkCopy(connection);
  bcp.DestinationTableName = tableName;
  bcp.WriteToServer(dataTable);
}

If I use Devart component SqlBulkCopy opens new connection (different SPID in Sql Server Profiler) and executes "INSERT BULK", while System.Data.SqlClient.SqlBulkCopy uses the same connection. It is a problem because I cannot do SqlBulkCopy into local temporary table and use that table somwhere else in application.
There is a second problem. If I want this procedure to work Devart.Data.SqlServer.SqlConnection ConnectionString must have: "Integrated Security=True" while System.Data.SqlClient.SqlConnection does not require that flag.
Is there some solution of my problem?

Components:
Devart.Data.SqlServer version 2.40.307.0

dbembic

I am trying to do this:
I have stored procedure that is returning dataset and I need to send parameter with some value and get scalar result through that same parameter. I am getting some strange behavior from SqlCommand. I used SQL Server Profiler to find out what is happening on the server. Here is sample code:

Code: Select all

Devart.Data.SqlServer.SqlCommand command = new Devart.Data.SqlServer.SqlCommand();
command.Connection = some_connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "test_proc";
Devart.Data.SqlServer.SqlParameter par = new Devart.Data.SqlServer.SqlParameter("@TestPar", Devart.Data.SqlServer.SqlType.Int);
par.Direction = System.Data.ParameterDirection.InputOutput;
par.Value = 42;
command.Parameters.Add(par);
Devart.Data.SqlServer.SqlDataReader reader = command.ExecuteReader();
// do something with dataset, get @TestPar value...

Here are few examples for stored procedure and command captured through profiler...

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SELECT 1

/* Profiler:
declare @p1 int
set @p1=42
exec test_proc @TestPar=@p1 output
select @p1*/

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SET @TestPar = @TestPar + 1
SELECT 1

/* Profiler:
declare @p1 int
set @p1=43
exec test_proc @TestPar=@p1 output
select @p1*/

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SET @TestPar = 22
SELECT 1

/* Profiler:
declare @p1 int
set @p1=22
exec test_proc @TestPar=@p1 output
select @p1*/

Expected behavior would be always like this:

Code: Select all

declare @p1 int
set @p1=42
exec test_proc @TestPar=@p1 output
select @p1

I know that there is a workaround. I can use procedure with 2 params, one for input and one for output, but I would like to avoid that. Is there something that I'm doing wrong?

Environment:
MS Visual Studio 2010
Devart dotConnect for SQL Server 2.40.239.0
SQL Server 2008 R2

Devart Forums

Search found 2 matches

SqlBulkCopy

Problem with stored procedures and output parameters