//public List<PurchaseOrederPositionModel> NarudbeniceSaPozicijam() //{ // // TODO: ovde sis tao 01.03.2020 u 14:34 // PurchaseOrederPositionModel p = new PurchaseOrederPositionModel(); // List<PurchaseOrederPositionModel> modeli = new List<PurchaseOrederPositionModel>(); // using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) // { // //string query = "SELECT * FROM Narudzbenice INNER JOIN Pozicije on Pozicije.Narudzbenica_ID = Narudzbenice.ID"; // string query1 = "SELECT * FROM Narudzbenice; SELECT * FROM Pozicije"; // //return connection.QuerySingle<NarudzbenicaModel>(query, new { Id = id }); // List<PurchaseOrderModel> nardudzbenice = new List<PurchaseOrderModel>(); // List<PositionModel> pozicije = new List<PositionModel>(); // List<PositionModel> jednaPozicija = new List<PositionModel>(); // var multi = connection.QueryMultiple(query1, null); // nardudzbenice = multi.Read<PurchaseOrderModel>().ToList(); // pozicije = multi.Read<PositionModel>().ToList(); // foreach (PurchaseOrderModel narudzbenica in nardudzbenice) // { // foreach (PositionModel po in pozicije) // { // if (narudzbenica.ID == po.PozicijeId) // { // jednaPozicija.Add(po); // } // } // modeli.Add(new NarudzbenicaPozicijaModel(narudzbenica.ID, // narudzbenica.BrojNarudzbenice, // narudzbenica.DatumNarudzbenice, // narudzbenica.DatumIstekaRokaNarudzbenice, // narudzbenica.OkvirniSporazum, // narudzbenica.Ogranak, // narudzbenica.NazivTrafostanice, // narudzbenica.DatumFakture, // narudzbenica.BrojFakture, // narudzbenica.IznosBezPDV, // narudzbenica.DatumPrijemEPS, // narudzbenica.BrojIzvestaja, // narudzbenica.Napomena, // narudzbenica.IzvestajPoslat, // narudzbenica.NarudzbenicaPDF, // narudzbenica.ZapsinikOIzvrsenjuUslugaPDF, // narudzbenica.FakturaPDF, // narudzbenica.PrimopredajniObrazacPDF, // narudzbenica.Korisnik, narudzbenica.Datum, jednaPozicija)); // //p.Id = narudzbenica.ID; // //p.BrojNarudzbenice = narudzbenica.BrojNarudzbenice; // //p.DatumNarudzbenice = narudzbenica.DatumNarudzbenice; // //p.DatumIstekaRokaNarudzbenice = narudzbenica.DatumIstekaRokaNarudzbenice; // //p.OkvirniSporazum = narudzbenica.OkvirniSporazum; // //p.Ogranak = narudzbenica.Ogranak; // //p.NazivTrafostanice = narudzbenica.NazivTrafostanice; // //p.DatumFakture = narudzbenica.DatumFakture; // //p.BrojFakture = narudzbenica.BrojFakture; // //p.IznosBezPDV = narudzbenica.IznosBezPDV; // //p.DatumPrijemEPS = narudzbenica.DatumPrijemEPS; // //p.BrojIzvestaja = narudzbenica.BrojIzvestaja; // //p.Napomena = narudzbenica.Napomena; // //p.IzvestajPoslat = narudzbenica.IzvestajPoslat; // //p.NarudzbenicaPDF = narudzbenica.NarudzbenicaPDF; // //p.ZapsinikOIzvrsenjuUslugaPDF = narudzbenica.ZapsinikOIzvrsenjuUslugaPDF; // //p.FakturaPDF = narudzbenica.FakturaPDF; // //p.PrimopredajniObrazacPDF = narudzbenica.PrimopredajniObrazacPDF; // //p.Korisnik = narudzbenica.Korisnik; // //p.Datum = narudzbenica.Datum; // } // //var invoice = multi.Read<Invoice>().First(); // //var invoiceItems = multi.Read<InvoiceItem>().ToList(); // //try // //{ // // //return (List<NarudzbenicaPozicijaModel>)connection.Query<NarudzbenicaPozicijaModel>(query); // // //var inerJoin = connection.Query<NarudzbenicaPozicijaModel, PozicijaModel, NarudzbenicaPozicijaModel>(query,(narudzbenicapozicijamodel, pozicijamodel) => pozicijamodel) // //} // //catch (Exception ex) // //{ // // MessageBox.Show("Doslo je do greske, detaljne informaciju su: " + ex, "Greska sa bazom!", MessageBoxButtons.OK, MessageBoxIcon.Error); // // return null; // //} // // Step 1: Use Dapper to return the flat result as a Dynamic. // //dynamic test = connection.Query<dynamic>(query); // //NarudzbenicaPozicijaModel pozicija = new NarudzbenicaPozicijaModel(); // //pozicija.Id = test.Id;// d.GetType().GetProperty("value2").GetValue(d, null); // //pozicija.Id = test.GetType().GetProperty("Id").GetValue(db, null); // //pozicija.BrojNarudzbenice = test.BrojNarudzbenice; // // Step 2: Use Slapper.Automapper for mapping to the POCO Entities. // // - IMPORTANT: Let Slapper.Automapper know how to do the mapping; // // let it know the primary key for each POCO. // // - Must also use underscore notation ("_") to name parameters; // // see Slapper.Automapper docs. // //Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(NarudzbenicaPozicijaModel), new List<string> { "Id" }); // //Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(PozicijaModel), new List<string> { "PozicijeId" }); // //p = (Slapper.AutoMapper.MapDynamic<NarudzbenicaPozicijaModel>(test) as IEnumerable<NarudzbenicaPozicijaModel>).ToList(); // //var testContact = (Slapper.AutoMapper.MapDynamic<NarudzbenicaPozicijaModel>(test) as IEnumerable<NarudzbenicaPozicijaModel>).ToList(); // return modeli; // } //} public void RemoveUser(int id) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = "delete from Korisnici where ID = @ID"; int i = connection.Execute(query, new { id = id }); } }
public PositionModel NapraviPoziciju(PositionModel pozicija) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = @"Insert into [Pozicije](PozicijeId,Narudzbenica_ID, ImePozicije) values (null, @Narudzbenica_ID, @ImePozicije);"; connection.Execute(query, pozicija, commandType: CommandType.Text); return(pozicija); } }
protected void Application_Start(object sender, EventArgs e) { InitProfilerSettings(); var dbFile = HttpContext.Current.Server.MapPath("~/App_Data/TestMiniProfiler.sqlite"); if (System.IO.File.Exists(dbFile)) { File.Delete(dbFile); } using (var cnn = new System.Data.SQLite.SQLiteConnection(WcfCommon.ConnectionString)) { cnn.Open(); cnn.Execute("create table RouteHits(RouteName,HitCount)"); // we need some tiny mods to allow sqlite support foreach (var sql in SqliteMiniProfilerStorage.TableCreationSQL) { cnn.Execute(sql); } } }
public PurchaseOrderModel Update(PurchaseOrderModel narudzbenica) { using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString())) { string query = @"UPDATE Narudzbenice SET BrojNarudzbenice = @BrojNarudzbenice,DatumNarudzbenice = @DatumNarudzbenice,DatumIstekaRokaNarudzbenice = @DatumIstekaRokaNarudzbenice,OkvirniSporazum = @OkvirniSporazum,Ogranak = @Ogranak,NazivTrafostanice = @NazivTrafostanice,DatumFakture = @DatumFakture,BrojFakture = @BrojFakture,IznosBezPDV = @IznosBezPDV,DatumPrijemEPS = @DatumPrijemEPS,BrojIzvestaja = @BrojIzvestaja,Napomena = @Napomena,IzvestajPoslat = @IzvestajPoslat,NarudzbenicaPDF = @NarudzbenicaPDF,ZapsinikOIzvrsenjuUslugaPDF = @ZapsinikOIzvrsenjuUslugaPDF,FakturaPDF = @FakturaPDF,PrimopredajniObrazacPDF = @PrimopredajniObrazacPDF,Korisnik = @Korisnik WHERE Id = @ID;"; //cnn.Execute("update Table set val = @val where Id = @id", new { val, id = 1 }); //string query = "update Racun set @Datum = '" + DateTimeSQLite(racun.Datum) + "', @VisaTarifa = '" + racun.VisaTarifa + "', @NizaTarifa = '" + racun.NizaTarifa + "', @Ukupno = '" + racun.Ukupno + "', @Info = '" + racun.Info + "', @Cena = '" + racun.Cena + "', @CenaSaRacuna = '" + racun.CenaSaRacuna + "' where ID = @ID;"; var i = connection.Execute(query, narudzbenica); narudzbenica.ID = i; return(narudzbenica); } }
protected void Application_Start(object sender, EventArgs e) { InitProfilerSettings(); var dbFile = HttpContext.Current.Server.MapPath("~/App_Data/TestMiniProfiler.sqlite"); if (System.IO.File.Exists(dbFile)) { File.Delete(dbFile); } using (var cnn = new System.Data.SQLite.SQLiteConnection(WcfCommon.ConnectionString)) { cnn.Open(); cnn.Execute("create table RouteHits(RouteName,HitCount)"); // we need some tiny mods to allow sqlite support foreach (var sql in SqliteMiniProfilerStorage.TableCreationSQL) { cnn.Execute(sql); } } }
public WindowItem GetOrCreateWindowItemFromDB(int?appId, int?fileId, string windowText) { Dictionary <string, object> args = new Dictionary <string, object>(); args.Add("ApplicationId", appId); args.Add("FileId", fileId); args.Add("WindowText", windowText); string selectSql = $"SELECT * FROM Window { GenerateWhereClauseWithNull(args) }"; WindowItem windowItem = _DBConnection.QueryFirstOrDefault <WindowItem>(selectSql, new { ApplicationId = appId, FileId = fileId, WindowText = windowText }); if (windowItem == null) { string insertSql = $"INSERT INTO Window { GenerateInsertClauseWithNull(args) }"; _DBConnection.Execute(insertSql, new { ApplicationId = appId, FileId = fileId, WindowText = windowText }); windowItem = _DBConnection.QueryFirst <WindowItem>(selectSql, new { ApplicationId = appId, FileId = fileId, WindowText = windowText }); } return(windowItem); }
private static void CadastrarPessoaNoBanco(Pessoa pessoa) { using (var conn = new System.Data.SQLite.SQLiteConnection("Data Source=db.db;Version=3;")) { conn.Open(); if (conn.State == System.Data.ConnectionState.Open) { conn.Execute( "INSERT INTO Pessoa (Nome, Sobrenome) VALUES (@Nome, @Sobrenome)", new { Nome = pessoa.Nome, Sobrenome = pessoa.Sobrenome }); } } }
private const string db = "NarudzbenicaDatabase"; // ime baze public UsersModel CreateUser(UsersModel u) { 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 [Korisnici](ID, KorisnickoIme, KorisnickaLozinka, Privilegija, Datum) values (null, @KorisnickoIme, @KorisnickaLozinka, @Privilegija, @Datum);"; //int id = connection.Query<int>(query, narudzbenica, commandType: CommandType.Text).Single(); //const string sql = @"INSERT INTO [Course] (Name, StudentLimit) VALUES (@Name, @StudentLimit)"; connection.Execute(query, u, commandType: CommandType.Text); //long id = connection.Query<long>(query, racun).First(); //narudzbenica.ID = id; return(u); } }
private static UserId InsertData() { var id = new UserId("Abc12c"); using (var connection = new System.Data.SQLite.SQLiteConnection(cnStr)) { connection.Open(); connection.Execute("INSERT INTO Users (Id, Age, Name) VALUES (@Id, @Age, @Name)", new { Age = 31, Id = id, Name = "Peter Gill" }); } return id; }
public TaskItem InsertNewTask(string description, long createdDateTime) { string sql = "INSERT INTO Task (Description, CreatedDateTime) VALUES (@Description, @CreatedDateTime)"; _DBConnection.Execute(sql, new { Description = description, CreatedDateTime = createdDateTime }); 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 " + "WHERE Task.Description = @Description AND CreatedDateTime = @CreatedDateTime"; return(_DBConnection.QueryFirst <TaskItem>(sql, new { Description = description, CreatedDateTime = createdDateTime })); }
/// <summary> /// Used for testing purposes - destroys and recreates the SQLITE file with needed tables. /// </summary> /// <param name="extraTablesToCreate">The Extra Tables To Create.</param> public void RecreateDatabase(params string[] extraTablesToCreate) { var path = ConnectionString.Replace("Data Source = ", string.Empty); // hacky if (File.Exists(path)) { File.Delete(path); } using (var cnn = new System.Data.SQLite.SQLiteConnection(MvcApplication.ConnectionString)) { cnn.Open(); // we need some tiny mods to allow sqlite support foreach (var sql in TableCreationScripts.Union(extraTablesToCreate)) { cnn.Execute(sql); } } }
/// <summary> /// Used for testing purposes - destroys and recreates the SQLITE file with needed tables. /// </summary> /// <param name="extraTablesToCreate">The Extra Tables To Create.</param> public void RecreateDatabase(params string[] extraTablesToCreate) { var path = ConnectionString.Replace("Data Source = ", string.Empty); // hacky if (File.Exists(path)) { File.Delete(path); } using (var cnn = new System.Data.SQLite.SQLiteConnection(MvcApplication.ConnectionString)) { cnn.Open(); // we need some tiny mods to allow sqlite support foreach (var sql in TableCreationScripts.Union(extraTablesToCreate)) { cnn.Execute(sql); } } }
protected void Application_Start() { RegisterRoutes(RouteTable.Routes); InitProfilerSettings(); var dbFile = HttpContext.Current.Server.MapPath("~/App_Data/TestMiniProfiler.sqlite"); if (System.IO.File.Exists(dbFile)) { File.Delete(dbFile); } using (var cnn = new System.Data.SQLite.SQLiteConnection(MvcApplication.ConnectionString)) { cnn.Open(); cnn.Execute("create table RouteHits(RouteName,HitCount)"); // we need some tiny mods to allow sqlite support foreach (var sql in SqliteMiniProfilerStorage.TableCreationSQL) { cnn.Execute(sql); } } var efDb = HttpContext.Current.Server.MapPath("~/App_Data/SampleWeb.EFCodeFirst.EFContext.sdf"); if (System.IO.File.Exists(efDb)) { File.Delete(efDb); } //Setup profiler for Controllers via a Global ActionFilter GlobalFilters.Filters.Add(new ProfilingActionFilter()); // initialize automatic view profiling var copy = ViewEngines.Engines.ToList(); ViewEngines.Engines.Clear(); foreach (var item in copy) { ViewEngines.Engines.Add(new ProfilingViewEngine(item)); } }
private static void RunScript(string filePath, SqliteConnection connection) { var script = File.ReadAllText(filePath); connection.Execute(script); }
private static void Setup() { var projLoc = Assembly.GetAssembly(typeof(Program)).Location; var projFolder = Path.GetDirectoryName(projLoc); if (File.Exists(projFolder + "\\Test.sqlite")) { File.Delete(projFolder + "\\Test.sqlite"); } var connectionString = "Data Source = " + projFolder + "\\Test.sqlite;"; using (var connection = new System.Data.SQLite.SQLiteConnection(connectionString)) { connection.Open(); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) "); } cnStr = connectionString; Console.WriteLine("Created database"); }