Archive for category Linq
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;
}
}
Recent Comments