none
Inserting unique data into database. RRS feed

  • Question

  • Im trying to do three things and right now im on step one...

    1:  Im gathering data and trying to insert into a database, my code is working and im using the xx.insertonsubmit(DATA) statement to do this,  How ever the API that im getting the data from returns some duplicate entries for some reason.  So how can i verify that each record as a unique "videoId" before i insert to the database.  The database has a index for the VideoID colum that is set to uniqe.  Below is my code.

    2: I will be gathering again the same data but compairing it to the data already stored in the database and ill need to compare and then update the data in the database.  What is the best approch for this?

    Code:

     YTDataContext dc = new YTDataContext();
            string UserName = ytUserName.Text.ToString();
            int count = getTotalFeedCount(UserName);


            string feedUrl = ytUserURLS.ytUseruploadFeed(UserName) + "?max-results=50&orderby=published";

            while (feedUrl != null)
            {

                YouTubeQuery query = new YouTubeQuery(feedUrl);
                YouTubeFeed videoFeed = service.Query(query);

                foreach (YouTubeEntry entry in videoFeed.Entries)
                {

                    var ytVideo = new ytVideo();
                    string str = entry.Id.Uri.Content.ToString();
                    ytVideo.ytVideoId = str.Remove(0, str.LastIndexOf("/") + 1);
                    ytVideo.ytId = UserName;
                    ytVideo.datePublished = entry.Published;

                    ytVideo.duration = Convert.ToInt32(entry.Duration.Seconds);

                    if (entry.Updated != null)
                    {
                        ytVideo.dateUpdated = entry.Updated;
                    }

                    if (entry.Statistics.ViewCount.Length != 0)
                    {
                        ytVideo.viewCount = Convert.ToInt32(entry.Statistics.ViewCount);
                    }

                    if (entry.Statistics.FavoriteCount != null)
                    {
                        ytVideo.favoriteCount = Convert.ToInt32(entry.Statistics.FavoriteCount);
                    }

                    if (entry.Statistics.SubscriberCount != null)
                    {
                        ytVideo.SubscriptionCount = Convert.ToInt32(entry.Statistics.SubscriberCount);
                    }
                    if (entry.Statistics.WatchCount != null)
                    {
                        ytVideo.WatchCount = Convert.ToInt32(entry.Statistics.WatchCount);
                    }


                    ytVideo.raterCount = entry.Rating.NumRaters;
                    ytVideo.averageRating = entry.Rating.Average;


                    dc.ytVideos.InsertOnSubmit(ytVideo);


                }
                if (videoFeed.NextChunk != null)
                {
                    feedUrl = videoFeed.NextChunk.ToString();
                }
                else
                {
                    feedUrl = null;
                }

            }



            dc.SubmitChanges();
        }

    Thanks Neil
    Sunday, September 7, 2008 11:58 PM

All replies

  • You may do one of the follwing things

    • Before inserting a videoID ,
      1. pre fetch all videoIDs from database and compare it to new object for no duplicate entry.
      2. Also if you are collecting multiple video objects before calling InsertOnSubmit() compare the objects videoID to all currently in cache to avoid duplicate unique key creation on client
    Wednesday, September 10, 2008 5:36 PM
  • In what way would be the most effecient way to retrive a list of all videos from the database and the API, then compare the two I either need to update or mark as deleted from database.
    Thursday, September 11, 2008 12:54 AM