0

I have a DataTable varriable in C# and I want to insert it to a ##TempTable in SQL Directly. I dont want to do like insert into ##TempTable row by row.

How can I do that ?

Select Into ##TempTable from (C# DataTable) ?

Or I m asking in a different way: how can we send a dataset to SQL in a Query from C#?

Note: I m using SqlClient, and SqlHelper classes

Ali CAKIL
  • 383
  • 5
  • 21
  • I haven't used it, but I think https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.110%29.aspx should give you what you're looking for. – Brian Riley Jan 17 '16 at 14:33
  • Possible duplicate of [Adding multiple parameterized variables to a database in c#](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp) – Zohar Peled Jan 17 '16 at 14:44
  • may be you can use table valued parameters or make your data as xml format and send it to sql then you can insert it in a single step – Arunprasanth K V Jan 17 '16 at 17:31

1 Answers1

0

Just in case anyone has the same requirement. Any comment is welcome. Code in VB.

Private Sub Connect(srcDT As DataTable, spParameter As String)
    Dim conString As String = GetConnectionString()

    Dim oSqlConnection As SqlConnection = New SqlConnection(conString)
    Try

        Dim oSqlCommand = New SqlCommand("Create Table #STG1 (
            [Username] [nvarchar](100) NULL,
            [FirstName] [nvarchar](100) NULL,
            [LastName] [nvarchar](100) NULL,
            [Active] [bit] NULL,
            [Department] [nvarchar](100) NULL
        )", oSqlConnection) With {
        .CommandType = CommandType.Text,
        .CommandTimeout = 0
    }
        oSqlConnection.Open()
        oSqlCommand.ExecuteNonQuery()
        Dim oSqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(oSqlConnection) With {
        .DestinationTableName = "#STG1"
    }
        oSqlBulkCopy.WriteToServer(srcDT)

        Dim command As New SqlCommand("spName", oSqlConnection) With {
        .CommandType = CommandType.StoredProcedure
    }
        command.Parameters.AddWithValue("@inParam", spParameter)
        command.ExecuteScalar()

    Finally
        oSqlConnection.Close()
        oSqlConnection.Dispose()
    End Try

End Sub
Ubaid
  • 3
  • 5