private static bool CheckTableExists(Schema schema)
{
string sql = string.Format(@"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", schema.GetTableName());
SqlCommand command = new SqlCommand()
{
CommandText = sql
};
return((int)SQLServerHelper.ExecuteScalar(schema.Repository, command) != 0);
}
public override bool TestDbConnection()
{
//Do not use sharing database, do not test the sharing connection string.
//if (SqlServerSettings.Instance.SharingDatabase == false)
//{
// return true;
//}
var shareConnectionString = SqlServerSettings.Instance.SharingDatabaseConnectionString;
return(SQLServerHelper.TestConnection(shareConnectionString));
}
public void Add(Models.TextContent content)
{
try
{
content.StoreFiles();
((IPersistable)content).OnSaving();
var folder = content.GetFolder().GetActualFolder();
var schema = content.GetSchema().GetActualSchema();
if (folder != null && folder.StoreInAPI)
{
var proxy = new BackendProxy();
var additionalData = new Dictionary <string, object>()
{
{ "CreatedBy", AuthHelper.GetCurrentUserName() },
{ "ModifiedBy", AuthHelper.GetCurrentUserName() },
{ "OwnerId", AuthHelper.GetCurrentUserName() }
};
//Get payload
//
var payload = JsonConvert.SerializeObject(content,
new CustomJsonDictionaryConverter(schema.GetJsonSerializationIgnoreProperties(), additionalData));
//Send data to API
//
proxy.Execute("POST", schema.Name, payload);
}
else
{
var command = dbCommands.Add(content);
if (command != null)
{
if (SQLServerTransactionUnit.Current != null)
{
SQLServerTransactionUnit.Current.RegisterCommand(command);
SQLServerTransactionUnit.Current.RegisterPostAction(delegate() { ((IPersistable)content).OnSaved(); });
}
else
{
SQLServerHelper.BatchExecuteNonQuery(content.GetRepository(), command);
((IPersistable)content).OnSaved();
}
}
}
}
catch (Exception e)
{
throw e;
}
}
public object ExecuteScalar(Repository repository, string queryText, System.Data.CommandType commandType = System.Data.CommandType.Text, params KeyValuePair <string, object>[] parameters)
{
var command = new System.Data.SqlClient.SqlCommand(queryText);
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters.Select(it => new SqlParameter()
{
ParameterName = it.Key, Value = it.Value
}).ToArray());
}
command.CommandType = commandType;
return(SQLServerHelper.ExecuteScalar(repository, command));
}
public void ExecuteNonQuery(Repository repository, string queryText, params KeyValuePair <string, object>[] parameters)
{
var command = new System.Data.SqlClient.SqlCommand(queryText);
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters.Select(it => new SqlParameter()
{
ParameterName = it.Key, Value = it.Value
}).ToArray());
}
SQLServerHelper.ExecuteNonQuery(repository, command);
}
public void Delete(Models.TextContent content)
{
var command = dbCommands.Delete(content);
if (SQLServerTransactionUnit.Current != null)
{
SQLServerTransactionUnit.Current.RegisterCommand(command);
SQLServerTransactionUnit.Current.RegisterPostAction(delegate() { TextContentFileHelper.DeleteFiles(content); });
}
else
{
SQLServerHelper.BatchExecuteNonQuery(content.GetRepository(), command);
TextContentFileHelper.DeleteFiles(content);
}
}
public void Update(Models.MediaContent @new, Models.MediaContent old)
{
((IPersistable)@new).OnSaving();
string sql = string.Format("UPDATE {0} SET FolderName = @FolderName,FileName = @FileName,VirtualPath=@VirtualPath) WHERE UUID=@UUID"
, @new.GetRepository().GetMediaContentTableName());
SqlCommand command = new SqlCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlParameter("UUID", @new.UUID));
command.Parameters.Add(new SqlParameter("FolderName", @new.FolderName));
command.Parameters.Add(new SqlParameter("FileName", @new.FileName));
command.Parameters.Add(new SqlParameter("VirtualPath", @new.VirtualPath));
SQLServerHelper.BatchExecuteNonQuery(@new.GetRepository().GetConnectionString(), command);
((IPersistable)@new).OnSaved();
}
public void Add(Models.MediaContent content)
{
((IPersistable)content).OnSaving();
string sql = string.Format("INSERT INTO {0}(UUID,FolderName,FileName,VirtualPath,UserId) VALUES(@UUID,@FolderName,@FileName,@VirtualPath,@UserId)"
, content.GetRepository().GetMediaContentTableName());
SqlCommand command = new SqlCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlParameter("UUID", content.UUID));
command.Parameters.Add(new SqlParameter("FolderName", content.FolderName));
command.Parameters.Add(new SqlParameter("FileName", content.FileName));
command.Parameters.Add(new SqlParameter("VirtualPath", content.VirtualPath));
command.Parameters.Add(new SqlParameter("@UserId", content.UserId));
SQLServerHelper.BatchExecuteNonQuery(content.GetRepository().GetConnectionString(), command);
((IPersistable)content).OnSaved();
}
public void Update(Models.TextContent @new, Models.TextContent old)
{
@new.StoreFiles();
((IPersistable)@new).OnSaving();
var command = dbCommands.Update(@new);
if (SQLServerTransactionUnit.Current != null)
{
SQLServerTransactionUnit.Current.RegisterCommand(command);
SQLServerTransactionUnit.Current.RegisterPostAction(delegate() { ((IPersistable)@new).OnSaved(); });
}
else
{
SQLServerHelper.BatchExecuteNonQuery(@new.GetRepository(), command);
((IPersistable)@new).OnSaved();
}
}
public void Update(Models.TextContent @new, Models.TextContent old)
{
@new.StoreFiles();
((IPersistable)@new).OnSaving();
var folder = @new.GetFolder().GetActualFolder();
var schema = @new.GetSchema().GetActualSchema();
if (folder != null && folder.StoreInAPI)
{
var proxyBackend = new BackendProxy();
//Add additional data
//
var additionalData = new Dictionary <string, object>
{
{ "ModifiedBy", AuthHelper.GetCurrentUserName() }
};
//Get payload
//
var payload = JsonConvert.SerializeObject(@new, new CustomJsonDictionaryConverter(schema.GetJsonSerializationIgnoreProperties(), additionalData));
//Send data to API
//
proxyBackend.Execute("PUT", string.Format("{0}({1})", schema.Name, @new.Id), payload);
}
else
{
var command = dbCommands.Update(@new);
if (SQLServerTransactionUnit.Current != null)
{
SQLServerTransactionUnit.Current.RegisterCommand(command);
SQLServerTransactionUnit.Current.RegisterPostAction(delegate() { ((IPersistable)@new).OnSaved(); });
}
else
{
SQLServerHelper.BatchExecuteNonQuery(@new.GetRepository(), command);
((IPersistable)@new).OnSaved();
}
}
}
public void Commit()
{
var connectionString = repository.GetConnectionString();
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var command in commands)
{
SQLServerHelper.LogCommand(repository, command);
try
{
SQLServerHelper.ResetParameterNullValue(command);
command.Transaction = trans;
command.Connection = conn;
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new KoobooException(e.Message + "SQL:" + command.CommandText, e);
}
}
trans.Commit();
}
}
//Execute post content events
foreach (var action in postActions)
{
action();
}
Clear();
}
public void ImportCategoryData(Repository repository, IEnumerable <Category> data)
{
SQLServerHelper.ExecuteNonQuery(repository,
data.Select(it => dbCommands.AddCategory(repository, it)).ToArray());
}
public void ImportSchemaData(Schema schema, IEnumerable <IDictionary <string, object> > data)
{
SQLServerHelper.ExecuteNonQuery(schema.Repository,
data.Select(it => dbCommands.Add(GetContent(schema, it))).Where(it => it != null).ToArray());
}
public void ClearCategories(TextContent content)
{
SQLServerHelper.BatchExecuteNonQuery(content.GetRepository(),
dbCommands.ClearCategories(content));
}
public void DeleteCategories(Models.TextContent content, params Models.Category[] categories)
{
SQLServerHelper.BatchExecuteNonQuery(content.GetRepository(),
categories.Select(it => dbCommands.DeleteCategory(content.GetRepository(), it)).ToArray());
}
