private static Ret Delete(TableInfo tableInfo, TPk id, TFilter filter) { using (var scope = new SequelScope()) using (var tx = scope.CreateTransactionScope()) { var idValue = default(TPk).Equals(id) ? null : (object)id; var parameters = filter?._GetMap(FilterInfo.FieldNames); var comparisons = string.Join(" and ", FilterInfo.FieldNames .Except(FilterInfo.RowNumberName) .Select(x => $"{x} matches if set @{x}") ); var rowNumber = (FilterInfo.RowNumberName != null) ? filter._Get(FilterInfo.RowNumberName) : null; var done = @"delete from @{p_table} where @{p_pk} in ( select @{p_pk} from (select @{p_pk} , row_number() over (order by @{p_pk} asc) as row_number from @{p_table} where (@p_id is set and @{p_pk} matches @p_id) or (@p_id is not set and @{p_comparisons}) ) as T where row_number matches if set @p_row_number ) ; select @@rowcount " .AsSql() .Set("p_table", tableInfo.TableName) .Set("p_pk", tableInfo.PkName) .Set("p_comparisons", comparisons) .Set("p_row_number", rowNumber) .Set("p_id", idValue) .Set(parameters) .ApplyTemplate() .Echo() .SelectOne <bool>(); tx.Complete(); return(done); } }
public static Ret <TTable[]> Find(TFilter filter = null) { try { using (var scope = new SequelScope()) { var parameters = filter._GetMap(FilterInfo.FieldNames); var comparisons = string.Join(" and ", FilterInfo.FieldNames .Except(FilterInfo.RowNumberName) .Select(x => $"{x} matches if set @{x}") ); var rowNumber = (FilterInfo.RowNumberName != null) ? filter._Get(FilterInfo.RowNumberName) : null; var entities = @"select * from (select * , row_number() over (order by @{p_pk} asc) as row_number from @{p_table} with (nolock) where @{p_comparisons} ) as T where row_number matches if set @p_row_number" .AsSql() .Set("p_table", TableInfo.TableName) .Set("p_pk", TableInfo.PkName) .Set("p_comparisons", comparisons) .Set("p_row_number", rowNumber) .Set(parameters) .SelectGraphArray <TTable>(); return(entities); } } catch (Exception ex) { throw ex; } }
public static Ret Update(TTable entity, params string[] fields) { try { using (var scope = new SequelScope()) using (var tx = scope.CreateTransactionScope()) { if (fields.Length == 0) { fields = TableInfo.FieldNames.Except(TableInfo.PkName).ToArray(); } var parameters = entity._GetMap(fields); var assertions = string.Join(", ", parameters.Keys.Select(x => $"{x} = @{x}")); var id = entity._Get(TableInfo.PkName); var done = @"update @{TableName} set @{assertions} where @{PkName} matches @id ; select @@rowcount" .AsSql() .Set("TableName", TableInfo.TableName) .Set("PkName", TableInfo.PkName) .Set("assertions", assertions) .Set("id", id) .Set(parameters) .SelectOne <bool>(); tx.Complete(); return(done); } } catch (Exception ex) { throw ex; } }
public static Ret <TTable> Find(TPk id) { try { using (var scope = new SequelScope()) { var entity = @"select * from @{p_table} with (nolock) where @{p_pk} matches @p_id" .AsSql() .Set("p_table", TableInfo.TableName) .Set("p_pk", TableInfo.PkName) .Set("p_id", id) .SelectOneGraph <TTable>(); return(entity); } } catch (Exception ex) { throw ex; } }
public static Ret Insert(TTable entity) { try { using (var scope = new SequelScope()) using (var tx = scope.CreateTransactionScope()) { var updatableFields = TableInfo.PkAutoIncrement ? TableInfo.FieldNames.Except(TableInfo.PkName).ToArray() : TableInfo.FieldNames.ToArray(); var parameters = entity._GetMap(updatableFields); var fields = string.Join(", ", parameters.Keys); var values = parameters.Values; var done = @"insert into @{p_table} (@{p_fields}) values (@{p_values}) ; select @@rowcount" .AsSql() .Set("p_table", TableInfo.TableName) .Set("p_pk", TableInfo.PkName) .Set("p_fields", fields) .Set("p_values", values) .Set(parameters) .SelectOne <bool>(); tx.Complete(); return(done); } } catch (Exception ex) { throw ex; } }
public static Ret InsertOrUpdate(TTable entity) { try { using (var scope = new SequelScope()) using (var tx = scope.CreateTransactionScope()) { var updatableFields = TableInfo.PkAutoIncrement ? TableInfo.FieldNames.Except(TableInfo.PkName).ToArray() : TableInfo.FieldNames.ToArray(); var parameters = entity._GetMap(updatableFields); var assertions = string.Join(", ", parameters.Keys.Select(x => $"{x} = @{x}")); var fields = string.Join(", ", parameters.Keys); var values = parameters.Values; var id = entity._Get(TableInfo.PkName); var affectedId = @"declare @x_affected_id int = null ; update @{p_table} set @{p_assertions} where @{p_pk} matches @p_id ; if @@rowcount > 0 begin set @x_affected_id = @p_id end ; insert into @{p_table} (@{p_fields}) select @{p_values} where not exists (select 1 from @{p_table} where @{p_pk} matches @p_id) ; if @@rowcount > 0 begin set @x_affected_id = scope_identity() end ; select @x_affected_id" .AsSql() .Set("p_table", TableInfo.TableName) .Set("p_pk", TableInfo.PkName) .Set("p_assertions", assertions) .Set("p_fields", fields) .Set("p_values", values) .Set("p_id", id) .Set(parameters) .SelectOne(); tx.Complete(); var done = affectedId != null; if (done) { entity._Set(TableInfo.PkName, affectedId); } return(done); } } catch (Exception ex) { throw ex; } }
static Db() { using (var scope = new SequelScope(connectionString)) { var version = @"select DFvalor from TBsis_config where DFchave = 'versao'" .AsSql() .TrySelectOne <int>(); if (version == 1) { return; } using (var tx = scope.CreateTransactionScope()) { // // DDL // @"create table TBsis_config ( DFchave nvarchar(256) not null constraint pk_TBsis_config primary key clustered, DFvalor nvarchar(4000) null ) ; insert into TBsis_config (DFchave, DFvalor) values ('versao', '1') ; create table TBgrupo_empresa ( DFid_grupo_empresa int not null identity(1,1) constraint pk_TBgrupo_empresa_DFid_grupo_empresa primary key clustered, DFnome nvarchar(256) not null ) ; create table TBempresa ( DFid_empresa int not null constraint pk_TBempresa primary key clustered, DFrazao_social nvarchar(256) not null, DFnome_fantasia nvarchar(256) not null, DFcnpj nvarchar(18) not null, DFativo bit not null constraint df_TBempresa_DFativo default (1), DFid_grupo_empresa int null constraint fk_TBempresa_TBgrupo_empresa foreign key references TBgrupo_empresa(DFid_grupo_empresa) on delete set null ) ; create index ix_TBempresa_DFid_grupo_empresa on TBempresa (DFid_grupo_empresa) ; create table TBpapel_usuario ( DFid_papel_usuario int not null identity(1,1) constraint pk_TBpapel_usuario primary key clustered, DFnome nvarchar(256) not null ) ; create table TBusuario ( DFid_usuario int not null identity(1,1) constraint pk_TBusuario primary key clustered, DFlogin nvarchar(100) not null constraint uk_TBusuario_DFlogin unique, DFnome nvarchar(100) not null, DFativo bit not null constraint df_TBusuario_DFativo default (1), DFsenha nvarchar(12) not null, DFid_empresa_padrao int null constraint fk_TBusuario_TBempresa foreign key references TBempresa(DFid_empresa) on delete set null, DFid_papel_usuario int null constraint fk_TBusuario_TBpapel_usuario foreign key references TBpapel_usuario(DFid_papel_usuario) on delete set null ) ; create index ix_TBusuario_DFid_empresa_padrao on TBusuario (DFid_empresa_padrao) ; create index ix_TBusuario_DFid_papel_usuario on TBusuario (DFid_papel_usuario) ; create table TBpostagem ( DFid_postagem int not null identity(1,1) constraint pk_TBpostagem primary key clustered, DFid_autor int not null constraint fk_TBpostagem_TBusuario foreign key references TBusuario(DFid_usuario) on delete cascade, DFtitulo nvarchar(100) not null, DFtexto nvarchar(max) not null, DFdata_publicacao datetime not null constraint df_TBpostagem_DFdata_publicacao default (getdate()), DFid_postagem_referente int null constraint fk_TBpostagem_TBpostagem foreign key references TBpostagem(DFid_postagem), DFtipo as (case when coalesce(DFid_postagem_referente, 0) = 0 then 'P' else 'C' end) ) ; create index ix_TBpostagem_DFid_autor on TBpostagem(DFid_autor) ; create index ix_TBpostagem_DFdata_publicacao on TBpostagem(DFdata_publicacao desc) ; create index ix_TBpostagem_DFid_postagem_referente on TBpostagem(DFid_postagem_referente) ; create index ix_TBpostagem_DFtipo on TBpostagem(DFtipo) ; create table TBclassificacao ( DFid_classificacao int not null identity(1,1) constraint pk_TBclassificacao primary key clustered, DFid_autor int not null constraint fk_TBclassificacao_TBusuario foreign key references TBusuario (DFid_usuario), DFid_postagem_referente int not null constraint fk_TBclassificacao_TBpostagem foreign key references TBpostagem (DFid_postagem) on delete cascade, DFclassificacao int not null constraint ck_TBclassificacao_DFclassificacao check (DFclassificacao = -1 or DFclassificacao = 1) )" .AsSql() .Execute(); // // DEMONSTRACAO // @"insert into TBgrupo_empresa (DFnome) values ('GRUPO SEM FRONTEIRAS') declare @id_grupo_empresa int = scope_identity() insert into TBempresa (DFid_empresa, DFrazao_social, DFnome_fantasia, DFcnpj, DFativo, DFid_grupo_empresa) values (1, 'SEM FRONTEIRAS VAREJISTA', 'SUPERMERCADO SEM FRONTEIRAS', '01.234.567/0001-95', 1, @id_grupo_empresa) , (2, 'SEM FRONTEIRAS ATACADISTA', 'CENTRAL SEM FRONTEIRAS', '01.234.567/0002-76', 1, @id_grupo_empresa) insert into TBpapel_usuario (DFnome) values ('ADMIN') declare @id_papel_admin int = scope_identity() insert into TBpapel_usuario (DFnome) values ('USUARIO') declare @id_papel_usuario int = scope_identity() insert into TBusuario (DFlogin, DFnome, DFativo, DFsenha, DFid_empresa_padrao, DFid_papel_usuario) values ('admin', 'ADMINISTRADOR', 1, 'qwer0987', 1, @id_papel_admin) , ('fulano', 'FULANO', 1, '123', 1, @id_papel_usuario) , ('beltrano', 'BELTRANO', 1, '123', 1, @id_papel_usuario) , ('cicrano', 'CICRANO', 1, '123', 1, @id_papel_usuario) , ('alano', 'ALANO', 2, '123', 1, @id_papel_usuario) , ('mengano', 'MENGANO', 2, '123', 1, @id_papel_usuario) , ('zutano', 'ZUTANO', 2, '123', 1, @id_papel_usuario) , ('citano', 'CITANO', 2, '123', 1, @id_papel_usuario) , ('perengano', 'PERENGANO', 2, '123', 1, @id_papel_usuario)" .AsSql() .Execute(); tx.Complete(); } } }