### Bulk Data Import
This is a simple example of using Soapi.CS for bulk data importation.
This sample creates/updates a SQLite database with the the complete profile of all Stack Exchange users with reputation >= 100 and assigns a rank according to order of appearance.
Soapi processes the ~1000 requests required in less than 5 minutes.
You may run this code against the same database and the records are updated in place.
This code requires references to [System.Data.SQLite][1] and Soapi.dll.
Code very similar to this is the core of the [SO-REP][2] service.
**Usage:**
new SoapiUserIndexer()
.Index("data source=users.db;new=true", apikey);
**Code:**
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Soapi.Domain;
namespace Soapi.Data
{
public class SoapiUserIndexer
{
/// <summary>
///
/// Provides you with a local database of all stack exchange users with
/// rep >=100, 90,000 records, in less than 5 minutes using Soapi.CS
///
/// It is not recommended to decrease the minimum rep value as the numbers
/// increase drastically with questionable benefit.
///
/// This is the core of the SO-REP Service, which ranks provides
/// ranking data.
///
/// Requires System.Data.SQLite
/// http://sqlite.phxsoftware.com/
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="apiKey"></param>
public void Index(string connectionString, string apiKey)
{
var ctx = new ApiContext(apiKey).Initialize(false);
var lastModified = DateTime.Now;
const int batchSize = 50;
const int minRep = 100;
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = CreateUsersTable;
cmd.ExecuteNonQuery();
foreach (Site site in ctx.Sites.Where(s => s.ApiEndpoint.IndexOf("stackauth") == -1))
{
// get the count of users with Rep >= 100 and div by 100 to get a page count
var pageCount = (int)(Math.Ceiling((double)site.Users.Min(minRep).Total / 100) + 1);
var batchCount = Math.Ceiling((double)pageCount / batchSize);
for (int i = 0; i < batchCount; i++)
{
var startPage = i*batchSize + 1;
var rank = (batchCount*i) + 1;
var users = site.Users.PageCount(batchSize).Page(startPage).Min(minRep).ToList();
using (var txn = connection.BeginTransaction())
{
cmd = BuildInsertCommand();
cmd.Connection = connection;
foreach (User item in users)
{
cmd.Parameters["@ApiEndpoint"].Value = item.Site.ApiEndpoint;
cmd.Parameters["@UserId"].Value = item.UserId;
cmd.Parameters["@AssociationId"].Value = item.AssociationId;
cmd.Parameters["@UserType"].Value = item.UserType.ToString();
cmd.Parameters["@DisplayName"].Value = item.DisplayName ?? "";
cmd.Parameters["@AboutMe"].Value = item.AboutMe ?? "";
cmd.Parameters["@Location"].Value = item.Location ?? "";
cmd.Parameters["@WebsiteUrl"].Value = item.WebsiteUrl ?? "";
cmd.Parameters["@EmailHash"].Value = item.EmailHash ?? "";
cmd.Parameters["@CreationDate"].Value = item.CreationDate;
cmd.Parameters["@LastAccessDate"].Value = item.LastAccessDate;
cmd.Parameters["@TimedPenaltyDate"].Value = item.TimedPenaltyDate;
cmd.Parameters["@AcceptRate"].Value = item.AcceptRate;
cmd.Parameters["@Age"].Value = item.Age;
cmd.Parameters["@AnswerCount"].Value = item.AnswerCount;
cmd.Parameters["@DownVoteCount"].Value = item.DownVoteCount;
cmd.Parameters["@QuestionCount"].Value = item.QuestionCount;
cmd.Parameters["@Reputation"].Value = item.Reputation;
cmd.Parameters["@UpVoteCount"].Value = item.UpVoteCount;
cmd.Parameters["@ViewCount"].Value = item.ViewCount;
cmd.Parameters["@BadgesGold"].Value = item.BadgeCounts.Gold;
cmd.Parameters["@BadgesSilver"].Value = item.BadgeCounts.Silver;
cmd.Parameters["@BadgesBronze"].Value = item.BadgeCounts.Bronze;
cmd.Parameters["@Rank"].Value = rank++;
cmd.Parameters["@LastModifiedDate"].Value = lastModified;
cmd.ExecuteNonQuery();
}
txn.Commit();
}
}
}
connection.Close();
}
}
private static SQLiteCommand BuildInsertCommand()
{
var cmd = new SQLiteCommand(SqlUerInsertOrUpdate)
{
CommandText = SqlUerInsertOrUpdate
};
foreach (var item in new List<object[]>
{
new object[]{"@ApiEndpoint", DbType.String,128},
new object[]{"@UserId", DbType.Int32},
new object[]{"@AssociationId", DbType.Guid},
new object[]{"@UserType", DbType.String,20},
new object[]{"@DisplayName", DbType.String,50},
new object[]{"@AboutMe", DbType.String,4000},
new object[]{"@Location", DbType.String,128},
new object[]{"@WebsiteUrl", DbType.String,256},
new object[]{"@EmailHash", DbType.String,64},
new object[]{"@CreationDate", DbType.DateTime},
new object[]{"@LastAccessDate", DbType.DateTime},
new object[]{"@TimedPenaltyDate", DbType.DateTime},
new object[]{"@AcceptRate", DbType.Int32},
new object[]{"@Age", DbType.Int32},
new object[]{"@AnswerCount", DbType.Int32},
new object[]{"@DownVoteCount", DbType.Int32},
new object[]{"@QuestionCount", DbType.Int32},
new object[]{"@Reputation", DbType.Int32},
new object[]{"@UpVoteCount", DbType.Int32},
new object[]{"@ViewCount", DbType.Int32},
new object[]{"@BadgesGold", DbType.Int32},
new object[]{"@BadgesSilver", DbType.Int32},
new object[]{"@BadgesBronze", DbType.Int32},
new object[]{"@Rank", DbType.Int32},
new object[]{"@LastModifiedDate",DbType.DateTime}
})
{
var p = new SQLiteParameter { ParameterName = (string)item[0], DbType = (DbType)item[1] };
if (p.DbType == DbType.String)
{
p.Size = (int)item[2];
}
cmd.Parameters.Add(p);
}
return cmd;
}
private const string SqlUerInsertOrUpdate = @"
INSERT OR REPLACE INTO [Users] ([ApiEndpoint],[UserId],[AssociationId],[UserType],[DisplayName],[AboutMe],[Location]
,[WebsiteUrl],[EmailHash],[CreationDate],[LastAccessDate],[TimedPenaltyDate],[AcceptRate],[Age],[AnswerCount],[DownVoteCount]
,[QuestionCount],[Reputation],[UpVoteCount],[ViewCount],[BadgesGold],[BadgesSilver],[BadgesBronze],[Rank],[LastModifiedDate])
VALUES (@ApiEndpoint,@UserId,@AssociationId,@UserType,@DisplayName,@AboutMe,@Location,@WebsiteUrl,@EmailHash,@CreationDate
,@LastAccessDate,@TimedPenaltyDate,@AcceptRate,@Age,@AnswerCount,@DownVoteCount,@QuestionCount,@Reputation,@UpVoteCount
,@ViewCount,@BadgesGold,@BadgesSilver,@BadgesBronze,@Rank,@LastModifiedDate);";
private const string CreateUsersTable = @"
CREATE TABLE IF NOT EXISTS [Users] (
[RowId] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[ApiEndpoint] varchar(128) NOT NULL,
[UserId] int NOT NULL,
[AssociationId] guid NOT NULL,
[UserType] varchar(20) NOT NULL,
[DisplayName] nvarchar(50) NOT NULL,
[AboutMe] nvarchar(4000) NOT NULL,
[Location] nvarchar(128) NOT NULL,
[WebsiteUrl] nvarchar(256) NOT NULL,
[EmailHash] varchar(64) NOT NULL,
[CreationDate] datetime NOT NULL,
[LastAccessDate] datetime NOT NULL,
[TimedPenaltyDate] datetime NOT NULL,
[AcceptRate] int NOT NULL,
[Age] int NOT NULL,
[AnswerCount] int NOT NULL,
[DownVoteCount] int NOT NULL,
[QuestionCount] int NOT NULL,
[Reputation] int NOT NULL,
[UpVoteCount] int NOT NULL,
[ViewCount] int NOT NULL,
[BadgesGold] int NOT NULL,
[BadgesSilver] int NOT NULL,
[BadgesBronze] int NOT NULL,
[Rank] int NOT NULL,
[LastModifiedDate] datetime NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS [IX_ApiEndpoint_UserId] ON [Users] ([ApiEndpoint], [UserId]);";
}
}
[1]: http://sqlite.phxsoftware.com/
[2]: https://stackapps.com/questions/543/sorep-trilogy-site-ranking-for-those-who-need-to-know