Archive for category Uncategorized
SQL Object Definition Keyword Search Stored Procedure
Posted by Kelly's Chronicles in Uncategorized on January 3, 2018
There are many times when you encounter a SQL database that has had a lot of fingers in it but not a lot of guidance for you to follow, that you need to find a given concept or word to find what objects you need to be looking at in a given situation. The stored procedure below will allow you to do that. It searches both parent and child objects definition for words that are like or equal to your search term and returns them in order of rank, and then in order of object creation using their object id.
USE [YOURDB]
GO
/****** Object: StoredProcedure [dbo].[Object_Dependency_Keyword_SP] Script Date: 1/3/2018 3:26:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- =============================================
CREATE PROCEDURE [dbo].[Object_Dependency_Keyword_SP]
@DefSearchTerm AS varchar(50)
AS
BEGIN
SET NOCOUNT ON;
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- step 1 create temp ta
--DROP TABLE #tempdep
CREATE TABLE #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
-- step 2 load temp table
INSERT INTO #tempdep
SELECT
tbl.object_id AS [ID],
3
FROM
sys.tables AS tbl
--WHERE
--(tbl.name=N'Employee' and SCHEMA_NAME(tbl.schema_id)=N'HumanResources')
-- step 3 find dependencies
declare @find_referencing_objects int
set @find_referencing_objects = 1
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
-- contains source objects
-- 2. @find_referencing_objects defines ordering
-- 1 order for drop
-- 0 order for script
declare @must_set_nocount_off bit
set @must_set_nocount_off = 0
IF @@OPTIONS & 512 = 0
set @must_set_nocount_off = 1
set nocount on
declare @u int
declare @udf int
declare @v int
declare @sp int
declare @def int
declare @rule int
declare @tr int
declare @uda int
declare @uddt int
declare @xml int
declare @udt int
declare @assm int
declare @part_sch int
declare @part_func int
declare @synonym int
set @u = 3
set @udf = 0
set @v = 2
set @sp = 4
set @def = 6
set @rule = 7
set @tr = 8
set @uda = 11
set @synonym = 12
--above 100 -> not in sys.objects
set @uddt = 101
set @xml = 102
set @udt = 103
set @assm = 1000
set @part_sch = 201
set @part_func = 202
/*
* Create #t1 as temp object holding areas. Columns are:
* object_id - temp object id
* object_type - temp object type
* relative_id - parent or child object id
* relative_type - parent or child object type
* rank - NULL means dependencies not yet evaluated, else nonNULL.
* soft_link - this row should not be used to compute ordering among objects
* object_name - name of the temp object
* object_schema - name the temp object's schema (if any)
* relative_name - name of the relative object
* relative_schema - name of the relative object's schema (if any)
* degree - the number of relatives that the object has, will be used for computing the rank
* object_key - surrogate key that combines object_id and object_type
* relative_key - surrogate key that combines relative_id and relative_type
*/
-- DROP TABLE #t1
create table #t1(
object_id int NULL,
object_type smallint NULL,
relative_id int NULL,
relative_type smallint NULL,
rank smallint NULL,
soft_link bit NULL,
object_name sysname NULL,
object_schema sysname NULL,
relative_name sysname NULL,
relative_schema sysname NULL,
degree int NULL,
object_key bigint NULL,
relative_key bigint NULL
)
create unique clustered index i1 on #t1(object_id, object_type, relative_id, relative_type) with IGNORE_DUP_KEY
declare @iter_no int
set @iter_no = 1
declare @rows int
set @rows = 1
declare @rowcount_ck int
set @rowcount_ck = 0
insert #t1 (relative_id, relative_type, rank)
select l.objid, l.objtype, @iter_no from #tempdep l
while @rows > 0
begin
set @rows = 0
if( 1 = @find_referencing_objects )
begin
--tables that reference uddts or udts (parameters that reference types are in sql_dependencies )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as c on c.user_type_id = t.relative_id
join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
where @iter_no = t.rank and (t.relative_type=@uddt OR t.relative_type=@udt)
set @rows = @rows + @@rowcount
--tables that reference defaults ( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.default_object_id = t.relative_id
join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
where @iter_no = t.rank and t.relative_type = @def
set @rows = @rows + @@rowcount
--types that reference defaults ( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.default_object_id = t.relative_id
join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
where @iter_no = t.rank and t.relative_type = @def
set @rows = @rows + @@rowcount
--tables that reference rules
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.rule_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @rule
set @rows = @rows + @@rowcount
--types that reference rules
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.rule_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @rule
set @rows = @rows + @@rowcount
--tables that reference XmlSchemaCollections
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as c on c.xml_collection_id = t.relative_id
join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
where @iter_no = t.rank and t.relative_type = @xml
set @rows = @rows + @@rowcount
--procedures that reference XmlSchemaCollections
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, case when o.type in ( 'P', 'RF', 'PC' ) then @sp else @udf end, @iter_no + 1
from #t1 as t
join sys.parameters as c on c.xml_collection_id = t.relative_id
join sys.objects as o on o.object_id = c.object_id
where @iter_no = t.rank and t.relative_type = @xml
set @rows = @rows + @@rowcount
--udf, sp, uda, trigger all that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, am.object_id, (case o.type when 'AF' then @uda when 'PC' then @sp when 'FS' then @udf when 'FT' then @udf
when 'TA' then @tr else @udf end), @iter_no + 1
from #t1 as t
join sys.assembly_modules as am on am.assembly_id = t.relative_id
join sys.objects as o on am.object_id = o.object_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount
-- CLR udf, sp, uda that reference udt
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select distinct t.relative_id,
t.relative_type,
am.object_id,
(case o.type
when 'AF' then @uda
when 'PC' then @sp
when 'FS' then @udf
when 'FT' then @udf
when 'TA' then @tr
else @udf end),
@iter_no + 1
from #t1 as t
join sys.parameters as sp on sp.user_type_id = t.relative_id
join sys.assembly_modules as am on sp.object_id = am.object_id
join sys.objects as o on sp.object_id = o.object_id
where @iter_no = t.rank and t.relative_type = @udt
set @rows = @rows + @@rowcount
--udt that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, at.user_type_id, @udt, @iter_no + 1
from #t1 as t
join sys.assembly_types as at on at.assembly_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount
--assembly that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ar.assembly_id, @assm, @iter_no + 1
from #t1 as t
join sys.assembly_references as ar on ar.referenced_assembly_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount
--table references table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, fk.parent_object_id, @u, @iter_no + 1
from #t1 as t
join sys.foreign_keys as fk on fk.referenced_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @u
set @rows = @rows + @@rowcount
--table,view references partition scheme
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, idx.object_id, case o.type when 'V' then @v else @u end, @iter_no + 1
from #t1 as t
join sys.indexes as idx on idx.data_space_id = t.relative_id
join sys.objects as o on o.object_id = idx.object_id
where @iter_no = t.rank and t.relative_type = @part_sch
set @rows = @rows + @@rowcount
--partition scheme references partition function
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
from #t1 as t
join sys.partition_schemes as ps on ps.function_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @part_func
set @rows = @rows + @@rowcount
--view, procedure references table, view, procedure
--procedure references type
--table(check) references procedure
--trigger references table, procedure
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, case when 'C' = obj.type then obj.parent_object_id else dp.object_id end,
case when obj.type in ('U', 'C') then @u when 'V' = obj.type then @v when 'TR' = obj.type then @tr
when obj.type in ( 'P', 'RF', 'PC' ) then @sp
when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
end, @iter_no + 1
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table, view procedure
( class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf) )
--reference type
or ( 2 = class and dp.referenced_major_id = t.relative_id and t.relative_type in (@uddt, @udt))
--reference xml namespace ( not supported by server right now )
--or ( 3 = class and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
join sys.objects as obj on obj.object_id = dp.object_id and obj.type in ( 'U', 'V', 'P', 'RF', 'PC', 'TR', 'TF', 'FN', 'IF', 'FS', 'FT', 'C')
where @iter_no = t.rank
set @rows = @rows + @@rowcount
end -- 1 = @find_referencing_objects
else
begin -- find referenced objects
--check references table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, dp.object_id, 77 /*place holder for check*/, @iter_no
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table
class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type = @u
join sys.objects as obj on obj.object_id = dp.object_id and obj.type = 'C'
where @iter_no = t.rank
set @rowcount_ck = @@rowcount
--view, procedure referenced by table, view, procedure
--type referenced by procedure
--check referenced by table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select distinct
case when 77 = t.relative_type then obj2.parent_object_id else t.relative_id end, -- object_id
case when 77 = t.relative_type then @u else relative_type end, -- object_type
dp.referenced_major_id, -- relative_id
case -- relative_type
when dp.class < 2 then
case when 'U' = obj.type then @u
when 'V' = obj.type then @v
when 'TR' = obj.type then @tr
when obj.type in ( 'P', 'RF', 'PC' ) then @sp
when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
when exists (select * from sys.synonyms syn where syn.object_id = dp.referenced_major_id ) then @synonym
end
when dp.class = 2 then (case
when exists (select * from sys.assembly_types sat where sat.user_type_id = dp.referenced_major_id) then @udt
else @uddt
end)
end,
@iter_no + 1
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table, view procedure
( class 0
begin
set @iter_no = @iter_no + 1
end
--defaults referenced by types
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.default_object_id, @def, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.user_type_id = t.relative_id and tp.default_object_id > 0
join sys.objects as o on o.object_id = tp.default_object_id and 0 = isnull(o.parent_object_id, 0)
where t.relative_type = @uddt
--defaults referenced by tables( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.default_object_id, @def, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.object_id = t.relative_id
join sys.objects as o on o.object_id = clmns.default_object_id and 0 = isnull(o.parent_object_id, 0)
where t.relative_type = @u
--rules referenced by types
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.rule_object_id, @rule, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.user_type_id = t.relative_id and tp.rule_object_id > 0
where t.relative_type = @uddt
--rules referenced by tables
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.rule_object_id, @rule, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.object_id = t.relative_id and clmns.rule_object_id > 0
where t.relative_type = @u
--XmlSchemaCollections referenced by table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
from #t1 as t
join sys.columns as c on c.object_id = t.relative_id and c.xml_collection_id > 0
where t.relative_type = @u
--XmlSchemaCollections referenced by procedures
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
from #t1 as t
join sys.parameters as c on c.object_id = t.relative_id and c.xml_collection_id > 0
where t.relative_type in ( @sp, @udf)
--partition scheme referenced by table,view
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
from #t1 as t
join sys.indexes as idx on idx.object_id = t.relative_id
join sys.partition_schemes as ps on ps.data_space_id = idx.data_space_id
where t.relative_type in (@u, @v)
--partition function referenced by partition scheme
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.function_id, @part_func, @iter_no + 1
from #t1 as t
join sys.partition_schemes as ps on ps.data_space_id = t.relative_id
where t.relative_type = @part_sch
end
--cleanup circular references
delete #t1 where object_id = relative_id and object_type=relative_type
--allow circular dependencies by cuting one of the branches
--mark as soft links dependencies between tables or table depending on udf
-- at script time we will need to take care to script fks and checks separately
update #t1 set soft_link = 1 where ( object_type = @u and relative_type = @u ) or
( 0 = @find_referencing_objects and object_type = @u and relative_type = @udf ) or
( 1 = @find_referencing_objects and relative_type = @u and object_type = @udf )
--add independent objects first in the list
insert #t1 ( object_id, object_type, rank)
select t.relative_id, t.relative_type, 1 from #t1 t where t.relative_id not in ( select t2.object_id from #t1 t2 where not t2.object_id is null )
--delete initial objects
delete #t1 where object_id is null
-- compute the surrogate keys to make sorting easier
update #t1 set object_key = object_id + convert(bigint, 0xfFFFFFFF) * object_type
update #t1 set relative_key = relative_id + convert(bigint, 0xfFFFFFFF) * relative_type
create index index_key on #t1 (object_key, relative_key)
update #t1 set rank = 0
-- computing the degree of the nodes
update #t1 set degree = (
select count(*)
from #t1 t_alias
where t_alias.object_key = #t1.object_key and
t_alias.relative_id is not null and
t_alias.soft_link is null)
-- perform topological sorting
set @iter_no=1
while 1=1
begin
update #t1 set rank=@iter_no where degree=0
-- end the loop if no more rows left to process
if (@@rowcount=0) break
update #t1 set degree=NULL where rank = @iter_no
update #t1 set degree = (
select count(*)
from #t1 t_alias
where t_alias.object_key = #t1.object_key and
t_alias.relative_key is not null and
t_alias.relative_key in (select t_alias2.object_key from #t1 t_alias2 where t_alias2.rank=0 and t_alias2.soft_link is null) and
t_alias.rank=0 and t_alias.soft_link is null)
where degree is not null
set @iter_no=@iter_no+1
end
--add name schema
update #t1 set object_name = o.name, object_schema = schema_name(o.schema_id)
from sys.objects AS o
where o.object_id = #t1.object_id and object_type in ( @u, @udf, @v, @sp, @def, @rule, @uda)
update #t1 set relative_type = case op.type when 'V' then @v else @u end, object_name = o.name, object_schema = schema_name(o.schema_id), relative_name =
op.name, relative_schema = schema_name(op.schema_id)
from sys.objects AS o
LEFT OUTER join sys.objects AS op on op.object_id = o.parent_object_id
--join sys.objects AS op on op.object_id = o.object_id
where o.object_id = #t1.object_id and object_type = @tr
update #t1 set object_name = t.name, object_schema = schema_name(t.schema_id)
from sys.types AS t
where t.user_type_id = #t1.object_id and object_type in ( @uddt, @udt )
update #t1 set object_name = x.name, object_schema = schema_name(x.schema_id)
from sys.xml_schema_collections AS x
where x.xml_collection_id = #t1.object_id and object_type = @xml
update #t1 set object_name = p.name, object_schema = null
from sys.partition_schemes AS p
where p.data_space_id = #t1.object_id and object_type = @part_sch
update #t1 set object_name = p.name, object_schema = null
from sys.partition_functions AS p
where p.function_id = #t1.object_id and object_type = @part_func
update #t1 set object_name = a.name, object_schema = null
from sys.assemblies AS a
where a.assembly_id = #t1.object_id and object_type = @assm
update #t1 set object_name = syn.name, object_schema = schema_name(syn.schema_id)
from sys.synonyms AS syn
where syn.object_id = #t1.object_id and object_type = @synonym
-- delete objects for which we could not resolve the table name or schema
-- because we may not have enough privileges
delete from #t1
where
object_name is null or
(object_schema is null and object_type not in (@assm, @part_func, @part_sch))
--final select
select a.object_id,
object_name,
--object_type,
ISNULL(c.type_desc,'') AS Object_Type,
--LTRIM(RTRIM(ISNULL(sm.definition,''))) AS PARENT_DEF,
ISNULL(relative_id,'') AS relative_id,
relative_name = ISNULL(b.name,''),
Rank,
--ISNULL(relative_type,'') AS Relative_Type,
--object_schema,
ISNULL(b.type_desc,'') AS Type_Desc--,
--LTRIM(RTRIM(ISNULL(smc.definition,''))) AS CHILD_DEF
from #t1 a
LEFT JOIN sys.objects b ON b.object_id = a.relative_id
LEFT JOIN sys.Objects c ON c.object_id = a.object_id
LEFT JOIN sys.sql_modules sm ON sm.object_id = c.object_id
LEFT JOIN sys.sql_modules smc ON smc.object_id = b.object_id
WHERE (sm.definition LIKE '%' + @DefSearchTerm + '%' OR smc.definition LIKE '%' + @DefSearchTerm + '%')
-- WHERE object_name = 'Organization_all'
order by Rank,relative_id
drop table #t1
drop table #tempdep
IF @must_set_nocount_off > 0
set nocount off
END;
GO
SQL Object (Table, Stored Procedures, Functions, Views) Dependencies
Posted by Kelly's Chronicles in .NET, C#, Linq, Reporting, Reports, SQL, Uncategorized, vb.net on December 19, 2017
I often get asked to help figure out a confusing and archaic SQL database structure as various developers over the years have added layer after layer of stored procedures, tables, views and functions and nobody in the present day actually knows what a lot of it is or does anymore and folks are paralyzed because they are afraid to make any kinds of changes. However, not all is lost! Using the sample below you can see how your stored procedures, tables, views and functions are connected to each other and what you will need to address and what you need to thoroughly test if you make any kind of changes. The format below is for SQL, but I am working on an update for MySQL, DB2 and Oracle as well so check back later for that.
USAGE:
select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P','TR', 'FN', 'V', 'TF','IF')
-- where o.name = 'Your Table Name'
--where sp.Name = 'your stored procedure, function or trigger or other type'
--order set to sort by table, object type and object name
order by o.Name, sp.type,sp.name
-- change order to drill down from procedure, trigger or function name or type
--order by sp.name,o.Name, sp.type_desc
--Type Options
-- C CHECK_CONSTRAINT
--D DEFAULT_CONSTRAINT
--F FOREIGN_KEY_CONSTRAINT
--FN SQL_SCALAR_FUNCTION
--IF SQL_INLINE_TABLE_VALUED_FUNCTION
--IT INTERNAL_TABLE
--P SQL_STORED_PROCEDURE
--PK PRIMARY_KEY_CONSTRAINT
--R RULE
--S SYSTEM_TABLE
--SQ SERVICE_QUEUE
--TF SQL_TABLE_VALUED_FUNCTION
--TR SQL_TRIGGER
--U USER_TABLE
--UQ UNIQUE_CONSTRAINT
--V VIEW
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P','TR', 'FN', 'V', 'TF','IF')
-- where o.name = 'Your Table Name'
--where sp.Name = 'your stored procedure, function or trigger or other type'
--order set to sort by table, object type and object name
order by o.Name, sp.type,sp.name
-- change order to drill down from procedure, trigger or function name or type
--order by sp.name,o.Name, sp.type_desc
--Type Options
-- C CHECK_CONSTRAINT
--D DEFAULT_CONSTRAINT
--F FOREIGN_KEY_CONSTRAINT
--FN SQL_SCALAR_FUNCTION
--IF SQL_INLINE_TABLE_VALUED_FUNCTION
--IT INTERNAL_TABLE
--P SQL_STORED_PROCEDURE
--PK PRIMARY_KEY_CONSTRAINT
--R RULE
--S SYSTEM_TABLE
--SQ SERVICE_QUEUE
--TF SQL_TABLE_VALUED_FUNCTION
--TR SQL_TRIGGER
--U USER_TABLE
--UQ UNIQUE_CONSTRAINT
--V VIEW
Dynamic Predicate (WHERE clause) for a LINQ Query
Posted by Kelly's Chronicles in .NET, C#, Linq, Reporting, Reports, SQL, Uncategorized, vb.net on December 16, 2017
I love LINQ! It really has been a godsend and anyone still using datatable.select seriously needs to learn it. One thing I didn’t like too much though was I was having to write each LINQ query out instead of being able to pass it off to a function as a parameter. I haven’t gotten all the way there yet but this was a start. The idea was when we have a source of data that we refer to often that we pass the predicate (WHERE statement) off to a function instead of having to rewrite the whole query each time. While this example uses a datatable, you can use it with any collection that can have LINQ written against it.
So how is it done? First we identify the single element type we need to use ( Of TRow As DataRow) and then identify the “source” we are using and tie the identifier to that source ((source As TypedTableBase(Of TRow)). Then we must specify the predicate, or the WHERE clause that is going to be passed (predicate As Func(Of TRow, Boolean)) which will either be returned as true or false. Then we identify how we want the returned information ordered (OrderByField As String). Our function will then return a EnumerableRowCollection(Of TRow), our collection of datarows that have met the conditions of our predicate(EnumerableRowCollection(Of TRow)). This is a basic example. Of course you must make sure your order field doesn’t contain nulls, or have handled that situation properly and make sure your column names (if you are using a strongly typed datasource never mind this, it will rename the columns for you) are standard.
What is left // TO DO is to accomplish this with a table join, which is what my next step will be. It won’t be hard. Hopefully this code helps you and I hope you all have a great Christmas!
VB
USAGE:
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim da As New DataSet1TableAdapters.OrdersTableAdapter
da.Fill(ds.Orders)
Dim MyRet = LINQ_Where(ds.Orders, Function(row As DataSet1.OrdersRow) row.Order_ID < 200 And row.Order_ID > 1, "Order ID")
If MyRet Is Nothing Then
MessageBox.Show("NO ROWS")
Else
DataGridView1.DataSource = MyRet.CopyToDataTable
End If
End Sub
Function LINQ_Where(Of TRow As DataRow)(source As TypedTableBase(Of TRow), predicate As Func(Of TRow, Boolean), OrderByField As String) As EnumerableRowCollection(Of TRow)
Try
Dim ReturnedRows = From row In source
Where predicate(row)
Order By row.Item(OrderByField)
Select row
If ReturnedRows Is DBNull.Value Then
Return Nothing
Else
Return ReturnedRows
End If
If ReturnedRows.Any = True Then
Return ReturnedRows
Else
Return Nothing
End If
Catch ex As Exception
Return Nothing
End Try
End Function
C#
USAGE:
private void Form1_Load(object sender, EventArgs e)
{
DataSet1TableAdapters.OrdersTableAdapter da = new DataSet1TableAdapters.OrdersTableAdapter();
da.Fill(ds.Orders);
var MyRet = LINQ_Where(ds.Orders, (DataSet1.OrdersRow row) => row.Order_ID < 200 && row.Order_ID > 1, "Order ID");
if (MyRet == null)
{
MessageBox.Show("NO ROWS");
}
else
{
DataGridView1.DataSource = MyRet.CopyToDataTable;
}
}
public EnumerableRowCollection<TRow> LINQ_Where<TRow>(TypedTableBase<TRow> source, Func<TRow, bool> predicate, string OrderByField) where TRow: DataRow
{
try
{
var ReturnedRows = from row in source
where predicate(row)
orderby row.Item(OrderByField)
select row;
if (ReturnedRows == DBNull.Value)
{
return null;
}
else
{
return ReturnedRows;
}
if (ReturnedRows.Any() == true)
{
return ReturnedRows;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
Northwind Database For DB2
Posted by Kelly's Chronicles in Uncategorized on June 21, 2017
Well if I thought Oracle was a mess getting the Northwind database to I hadn’t seen anything yet. I don’t care for the user tools at all in IBM Data Studio. The mapping to get SSIS to push a copy of my SQL based Northwind database was all over the map. If you don’t know, Northwind is the training tool of choice for many developers, businesses and consultants.
So again, I am doing you a solid. Linked here is the SSIS package you can pick up and use to transfer your SQL based version of Northwind to DB2. I also included the DB2 ddl for the database for your reference. I haven’t found an easy way to do it from the Access version. Sorry folks.
A couple things. You will need to create the database on your DB2 instance like below. use your standard command prompt with admin privileges.
create database NORTHWIN using codeset UTF-8 territory en
You may notice I left off the D. Yes I did. DB2 prefers its databases to be 8 characters or less. So that is why
Once you have it open in Notepad and edit these lines with your information:
DTS:ConnectionString=”Data Source=DESKTOP-P1TI349;Initial Catalog=Northwind;Provider=SQLOLEDB;Integrated Security=SSPI;Auto Translate=false;” />
DTS:ConnectionString=”Data Source=NORTHWIN;User ID=db2admin;Provider=IBMOLEDB.DB2COPY1;Persist Security Info=True;Location=DESKTOP-P1TI349:50000;Extended Properties="";”>
Find/Replace DB2COPY1 with the name of your instance.
Save the file and open in SSIS.
And then you should be good to go…. if you have changes in your SQL Northwind database that aren’t reflected here (it will prompt you) you will need to use SSIS to transfer the data. Be sure for your destination you choose the instance name of your DB2 instance. (Mine was DB2COPY1).
That’s about it. Have a good day….
Email Excel Spreadsheet as Email Body Issues
Posted by Kelly's Chronicles in Uncategorized on May 14, 2016
Hello all. I had a production manager wanting an excel spreadsheet mailed as the body of the email. As some of you know the code generated by excel to produce the email is pretty crazy. But as a result, it showed up fine in Outlook and Android but it did not show the gridlines on the spreadsheet. So this code is based on the excellent work by Ron DeBruin over at http://www.rondebruin.nl/win/s1/outlook/bmail3.htm . I did a replacement for the HTML Range in this manner and the grid lines did appear. And the manager was happy.
Sub Mail_Selection_Range_Outlook_Body()
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
‘Don’t forget to copy the function RangetoHTML in the module.
‘Working in Excel 2000-2016
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
‘MsgBox Cells(5, 9).Value
Set rng = Nothing
On Error Resume Next
‘Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
‘You can also use a fixed range if you want
‘Set rng = Sheets(“YourSheet”).Range(“D4:D12”).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox “The selection is not a range or the sheet is protected” & _
vbNewLine & “please correct and try again.”, vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.BodyFormat = olFormatHTML
.To = “you@you.com”
.CC = “”
.BCC = “”
.Subject = “Testing Purchase Order Email To Steve”
.HTMLBody = RangetoHTML(rng)
Replace .HTMLBody, “border-left:none”, “border-left:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-right:none”, “border-right:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-bottom:none”, “border-bottom:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-top:none”, “border-bottom:solid;border-width: 1px;border-color:black”
.Send
‘or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Get CPU Usage of a Process using C#
Posted by Kelly's Chronicles in Uncategorized on November 5, 2012
This was a fun assignment. I was asked to write a piece of code that when a particular process id was passed to it it would return its CPU usage. WMI is fun. Have a great night….
public static decimal GetUssage(string pid)
{
//get the process
ManagementObjectSearcher searcher = new ManagementObjectSearcher(“SELECT * FROM Win32_Process WHERE ProcessID = ” + pid);
decimal PercentProcessorTime = 0;
foreach (ManagementObject queryObj in searcher.Get())
{
DateTime firstSample, secondSample;
//populate the process info
firstSample = DateTime.Now;
queryObj.Get();
//get cpu usage
ulong u_oldCPU = (ulong)queryObj.Properties[“UserModeTime”].Value
+ (ulong)queryObj.Properties[“KernelModeTime”].Value;
//sleep to create interval
System.Threading.Thread.Sleep(1000);
//refresh object
secondSample = DateTime.Now;
queryObj.Get();
//get new usage
ulong u_newCPU = (ulong)queryObj.Properties[“UserModeTime”].Value
+ (ulong)queryObj.Properties[“KernelModeTime”].Value;
decimal msPassed = (decimal)(secondSample – firstSample).TotalMilliseconds;
//formula to get CPU ussage
if (u_newCPU > u_oldCPU)
PercentProcessorTime = (decimal)((u_newCPU – u_oldCPU)
/ (msPassed * 100 * Environment.ProcessorCount));
Console.WriteLine(“processor time ” + PercentProcessorTime);
}
return PercentProcessorTime;
}
How to make Linq and IList sort Dynamically using C#
Posted by Kelly's Chronicles in Uncategorized on October 26, 2012
I apologize that it has been a while. I have been extremely busy and in the time that I have been away I went through treatment for a medical issue. Needless to say I had other issues to deal with other than this blog. But I will try to publish more frequently. Today’s topic was a question from another developer which was to have have a Linq and IList sort dynamically. So long as you don’t force execution by using ToList, you can keep tacking on to your query until you are ready to actually run it. This is because much of LINQ uses “deferred execution”. For example, you could do something similar to:
var query = apps.AsQueryable()
.Select
(Application => new
{
ID = Application.ApplicationId,
Force = Application.Force.Description,
Function = Application.BusinessFunction.Description,
Category = Application.Category.Description,
SubCategory = Application.SubCategory.Description,
Name = Application.ApplicationName,
Description = Application.ApplicationDescription
}
).Take(10);
switch (sortType)
{
case “ID”:
query = query.Orderby(Application => Application.ApplicationId);
break;
case “Force”:
query = query.Orderby(Application => Application.Force);
break;
}
IList tempList = query.ToList();
As always it has been great getting your all feedback while I was gone. Thank You!
POST and HTTP Protocol Violation Resolution in vb.net
Posted by Kelly's Chronicles in Uncategorized on May 5, 2009
Good Morning! My daughter played volleyball last night and was once again outstanding. MY eldest daughter has been released. Unfortunately that has not gone so well. She is moving out of our house no later than next Monday and to be honest I am ok with that.
Today’s topic is one that has plagued me for a while and isn’t really a coded solution but a hack. I thought I would share it in case some of you run into the same problem. Basically we want to POST data to the server, and the server saves the file and its response is a tar archive. Making the request works fine. Where I ran into trouble was the saving of the binary response to a variable and eventually a file. First some of the code to give you an idea of what happened:
‘ Download a single binary file from a server and save it to a local folder
Public Sub DownloadAndSaveFile(ByVal Url As String, ByVal Filename As String, ByVal User As String, ByVal Pass As String, ByVal Debug As Integer)
‘ The post data template
Dim PostdataTemplate As String = _
"handler=SOME_URLENCODED_DATA"
Dim PostdataArray As Byte() = Encoding.ASCII.GetBytes(Postdata)
‘ Create a new NetworkCredential object
Dim NetworkCredential As New NetworkCredential(User, Pass)
Try
‘ Create a new WebClient instance
Dim myWebClient As New WebClient
‘ Set Preauthenticate property to true
‘myWebClient.PreAuthenticate = True
‘ Associate the NetworkCrbedential object with the ‘WebRequest’ object
myWebClient.Credentials = NetworkCredential
‘ Add required HTTP headers to request
myWebClient.Headers.Add("Accept", "*/*")
myWebClient.Headers.Add("Content-Type", "application/x-www-form-urlencoded")
‘ UploadData method implicitly sets HTTP POST as the request method
Dim responseArray As Byte() = myWebClient.UploadData(Url, PostdataArray)
‘ The response array as byte generates an exception!
Catch Ex As Exception
WriteLine("Error: " & Ex.Message)
End Try
End Sub
I get an exception when saving the response as a byte:
Error: The underlying connection was closed: The server committed an HTTP protocol violation.
The problem was that the .NET Framework detected the server did not comply with HTTP 1.1 RFC. This problem may occur when the response contains incorrect headers or incorrect header delimiters.
S0 what to do? I don’t have control of the production server so I can’t fix it on that end. So here is where the hack came in. I modified the app.exe.config file in the following way. You can also modify the machine.config file this way but don’t do that.I should note that I know that the server inserting a header with no name or value is against RFC2616. But I don’t have the ability to modify the server’s response in the production environment. Make it a great day!
——————————————————————
<configuration>
<system.net>
<settings>
<httpWebRequest useUnsafeHeaderParsing="true" />
</settings>
</system.net>
</configuration>
——————————————————————
Technorati Tags: vb.net,csharp,Issue,HTTP,POST,Protocol,Violation,Resolution,data,server,response,archive,code,Download,folder,Public,
DownloadAndSaveFile,Filename,User,Pass,Integer,template,PostdataTemplate,handler,PostdataArray,Byte,ASCII,GetBytes,Postdata,Create,
WebClient,instance,Preauthenticate,True,Associate,NetworkCredential,WebRequest,Credentials,Headers,Accept,Content,Type,
UploadData,method,exception,Catch,WriteLine,Error,Message,connection,.NET Framework,header,production,machine,environment,configuration,system,config
Refresh Treeview From Another Form with vb.net
Posted by Kelly's Chronicles in Uncategorized on April 29, 2009
Good Morning! Well Brett Favre is at it again. He has requested his release from the New York Jets but says he has no intentions of returning “at this time”. Is there any doubt he will be in a Vikings uniform once the season starts? I have just had it with him. The Vikings can have him.
Today’s topic was requested last night. Basically the user wants to update the treeview on another form after the data is updated. Its not complicated. Basically we add an event handler to the original form that handles the update. On the calling form we call that event when the update occurs. Make it a great day!
Private Sub CallingForm_AfterUpdate(ByVal Sender As CallingForm, ByVal Item As Object)
Dim nd As TreeNode
nd = Me.SearchNodesForHierarchyItem(Me.mytreeview.Nodes, Item.ID)
If Not nd Is Nothing Then
nd.Text = Item.Name
nd.EnsureVisible()
Me.mytreeview.SelectedNode = nd
Else
Me.Refresh()
nd = Me.SearchNodesForHierarchyItem(Me.mytreeview.Nodes, Item.ID)
If Not nd Is Nothing Then
nd.EnsureVisible()
Me.mytreeview.SelectedNode = nd
End If
End If
End Sub
Private Sub Refresh()
Try
Me.mytreeview.Nodes.Clear()
If Me.cboTree.SelectedKey = 0 Then
For Each h As Hierarchy In Hierarchy.TMHierarchies
Dim n As New HierarchyNode(h.Name)
n.ImageIndex = 0
n.SelectedImageIndex = n.ImageIndex
n.Tag = h
mytreeview.Nodes.Add(n)
If h.Children.Count > 0 Then
n.Nodes.Add(New HierarchyItemNode)
End If
n.Expand()
Next
Else
Dim h As Hierarchy = New Hierarchy(CInt(Me.cboTree.SelectedKey))
Dim n As New HierarchyNode(h.Name)
n.ImageIndex = 0
n.SelectedImageIndex = n.ImageIndex
n.Tag = h
mytreeview.Nodes.Add(n)
If h.Children.Count > 0 Then
n.Nodes.Add(New HierarchyItemNode)
End If
n.Expand()
End If
If Me.mytreeview.GetNodeCount(False) > 0 Then Me.mytreeview.SelectedNode = Me.mytreeview.Nodes(0)
Catch ex As Exception
End Try
End Sub
and on the calling form simly call after an update:
Public Event AfterUpdate(ByVal Sender As CallingForm, ByVal Item As Object)
Technorati Tags: vb.net,.NET Framework,csharp,Refresh,Treeview,From,Another,Form,Brett,Favre,Vikings,user,data,event,handler,Join,Facebook,Sender,Item,
Object,TreeNode,Nodes,Text,Name,EnsureVisible,SelectedNode,SelectedKey,ImageIndex,SelectedImageIndex,Children,Count,Expand,GetNodeCount,
Exception,Public,AfterUpdate
Recent Comments