public PurchaseOrderModel NapraviNarudzbenicu(PurchaseOrderModel narudzbenica) { // TODO : preimenuj imena promenljivih u upitu za bazu i promeni ih i u bazi posto si modele pisao na engleskom using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { //tring query = @"Insert into Racun (Datum, VisaTarifa, NizaTarifa, Ukupno, Info, Cena, CenaSaRacuna ) values (@Datum , @VisaTarifa, @NizaTarifa , @Ukupno , @Info, @Cena, @CenaSaRacuna);select max(ID) from Racun"; string query = @"Insert into [Narudzbenice] (Id, BrojNarudzbenice, DatumNarudzbenice, DatumIstekaRokaNarudzbenice, OkvirniSporazum, Ogranak, NazivTrafostanice, DatumFakture, BrojFakture, IznosBezPDV, DatumPrijemEPS, BrojIzvestaja, Napomena, IzvestajPoslat, NarudzbenicaPDF, ZapsinikOIzvrsenjuUslugaPDF, FakturaPDF, PrimopredajniObrazacPDF, Korisnik, Datum ) values (null,@BrojNarudzbenice,@DatumNarudzbenice,@DatumIstekaRokaNarudzbenice,@OkvirniSporazum,@Ogranak,@NazivTrafostanice,@DatumFakture,@BrojFakture,@IznosBezPDV,@DatumPrijemEPS,@BrojIzvestaja,@Napomena,@IzvestajPoslat,@NarudzbenicaPDF,@ZapsinikOIzvrsenjuUslugaPDF,@FakturaPDF,@PrimopredajniObrazacPDF,@Korisnik,@Datum);select max(Id) from Narudzbenice"; //int id = connection.Query<int>(query, narudzbenica, commandType: CommandType.Text).Single(); //const string sql = @"INSERT INTO [Course] (Name, StudentLimit) VALUES (@Name, @StudentLimit)"; try { //connection.Execute(query, narudzbenica, commandType: CommandType.Text); int id = connection.Query <int>(query, narudzbenica).Single(); //long id = connection.Query<long>(query, racun).First(); narudzbenica.ID = id; return(narudzbenica); } catch (Exception ex) { MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error); // TODO : Pocisti ovo return(null); } //long id = connection.Query<long>(query, racun).First(); //narudzbenica.ID = id; } }
public List <PurchaseOrderModel> GetAllForThisYear(string y) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = "SELECT * FROM Narudzbenice WHERE strftime('%Y', Datum) = '" + DateTime.Now.Year.ToString() + "'"; return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query)); } }
public List <PurchaseOrderModel> GetAllWhereIzvestajPoslat(int poslat, string y) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = "SELECT * FROM Narudzbenice where IzvestajPoslat = " + poslat + " AND strftime('%Y', Datum) = '" + y + "';"; return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query)); } }
public List <PurchaseOrderModel> GetAllOlderThen(int days) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = "SELECT * FROM Narudzbenice WHERE(julianday('now') - julianday(DatumNarudzbenice)) <= " + days + "; "; return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query)); } }
public static void SelectData(UserId id) { using (var connection = new System.Data.SQLite.SQLiteConnection(cnStr)) { connection.Open(); var data = connection.Query<Poco.Users>("SELECT * FROM Users WHERE Id = @Id", new { Id = id }).FirstOrDefault(); System.Diagnostics.Debug.Assert(id.Equals(data.Id)); } }
public List <TaskItem> LoadTasks(bool activeTasksOnly = true) { string sql = "SELECT * FROM Task LEFT JOIN " + "(SELECT DateTracked, TaskId, SUM(SecondsTracked) AS SecondsTracked FROM TaskHistory " + "GROUP BY DateTracked, TaskId) AS TH " + "ON Task.TaskId = TH.TaskId"; if (activeTasksOnly) { sql += " WHERE Task.DeletedDateTime IS NULL"; } //List<TaskItem> taskItems = _DBConnection.Query<TaskItem, WBS, TaskItem>( // sql, // (taskItem, wbs) => // { // taskItem.WBSCode = wbs; // return taskItem; // }, // splitOn: "WBSId").AsList(); return(_DBConnection.Query <TaskItem>(sql).AsList()); }
private static IEnumerable <Pessoa> CarregarPessoasDoBanco() { IEnumerable <Pessoa> pessoas = null; using (var conn = new System.Data.SQLite.SQLiteConnection("Data Source=db.db;Version=3;")) { conn.Open(); if (conn.State == System.Data.ConnectionState.Open) { pessoas = conn.Query <Pessoa>("SELECT * FROM Pessoa"); } } return(pessoas); }
public List <PurchaseOrderModel> GetAll() { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = "select * from Narudzbenice"; try { return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query)); } catch (Exception ex) { MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } } }
public List <PurchaseOrderModel> GetAllFromLastMonth(string mesec) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { //string query = "select * from Narudzbenice where Datum like '" + mesec + "-%-% %'"; //string query = "SELECT * FROM Narudzbenice WHERE strftime('%m', Datum) = '" + mesec + "'"; string query = "SELECT * FROM Narudzbenice WHERE strftime('%Y %m', Datum) = '" + DateTime.Now.Year.ToString() + " " + mesec + "'"; try { return((List <PurchaseOrderModel>)connection.Query <PurchaseOrderModel>(query)); } catch (Exception ex) { MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } } }
/// <summary> /// Checking if database is empty /// </summary> /// <returns></returns> public bool DatabaseEmpty() { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { var number = 0; //string query1 = "select exists (select 1 from " + tabela + ");"; string query = "select exists (select 1 from Korisnici);"; number = connection.Query <int>(query).Single(); if (number == 0) { return(true); } else { return(false); } } }
public List <WindowItem> GetAllWindowItemsInDateRange(long?startDate, long?endDate) { List <WindowItem> windowItems = new List <WindowItem>(); string sql = @"SELECT * FROM WindowHistory LEFT JOIN Window ON Window.WindowId = WindowHistory.WindowId LEFT JOIN Application ON Window.ApplicationId = Application.ApplicationId LEFT JOIN File ON Window.FileId = File.FileId"; string whereClause = null; if (startDate != null) { if (whereClause == null) { whereClause = " WHERE"; } whereClause += " LoggedDateTime >= @StartDate"; } if (endDate != null) { if (whereClause == null) { whereClause = " WHERE"; } else { whereClause = " AND"; } whereClause += " LoggedDateTime <= @EndDate"; } if (whereClause != null) { sql += whereClause; } Dictionary <int, WindowItem> windowItemDictionary = new Dictionary <int, WindowItem>(); windowItems = _DBConnection.Query <WindowHistoryItem, WindowItem, ApplicationItem, FileItem, WindowItem>( sql, (windowHistoryItem, windowItem, applicationItem, fileItem) => { WindowItem windowItemEntry; if (!windowItemDictionary.TryGetValue(windowItem.WindowId, out windowItemEntry)) { windowItemEntry = windowItem; windowItemEntry.Application = applicationItem; windowItemEntry.File = fileItem; windowItemEntry.TimeSpent = new TimeSpan(); windowItemDictionary.Add(windowItemEntry.WindowId, windowItemEntry); } windowItemEntry.TimeSpent += TimeSpan.FromSeconds(1); return(windowItem); }, splitOn: "WindowId,ApplicationId,FileId", param: new { StartDate = startDate, EndDate = endDate }) .AsList(); return(windowItems); }
static void Main(string[] args) { var services = ConfigureServices(); var repository = services.GetService <IDML>(); while (true) { Random random = new Random(); Stopwatch stopwatch = new Stopwatch(); Console.WriteLine("1.add;2.query,3.get,4.remove"); var key = Console.ReadLine(); stopwatch.Start(); switch (key) { case "add": case "1": List <Students> students = new List <Students>(); for (int i = 0; i < 100000; i++) { students.Add(new Students { StudentName = Guid.NewGuid().ToString(), CreateDate = DateTime.Now }); } repository.Add(students.ToArray()); Console.WriteLine(repository.SaveChanges()); break; case "2": case "query": //var child= services.GetService<Query<Students>>().Select(t =>new { t.Id }); var rStudent = services.GetService <Query <Students> >().Where(t => t.Id >= 0) //.Select(t=>new {t.StudentName,t.CreateDate,t.Id,t.StudentEnum }) .ToArray(); Console.WriteLine(rStudent.Count()); break; case "3": case "dapper": System.Data.IDbConnection db = new System.Data.SQLite.SQLiteConnection($"Data Source={AppDomain.CurrentDomain.BaseDirectory}TestData.db"); var rDapper = db.Query <Classes>("SELECT * FROM CLASSES WHERE ID>=0"); break; //case "4": // var rStudent = services.GetService<Query<Students>>().Select(t=>) // break //case "3": //case "get": // var r = services.GetService<Query<Students>>().Get("a"); // Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(r)); // break; //case "4": //case "remove": // //repository.Remove(t => t.Id >= 10000); // repository.Remove(new Students() { Id = 10000 }); // Console.WriteLine(repository.SaveChanges()); // break; default: break; } stopwatch.Stop(); if (stopwatch.Elapsed.TotalSeconds > 0) { Console.WriteLine($"共用时{stopwatch.Elapsed.TotalSeconds}秒。"); } } }