public void SqlServerBulkInsertStatements()
{
using (var tr = _dbSqlServer.GetTransaction())
{
_dbSqlServer.BulkInsertRecords(GetData(), tr, _sqlServerSyntax, useNativeSqlPlatformBulkInsert: false);
tr.Complete();
}
}
public void SqlCeOneByOne()
{
using (var tr = _dbSqlCe.GetTransaction())
{
_dbSqlCe.BulkInsertRecords(GetData(), tr, _sqlCeSyntax, useNativeSqlPlatformBulkInsert: false);
tr.Complete();
}
}
public void SetPermissions(SetAccountPermissionsRequest accountPermissions)
{
var entries = new List <AccountSettings>();
foreach (var permission in accountPermissions.Permissions)
{
var accountSettingEntry = new AccountSettings();
// Always default to empty for niw
accountSettingEntry.Notes = "";
// Default to read until we have functionality for other permissions
accountSettingEntry.Permission = Permissions.Read; // TODO: functionality for Write permissions
accountSettingEntry.AccountId = accountPermissions.AccountId;
accountSettingEntry.PropertyTypeAlias = permission.PropertyAlias;
accountSettingEntry.DocTypeAlias = permission.DoctypeAlias;
accountSettingEntry.IsBuiltInProperty = permission.IsBuiltInProperty;
accountSettingEntry.CreatedOn = DateTime.UtcNow;
accountSettingEntry.UpdatedOn = DateTime.UtcNow;
entries.Add(accountSettingEntry);
}
// Clear existing settings
var sql = new Sql("DELETE FROM GraphQL_AccountSettings WHERE AccountId=@0", accountPermissions.AccountId);
_database.Execute(sql);
// Insert in bulk so it's handled within a single query
_database.BulkInsertRecords(entries, ApplicationContext.Current.DatabaseContext.SqlSyntax);
}
public static void InsertInitialSettings(UmbracoDatabase db, ISqlSyntaxProvider sqlSyntaxProvider)
{
var data = FortressSettings.GetDefaultSettings();
db.BulkInsertRecords(data, sqlSyntaxProvider);
}
public IHttpActionResult SaveDocTypeConfig(Dictionary <int, List <UserGroupPermissionsPoco> > model)
{
try
{
if (null != model)
{
UmbracoDatabase db = DatabaseContext.Database;
// set defaults for doctype - delete all previous if any model data exists
db.Execute("DELETE FROM WorkflowUserGroupPermissions WHERE ContentTypeId != 0");
if (model.Any())
{
foreach (KeyValuePair <int, List <UserGroupPermissionsPoco> > permission in model)
{
if (permission.Value.Any())
{
db.BulkInsertRecords(permission.Value, DatabaseContext.SqlSyntax);
}
}
}
}
}
catch (Exception ex)
{
string msg = $"Error saving config. {ex.Message}";
return(Content(HttpStatusCode.InternalServerError, ViewHelpers.ApiException(ex, msg)));
}
return(Ok());
}
public IHttpActionResult SaveConfig(Dictionary <int, List <UserGroupPermissionsPoco> > model)
{
try
{
UmbracoDatabase db = DatabaseContext.Database;
if (null != model && model.Any())
{
KeyValuePair <int, List <UserGroupPermissionsPoco> > permission = model.First();
db.Execute("DELETE FROM WorkflowUserGroupPermissions WHERE NodeId = @0", permission.Key);
if (permission.Value.Any())
{
db.BulkInsertRecords(permission.Value, DatabaseContext.SqlSyntax);
}
}
}
catch (Exception ex)
{
string msg = $"Error saving config. {ex.Message}";
return(Content(HttpStatusCode.InternalServerError, ViewHelpers.ApiException(ex, msg)));
}
return(Ok());
}
public static void InsertInitialSettings(UmbracoDatabase db)
{
var data = FortressSettings.GetDefaultSettings();
db.BulkInsertRecords(data);
}
public void Can_Bulk_Insert_Native_Sql_Server_Bulk_Inserts()
{
//create the db
var dbSqlServer = new UmbracoDatabase(
"server=.\\SQLExpress;database=YOURDB;user id=YOURUSER;password=YOURPASSWORD",
Constants.DatabaseProviders.SqlServer,
new DebugDiagnosticsLogger());
//drop the table
dbSqlServer.Execute("DROP TABLE [umbracoServer]");
//re-create it
dbSqlServer.Execute(@"CREATE TABLE [umbracoServer](
[id] [int] IDENTITY(1,1) NOT NULL,
[address] [nvarchar](500) NOT NULL,
[computerName] [nvarchar](255) NOT NULL,
[registeredDate] [datetime] NOT NULL CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()),
[lastNotifiedDate] [datetime] NOT NULL,
[isActive] [bit] NOT NULL,
[isMaster] [bit] NOT NULL,
CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)");
var data = new List <ServerRegistrationDto>();
for (var i = 0; i < 1000; i++)
{
data.Add(new ServerRegistrationDto
{
ServerAddress = "address" + i,
ServerIdentity = "computer" + i,
DateRegistered = DateTime.Now,
IsActive = true,
DateAccessed = DateTime.Now
});
}
var sqlServerSyntax = new SqlServerSyntaxProvider();
using (var tr = dbSqlServer.GetTransaction())
{
dbSqlServer.BulkInsertRecords(data, tr, sqlServerSyntax, useNativeSqlPlatformBulkInsert: true);
tr.Complete();
}
// Assert
Assert.That(dbSqlServer.ExecuteScalar <int>("SELECT COUNT(*) FROM umbracoServer"), Is.EqualTo(1000));
}
public void SetGroupsForUser(int userId, IEnumerable <int> groupIds)
{
var currentSql = new Sql().Select("[GroupId]").From(AppConstants.TableNames.EasyADGroup2Users).Where <EasyADGroup2User>(g => g.UserId == userId);
var currentGroups = _database.Fetch <int>(currentSql);
var deleteGroupIds = currentGroups.Except(groupIds);
var addGroupIds = groupIds.Except(currentGroups);
if (deleteGroupIds.Any())
{
var deleteSql = new Sql().Where("[UserId] = @0 AND [GroupId] IN (@1)", userId, deleteGroupIds);
_database.Delete <EasyADGroup2User>(deleteSql);
}
if (addGroupIds.Any())
{
_database.BulkInsertRecords <EasyADGroup2User>(addGroupIds.Select(g => new EasyADGroup2User {
GroupId = g, UserId = userId
}));
}
}
public void Start()
{
var fullPath = HostingEnvironment.MapPath(ConfigFilePath);
if (!File.Exists(fullPath))
{
return;
}
try
{
var oldNodes = ReadConfig(fullPath);
if (oldNodes.Count() > 0)
{
_database.BulkInsertRecords(oldNodes);
}
}
catch (Exception e)
{
UC.Logging.LogHelper.Error(typeof(Migrator), e.Message, e);
}
}
private static void CreateDataBase(UmbracoDatabase db)
{
if (!db.TableExist("ContactMessage"))
{
db.CreateTable <ContactPoco>(false);
}
if (!db.TableExist("ContactSettings"))
{
db.CreateTable <ContactSettings>(false);
var settingsTwo = new List <ContactSettings>()
{
new ContactSettings()
{
ConfigName = "PageSize",
ConfigValue = "10",
ConfigText = "Page Size",
ConfigHelper = "",
ConfigSort = 9
},
new ContactSettings()
{
ConfigName = "AutoReplyMessage",
ConfigValue = "Thanks for contacting us",
ConfigText = "Auto Reply Message",
ConfigHelper = "",
ConfigSort = 3
},
new ContactSettings()
{
ConfigName = "TemplateNode",
ConfigValue = "",
ConfigText = "Auto Reply Template",
ConfigHelper = "",
ConfigSort = 4
},
new ContactSettings()
{
ConfigName = "SendNotificationTo",
ConfigValue = "",
ConfigText = "Send Notification To",
ConfigHelper = "*Use commas to include multiple email",
ConfigSort = 7
},
new ContactSettings()
{
ConfigName = "NotificationMessage",
ConfigValue = "You have new message from %name%",
ConfigText = "Notification Message",
ConfigHelper = "",
ConfigSort = 6
},
new ContactSettings()
{
ConfigName = "AutoReplySubject",
ConfigValue = "Thanks for contacting us %name%",
ConfigText = "Auto Reply Subject",
ConfigHelper = "",
ConfigSort = 2
},
new ContactSettings()
{
ConfigName = "NotificationSubject",
ConfigValue = "New message from %name%",
ConfigText = "Notification Subject",
ConfigHelper = "",
ConfigSort = 5
},
new ContactSettings()
{
ConfigName = "NotificationTemplateNode",
ConfigValue = "",
ConfigText = "Notification Template",
ConfigHelper = "",
ConfigSort = 8
},
new ContactSettings()
{
ConfigName = "SenderEmail",
ConfigValue = "*****@*****.**",
ConfigText = "Sender Email",
ConfigHelper = "",
ConfigSort = 0
},
new ContactSettings()
{
ConfigName = "DisplayNameSender",
ConfigValue = "Noreply You Website",
ConfigText = "Display Name Sender",
ConfigHelper = "",
ConfigSort = 1
}
};
db.BulkInsertRecords(settingsTwo);
db.Execute(
"CREATE TABLE uContactorVersion (DbVersion INT)");
db.Execute(
"INSERT INTO uContactorVersion values(1)");
}
}