No exports were found that match the constraint vs2012

I installed VS 2013 Express edition over the weekend and suddenly when I opened my VS 2012 I got this version. I got rid of this error by deleting the following folder.

%AppData%\..\Local\Microsoft\VisualStudio\11.0\ComponentModelCache

If you have a VS 2013 version and getting the same error you can remove the following folder and restart your Visual Studio and you should be good to go..

%AppData%\..\Local\Microsoft\VisualStudio\12.0\ComponentModelCache

Advertisements
Posted in .NET Framework | Leave a comment

Postman–A new tool to unit test Web APIs

I was going through some articles on the Web API and I found a tool “Postman” which can be used to test our APIs.

Postman is a Chrome extension that helps us building HTTP requests with parameters, headers, authentication, and so on. It sends the request and reads the response.

We can use this for testing our APIs without creating any unit tests and it is a free tool J.

More info at http://www.getpostman.com/ .

Screen shot of GET Operation:

clip_image002

Screen shot of POST Operation :

clip_image004

Posted in Interesting Stuff | Leave a comment

Unexpected results when querying a SQL View

The other day I was trying to run a select statement from a view in SQL Server and I got some unexpected errors with the data types in the result set from the view with respect to the base table. When I dig deeper into the view I found a SELECT *  in one of the queries used for defining the view. As well know that SELECT * is bad for performance reasons because of the extra I/O s required to read the unnecessary columns and should be avoided. On further observation we identified that the table on which SELECT  * was there has recently gone a change in terms of new columns , renaming of columns . Are these the reason for my inconsistency in data types. To understand the problem correctly lets run the following in SSMS.

CREATE DATABASE TEST
GO

CREATE TABLE TestTable
(
    ColumnA int ,
    ColumnB int
)

CREATE VIEW MyView
AS
SELECT * FROM TestTable -- Not a good practice

Now insert some records and verify the view

INSERT INTO TestTable VALUES(1,1)
INSERT INTO TestTable VALUES (2,2)


SELECT * FROM MyView



image

Now rename the “ColumnB”  to “Column_Renamed” and  changing the datatype of column B to varchar as below.

EXEC sp_rename 'dbo.TestTable.ColumnB','Column_Renamed', 'COLUMN';
GO

ALTER TABLE TestTable ADD ColumnB varchar(10) NOT NULL DEFAULT 'Test'
GO

Now run the query to select from the View

SELECT * FROM MyView

You will see the same results as before i.e the new column addition and changing of data types are not reflected in the view and the Column B still shows as integer.

image

How to avoid this scenario. There are 2 ways you can approach this problem.

1. Use SP_RefreshModule to refresh the View.

EXEC sp_refreshsqlmodule 'dbo.MyView'
GO

SELECT * FROM MyView

image

We have to call this for each view dependent on the changed column and if we miss any view then we will get the same unexpected or invalid results as defined above.

2. Use WITH SCHEMABINDING option while creating the view and explicitly selecting the columns from the table. This option will proactively error out if we are doing any datatypes changes in the table without dropping the view.

CREATE VIEW MyView WITH SCHEMABINDING AS 

SELECT ColumnA,ColumnB FROM dbo.TestTable  -- Not a good practice

GO;
EXEC sp_rename 'dbo.TestTable.ColumnB','Column_Renamed', 'COLUMN';
GO
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497
Object 'dbo.TestTable.ColumnB' cannot be renamed because the object participates in enforced dependencies.

Here first you have to drop the view and then modify the columns and then recreate the view. This is more of a proactive approach .

Hope this helps.

Posted in Interesting Stuff | Leave a comment

Identity vs SCOPE_IDENTITY vs IDENT_CURRENT

It is a common practice to use IDENTITY value for columns which are simple integer increments. We can also specify what is the initial value and how we want to increment using seed values. In some cases the client applications needs to know what is the IDENTITY value inserted into the table. Consider a scenario of a new customer ordering some products in an ecommerce application. In this case we have to do the following things.

  1. We have to insert the Customer (assuming there is a IDENTITY column set for CustomerID)
  2. Select the CustomerID that was inserted in Step 1 and associate with Order in client application and  insert the Order record (assuming we have a IDENTITY value set for OrderID Columns as well)
  3. Select the OrderID from Step 2 and insert multiple records into the OrderDetail table

Before 2008, this simple process involves multiple round trips to the database and application for transferring the identity values. With the advent of Table Valued Parameters, now we can encapsulate all this information into a tables and sent to Stored procedures there by reducing the round trips. But still there exists one problem in getting the IDENTITY values for different tables. As we all know , for example, we cannot get consecutive values for IDENTITY if we have concurrent transactions running, can have gaps in IDENTITY if the server restarts etc. In the previous scenario of Cust-Order-OrderDetail we can get the last inserted value using @@IDENTITY function. This function gives you the last inserted  regardless of the scope. There is one more function called SCOPE_IDENTITY(). This function returns the last inserted value within the same scope. To clearly understand the distinction between these 2 lets fire up SSMS and run the following.

Create 3 tables : Customer, Order and Email( this will be used for trigger)

CREATE DATABASE TEST
GO

USE TEST
GO

IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
  DROP TABLE dbo.Customer
 

IF OBJECT_ID('dbo.Ordertbl', 'U') IS NOT NULL
  DROP TABLE dbo.Ordertbl
  

  IF OBJECT_ID('dbo.Email', 'U') IS NOT NULL
  DROP TABLE dbo.Email
  


CREATE TABLE dbo.Customer
(
CustID smallint IDENTITY(100, 10) NOT NULL,
CustName varchar(50) NOT NULL
);

CREATE TABLE dbo.Ordertbl
(
    OrderID INT IDENTITY(10,10) NOT NULL,
    CustID smallint,
    OrderDate datetime
);

CREATE TABLE Email
(
    ID INT IDENTITY(10,50) NOT NULL,
    EmailMsg NVARCHAR(100)
)

Create a new trigger which will insert into the Email table for each successful order placed.

CREATE TRIGGER SendMail
ON Ordertbl
FOR INSERT AS 
   BEGIN
   INSERT INTO Email VALUES ('Order placed successfully')
   END

Now insert records into Customer and Order and observe the values for SCOPE_IDENTITY and @@IDENTITY

/*Insert into the Customer-Order table and observe the identity values 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
BEGIN
INSERT INTO customer VALUES('Bill Gates')
INSERT INTO customer VALUES('Steve Jobs')

INSERT INTO Ordertbl VALUES(100,GETDATE());
INSERT INTO Ordertbl VALUES(110,GETDATE());


end


SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

image

As you can see , there are 2 scopes with in the insert statements above. One, inserting into Customer and Order table and other inserting into Email table because of trigger. Thus the SCOPE_IDENTITY() returns the last identity value inserted , in this case (10+10=20 ; since we declared the initial seed as 10 and increment as 10 and inserted 2 records) and @@IDENTITY returns the last identity value inserted regardless of scope. i.e. from Email table (10+50=60).

But how will we identify the last inserted record in the specific table regardless of scope and session ?

We can use  IDENT_CURRENT in that case

SELECT IDENT_CURRENT('Customer');

To test this, open a new session window and run the following query

USE TEST
GO

INSERT INTO customer VALUES('Larry Elison')

Insert into Ordertbl VALUES(120,GETDATE())

Now if you come back to session window 1 and execute the following

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT IDENT_CURRENT('Ordertbl') AS [IDENT_CURRENT]
GO

The result is as follows

image

So use

IDENT_CURRENT : if you want to know the last inserted value into the table regardless of connection

SCOPE_IDENTITY : Know the last inserted value into the table with in the current session

@@IDENTITY: Use only if there are no triggers and  no concurrent users

 

Hope this article helps.

Posted in SQL Server | Leave a comment

Parameter passing in C#

Many people are confused on how parameters are passed as references specifically for reference types. Jon Skeet has a nice article which clearly explains the differences here. Same concepts explained with more pictures  here

Posted in .NET Framework, C# | Tagged , | Leave a comment

Update Panel within a Repeater Control

We all know Update Panels are used to make asynchronous post backs to server. But when we have some fairly sophisticated controls like GridView, Repeaters etc. the general tendency is to put these controls inside the update panel so on async post back (depending on the UpdateMode) we can refresh these controls. But what if there is a need only to update a single row or a control with in this grid view or repeater. In that case we can also put the update panel with in the GridView or Repeater. The following example demonstrates this behavior. In this example let’s assume you are an airport controller looking for status of various flights.  I have added the following code to simulate this scenario

</pre>
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RptrUpdatePanel2.aspx.cs" Inherits="AJAXinAction_1.RptrUpdatePanel2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<h1>Get Flight Status</h1>

<div>

<asp:XmlDataSource ID="data" runat="server">

<Data>

<stocks>

<stock name="United Airlines" value="STOCK1" />

<stock name="JetBlue" value="STOCK2" />

<stock name="Delta" value="STOCK3" />

<stock name="South West" value="STOCK4" />

</stocks>

</Data>

</asp:XmlDataSource>

</div>

<div style="width: 340px; border: 1px solid gray; padding: 5px;">

<asp:Repeater ID="Stocks" runat="server" DataSourceID="data" DataMember="stock">

<ItemTemplate>

<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">

<ContentTemplate>

<asp:Label runat="server" ID="FlightName" Text='<%# Eval("name") %>' />

<asp:Button runat="server" ID="btnStatus" Text="Get Flight Status" OnClick="UpdateStock_Click" />

<asp:Label runat="server" ID="FlightStatus" Text="" />

</ContentTemplate>

</asp:UpdatePanel>

</ItemTemplate>

<SeparatorTemplate>

<hr style="border: 1px dashed gray;" />

</SeparatorTemplate>

</asp:Repeater>

</div>

</div>

</form>

</body>

</html>
<pre>

The Design of the Code looks as follows.092513_2138_UpdatePanel1.png

And I am hard coding some code to show different status for each button as follows


protected void UpdateStock_Click(object sender, EventArgs e)

{

Button b = (Button)sender;

Label flight = (Label)b.NamingContainer.FindControl("FlightName");

Label status = (Label)b.NamingContainer.FindControl("FlightStatus");

status.Text = LookupStockprice(flight.Text);

}

private string LookupStockprice(string name)

{

string status = "$0.00";

switch (name)

{

case "United Airlines":

status = "On Time";

break;

case "JetBlue":

status = "Delayed by 5 min";

break;

case "Delta":

status = "Left Just Now";

break;

case "South West":

status = "Arriving";

break;

}

return status;

}

If you observe the code above I am putting the Update Panel inside the repeater control and when I click the button in each row, only that row gets updated and not all the rows  as shown below.

092513_2138_UpdatePanel2.png

Hope this helps.

Posted in .NET Framework | Leave a comment

ChildrenasTriggers in UpdatePanel

Recently I was reading about the UpdatePanel and I came across a property called ChildrenAsTriggers. As you all know UpdatePanels has 2 types of triggers

  1. AsyncPostbackTrigger
  2. PostBackTrigger

Both of these triggers cause a postback to server depending on the trigger type we use. But there is another property called ChildrenasTriggers. This property tells you whether child controls needs to be update when a post back happens. By default this property is set to True i.e always update the child controls. If you are wondering why we need to set this property to false , consider a scenario where we have update panel inside an update panel (Nested Update Panel) like the below.

<form id=”form1″ runat=”server”>

<div><asp:ScriptManager ID=”ScriptManager1″ runat=”server”></asp:ScriptManager

<asp:UpdatePanel ID=”upOuterPanel” runat=”server” ChildrenAsTriggers=”false” UpdateMode=”Conditional”><ContentTemplate>

<div>Outer Last Updated = <%=DateTime.Now.ToLongTimeString()%> </div>

<div><asp:Button ID=”btnOuter” runat=”server” Text=”Outer Update” /> </div>

<asp:UpdatePanel runat=”server”  ID=”upInnerPanel” >

<ContentTemplate<divInner Last Updated = <%=DateTime.Now.ToLongTimeString()%></div

<div<asp:Button ID=”btnInnerUpdate” runat=”server” Text=”Inner Update” />

</div>

</ContentTemplate>

</asp:UpdatePanel

</ContentTemplate>

</asp:UpdatePanel>

</div>

</form>

In the above code I have a button control in each of the update panels and notice the ChildrenasTriggers set to false for outer update panel. So this means when we click the Outer Update button the text for Outer Last Updated field is not updated. Also ChildrenasTriggers can be set to False only when the UpdateMode is set to Conditional. Attempting to do so without this condition results in an InvalidOperationException being thrown by the ScriptManager during the page’s PreRender event. Now if we click any button ( Inner or Outer ) the text for Outer Label will not change because we are telling the ScriptManager not to update the contents of the Outer Text whenever any async post back happens. But the inner label time always changes with any button click because upInnerPanel is a child control of upOuterPanel. If we set UpdateMode=”Conditional”
for upInnerPanel then if we click btnInnerUpdate then only upInnerPanel will be updated. Hope this helps..

Posted in .NET Framework | 1 Comment