//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");
}
