Posts

Posted on

Coding Tutorials

Old schoolers!  Keep up to date with new fangled ways of coding with these handy tutorials.

Hello World

This concise “Hello World” C# tutorial covers the basics including types and variables, classes and objects, LINQ and exception handling.

If you are interested in learning more about MVC, then check out this free Pluralsight video tutorial on MVC4. It’s hours of content by Scott Allen, who has the Ode to Code blog, and it’s very good.

Finally there’s this tutorial on WPF and XAML.  It looks as if WPF is hanging in there as a desktop app solution, whether it will replace WinForms for line of business apps remains to be seen.

Posted on

Data Layer Class Generator

I can’t remember where I found it, but this SQL script will generate a Dapper compatible class file for the table you specify.

Also this script is handy to use in the business / mapping layer of you application:

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
SO.name AS [ObjectName],
REPLACE(p.name, '@', 'rec.') + ',' as prop,
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P 
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN'))
AND SO.name = 'YourProcedureSave'
ORDER BY [Schema], SO.name, P.parameter_id
Posted on

Enhanced Stored Procedure Generator

Take the tedium out of coding CRUD stored procedures and use this enhanced code generator crud_generator instead.  It’s the one from www.sqlbook.com with the following enhancements:

1) Suffixes changed to Search, Load, Save and Delete
2) Generates a comment header for each procedure
3) Generates default values when null parameters supplied to Save procedure
4) Uses char(13) + char(10) for new lines

This is a great way to jump start your coding after your data model is finalised.  Then use Dapper to build your c# data access layer.  Job done.

Posted on

Generate All Grants in a Database

Use this handy SQL to generate all grants that exist in the current database.

SELECT
 (
 dp.state_desc + ' ' +
 dp.permission_name collate latin1_general_cs_as + 
 ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
 ' TO ' + '[' + dpr.name + ']'
 ) AS GRANT_STMT
FROM sys.database_permissions AS dp
 INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
 INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
 INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('guest')
Posted on

Find Agent Jobs Containing a String

Use this handy query to find all agent jobs that have steps containing a search string.

DECLARE @Search varchar(255)
SET @Search='usp_YourStoredProc'
 
SELECT j.name, j.enabled, s.step_id, s.database_name, s.subsystem, s.command
FROM msdb.dbo.sysjobsteps s
INNER JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id
WHERE command LIKE '%'+@Search+'%'
ORDER BY 1, 3

Thanks to this StackOverflow post for pointing me in the right direction.

Posted on

SQL Server Autofix Logins

I need these commands all the time when restoring databases. This will lists the orphaned users:

 EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

 EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

 EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Hat tip here.

Posted on

Find a String in a Stored Procedure

Use this handy query to find all procedures containing the search string @Search

DECLARE @Search varchar(255)
SET @Search='Document'

SELECT DISTINCT
 o.name AS Object_Name,o.type_desc
 FROM sys.sql_modules m 
 INNER JOIN sys.objects o ON m.object_id=o.object_id
 WHERE m.definition Like '%'+@Search+'%'
 ORDER BY 2,1

Thanks to StackOverflow for this. Or alternatively make use of the information_schema:

SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION 
 FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_DEFINITION LIKE '%Document%'

Hat tip here.