public IEnumerable <Category> ExportCategoryData(Repository repository)
{
var connectionString = repository.GetConnectionString();
string sql = string.Format("SELECT UUID,CategoryFolder,CategoryUUID FROM [{0}] ", repository.GetCategoryTableName());
List <Category> list = new List <Category>();
SqlCeConnection connection;
using (var reader = SQLCeHelper.ExecuteReader(connectionString, new SqlCeCommand()
{
CommandText = sql
}, out connection))
{
try
{
while (reader.Read())
{
Category category = new Category();
category.ContentUUID = reader.GetString(0);
category.CategoryFolder = reader.GetString(1);
category.CategoryUUID = reader.GetString(2);
list.Add(category);
}
}
finally
{
reader.Close();
connection.Close();
}
}
return(list);
}
public IEnumerable <IDictionary <string, object> > ExportSchemaData(Schema schema)
{
var connectionString = schema.Repository.GetConnectionString();
string sql = string.Format("SELECT * FROM [{0}] ", schema.GetTableName());
List <IDictionary <string, object> > list = new List <IDictionary <string, object> >();
SqlCeConnection connection;
using (var reader = SQLCeHelper.ExecuteReader(connectionString, new SqlCeCommand()
{
CommandText = sql
}, out connection))
{
try
{
while (reader.Read())
{
list.Add(reader.ToContent <TextContent>(new TextContent()));
}
}
finally
{
reader.Close();
connection.Close();
}
}
return(list);
}
public IEnumerable <Category> QueryCategories(TextContent content)
{
List <Category> list = new List <Category>();
SqlCeConnection connection;
using (var dataReader = SQLCeHelper.ExecuteReader(content.GetRepository().GetConnectionString(),
dbCommands.QueryCategories(content), out connection))
{
try
{
while (dataReader.Read())
{
Category category = new Category()
{
CategoryFolder = dataReader.GetString(dataReader.GetOrdinal("CategoryFolder")),
CategoryUUID = dataReader.GetString(dataReader.GetOrdinal("CategoryUUID")),
ContentUUID = dataReader.GetString(dataReader.GetOrdinal("UUID")),
};
list.Add(category);
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(list);
}
public IEnumerable <IDictionary <string, object> > ExecuteQuery(Repository repository, string queryText, params KeyValuePair <string, object>[] parameters)
{
var connectionString = repository.GetConnectionString();
var command = new System.Data.SqlServerCe.SqlCeCommand(queryText);
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters.Select(it => new System.Data.SqlServerCe.SqlCeParameter()
{
ParameterName = it.Key, Value = it.Value
}).ToArray());
}
List <IDictionary <string, object> > list = new List <IDictionary <string, object> >();
SqlCeConnection connection;
using (var dataReader = SQLCeHelper.ExecuteReader(connectionString, command, out connection))
{
try
{
while (dataReader.Read())
{
TextContent content = new TextContent();
dataReader.ToContent(content);
list.Add(content);
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(list);
}
public void Commit()
{
var connectionString = repository.GetConnectionString();
using (var conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var command in commands)
{
try
{
SQLCeHelper.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();
}
// private static void CreateMediaContentTable(Repository repository)
// {
// string ddl = string.Format(@"CREATE TABLE [{0}] (
// Id int IDENTITY (100,1),
// UUID nvarchar(36) NOT NULL PRIMARY KEY,
// UserKey nvarchar(526),
// UtcCreationDate datetime,
// UtcLastModificationDate datetime,
// Published bit,
// OriginalUUID nvarchar(36),
// FolderName nvarchar(256),
// FileName nvarchar(256),
// VirtualPath nvarchar(256),
// UserId nvarchar(256)
// );", repository.GetMediaContentTableName());
// ExecuteDDL(repository, ddl);
// }
protected static void ExecuteDDL(Repository repository, string ddl)
{
if (!string.IsNullOrEmpty(ddl))
{
SQLCeHelper.ExecuteNonQuery(repository.GetConnectionString(),
ddl.Split(';').Where(it => !string.IsNullOrEmpty(it)).Select(it => new SqlCeCommand(it, null)).ToArray());
}
}
public void Delete(Models.MediaContent content)
{
string sql = string.Format("DELETE FROM {0} WHERE UUID=@UUID", content.GetRepository().GetMediaContentTableName());
SqlCeCommand command = new SqlCeCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlCeParameter("UUID", content.UUID));
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(), command);
}
private static bool CheckTableExists(Schema schema)
{
string sql = string.Format(@"SELECT count(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '{0}' AND TABLE_TYPE='TABLE'", schema.GetTableName());
SqlCeCommand command = new SqlCeCommand()
{
CommandText = sql
};
return((int)SQLCeHelper.ExecuteScalar(schema.Repository.GetConnectionString(), command) != 0);
}
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());
SqlCeCommand command = new SqlCeCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlCeParameter("UUID", @new.UUID));
command.Parameters.Add(new SqlCeParameter("FolderName", @new.FolderName));
command.Parameters.Add(new SqlCeParameter("FileName", @new.FileName));
command.Parameters.Add(new SqlCeParameter("VirtualPath", @new.VirtualPath));
SQLCeHelper.ExecuteNonQuery(@new.GetRepository().GetConnectionString(), command);
((IPersistable)@new).OnSaved();
}
public void Delete(Models.TextContent content)
{
var command = dbCommands.Delete(content);
if (SQLCeTransactionUnit.Current != null)
{
SQLCeTransactionUnit.Current.RegisterCommand(command);
SQLCeTransactionUnit.Current.RegisterPostAction(delegate() { TextContentFileHelper.DeleteFiles(content); });
}
else
{
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(), command);
TextContentFileHelper.DeleteFiles(content);
}
}
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());
SqlCeCommand command = new SqlCeCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlCeParameter("UUID", content.UUID));
command.Parameters.Add(new SqlCeParameter("FolderName", content.FolderName));
command.Parameters.Add(new SqlCeParameter("FileName", content.FileName));
command.Parameters.Add(new SqlCeParameter("VirtualPath", content.VirtualPath));
command.Parameters.Add(new SqlCeParameter("UserId", content.UserId));
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(), command);
((IPersistable)content).OnSaved();
}
public object ExecuteScalar(Repository repository, string queryText, params KeyValuePair <string, object>[] parameters)
{
var connectionString = repository.GetConnectionString();
var command = new System.Data.SqlServerCe.SqlCeCommand(queryText);
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters.Select(it => new System.Data.SqlServerCe.SqlCeParameter()
{
ParameterName = it.Key, Value = it.Value
}).ToArray());
}
return(SQLCeHelper.ExecuteScalar(connectionString, command));
}
public void Update(Models.TextContent @new, Models.TextContent old)
{
@new.StoreFiles();
((IPersistable)@new).OnSaving();
var command = dbCommands.Update(@new);
if (SQLCeTransactionUnit.Current != null)
{
SQLCeTransactionUnit.Current.RegisterCommand(command);
SQLCeTransactionUnit.Current.RegisterPostAction(delegate() { ((IPersistable)@new).OnSaved(); });
}
else
{
SQLCeHelper.ExecuteNonQuery(@new.GetRepository().GetConnectionString(), command);
((IPersistable)@new).OnSaved();
}
}
public void Add(Models.TextContent content)
{
content.StoreFiles();
((IPersistable)content).OnSaving();
var command = dbCommands.Add(content);
if (command != null)
{
if (SQLCeTransactionUnit.Current != null)
{
SQLCeTransactionUnit.Current.RegisterCommand(command);
SQLCeTransactionUnit.Current.RegisterPostAction(delegate() { ((IPersistable)content).OnSaved(); });
}
else
{
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(), command);
((IPersistable)content).OnSaved();
}
}
}
public void DeleteCategories(Models.TextContent content, params Models.Category[] categories)
{
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(),
categories.Select(it => dbCommands.DeleteCategory(content.GetRepository(), it)).ToArray());
}
public void ClearCategories(TextContent content)
{
SQLCeHelper.ExecuteNonQuery(content.GetRepository().GetConnectionString(), dbCommands.ClearCategoreis(content));
}
public void ImportCategoryData(Repository repository, IEnumerable <Category> data)
{
SQLCeHelper.ExecuteNonQuery(repository.GetConnectionString(),
data.Select(it => dbCommands.AddCategory(repository, it)).ToArray());
}
public void ImportSchemaData(Schema schema, IEnumerable <IDictionary <string, object> > data)
{
SQLCeHelper.ExecuteNonQuery(schema.Repository.GetConnectionString(),
data.Select(it => dbCommands.Add(GetContent(schema, it))).Where(it => it != null).ToArray());
}
