Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

January 2015

BreezeJS – Edit SQL table with < 100 lines of JS code [VIDEO]

With SharePoint 2013 and industry movement towards the cloud I’ve been exploring JavaScript as a primary way to develop rich applications.

One common challenge is data access.  

With C# I have years of experience with [System.Data] and can perform CRUD against SQL relational databases in my sleep.  Open connection, query, data adapter, fill DataTable, and voila!    Muscle memory. Second nature.  Tried and true methods.   However, in the new client side JS world I had no clue where to begin.

Enter Breeze.

 

People describe Breeze as “Entity Framework on the client in JavaScript” which sounds simple yet has profound implication for the developer.   CRUD operations, LINQ style query, navigating primary/foreign keys, input validation, caching, batch updates, and more.   That’s a lot to consider and new ideas take time to absorb.   Breeze could potentially replace:

  • ASP.Net (ASPX) web forms
  • ASCX user controls
  • InfoPath forms
  • SharePoint web parts
  • WCF 5.6 data services
  • OData
  • Classic WebAPI

 

I set out to code an example with a few goals:

  • Create simple SQL schema (two tables – parent/child – one to many)
  • Execute CRUD operations in JS against SQL  tables
  • Leverage JS plugins and NuGet “Install-Package” to load third party components
    • Install-Package breeze.webapi2.ef6
    • Install-Package breeze.angular
    • Install-Package angularjs.core
  • Little code as possible

The whole thing took less than 30 minutes and I edited video down to just 15.    I was impressed by how straightforward and easy the process was.   Breeze# in ASP.Net MVC for the back end WebAPI controller was nearly identical to the Breeze example code.   Add one C# method per entity (SQL table) and Breeze does the rest.  The JS front end took a little more time to understand but was also easy to apply.   Connect  Entity Manager to the Breeze URL and you’re ready for CRUD queries.    Amazing!     Given how easy Breeze is I would be hard pressed to use OData or manually created WebAPI controllers with C# code to query a database.   If you can use Breeze, then use it!    You’ll save lots of effort.

Please leave a comment if you found this helpful.   Thank you! 

shades_smile

 

Watch Video

[BreezeJS and WebAPI – Edit SQL table in JS with less than 100 lines of code]

 

Download Code

Download

http://spjeff.com/etc/Appraisal-Breeze-VS2013.zip

 

Screenshots

image
image

 

image

 

References

 

SharePoint 2013 upgrade QA with HTTP homepage checks

After upgrading it can be smart to run Quality Assurance checks.   Microsoft gives us Test-SPContentDatabase for post upgrade scanning.   I wanted to take that one step further and simulate user visits to every site homepage.   If the HTML response contains an error keyword then we’ll research those URLs further and dig into root cause.

If a SharePoint site homepage won’t load that creates a road block where users aren’t able to navigate Lists, Libraries, Settings, etc. to even troubleshooting the issue. 

By simulating HTTP visits to every site homepage we can increase upgrade quality with higher confidence that users can continue working uninterrupted.   Knowing that all homepages gave HTTP 200 with a clean page title is a great way to complete any upgrade.   Hope you found this helpful!    

shades_smile

 

QA Checks

  1. Page title contains “error”
  2. Page HTML source contains “Correlation ID”
  3. Page HTML source contains “Microsoft.Office.DataParts” assembly name

 

CSV Output

image

 

Screenshots

1-14-2015 11-44-33 AM
3

 

Code

#declare functions
Function HttpQA($url, $type) {
	#create web request
	$res = Invoke-WebRequest $url -UseBasicParsing -UseDefaultCredentials
	#parse 
	$statuscode = $res.StatusCode
	$a = $res.Content.indexOf("<title>")
	$b = $res.Content.indexOf("")
	$title = $res.Content.Substring($a+7,$b-$a-7).Trim().replace(([char]13).ToString(),"").replace(([char]10).ToString(),"")
	
	#parse Correlation ID
	$a = $res.Content.indexOf("Correlation ID:")
	if ($a -gt -1) {
		$corrpage = $res.Content.Substring($a+15,37).Trim().replace(([char]13).ToString(),"").replace(([char]10).ToString(),"")
	}
	
	#parse assembly name "Microsoft.Office.DataParts"
	$owc = $res.Content.indexOf("Microsoft.Office.DataParts")
	if ($owc -gt 0) {$owcfound = $true} else {$owcfound = $false}
	
	#HTTP headers
	$corrhead = $res.Headers["SPRequestGuid"]
	$sper = $res.Headers["SharePointError"]
	if ($sper) {$sperfound = $true} else {$sperfound = $false}
	
	#format output
	$f = $s.Url -replace("/","-")
	$global:coll += New-Object -TypeName PSObject -Prop (@{"Type"=$type;"URL"=$url;"HTTP"=$statuscode;"Title"=$title;"CorrelationIDPage"=$corrpage;"OWCFound"=$owcfound;"SharePointError"=$sperfound;"CorrelationIDHeader"=$corrhead});
}
#configuration
$sites = Get-SPSite -Limit All | Get-SPWeb -Limit All
$c = $sites.Count
$i = 0
$global:coll = @()
#loop each site
foreach ($s in $sites) {
	#display progress
	$i++
	$surl = $s.url
	Write-Progress -Activity "HTTP test " -Status $surl -PercentComplete (($i/$c)*100.0)
	
	#run HTTP tests
	HttpQA "$surl" "HomePage"
	HttpQA "$surl/_layouts/viewlsts.aspx" "Viewlsts"
}
#save results
$global:coll | Export-Csv http-qa.csv -NoTypeInformation

Wrap Test-SPContentDatabase with database name

 

While testing an upgrade to SharePoint 2013 I wanted to review missing server dependencies.   Test-SPContentDatabase does a great job.   However, it doesn’t output the SQL content database name.   That makes it hard to read output on a large farm with many content databases.   By leveraging a custom variable for database name we can augment output with an additional column showing the parent database name for each issue.    Hope you find this helpful!   

shades_smile

 

Code

Get-SPDatabase |? {$_.Type -like 'Content*'} |% {$n=$_.Name; $_} | Test-SPContentDatabase | Select *,{$n}

 

References

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲