locked
Datatable to Oracle recordser RRS feed

  • Question

  • User-59962394 posted

    Hi

    I am storing my data in datatable using c#. This datatable needs to be sent to oracle procedure which accepts a recordset.

    Can I directly assign my datatable object to recordset parameter?

    If not then please let me know how can I pass data to recordset using the data inside datatable.

    Thanks in advance.

    Tuesday, March 1, 2011 11:27 AM

Answers

  • User-59962394 posted

    Hi,

    Got a way to work out for this. I wil be making it a big string with carriage return for each row, so that oracle procedure will be handling the other part.

    I can see one more way which may be useful for the ones who are looking for a solution.

    Simply loop through the datatable for each record and execute the Oracle procedure passing the values for each record.

    For ex:-

    Data in Datatable

    Col1  col2    col3

    1     Test1  Data1

    2     Test2   Data2

    etc

    Oracle procedure as below

    PROCEDURE CreateRecord(-- Input parameters Begin
                                col1 IN NUMBER, col2 IN VARCHAR2, col3 IN VARCHAR2)

    Loop the datatable for each row calling the procedure.

    Note: This is good only for less number of records but may need a different solution if its large amount of data

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2011 9:06 AM

All replies

  • User269602965 posted

        ' Insert code to get your recordset values into variables in code behind form '
        ' In this example I have two variables strTicketID and dateDateTimeStamp      '
        ' Pass to Oracle Command parameters of the appropriate datatype and INSERT into database '
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString
        Try
          Dim SQL = <SQL>
                      INSERT INTO {SCHEMA_NAME}.HELP_TICKET
                      (TICKET_ID, TICKET_TIMESTAMP)
                      VALUES (:TICKET_ID, :TICKET_TIMESTAMP)
                    </SQL>
          Using conn As New OracleConnection(connectionString)
            Using cmd As New OracleCommand(SQL.Value, conn)
              cmd.Parameters.Clear()
              cmd.Parameters.Add("TICKET_ID", OracleDbType.Varchar2, strTicketID, ParameterDirection.Input)
              cmd.Parameters.Add("TICKET_TIMESTAMP", OracleDbType.Date, dateDateTimeStamp, ParameterDirection.Input)
              conn.Open()
              cmd.ExecuteNonQuery()
            End Using
          End Using
        Catch ex As Exception
        End Try

    Tuesday, March 1, 2011 6:50 PM
  • User-59962394 posted

    Hi

    Thanks for the reply. I am trying to add whole data in the datatable into oracle record set. If I have 10 to 15 records in datatable, then I may have to call the procedure that many times to insert all the data if I am calling the procedure for each record.

    Is there any other way where I can directly pass the datatable data completely at one call to procedure.

    Thanks in advance.

    Wednesday, March 2, 2011 6:03 AM
  • User-59962394 posted

    Hi,

    Can any one help me in passing data table to Oracle stored procedure.

    Please let me know how can I do this.

    Thanks in advance.

    Wednesday, March 2, 2011 7:23 AM
  • User-59962394 posted

    Hi,

    Got a way to work out for this. I wil be making it a big string with carriage return for each row, so that oracle procedure will be handling the other part.

    I can see one more way which may be useful for the ones who are looking for a solution.

    Simply loop through the datatable for each record and execute the Oracle procedure passing the values for each record.

    For ex:-

    Data in Datatable

    Col1  col2    col3

    1     Test1  Data1

    2     Test2   Data2

    etc

    Oracle procedure as below

    PROCEDURE CreateRecord(-- Input parameters Begin
                                col1 IN NUMBER, col2 IN VARCHAR2, col3 IN VARCHAR2)

    Loop the datatable for each row calling the procedure.

    Note: This is good only for less number of records but may need a different solution if its large amount of data

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2011 9:06 AM