79213175

Date: 2024-11-21 23:20:50
Score: 2.5
Natty:
Report link

I got my code to work. My code now looks like this. This article got me pointed in the right direction. How do I specify to use server defined values in a Table Valued Parameter when my source is a DataReader?

    public static async Task StreamUserHistoryToDWAsync3(IConfigurationRoot config)
{
    try
    {
        var watch = System.Diagnostics.Stopwatch.StartNew();

        string query = @"WBH_DATAWAREHOUSE.WBH_UserHistory";
        string insertQuery = "[Synapse].[InsertUserHistory]";

        using (SqlConnection sqlCn = new SqlConnection(config.GetConnectionString("powerbi")))
        using (OracleConnection oraCn = new OracleConnection(config.GetConnectionString("SynapseProd")))
        {
            await sqlCn.OpenAsync();
            await oraCn.OpenAsync();

            using (OracleCommand oraCmd = new OracleCommand(query, oraCn))
            using (SqlCommand sqlCmd = new SqlCommand(insertQuery, sqlCn))
            {
                oraCmd.CommandType = CommandType.StoredProcedure;
                oraCmd.Parameters.Add(new OracleParameter("MaxBegtime", GetMaxUserHistory(config)));
                oraCmd.Parameters.Add(new OracleParameter("rcUserHistory", OracleDbType.RefCursor, ParameterDirection.Output));
                
                sqlCmd.CommandType = CommandType.StoredProcedure;

                using (OracleDataReader odr = oraCmd.ExecuteReader())
                {
                    SqlParameter tvp = new SqlParameter("@UserHistory", SendRowsToProc(odr));
                    tvp.SqlDbType = SqlDbType.Structured;
                    tvp.TypeName = "[Synapse].[UserHistoryTableType]";
                    SqlParameter rtn = new SqlParameter("@rtn_result", SqlDbType.Int);
                    rtn.Direction = ParameterDirection.Output;
                    sqlCmd.Parameters.Add(tvp);
                    sqlCmd.Parameters.Add(rtn);
                    await sqlCmd.ExecuteNonQueryAsync();
                }
            }
        }


        watch.Stop();
        var elapsed = watch.ElapsedMilliseconds;

        Console.WriteLine(elapsed.ToString());

        logger.Info("User History elapsed time: {time}", elapsed.ToString());

    }
    catch (Exception ex)
    {
        logger.Error(ex, "StreamUserHistoryToDW Error");
    }
}


private static IEnumerable<SqlDataRecord> SendRowsToProc(OracleDataReader reader)
{
    if (!reader.HasRows)
    {
        yield break;
    }

    SqlDataRecord resultRow = new SqlDataRecord(new SqlMetaData[] {
        new SqlMetaData("NAMEID", SqlDbType.VarChar,12),
        new SqlMetaData("BEGTIME", SqlDbType.DateTime),
        new SqlMetaData("EVENT", SqlDbType.VarChar,4),
        new SqlMetaData("ENDTIME", SqlDbType.DateTime),
        new SqlMetaData("FACILITY", SqlDbType.VarChar,3),
        new SqlMetaData("CUSTID", SqlDbType.VarChar,10),
        new SqlMetaData("EQUIPMENT", SqlDbType.VarChar,2),
        new SqlMetaData("UNITS", SqlDbType.Int),
        new SqlMetaData("ETC", SqlDbType.VarChar,255),
        new SqlMetaData("ORDERID", SqlDbType.Int),
        new SqlMetaData("SHIPID", SqlDbType.SmallInt),
        new SqlMetaData("LOCATION", SqlDbType.VarChar,10),
        new SqlMetaData("LPID", SqlDbType.VarChar,20),
        new SqlMetaData("ITEM", SqlDbType.VarChar, 50),
        new SqlMetaData("UOM", SqlDbType.VarChar, 4),
        new SqlMetaData("BASEUOM", SqlDbType.VarChar,4),
        new SqlMetaData("BASEUNITS", SqlDbType.Int),
        new SqlMetaData("CUBE", SqlDbType.Float),
        new SqlMetaData("WEIGHT", SqlDbType.Decimal, 17,8),
        new SqlMetaData("EMPLOYEECOST", SqlDbType.Decimal,10,2),
        new SqlMetaData("EQUIPMENTCOST", SqlDbType.Decimal, 10, 2)
    });

    while (reader.Read())
    {
        try
        {
            resultRow.SetString(0, ConvertFromDBVal<string>(reader.GetValue("NAMEID").ToString()));
            resultRow.SetDateTime(1, ConvertFromDBVal<DateTime>(reader.GetValue("BEGTIME")));
            resultRow.SetString(2, ConvertFromDBVal<string>(reader.GetValue("EVENT").ToString()));
            resultRow.SetDateTime(3, ConvertFromDBVal<DateTime>(reader.GetValue("ENDTIME")));
            resultRow.SetString(4, ConvertFromDBVal<string>(reader.GetValue("FACILITY").ToString()));
            resultRow.SetString(5, ConvertFromDBVal<string>(reader.GetValue("CUSTID").ToString()));
            resultRow.SetString(6, ConvertFromDBVal<string>(reader.GetValue("EQUIPMENT").ToString()));
            resultRow.SetInt32(7, ConvertFromDBVal<Int32>(reader.GetValue("UNITS")));
            resultRow.SetString(8, ConvertFromDBVal<string>(reader.GetValue("ETC").ToString()));
            resultRow.SetSqlInt32(9, ConvertFromDBVal<Int32>(reader.GetValue("ORDERID")));
            resultRow.SetSqlInt16(10, ConvertFromDBVal<Int16>(reader.GetValue("SHIPID")));
            resultRow.SetString(11, ConvertFromDBVal<string>(reader.GetValue("LOCATION").ToString()));
            resultRow.SetString(12, ConvertFromDBVal<string>(reader.GetValue("LPID").ToString()));
            resultRow.SetString(13, ConvertFromDBVal<string>(reader.GetValue("ITEM").ToString()));
            resultRow.SetString(14, ConvertFromDBVal<string>(reader.GetValue("UOM").ToString()));
            resultRow.SetString(15, ConvertFromDBVal<string>(reader.GetValue("BASEUOM").ToString()));
            resultRow.SetInt32(16, ConvertFromDBVal<Int32>(reader.GetValue("BASEUNITS")));
            resultRow.SetDouble(17, ConvertFromDBVal<double>(reader.GetValue("CUBE")));
            resultRow.SetSqlDecimal(18, ConvertFromDBVal<decimal>(reader.GetValue("WEIGHT")));
            resultRow.SetSqlDecimal(19, ConvertFromDBVal<decimal>(reader.GetValue("EMPLOYEECOST")));
            resultRow.SetSqlDecimal(20, ConvertFromDBVal<decimal>(reader.GetValue("EMPLOYEECOST")));
        }
        catch(Exception ex)
        {
            logger.Error(ex, "Setting up resultrow Error");
        }
        
        yield return resultRow;
    }
}

public static T ConvertFromDBVal<T>(object obj)
{
    if (obj == null || obj == DBNull.Value)
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj;
    }
}
Reasons:
  • Blacklisted phrase (1): This article
  • Blacklisted phrase (1): How do I
  • Probably link only (1):
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: David Gerst