2

I need advice what is best approach to connect data between master and child table.

I have console application written in C# which scrape data from web, process data and insert them in table.

Master table with fields Id, OrdinalNumber, StringDate and child table with fields Id, OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate.

Because I scrap data from site I don't have some unique identifier so I'm looking on Ordinal number (number from site) and StringDate(created like date but in string format ddMMyyyy I scrap site ever day once daily).

While I insert data in table it is done like this:

INSERT INTO MasterTable(OrdinalNumber, StringDate) VALUES (@OrdinalNumber, @StringDate)

and after that I insert data in child table:

INSERT INTO ChildTable(OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate) 
VALUES(@OrdinalNumber, @StringDate, @Name, @Amount, @StartDate, @EndDate)

Is this okay approach or better approach would be to get Id from master table when I insert data call SELECT SCOPE_IDENTITY() to get last inserted Id and put them in child table with this query:

INSERT INTO ChildTable(MasterId, Name, Amount, StartDate, EndDate) 
VALUES(@MasterId, @Name, @Amount, @StartDate, @EndDate)

My question is it better to stick with first approach or use SCOPE_IDENTITY() to get last ID and populate child data with it and save in database? I will insert approximately 10000 rows in Master table and 20000+ rows in child table.

I'm using dapper for inserting data in tables.

1.

IDbTransaction transaction = null;

try
{
    using (IDbConnection connection = new SqlConnection(DbConnectionString))
    {

        if (connection.State != ConnectionState.Open)
            connection.Open();

            transaction = connection.BeginTransaction();

            string sql = "INSERT INTO Master(OrdinalNumber, StringDate)" +
                         "VALUES(@OrdinalNumber, @StringDate)";

            connection.Execute(sql, master, transaction);

            string sql = "INSERT INTO Child(OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate)" +
                         "VALUES(@OrdinalNumber, @StringDate, @Name, @Amount, @StartDate, @EndDate)";

            connection.Execute(sql, child, transaction);

            transaction.Commit();

        }
    }
    catch (Exception ex)
    {
        if (transaction != null)
        {
            transaction.Rollback();
        }
        throw ex;
    }
    finally
    {
        if (transaction != null)
            transaction.Dispose();
    }
  1. IDbTransaction transaction = null;

    try { using (IDbConnection connection = new SqlConnection(DbConnectionString)) {

          if (connection.State != ConnectionState.Open)
              connection.Open();
    
              transaction = connection.BeginTransaction();
    
              foreach(var m in master)
              {
                  string sql = "INSERT INTO Master(OrdinalNumber, StringDate)" +
                           "VALUES(@OrdinalNumber, @StringDate)" +
                           "SELECT SCOPE_IDENTITY()";
    
                  var id = connection.ExecuteScalar<int>(sql, m, transaction);
    
                  foreach(var c in child.Where(d => d.OrdinalNumber == m.OrdinalNumber))
                  {
                      string sql = "INSERT INTO Child(MasterId, Name, Amount, StartDate, EndDate)" +
                                   "VALUES(@MasterId, @Name, @Amount, @StartDate, @EndDate)";
    
                      connection.Execute(sql, new {MasterId = id, c.Name, c.Amount, c.StartDate, c.EndDate}, transaction);
                  }
              }               
    
              transaction.Commit();
    
          }
      }
      catch (Exception ex)
      {
          if (transaction != null)
          {
              transaction.Rollback();
          }
          throw ex;
      }
      finally
      {
          if (transaction != null)
              transaction.Dispose();
      }
    
  • It's "scrape," not "scrap." "Scrap" is what you do to an old car when it becomes unusable. – Robert Harvey Sep 30 '20 at 17:30
  • Oh my God kill me for wrongly written one word. – TJacken Sep 30 '20 at 17:37
  • The computer won't kill you. You'll just get a compile error. – Robert Harvey Sep 30 '20 at 17:42
  • Hahaha I will wait for Skynet ;) – TJacken Sep 30 '20 at 17:53
1

Unless you are generating GUIDs for ID's, it's probably better to allow the database to manage your ID's for you. That means retrieving SCOPE_IDENTITY() from the master table, and using that to populate your related records.

You haven't stated how you are obtaining an "Ordinal Number," but the usual way in order to avoid key duplication is to retrieve the highest numbered key from the master table using a query. In addition to being costly from a performance standpoint, this technique also has a race condition: someone else could add a record while you're trying to figure out what ID to use, and now your related records are tied to the wrong master record.

But SCOPE_IDENTITY() already does this work for you, without the race condition.

It's worth noting that, if you use Dapper.Contrib, it will manage these IDs for you automatically:

[Table("MyDatabase.dbo.MasterTable")]
public class MasterRecord
{
    [Key]
    int MasterID { get; set; } // Defined as Primary Key, Auto-Increment in database
    string Name { get; set; }
    int Amount { get; set; }
    DateTime StartDate { get; set; }
    DateTime EndDate { get; set; }
}

var myMasterRecord = new MasterRecord
{
    Name = "Aaron Aames",
    Amount = 6,
    StartDate = DateTime.Now;
    EndDate = StartDate.AddDays(7);
}

connection.Insert(myMasterRecord);  // myMasterRecord.MasterID now contains the new ID.
  • Thank you for advice, ordinal number is same every time (I have 10000 ordinal numbers, so my idea was to prevent duplicate data with StringDate value because I perform scraping once on a day and check for it). – TJacken Sep 30 '20 at 17:51
  • Use actual ID's instead. You'll thank me later. – Robert Harvey Sep 30 '20 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.