/// <summary> /// 計算分頁 /// </summary> /// <returns></returns> protected override PagedResult ComputePaged() { CheckVersion(); var skipCount = Page <= 1 ? 0 : (Page - 1) * PageSize; var fristFrom = QueryString.ToUpper().IndexOf(" FROM ", StringComparison.Ordinal); var countString = QueryString.Substring(fristFrom, QueryString.Length - fristFrom); var lastOrderBy = countString.ToUpper().LastIndexOf(" ORDER BY ", StringComparison.Ordinal); return(new PagedResult( QueryString + $" OFFSET {skipCount} ROWS FETCH FIRST {PageSize} ROWS ONLY ", Cnn.Query <int>("SELECT COUNT(*) " + countString.Substring(0, lastOrderBy)).FirstOrDefault(), PageSize )); }
public long InsertActa(Acta acta, string tableName = "Actas") { if (acta == null) { return(0); } long actaId = Cnn.Query <long>($@"INSERT INTO {tableName} (Pais,NumeroDepartamento,Departamento,Provincia, NumeroMunicipio,Municipio,Circunscripcion,Localidad,Recinto, NumeroMesa,CodigoMesa,Eleccion,Inscritos, CC,FPV,MTS,UCS,MAS_IPSP,F21,PDC,MNR,PAN_BOL, VotosValidos,Blancos,Nulos,EstadoActa, Fecha,Origen,TimeStamp,Extras,Otros) " + $@"VALUES (@Pais,@NumeroDepartamento,@Departamento,@Provincia, @NumeroMunicipio,@Municipio,@Circunscripcion,@Localidad,@Recinto, @NumeroMesa,@CodigoMesa,@Eleccion,@Inscritos, @CC,@FPV,@MTS,@UCS,@MAS_IPSP,@F21,@PDC,@MNR,@PAN_BOL, @VotosValidos,@Blancos,@Nulos,@EstadoActa, @Fecha,@Origen,@TimeStamp,@Extras,@Otros)" + $@";SELECT last_insert_rowid()", acta).First(); return(actaId); }
private void CheckVersion() { lock (CheckVersionLoch) { if (!SqlVersions.ContainsKey(Cnn.ConnectionString)) { var sqlVersion = Cnn.Query <string>("SELECT SERVERPROPERTY('ProductVersion')") .FirstOrDefault()? .Split(new[] { "." }, StringSplitOptions.RemoveEmptyEntries) .FirstOrDefault(); if (!string.IsNullOrEmpty(sqlVersion)) { SqlVersions.Add(Cnn.ConnectionString, int.Parse(sqlVersion)); } } } if (!SqlVersions.ContainsKey(Cnn.ConnectionString) || SqlVersions[Cnn.ConnectionString] < 11) { throw new Exception("SQL Server is not 2012 or above "); } }
public IEnumerable <Acta> GetAllActas(string tableName = "Actas") { IEnumerable <Acta> query = Cnn.Query <Acta>($"SELECT * FROM {tableName}"); return(query); }