SQL: Query to get Database Role Members

Script used to get the roles in the database and their members.

create table ##RolesMembers
(
    [Database] sysname,
    RoleName sysname,
    MemberName sysname
)

exec dbo.sp_MSforeachdb 'insert into ##RolesMembers select ''?'', '''' + r.name + '''', '''' + m.name + ''''
from [?].sys.database_role_members rm
inner join [?].sys.database_principals r on rm.role_principal_id = r.principal_id
inner join [?].sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = ''db_owner'' and m.name != ''dbo'' -- you may want to uncomment this line';

select distinct * from ##RolesMembers
order by [Database], [RoleName]


----------------------

select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id

SSRS: Refreshing fields not taking effect after changing the stored procedure on the DataSet

I am modifying one RDL with a dataset calling a stored procedure. I changed the stored procedure for the dataset. Refreshing fields does not take any effect.

Solution:

a.
BIDS uses a data cache when designing reports to speed up the development and improve design-preview iterations.

To turn off the feature, modify the config file that controls the Report Designer in BIDS.

Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config

Change CacheDataForPreview to “false” , if you want to turn it on, just change CacheDataForPreview to “True”


b. Change the Query Type to Text and provide the parameter values on the EXEC call to the stored procedure.

Web: How to get the exact error on an IIS site

You will get the above error on windows 2008 even if you have set CustomErrors=Off in the web.config file .
To get the actual error on the remote machine or remote browsers
Double click on the website in IIS 7.x.
Double click asp.
Double click debugging properties option and set send Errors to browser equal to True and click apply.
Double click on the website.
Double click error pages.
Click Edit feature settings.
Check the radio button “Detailed Errors” and click ok.

SQL: How to recreate a SQL login without you knowing the password

Issue:

No one in the office remembers the password of a SQL login. I needed to create the SQL login for an application that uses it. The password is not stored on any of the web config files.


Resolution:

On the old DB server, select the "password hash" of the login.

select LOGINPROPERTY('sqluser', 'PasswordHash' )
Take the PasswordHash value.
ex. 0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC

On the new DB server, create the login:
CREATE LOGIN [sqluser] WITH PASSWORD=0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC Hashed;

SQL: DMVs

 Got this from the web:

To identify the most costly queries.


SELECT TOP 20  qs.sql_handle,
 qs.execution_count,
 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 qs.total_worker_time/1000000,
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 qs.total_elapsed_time,
 total_elapsed_time_inSeconds = --Converted from microseconds
 qs.total_elapsed_time/1000000,
 st.text,
 qp.query_plan
from
 sys.dm_exec_query_stats as qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
 cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc



http://forums.iis.net/t/1146821.aspx?w3wp+exe+high+cpu+usage

SQL: How to Identify Fragmented Indexes

(To be modified later...)



SELECT OBJECT_NAME(OBJECT_ID) as tableName,index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), Object_Id(‘Emp’), Default, Default,Default)

avg_fragmentation_in_percent :-This column value represents the %age of logical fragmentation of indexes or extent fragmentation of heap.

avg_page_space_used_in_percent:-This column represents the internal fragmentation within the page.


If avg_fragmentation_in_percent(%age) >5 and avg_fragmentation_in_percent <30 % then we should re-organize the indexes to minimize the fragmentation.


--------------------------------------------------

Script to re-organize index:
alter index xx on emp reorganize

To reorganize particular index(“xx”) on Emp table
alter index all on emp reorganize

To reorganize all the indexes on emp table.

If avg_fragmentation_in_percent(%age) >30 then we should re-build the indexes to minimize the fragmentation.

To rebuild all the indexes on emp table.
alter index all on emp rebuild
To rebuild particular index(“xx”) on Emp table
alter index xx on emp rebuild


 ------------------------------

SELECT index_id,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(‘AdventureWorks’),
OBJECT_ID(‘AdventureWorks’),
NULL, NULL, ‘DETAILED’
)


-------------------------------------


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

------------------------------

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC


Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.


-----------------------------------------


DECLARE @DATABASE VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
SET @DATABASE = 'SQLServerPlanet'
SET @TableName = 'Users'
SET @IndexName = NULL

SELECT
    avg_fragmentation_in_percent
    ,page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(@DATABASE)
    ,OBJECT_ID(@TableName)
    ,OBJECT_ID(@IndexName)
    ,NULL
    ,NULL
)

----------------------------------


Script recently used in checking for fragmented indexes/heaps:


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
 FROM sys.dm_db_index_physical_stats (DB_ID('winips'), NULL, NULL, NULL, NULL) AS indexstats
 INNER JOIN winips.sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
 INNER JOIN winips.sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
 INNER JOIN winips.sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
 AND indexstats.index_id = dbindexes.index_id
 WHERE indexstats.database_id = DB_ID('winips')
 ORDER BY indexstats.avg_fragmentation_in_percent desc

SharePoint: Cannot complete this action after installing Service Pack 2

(to be continued...)

Check the SharePoint Central Administration site in IIS. Go to Authentication. Enable Impersonation

SharePoint: STSADM Failure Occurred during the processing

(to be continued...)

–Click on “start” button of the windows server 2008 and open “control panel” window
–Now in the “control panel” window, on the top left hand side we will see “classic view”, click on that
–Now we have icon, open “user accounts”
–Just under “make changes to your user account”, you will see “turn user account control on or off”. Click on that
–You will get a window asking you to click “continue”, click “continue” on that prompt
–Now you will see a check box saying “use user account control (UAC) to help protect your computer”
–Uncheck the option and click “Ok”
–After that you will be prompted to “Restart Now” you server. Save you work and restarted you server.
–After Restarting try to run the “stsadm” command and you will see it will run normally.

SharePoint: How to Fix "Failure trying to synch web application"

Error message on Event Log:


Failure trying to synch web application 986c87c1-66b0-4655-972f-f62a32fcd431, ContentDB 91725678-be7e-4c0b-b4d8-db7c747875b2 Exception message was A duplicate site ID 2631c92c-92dc-44b2-ae6d-1b7b8ecb8ad5(http://someserver:41772) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.

Resolution:

• Verify all the data has been synchronized for the user profiles. (On my sample - 2 days)

          Stsadm -o sync -listolddatabases 2

• If old data exists, run the following command

         Stsadm -o sync -deleteolddatabases 2

 

SQL: Sample Cursors

declare @sqlhandle as varbinary(64)
DECLARE sqlhandle CURSOR FOR
select most_recent_sql_handle from sys.dm_exec_connections ec1
open sqlhandle
fetch next from sqlhandle into @sqlhandle
while @@FETCH_STATUS = 0
begin
     begin try
           insert into #temp(sqltext)
           select text from sys.dm_exec_sql_text(@sqlhandle)
    end try
    begin catch
           print @sqlhandle
    end catch
    fetch next from sqlhandle into @sqlhandle
end
close sqlhandle
deallocate sqlhandle

SQL: Script to Identify Blocking

Options:

• Use Activity Monitor through SQL Server Management Studio
• Execute sp_who2 to identify blocking ID.  (Refer to BlkBy column.)

Found this script on the web:



SELECT  db.name DBName,
 tl.request_session_id,
 wt.blocking_session_id,
 OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
 tl.resource_type,
 h1.TEXT AS RequestingText,
 h2.TEXT AS BlockingTest,
 tl.request_mode
 FROM sys.dm_tran_locks AS tl
 INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
 INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
 INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
 INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
 INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
 CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

SQL: How to fix "Cannot open user default database"

I set my default database to offline, and I could not log in again to the database server saying "Cannot open user default database." To fix this, assign another database to the login.

Open command prompt.
If you are using Windows authentication, type the following:

sqlcmd -E -S <instance name> -d master

ex.
sqlcmd -E -S someservername\someinstance,someport -d master

If you are using SQL Server authentication:
sqlcmd -S <instance name> -d master -U <sqllogin> -P <password>

At the sqlcmd prompt, type the following:

alter login <sql login that you want to fix> with default_database = <an existing DB or master>

ex.
alter login [somedomain\someusername] with default_database = master
go

SQL: How to Resolve "Alter Database Failed because a lock could not be placed on database"

I was trying to set a database offline, and I encountered this error:

It's possible that a connection was not terminated.
To fix, identify the process that causing the lock on the database.

exec sp_who2

and kill the process

kill <spid>

SharePoint: How To Move Databases to a New DB Server

Back up SharePoint visa the built-in tools prior to performing the steps.
Back up all SharePoint databases.

Move SharePoint_AdminContent database (Central Administration)

• Put the database offline. Go to Central Administration > Application Management > Content Databases > (select proper web application) - SharePoint Central Administration, and change database status to Offline. DO NOT tick "Remove content database".
• Prepare to move

stsadm -o preparetomove -contentdb <current db server\current db instance,port>:<current SharePoint_AdminContent database name> -site  http://<current SP central admin site with port no>

• Disconnect the DB

stsadm -o deletecontentdb -url "http://<current SP central admin site with port no>" -databasename "<current SharePoint_AdminContent database name>"

• Restore the DB to the new database server. This time around, rename to DB (get rid of the GUID) to something that's easier to identify/understand.
• Attach the DB back to Central Administration

stsadm -o addcontentdb -url http://<current SP central admin site with port no> -databasename "SharePoint_AdminContent" -databaseserver "<new database server>"

• iisreset /noforce
• Run Configuration Wizard

Move SharePoint_Config database

• Disconnect DB from the server
  psconfig.exe -cmd configdb -disconnect
• Backup SharePoint_Config and restore to the new server.
• Connect database back to sharePoint farm
  psconfig.exe -cmd configdb -connect -server "<new db server>" -database "SharePoint_Config" -user "domain name\username" -password "password"
• Run Configuration Wizard and provision new Central Administration. Choose "No, do not disconnect", and click Next. Change the port to <some port used as before>, NTLM, click Next.

Move other SharePoint Content databases

• Restore WSS_Content to the new database server.
• Go to Central Administration > Application Management > Content Databases
• Change the web application from the drop down list as appropriate.
• Set it to offline, and check Remove Content Database..
• Once done, click  "Add Content Database" and add the newly-restored database from the new database server.

Restore SSP

• Restore SharedServices1_DB to the new DB server.
• Enable indexers in Central Administration. Go to Operations > Servers on Farm > Office SharePoint Server Search.
• On Shared Services Administration, click on New SSP. Name it something else. Create a new web application. Point all the databases to the new database server. Create new databases.
• After a successful creation, you should be able to see two Shared Services. Change Default SSP to the new one. Delete the old Shared Services. To delete, hover over SharedServices1, and on the drop down menu, select Delete. Check "Remove without deleting the associated database." (NOTE: if you delete the associated database, you will lose all your existing profile information and audiences.) Once the SSP is deleted, the web application associated to it will be assigned under the default SSP.
• Restore SSP. You can assign a name same as the deleted one - so you won't get confused. But it doesn't have to. Select the web application that was previously associated with the deleted SSP. On the SSP database, select the previously restored database - SharedServices1_DB. (NOTE: when restoring, the DB should already exist.) On the Search database, you can enter a new DB name or same as the old one. This search DB will be created.
• iisreset /noforce
• Run Configuration Wizard.
• Check the newly restored Shared Services Portal (SSP). Ensure that the content sources, rules, scopes are there - just like before.
• Run full crawls.
• Delete the new SSP you created on Step 3.
• Delete the web application associated to the newly-created SSP. Central Administration > Application Management > Delete Web Application

Correct Configuration Database

• After doing all the steps above, you'll still find in Central Administration that your default configuration database is the old DB server. Open SharePoint_Config database on the new DB server.

select * from objects where name = 'SharePoint_Config'

Note down the ID of the other row where parent ID is not equal to its own. Let's call this ID1.

Id	ParentId	Name	Remarks
91C0FDF7-4B52-4DE6-A336-010F1AE3C921	B57DC01E-40D5-4FB6-A8A4-9E58E19ECCC0	SharePoint_Config	ID1
AF086F84-9931-4C0D-9AB5-342103B2272D	AF086F84-9931-4C0D-9AB5-342103B2272D	SharePoint_Config

• Get  ID fo the new SQL Server. Let's call this ID2.

Id	ParentId	Name	Remarks
F9209CAB-4E05-4202-A262-8F696C40FF49	AF086F84-9931-4C0D-9AB5-342103B2272D	<new sql server>	ID2

• Select ID from objects where parentid = ID2. Note the ID as ID3.
  

  Id	ParentId	Name	Remarks
  2B496A84-8202-4421-B966-E2DBAB8469B0	F9209CAB-4E05-4202-A262-8F696C40FF49	<new DB instance,port>	ID3

• Replace the IDs on the statement. Update objects set parentid = ID3 where ID = ID1

Still the database will still be listed on the "Servers in the Farm".  But it's no longer the default configuration server. To remove the old database server,

• Select * from objects for any references on the old DB server
  

  Id	ParentId	Name	Remarks
  3B55AC46-8729-494C-A1D5-546EFCFC577E	AF086F84-9931-4C0D-9AB5-342103B2272D	<old db server>
  B57DC01E-40D5-4FB6-A8A4-9E58E19ECCC0	3B55AC46-8729-494C-A1D5-546EFCFC577E	<old db instance, port>

• Select * from dependencies where object ID is  any of the above IDs
• select * from objects to get the corresponding ID of the DB instance and port
  

  Id	ParentId	Name
  2B496A84-8202-4421-B966-E2DBAB8469B0	F9209CAB-4E05-4202-A262-8F696C40FF49	<new DB instance, port>

• Update dependencies  set the object ID to the new one.

 After performing the above step, you will be able to remove the server from the farm.

SharePoint: How to fix: Shared Services Provider creation failed (User cannot be found)

I tried creating a new Shared Service Provider, and it's giving me an error: Shared Services Provider creation failed Reason: User cannot be found.  I looked at the logs (c:\program files\common files\microsoft shared\web server extensions\12\logs), and there revealed the error.

• Go to SharePoint Central Administration > Application Management
• Go to Site Collection Administrators under SharePoint Site Management
• Update the Primary (and/or secondary) Site Collection Administrator accounts.
• Execute the following command: stsadm -o execadmsvcjobs

Verify the status of the SSP by clicking on the "Shared Services Administration" link.

SharePoint: is an invalid or loopback address. Specify a valid address.

The fix:

Use servername\default instance, port number.
example AUHDC-SQL1\MSSQLSERVER,1440

MVC: Data Validations

Entity Framework
- part of the .Net framework
- allows you to access a relational database with strongly-typed LINQ queries, or VB code, or really any .Net language.

LINQ - Language Integrated Query
- feature that's in both C# and VB.

Ways to start with EF

• Schema First - you open up a graphical designer in VS, point it to an existing database, and it can import the database schema, and generate all the classed you need to query and update that database.
• Model First - you use the same graphical designer in VS to draw a conceptual model for your app, what classes you want. EF will generate both your class definitions and the database schema.
• Code First - you write C# classes and the EF can use those class definitions to create a database for you.

Notes:
- create the classes for entities in Models folder. You may consider having a separate folder or even a separate project where you define the entities.
- create a class to persist and retrieve this data. It needs to derive from an Entity Framework class known as DBContext. Add DBSets.

public class OdeToFoodDB : DBContext
{
     public DBSet<Restaurant> Restaurants {get; set;}
     public DBSet<Restaurant> Restaurants {get; set;}
}

On the Home Controller, instantiate your new class, and point out that this is a disposable resource.

OdeToFoodDB _db = new OdeToFoodDB();

protected override void Dispose(bool disposing)
{
     if (_db != null)
     {
          _db.Dispose();
     }
     base.Dispose(disposing);
}

In your view, Index.cshtml

@model IEnumerable<OdeToFood.Models.Restaurant>
@foreach (var item in Model)
{
    <div>
            <h4>@@item.Name</hr>
           <div>@item.City, @item.Country</div>
    </div>
}


public ICollection<RestaurantReview> Reviews {get; set:}

MVC Basics

(Pre-requisite: Install Visual Studio Express for Web with Windows Azure SDK through Windows Platform Installer)

These are my notes from my online training in Pluralsight.

MVC - design pattern for building interface; abstraction layer
Purpose: To separate the responsibilities of your components in your user interface layer.

• Model
• View - used as templates to control the placement of data. Important part to avoid scripting vulnerabilities
• Controller - does all the hard work. Relatively plain C# classes. You can instantiate them without having a web server or a web browser or an HTTP request running. Target of your external stimulus.

MVC: Razor Views

Razor Templates
    - used to produce HTML with cshtml extension.
    - templates that consist of HTML markups.
--------------------------------------------

@ - C# code on cshtml, similar to Response.Write
Razor will automatically HTML encode any output sent through the @sign to help prevent cross site scripting attack (XSS)

--------------------------------------------

Foreach statement - allows us to iterate through the model pulling out one item at a time


@foreach (var item in Model){
<tr><td>
      @:Review => tells Razor that this is a literal text, and not part of C# code expression
      @item.Rating  => implicit code expression in Razor
      @(item.Rating / 10) => (ex: you want to divide by 10) explicit code expression with parenthesis
      R@(item.Rating) => if you want to prefix the value with R.
      @@Test => to escape the @sign, and output the text literally. intended to do for a Twitter handle.
      @Html.DisplayFor(modelItem => item.Rating)  =>  usual

</td></tr>
<tr><td>
     @Html.ActionLink("Edit", "Edit", new { id = item.Id})  => text, action, ID wrapped up in anonymous object

</td></tr>
}


On the controller:

public ActionResult Edit(int id)
{
     var review = _reviews.Single(r => r.Id == id); => give me a single object out of this "_reviews" collection that matches this criteria.
     return View(review);
}
--------------------------------------------

Code Blocks - code inside of the block is going to execute. It's not going to have its expressions evaluated and written to the client. It simply executes and produces side effects.
You can also declare a variable and assign a value.

@{
    ViewBag.Title = "Index";
    var firstReview = Model.First();
}
@firstReview.Name

--------------------------------------------

@Html.Raw(item.City) - displays the text or value as is. Not encoded.


--------------------------------------------

Comments use @* and *@

You can comment a section of code by selecting the code and hitting Ctrl + K, Ctrl + C

To automatically reformat your code, to fit right into the file or align property Ctrl + K , Ctrl + D

--------------------------------------------

File extensions:
.cshtml => C# Razor
.vbhtml => VB Razor
.aspx, .ascx => Web Forms (legacy)

--------------------------------------------

Layout with Razor

Views > Shared > _layout.cshtml
The underscore is just a convention to identify views that are not primary content views like the index view that we wrote.

This is just another Razor view. It has literal text that can have C# code expressions, and can have code blocks, where you typically have your doctype, and where you want to have your head tag, and you typically have a definition for the body element. Any changes will be reflected across the entire application. This is the only layout view we'll be using. You can have multiple layout files, but only 1 can be used by the application.

<!DOCTYPE html>

Use inherited methods to specify content areas
RenderBody
RenderSection - optional, provides a content view like index.cshtml

Views > _ViewStart.cshtml = determines the view to be used by MVC. This has the ability to execute this code before my view starts rendering and it sets this property.

@{
     Layout = "~Views/Shared/_Layout.cshtml";
}

Anything you put in that code block at the top will be able to execute before the view does.

If you want to have another layout for another folder, just create a new _ViewStart.cshtml on the folder, and define the layout file to be used.

Set Layout to null if you don't want to use a layout page.

@{
     Layout = null; => can also be set on the cshtml file.
}

@RenderSection("featured", required: false)  => not a required section, which means a content view can have this section that cannot ...

in index.cshtml file:

@section featured{
      We are showing the latest @Model.Count() reviews
}

--------------------------------------------

HTML Helpers
- Make it easy to create small blocks of HTML. To keep views simple.

HTML is a property of the ViewPage base class.
- Create inputs
- Create links
- Create forms

@Html.BeginForm()
     - equivalent to <form action ="Reviews/Edits/3 <something else>" method = "post"> post means to go to the URL where we came from when the user clicks on the button.

@Html.HiddenFor
     <input type="hidden" .../>

@Html.LabelFor(model => model.Name)
     - great for accessibility
     <label for = "Name">Name</label>
     for is equivalent to the name of the property.

@Html.EditorFor(model => model.Name)
     - saying "I want an editor for this property."
     <input class="<name for the CSS class>" id = "Name" type = "text" .../>
    type can be a text or number
@Html.ValidationMessageFor(model => model.Country)


[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
     var review = _reviews.Single(r => r.Id == id);
     if (TryUpdateModel(review))  ==> to go through a process known as model binding. Model binding happens anytime you even have a parameter in an action method. This relies on the names of the properties to match up data with what needs to get pushed into the model.
     {
          //part where the data should be saved into the database. (but on this sample - redirect to another page where user can view the changed result)
          return RedirectToAction("Index");

     }
     return View(review);
}


--------------------------------------------

Partial Views
- Render a portion of a page
- Still .cshtml files and can still be strongly typed, still has a model that it can work against
- Allow you to put HTML on C# code into a file that you can reuse across multiple other views.
- To simplify a view


On the sample, the partial view is named _Review.cshtml.

When you select "Create as a partial view" on the Add View dialog box, it won't automatically add a code block at the top of the view to set the page title.

Note that this will be available only to other views that are in the same folder. If this is placed in the Shared folder, it would be available anywhere in the application.

Sample:
@model OdeToFood.Models.RestaurantReview
<div class="review">
    <h4>@Model.Name</h4>
    <span>@Model.Rating</span>
    <span class="right">@Html.ActionLinkEdit", "Edit", new {id=Model.Id})</span>
</div>

On your main view:

@foreach var item in Model)
{
     @Html.Partial("_Review", item)      ==> no need to specify the file extension
                                                               ==>  model to be passed
}
<p>
     @Html.ActionLink("Create New", "Create")
</p>


--------------------------------------------

@Html.Action vs @Html.Partial

_Layout.cshtml ==> executes for every page in the application.

To show a view on every page of the application through Layout view:

<div>
    @Html.Action("BestReview", "Reviews")   ==> sets up a sub request inside of this primary MVC request that can go out and call another controller action
==> action
==> Reviews controller
</div>

On the ReviewsController, create a new BestReview action.


[ChildActionOnly]   ===> attribute that prevents the action from being called directly using a HTTP request. Can be accessed by calling it as a child request.
public ActionResult BestReview()
{
     var bestReview = from r in _reviews
                                  orderby r.Rating descending
                                  select r;
     return PartialView("_Review", bestReview.First());  ==> PartialViewResult is a type of ActionResult
===> pass the partial view created. no extension needed here.
===> pass the first review that we picked up
}

@Html.Partial - which allows you to simplify a view and reuse HTML.
@Html.Action - which allows you to set up a completely independent subrequest that builds its own model and renders its own PartialView.

MVC: Controllers

Action Filters
- apply pre and post processing logic to a controller action and its result.
- components that you want to apply cross cutting logic - the logic that has to execute across multiple controller actions but you don't want to duplicate code inside of individual controllers.

• OutputCache

         - tells the runtime that it's allowed to cache the final output of some action and to use that cached result to service future requests.

• ValidateInput

        - turn off request validation and allow dangerous input

• Authorize

         - allows you to ensure a user is logged in and perhaps in a specific role like an admin.

        [Authorize (Roles="Admin")]
        or

        [Authorize]
        public ActionResult Search(string name = "French")
        {
               var message = Server.HtmlEncode(name);
               return Content(message);
         }
         - which means a user has to be logged in for him to be able to use the search action. You'll be redirected to a login screen if you're not logged in.

        Action filter can also be placed on a controller to apply to all the functions within the Controller class.
         [Authorize]
        public class CuisineController : Controller
         {
                   public ActionResult Search(string name = "French")
                  {
                         var message = Server.HtmlEncode(name);
                          return Content(message);
                   }
          }

• ValidateAntiForgeryToken

       - helps prevent cross site request for forgeries

• HandleError

       - can specify a view to render in the event of an unhandled exception


Global Filters
- typically registered during application start in Global.asax.cs.

protected void Application_Start()
{
     FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
}

RegisterGlobalFilters is inside FilterConfig.cs under App_Start folder.

public static RegisterGlobalFilters(GlobalFilterCollection filters)
{
    filters.Add(new HandleErrorAttribute());
}
HandleErrorAttribute - will be in effect for every single request that is processed by any controller inside of your application. Its purpose is to display friendly error page to users when something goes wrong.

SQL: BCP

Sample

In one batch file named "test.bat", perform the BCP to extract the records from the database through a stored procedure:

bcp "EXEC get_she_codes %1" queryout "%2.csv" -c -t, -S localhost -T

In another batch file, call test.bat to create the csv file: From here, you can call test.bat multiple times using different parameters:
call test.bat 1039646 JennyCompany-%date:~-4,4%%date:~-10,2%%date:~-7,2%
Output file:
JennyCompany-20153007.csv

Windows: Random Notes

Common DOS commands:

msinfo32 => shows you the system information

MVC: Routing Basics

The convention is to map URLs to a particular action (a method) on a particular controller. The action then executes (usually, but not always) returns an instance of ActionResult. The ActionResult handles Framework logic such as rendering to HTML or JSON, and writing to the HTTP response that will be returned to the user's browser.

Basic version: http://mydomain/controllername/methodname

Web: Random Notes

URL Paths

Two types of Paths:

• Absolute - starts with a leading slash
• Relative - does not start with slash. Location depends on where you are using them

default.asp is: /pages/default.asp - if called from /pages/linktool.asp

Two special directory names:
. means current directory
.. means parent directory

../default.asp is /pages/default.asp if called from /pages/tools/linktools.asp
../../default.asp is  /pages/default.asp if called from /pages/admin/tools/linktools.asp

~ tilde means root of the application
------------------------------------------------------------------

Nullable value types

private int? _copid;
protected double? d = 5;

The variable can be assigned "null". Normally primitives like "int" and "double" can't be null.

------------------------------------------------------------------

Null coalescing operator

x = y ?? z

x is assigned y's value. If y is null, then it's assigned z's value.

------------------------------------------------------------------
TryParse Method

Syntax: public static bool TryParse(string s, out int result)


if ( int.TryParse( itemId, out parsedItemId ) && parsedItemId > 0 )

Meaning: Parse itemId, assign it to parsedItemID as int. If parsedItemID is int and greater than 0 then...

------------------------------------------------------------------

(if) ? then : else

var objectInstance = condition ? foo : bar;

userType = user.Type == 0 ? "Admin"
         : user.Type == 1 ? "User"
         : user.Type == 2 ? "Employee"
         : "The default you didn't specify";


------------------------------------------------------------------

Web.Config - Handling errors

Default value = RemoteOnly - which shows a pretty error page to remote users.

<system.web>
<customErrors mode="On">
</system.web>

Error message:
Error. An error occurred while processing your request.

Error view - just a razor view where you can modify it to include other information.
Views > Shared > Error.cshtml

TSQL: For XML Path

To retrieve results of a SQL query as XML, specify FOR XML clause in the query.
FOR XML has PATH as one of its modes. It provides a simpler way to mix elements and attributes.

In PATH mode, column names or column aliases are treated as XPATH expressions, which indicate how the values are being mapped to XML.

To better illustrate the use of FOR XML PATH, I have this table #tmp:

create table #tmp
 (num int)

 insert into #tmp
 values(1),(2),(3)

Script	Output
SELECT num from #tmp FOR XML PATH	<row><num>1</num></row><row><num>2</num></row><row><num>3</num></row>
SELECT cast(num as varchar) from #tmp FOR XML PATH	<row>1</row><row>2</row><row>3</row>
SELECT num from #tmp FOR XML PATH ('')	<num>1</num><num>2</num><num>3</num>
SELECT cast(num as varchar) from #tmp FOR XML PATH ('')	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	,1,2,3
select (SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)')	,1,2,3
select stuff((SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)'), 1, 1, '')	1,2,3

I just used my samples on one of my query requirements. More examples in Books Online.

SQL: Random Notes

How to set the database from Single_User to Multi_User

ALTER DATABASE <databasename>
SET MULTI_USER;

If you're working on SQL Server Management, and one query window is connected to the database you'd like to alter, switch the database to master and run the alter query again.

Or to Single_User:
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE


To set the recovery model to simple:
ALTER DATABASE <database name> SET RECOVERY SIMPLE WITH NO_WAIT


Get some local system information:

SELECT NAME as "Server" FROM SYS.SERVERS; -- list of servers including linked servers
SELECT HOST_NAME() as "Host";
SELECT @@version as "Version";


To get some backup information - when the database was last backed up

 select database_name, type, backup_size, backup_start_date, backup_finish_date, DATEDIFF(dd,backup_start_date,getDate()) as "days"
from msdb.dbo.backupset s
where s.backup_set_id in
(
select max(s1.backup_set_id)
from msdb.dbo.backupset s1
where s1.database_name = s.database_name and s1.type = s.type
) and s.database_name in
(
select name
from msdb.sys.databases
)
order by database_name, type;

Log space
 DBCC sqlperf(logspace);


Database size

 select s.instance_name 'database', s.cntr_value DataSize, (select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Size (KB)')) LogFileSize,
(select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Used Size (KB)')) LogFileUsedSize
from sys.sysperfinfo s
where s.counter_name in ('Data File(s) Size (KB)')
and s.instance_name not in ('_Total', 'mssqlsystemresource')
order by s.instance_name;