Пример #1
0
        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;
            }
        }
Пример #2
0
 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));
     }
 }
Пример #3
0
 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));
     }
 }
Пример #4
0
 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));
     }
 }
Пример #5
0
        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());
        }
Пример #7
0
        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);
        }
Пример #8
0
 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);
         }
     }
 }
Пример #9
0
 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);
         }
     }
 }
Пример #10
0
 /// <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);
         }
     }
 }
Пример #11
0
        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);
        }
Пример #12
0
        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}秒。");
                }
            }
        }