public TableModel(QObject parent = null)
: base(parent)
{
using (var context = new SqliteContext())
{
list = context.Products.ToList();
}
}
private void KlientDG_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
RezerwacjeDG.Items.Clear();
using (var context = new SqliteContext())
{
Customer customer = e.AddedItems[0] as Customer;
foreach (var x in context.Reservations.Where(x => x.IdCustomer == customer.Id))
{
RezerwacjeDG.Items.Add(new { x.Room.RoomNumber, x.StartTime, x.StopTime });
}
}
}
public async Task<RowInfo> CreateOrUpdateRowAsync(RowInfo row)
{
using (context = new SqliteContext(BaseConnstr))
{
if (row.Id == 0 || context.Row.Find(row.Id) == null)
await context.AddAsync(row);
else context.Update(row);
await context.SaveChangesAsync();
return row;
}
}
public async Task <LocalIssue> GetIssueAsync(int issueId)
{
using (context = new SqliteContext(BaseConnstr))
{
return(await context.Issue
.Include(i => i.Row)
.Include(i => i.Column)
.Include(i => i.Board)
.Select(i => mapper.Map <LocalIssue>(i))
.FirstAsync(i => i.Id == issueId));
}
}
private void SpratanieDG_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
SprzatanieDG.Items.Clear();
using (var context = new SqliteContext())
{
Worker worker = e.AddedItems[0] as Worker;
foreach (var x in context.Cleanings.Where(x => x.IdWorker == worker.Id))
{
SprzatanieDG.Items.Add(new { x.Room.RoomNumber, x.CleanTime });
}
}
}
/// <summary>
/// 构建动态恢复的表
/// </summary>
/// <param name="sourcedb"></param>
/// <returns></returns>
private string RecoveryTables(string sourcedb)
{
var sqliteObj = new SqliteContext(sourcedb);
var listTables = sqliteObj.Find(new SQLiteString("select tbl_name from sqlite_master where type = 'table'")).
Select(table => DynamicConvert.ToSafeString(table.tbl_name)).Cast <string>().Where(t => t.StartsWith("Chat_")).ToList();
listTables.Add("Friend");
listTables.Add("Friend_Ext");
return(string.Join(",", listTables));
}
static void Main(string[] args)
{
var context = new SqliteContext(new Uri("http://localhost.fiddler:59145/odata/"));
context.Format.UseJson();
// Call some basic Get
var eventDataItems = context.EventData.ToList();
var animalsItems = context.AnimalType.ToList();
var skillLevels = context.SkillLevels.Expand("Levels").GetValue();
var players = context.Player.Expand(c => c.PlayerStats).Where(u => u.PlayerStats.SkillLevel == 2).ToList();
// Create a new entity
var newObjectEventData = new EventData
{
AnimalTypeId = animalsItems.First().Key,
Factor = 55,
FixChange = 55.0,
StringTestId = "_ok_testdatafromodataclient",
AnimalType = animalsItems.First()
};
var secondNewObjectEventData = new EventData
{
AnimalTypeId = 200,
Factor = 44,
FixChange = 44.0,
StringTestId = "_nok_testdatafromodataclient",
};
// Update a new entity
var dataToUpdate = eventDataItems.FirstOrDefault();
dataToUpdate.Factor = -1;
dataToUpdate.FixChange = 97;
context.AddToEventData(newObjectEventData);
context.UpdateObject(dataToUpdate);
context.AddToEventData(secondNewObjectEventData);
context.AddAndUpdateResponsePreference = DataServiceResponsePreference.IncludeContent;
// Add the data to the server
DataServiceResponse response = context.SaveChanges(SaveChangesOptions.BatchWithSingleChangeset);
foreach (OperationResponse individualResponse in response)
{
Console.WriteLine(" status code = {0}", individualResponse.StatusCode);
}
Console.ReadLine();
}
private async Task Cliente_MessageReceived(SocketMessage arg)
{
var mensagem = arg as SocketUserMessage;
if (mensagem == null || mensagem.Author.IsBot)
{
return;
}
var context = new SocketCommandContext(_cliente, mensagem);
int argPost = 0;
//Se for mensagem privada para o bot
if (mensagem.Channel is SocketDMChannel)
{
var array = mensagem.Content.Split(" ");
if (array.Length < 2)
{
await mensagem.Author.SendMessageAsync("Cara alguma coisa errada não ta certa, confere o que você digitou ai.");
}
var response = new GoobTeams().Login(array[0], array[1]);
if (response != null && response.Sucesso && !string.IsNullOrWhiteSpace(response.Token))
{
using (var db = new SqliteContext())
{
db.Usuarios.Add(new Usuario()
{
Login = array[0], Senha = array[1], IdDiscord = mensagem.Author.Id.ToString()
});
db.SaveChanges();
}
await mensagem.Author.SendMessageAsync("Boaaa, consegui autenticar você, volta no chat lá e manda o comando pra modificar o humor, valeu!");
}
else
{
await mensagem.Author.SendMessageAsync("Não consegui fazer o login com a info que você me passou, tem certeza que passou os dados certos?");
}
}
else if (mensagem.HasStringPrefix("?", ref argPost))
{
var result = await _commands.ExecuteAsync(context, argPost, _services);
if (!result.IsSuccess)
{
Console.WriteLine(result.ErrorReason);
}
}
}
/// <summary>
/// 查询某个数据库表的创建sql语句，用于判断表中是否包含某个字段
/// </summary>
/// <param name="context">数据库操作对象</param>
/// <param name="tableName">数据表名</param>
/// <returns>返回创建表的sql语句</returns>
private string GetCreateTableSql(SqliteContext context, string tableName)
{
try
{
string selectSql = string.Format(@"select sql from sqlite_master where tbl_name='{0}' and type='table'", tableName);
var data = context.Find(new SQLiteString(selectSql));
return(DynamicConvert.ToSafeString(data.FirstOrDefault().sql));
}
catch
{
return(string.Empty);
}
}
/// <summary>
/// 检查表是否存在
/// </summary>
/// <param name="file"></param>
/// <param name="tablename"></param>
/// <returns></returns>
public bool TableIsExist(string file, string tablename)
{
try
{
SqliteContext context = new SqliteContext(file);
return(context.Exist(tablename));
}
catch (Exception ex)
{
Console.WriteLine(string.Format("SQL查询失败", file, ex.Message));
throw;
}
}
public async Task MigrateLite(CommandContext ctx)
{
Console.WriteLine("*** migrating... ***");
await using SqliteContext lite = new SqliteContext();
if (lite.Database.GetPendingMigrationsAsync().Result.Any())
{
await lite.Database.MigrateAsync();
}
await ctx.Channel.SendMessageAsync($"Sqlite Migration complete");
}
public static void Add(Room room)
{
using (var conn = new SqliteContext(@"testDb"))
{
if (conn.Rooms.Any(p => p.Id == room.Id))
throw new Exception("Pokój o tym ID już istnieje");
if (conn.Rooms.Any(p => p.RoomNumber == room.RoomNumber))
throw new Exception("Pokój o tym numerze już istnieje");
conn.Rooms.Add(room);
conn.SaveChanges();
}
}
private async void CreateCustomer_Click(object sender, RoutedEventArgs e)
{
//TryCatch Get custoemr
await SqliteContext.CreateCustomerAsync(new Customer
{
FirstName = TbFirstName.Text,
LastName = TbLastName.Text,
Email = TbEmail.Text,
});
await LoadCustomersAsync();
}
private async void CreateComment_Click(object sender, RoutedEventArgs e)
{
await SqliteContext.CreateCommentAsync(
new Comment
{
Description = CommentBox.ToString(),
IssueId = await SqliteContext.GetIssuesAsync(cmbIssueTitle.SelectedItem.ToString())
}
);
await LoadIssueByTitleAsync();
}
private async void CreateIssue_Click(object sender, RoutedEventArgs e)
{
await SqliteContext.CreateIssueAsync(
new Issue
{
Title = "CAS-" + Guid.NewGuid().ToString(),
Description = "Detta är ett ärende",
CustomerId = await SqliteContext.GetCustomerIdByName(cmbCustomers.SelectedItem.ToString())
}
);
await LoadIssuesAsync();
}
private int CalculateDishes()
{
using (var context = new SqliteContext())
{
var today = DateTime.Today;
var reservationCount = context.Reservations
.Count(x =>
x.StartTime < today &&
today < x.StopTime);
return(reservationCount);
}
}
public static void Add(Worker worker)
{
using (var conn = new SqliteContext(@"testDb"))
{
if (conn.Workers.Any(w => w.Id == worker.Id))
{
throw new Exception("Pracownik o tym ID już istnieje");
}
conn.Workers.Add(worker);
conn.SaveChanges();
}
}
/// <summary>
/// 写数据
/// </summary>
/// <param name="queueGroup"></param>
private void WriteData(QueueGroup queueGroup)
{
try
{
foreach (var key in queueGroup.QueueDic.Keys)
{//TODO:根据类型获取表名
if (!_tableList.Contains(Class2DbHelper.Instance[key].ClassAttribute.TableName))
{
//创建表
CreateTable(key);
_tableList.Add(Class2DbHelper.Instance[key].ClassAttribute.TableName);
}
//插入数据
int queueCurrentCount = queueGroup.QueueDic[key].Count;
if (queueCurrentCount > 0)
{
SqliteContext sqliteContext = new SqliteContext(new DBContextBridge
{
ConnectionString = _connStr,
Provider = "sqlite",
});
int transid = 0;
for (int i = 0; i < queueCurrentCount; i++)
{
if (transid == 0)
{
transid = sqliteContext.RegisteTrans();
}
var entity = queueGroup.QueueDic[key].Dequeue() as SqliteEntity;
if (entity != null)
{
//string opid = entity.GetValue(entity, "ContentOpid").ToString();
//string domainId = entity.GetValue(entity, "DomainId").ToString();
//string createTime = entity.GetValue(entity, "CreateTime").ToString();
//Logger.WriteError("输出GetValue", string.Format("ContentOpid:{0},DomainId:{1},CreateTime:{2}", opid, domainId, createTime));
sqliteContext.ExecuteSQL(transid, Class2DbHelper.Instance[key].GetInsertSqliteSql(entity));
if ((i > 0 && i % 10000 == 0) || i == queueCurrentCount - 1)
{
sqliteContext.TransCommit(transid);
transid = 0;
}
}
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException("写sqlite数据时出现异常", ex);
}
}
private LIST_FILE_RAW_TYPE_INFO CreatRawLink()
{
if (_listRawTypeInfos == null)
{
_listRawTypeInfos = new List <LIST_FILE_RAW_TYPE_INFO>();
try
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from FileClass where 1 <> 1 ");
RAW_TYPES.ForEach(s => sb.AppendFormat(" or FileType = {0} ", s));
SqliteContext sql = new SqliteContext(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "FileRawTypeLib.db"));
DataTable dt = sql.FindDataTable(sb.ToString());
IntPtr lastPtr = IntPtr.Zero;
foreach (DataRow dr in dt.Rows)
{
FILE_RAW_TYPE_INFO raw = new FILE_RAW_TYPE_INFO();
raw.FileType = dr["FileExName"].ToSafeString().Replace(";", "_"); //dr["FileTypeName"].ToSafeString();
raw.Size = dr["FileDefaultSize"].ToSafeString().ToSafeInt();
raw.ExtensionName = dr["FileExName"].ToSafeString();
raw.HeadMagic = dr["FileHeadFlag"].ToSafeString();
raw.HeadOffsetBytes = (ushort)dr["FileHeadBeginPos"].ToSafeString().ToSafeInt();
raw.FootMagic = dr["FileEndFlag"].ToSafeString();
raw.FootOffsetBytes = dr["FileEndPOS"].ToSafeString().ToSafeInt();
raw.HeadLen = dr["FileHeadLength"].ToSafeString().ToSafeInt();
raw.FootLen = 0;
LIST_FILE_RAW_TYPE_INFO rawlist = new LIST_FILE_RAW_TYPE_INFO();
rawlist.Check = 1; //0 - 不检查, 1 - 检查
rawlist.Num = 0;
rawlist.rt = raw;
rawlist.next = lastPtr;
int size = Marshal.SizeOf(rawlist);
IntPtr rawptr = Marshal.AllocHGlobal(size);
Marshal.StructureToPtr(rawlist, rawptr, true);
_listRawTypeInfos.Add(rawlist);
lastPtr = rawptr;
}
}
catch (Exception ex)
{
LoggerManagerSingle.Instance.Error("获取Raw文件类型失败！", ex);
}
}
return(_listRawTypeInfos.Count > 0 ? _listRawTypeInfos[_listRawTypeInfos.Count - 1] : new LIST_FILE_RAW_TYPE_INFO());
}
public void Edit(Reservation rez)
{
using (var conn = new SqliteContext())
{
if (rez.StartTime > rez.StopTime)
{
throw new Exception("Data zakończenia pobytu nie może być mniejsza niż rozpoczęcie pobytu.");
}
Reservation rezToChange = conn.Reservations.Where(p => p.Id == rez.Id).FirstOrDefault();
Reservation rezBeforeChanges = new Reservation
{
StartTime = rezToChange.StartTime,
StopTime = rezToChange.StopTime
};
if (rezToChange != null)
{
rezToChange.IdCustomer = rez.IdCustomer;
rezToChange.IdRoom = rez.IdRoom;
rezToChange.StartTime = rez.StartTime;
rezToChange.StopTime = rez.StopTime;
IList <Reservation> reservations = conn.Reservations.Where(p => p.Id != rez.Id && p.IdRoom == rez.IdRoom).ToList();
if (reservations.Any(p => p.StartTime.Date == rez.StartTime.Date || p.StopTime.Date == rez.StopTime.Date ||
(p.StartTime.Date > rez.StartTime.Date && p.StartTime.Date < rez.StopTime.Date) ||
(p.StartTime.Date < rez.StartTime.Date && p.StopTime.Date > rez.StartTime.Date) ||
(p.StartTime.Date > rez.StartTime.Date && p.StopTime.Date < rez.StopTime.Date) ||
(p.StartTime.Date < rez.StartTime.Date && p.StopTime.Date > rez.StopTime.Date)))
{
throw new Exception("W tym terminie pokój jest już zarezerwowany, nie można zmienić terminu rezerwacji");
}
rezToChange.StartTime = rezToChange.StartTime.AddHours(15);
rezToChange.StopTime = rezToChange.StopTime.AddHours(10);
Customer cus = conn.Customers.FirstOrDefault(p => p.Id == rez.IdCustomer);
Room room = conn.Rooms.FirstOrDefault(p => p.Id == rez.IdRoom);
rez.Customer = cus ?? throw new Exception("Nie ma klienta o ID: " + rez.IdCustomer);
rez.Room = room ?? throw new Exception("Nie ma pokoju o ID: " + rez.IdRoom);
conn.SaveChanges();
EditClining(rezBeforeChanges, rezToChange, conn);
}
else
{
throw new Exception("Nie znaleziono rezerwacji do zmodyfikowania");
}
}
}
public static void ResetData()
{
using var context = new SqliteContext();
if (context.InterestRates.Any())
{
var data = context.InterestRates.ToList();
foreach (var rate in data)
{
context.InterestRates.Remove(rate);
}
context.SaveChanges();
}
}
public MetaData GetMetaData(int id)
{
// random song selection
using var sql = new SqliteContext();
if (id == 0)
{
// random song selection
int total = sql.MetaDatas.Count();
Random random = new Random();
int offset = random.Next(0, total);
return(sql.MetaDatas.FirstOrDefault(q => q.Id == offset));
}
return(sql.MetaDatas.FirstOrDefault(q => q.Id == id));
}
// Constructor
public TestSqliteProvider()
{
// Created Test connection for InMemory Sqlite.
var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
// Adding connection to the builder.
var builder = new DbContextOptionsBuilder <SqliteContext>();
builder.UseSqlite(connection);
_context = new SqliteContext(builder.Options);
// Need to ensure the database is created and the schema is generated.
_context.Database.EnsureCreated();
}
public static void ResetData()
{
using var context = new SqliteContext();
if (context.CreditCards.Any())
{
var data = context.CreditCards.ToList();
foreach (var product in data)
{
context.CreditCards.Remove(product);
}
context.SaveChanges();
}
}
/// <summary>
/// 通过父节点获取章节
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
public List <Node> getNodeByParentNode(Node node)
{
using (SqliteContext context = new SqliteContext())
{
try
{
return(context.Node.Where(n => n.lawId == node.Id && n.nodeLevel == node.nodeLevel + 1).OrderBy(n => n.nodeOrder).ThenBy(n => n.nodeOrder).ToList());
}
catch (Exception)
{
return(new List <Node>());
}
}
}
public static void Delete(Room room)
{
using (var conn = new SqliteContext(@"testDb"))
{
Room toDelete = conn.Rooms.FirstOrDefault(p => p.Id == room.Id);
if (toDelete != null)
{
conn.Rooms.Remove(toDelete);
conn.SaveChanges();
}
else
throw new Exception("Nie znaleziono pokoju do usunięcia");
}
}
private static void Seed(SqliteContext context)
{
if (context.RestaurantLists.Any())
{
return;
}
List <RestaurantList> restaurantNames = new List <RestaurantList>()
{
new RestaurantList()
{
RestaurantName = "德克斯", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "鴨肉飯", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "雞肉飯", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "甜不辣", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "甘泉魚麵", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "牛樂麵", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "早餐店", Area = Area.WuxingStreat
},
new RestaurantList()
{
RestaurantName = "八方雲集", Area = Area.WuxingStreat
}
};
foreach (var item in restaurantNames)
{
context.RestaurantLists.Add(item);
}
context.SaveChanges();
}
public static async Task PopulateByStatusAsync(string status, int maxItemsCount)
{
_issues.Clear();
List <Issue> list = await SqliteContext.GetIssuesByStatusAsync(status, maxItemsCount);
foreach (Issue issue in list)
{
issue.Comments = await SqliteContext.GetCommentsByIssueIdAsync(issue.Id, maxItemsCount);
issue.Customer = await SqliteContext.GetCustomerByIdAsync(issue.CustomerId);
_issues.Add(issue);
}
}
public async Task<ColumnInfo> CreateOrUpdateColumnAsync(ColumnInfo column)
{
using (context = new SqliteContext(BaseConnstr))
{
if (column.Id == 0 || context.Column.AsNoTracking()
.FirstOrDefault(c => c.Id == column.Id) == null)
await context.AddAsync(column);
else context.Update(column);
await context.SaveChangesAsync();
return column;
}
}
/// <summary>
/// 通过章节id获取章节
/// </summary>
/// <param name="nodeId"></param>
/// <returns></returns>
public Node getNodeById(string nodeId)
{
using (SqliteContext context = new SqliteContext())
{
try
{
return(context.Node.FirstOrDefault(n => n.Id == nodeId));
}
catch (Exception)
{
return(new Node());
}
}
}
private async void createComment_Click(object sender, RoutedEventArgs e)
{
var issueId = (sender as Button).Tag;
await SqliteContext.CreateCommentAsync(
new Comment
{
IssueId = (long)issueId,
Description = tbxComment.Text
}
);
await LoadIssuesAsync();
}
public StationRepository(SqliteContext context)
{
_context = context;
}
public FavoriteTrainRepository(SqliteContext context)
{
_context = context;
}
