Execute an sql query as string using sp_executesql and store the result in a variable

Small but useful tip for Sql Server 2012.

Here is a simple example for how we can execute an sql string query, execute it and store the result in a variable for further use.

declare @query nvarchar(1000), @result datetime

set @query = ‘select @result = GETDATE()’

EXEC sp_executesql @query,
N’@result datetime OUTPUT’,
@result OUTPUT

print CAST(@result as nvarchar(100))

How to open partial view as modal dialog in asp.net mvc

Asp.Net MVC is picking up fast and yet there is not much expertise around it. Main reason I feel is now developer has complete control on markup so they approach in different ways. It is difficult to for a beginner to implement any design principles.

Anyway, today I am going to showcase how to open a partial view dynamically as a popup using Jquery. I am hoping you have basic idea of how MVC works and you know the Jquery syntax.

View code: Main page ProductView.cshtml. click on the image to see the .cshtml code.
wordpress

Partial view html for list.
code2

Old classic view of productList page (it is also a partial view which rendres as a grid) with edit/delete links in each row. When you click on edit link in the grid, updateDialog div will open as a modal popup and load a partial view in that div dynamically using below code.

Controller action method which returns the partial view to be loaded in div updateDialog.

public ActionResult EditPartialGet(int id)
{
ProductViewModel productVM = new ProductViewModel();
Product p = new Product();
p = productVM.GetById(id);
return PartialView("ProductUpdatePartial",p);
}

finally jquery code to open popup and load partial view using ajax call.

$(function () {
// on click of editLink in grid.
$(“#editLink”).live(‘click’, function () {

var hrefValue = $(this).attr(“href”);
var index = hrefValue.lastIndexOf(“/”) + 1;
var productId = hrefValue.substring(index, hrefValue.length);
// this will call EditPartialGet action method in Products controller which returns
// a partial view html.
$.ajax({
type: “GET”,
url: “Products/EditPartialGet?id=” + productId,
datatype: “json”,
contentType: “application/json”,
success: function (data) {
$(“#updateDialog”).html(data);// returned data is an html view which will be rendered inside the div.
return false;
},
error: function (a, b, c) { alert(b); }
});

$(“#updateDialog”).dialog(
{
modal: true,
buttons: {
// in modal dialog popup Save button will be created. write what you want to do here.
Save: function () {

var ctx = $(“#updateDialog”);
debugger;
// parse the DOM within context of div id.
var product = {
ProductID : $(“#ProductID”,ctx).val(),
ProductName : $(“#ProductName”,ctx).val(),
SupplierID : $(“#SupplierID”, ctx).val(),
CategoryID : $(“#CategoryID”, ctx).val(),
QuantityPerUnit: $(“#QuantityPerUnit”, ctx).val(),
UnitPrice : $(“#UnitPrice”, ctx).val(),
UnitsInStock : $(“#UnitsInStock”, ctx).val(),
UnitsOnOrder : $(“#UnitsOnOrder”, ctx).val(),
ReorderLevel : $(“#ReorderLevel”, ctx).val(),
Discontinued : $(“#Discontinued”, ctx).attr(“checked”)
};

$.ajax({
type: “POST”,
url: “Products/Update”,
data: JSON.stringify(product),
datatype: “json”,
contentType: “application/json”,
success: function (data) {
// submit the data
alert(‘done’);
return false;
},
error: function (a, b, c) { debugger; alert(b); }
});

$(“#updateDialog”).dialog(‘close’);
return false;
},
Cancel: function () { $(“#updateDialog”).dialog(‘close’); }
}
});
return false;
});
});

I think the above code is all you need to implement dynamically loading of partial view in a div.

I hope it helps.

How to find query/stored procedure used in an SSRS report

If yor are troubleshooting an SSRS report which you have not created by yourself. Finding it’s location may take sometime even though you know that issue is with the query. Hence to find the query/stored procs used in the report we can directly query ReportServer database and get those details from it’s report dataset.

SELECT Name As ReportName,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportInXMLFormat
FROM ReportServer.dbo.Catalog
where Type = 2 
order by Name

it will give an XML result which will look like this.

<DataSets>
    <DataSet Name="EmployeeDetails">
        <Query>
            <CommandType>StoredProcedure</CommandType>
            <CommandText>usp_getEmpDetails</CommandText>
            <QueryParameters>
                <QueryParameter Name="@EMPLOYEEID">
                    <Value>=Parameters!EMPID.Value</Value>
                </QueryParameter>               
            </QueryParameters>
            <DataSourceName>DVEMP02</DataSourceName>
        </Query>
        .
        .
        .
   </DataSet>
    <DataSet Name="Department">
        <Query>
            <DataSourceName>DVEMP02</DataSourceName>
            <CommandText>
                SELECT DepartmentId AS ID, DeparmentName 
                FROM Department               
            </CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
        </Query>
    </DataSet>
    .

Query tag will tel you about the sql query and stored proc used in the reports. You can start troubleshooting right away if it’s a data or a query issue.

I hope it helps!

Moving Items from one ListBox to another ListBox using Jquery

Jquery can do this real quick and with good user experience. You might need to style it up a bit.

UI code:


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Test.aspx.cs" Inherits="Test" %>

 <!DOCTYPE html>

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
  <title></title>
  <script src="Scripts/jquery-1.7.1.js"></script>

 <script>

 $(document).ready(function () {

 var productLineId = $("#ddlProductLine").val();
  var lBox = $('select[id$=lbCountriesAll]');
  var lBox2 = $('select[id$=lbSelectedCountries]');

 $("#btnSelect").click(function (e) {

 var selectedValues = $('#lbCountriesAll option:selected');
  if(selectedValues.length == 0)
  {
  alert('Please select a country.');
  e.preventDefault();
  }

 if (selectedValues.length > 3) {
  alert('You cannot select more than 3 countries.');
  e.preventDefault();
  }
  else {
  //debugger;
  var children = $('#lbSelectedCountries').children();
  if (children.length >= 3) {
  alert('You cannot select more than 3 countries.');
  e.preventDefault();
  }
  else {
  $('#lbSelectedCountries').append($(selectedValues).clone());
  $(selectedValues).remove();
  e.preventDefault();
  }

 }
  })

 $("#btnUnSelect").click(function (e) {
  //debugger;

 var selectedValues = $('#lbSelectedCountries option:selected');
  if (selectedValues.length == 0) {
  alert('Please select a country.');
  e.preventDefault();
  }

 $('#lbCountriesAll').append($(selectedValues).clone());
  $(selectedValues).remove();
  e.preventDefault();

 })

  });
  </script>

 </head>

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

 <table>

 <tr><td>

 <asp:ListBox ID="lbCountriesAll" runat="server" Width="200px" Height="350px" SelectionMode="Multiple" ></asp:ListBox></td>

  <td>
  <asp:Button ID="btnSelect" runat="server" Text=">>"></asp:Button><br />
  <asp:Button ID="btnUnSelect" runat="server" Text="<<"></asp:Button>
  </td>
  <td>
  <asp:ListBox ID="lbSelectedCountries" runat="server" Width="200px" Height="350px" SelectionMode="Multiple"></asp:ListBox></td>
  </tr>

 </table>

 </div>
  </form>
 </body>
 </html>

The above code will transfer the items from one Listbox to another and can also limit the number of items a user can transfer. You can remove that code if you do not need it.

Code Behind:The code below only binds the Listbox with some countries.


protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack)
 {
 lbCountriesAll.DataSource = GetCountries();
 lbCountriesAll.DataTextField = "Name";
 lbCountriesAll.DataValueField = "ID";
 lbCountriesAll.DataBind();
 }

}

&nbsp;

public static List<Country> GetCountries()
 {
 List<Country> Countries = new List<Country>()
 {
 new Country() { ID=4, Name="Afghanistan"},
 new Country() { ID=918, Name="Åland Islands"},
 new Country() { ID=8, Name="Albania"},
 new Country() { ID=12, Name="Algeria"},
 new Country() { ID=16, Name="American Samoa"},
 new Country() { ID=20, Name="Andorra"},
 new Country() { ID=24, Name="Angola"},
 new Country() { ID=660, Name="Anguilla"},
 new Country() { ID=10, Name="Antarctica"},
 new Country() { ID=28, Name="Antigua and Barbuda"},
 new Country() { ID=32, Name="Argentina"},
 new Country() { ID=51, Name="Armenia"},
 new Country() { ID=533, Name="Aruba"},
 new Country() { ID=36, Name="Australia"},
 new Country() { ID=40, Name="Austria"},
 new Country() { ID=31, Name="Azerbaijan"},
 new Country() { ID=44, Name="Bahamas, The"},
 new Country() { ID=48, Name="Bahrain"},
 new Country() { ID=50, Name="Bangladesh"},
 new Country() { ID=52, Name="Barbados"},
 new Country() { ID=112, Name="Belarus"},
 new Country() { ID=56, Name="Belgium"},
 new Country() { ID=84, Name="Belize"},
 new Country() { ID=204, Name="Benin"},
 new Country() { ID=60, Name="Bermuda"},
 new Country() { ID=64, Name="Bhutan"},
 new Country() { ID=68, Name="Bolivia"},
 new Country() { ID=912, Name="Bonaire, Sint Eustatius and Saba"},
 new Country() { ID=70, Name="Bosnia and Herzegovina"},
 new Country() { ID=72, Name="Botswana"},
 new Country() { ID=74, Name="Bouvet Island"},
 new Country() { ID=76, Name="Brazil"},
 new Country() { ID=86, Name="British Indian Ocean Territory"},
 new Country() { ID=96, Name="Brunei"},
 new Country() { ID=100, Name="Bulgaria"},
 new Country() { ID=854, Name="Burkina Faso"},
 new Country() { ID=108, Name="Burundi"}

};

return Countries;

}

I am using a web application to demonstrate here. You can use the same code in MVC too, only thing you need to change is the URL you are pointing to in $.ajax call. It would point to an action method from a controller class.
Similarly, you can use the same code with HTML 5 app, only the Url will point to some Http service.

I hope it helps!

 

Gridview Model binding with Control attribute as parameter

Asp.Net MVC had enjoyed the benefit of Model binding since its inception. It helped in few things such as

  • UI and data model separation.
  • Unit testing of the data binding method. (great! isn’t it?)

With Asp.Net 4.5, Model binding is now available for Web forms as well. Moreover it is integrated in a seamless manner.

so here we go!

UI Code:

New Gridview has two properties to be noticed.

  • ItemType:
  • It needs a type name that you are binding to. It can be a dll which contains the actual method.

  • SelectMethod:
  • It needs a method which returns an IEnumerable or IQueryable type.

    Similarly we have UpdateMethod which is not covered in this post.

    <asp:DropDownList ID="ddlNameFilter" runat="server" AutoPostBack="true" Width="500px" >
                    <asp:ListItem Value="" Text="-- Select word name starts with --" Selected="True" />
                    <asp:ListItem Value="KE" Text="KE" />
                    <asp:ListItem Value="TE" Text="TE" />
                </asp:DropDownList>
    
    </br></br>
                
             <asp:GridView ID="gvPersons" runat="server" 
                    AllowPaging="True" AutoGenerateColumns="False" PageSize="20"             
                 ItemType="WebFormSamples.Person" SelectMethod="GetPersonList" 
                 OnRowDataBound="gvPersons_RowDataBound"
    
                    OnPageIndexChanging="gvPersons_PageIndexChanging">
                    <Columns>
                        <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
                            SortExpression="BusinessEntityID" />
                        <asp:BoundField DataField="PersonType" HeaderText="PersonType"
                            ReadOnly="True" SortExpression="PersonType" />
                        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                            SortExpression="FirstName" />
                        <asp:BoundField DataField="LastName" HeaderText="LastName"
                            SortExpression="LastName" />
                    </Columns>
                    <PagerStyle HorizontalAlign="Right" />
                    <PagerSettings Mode="Numeric" />
                </asp:GridView>
    

    Code Behind:

     
            // ddlNameFIlter is a dropdown which user selects and selected value is passed as parameter to the below //<strong>SelectMethod</strong> of Gridview. 
            public IQueryable<Person> GetPersonList([Control("ddlNameFilter")] string searchName)
            {
                List<Person> persons = new List<Person>();
                if ( string.IsNullOrEmpty(searchName))
                {
                    persons = Person.GetPersonList();
                }
                else
                    persons = Person.GetPersonList().Where(p => p.FirstName.ToLower().StartsWith(searchName.ToLower())).ToList();
    
                return persons.AsQueryable();
            }

    There are few more Value providers introduced in Asp.Net 4.5. [QueryString],[Cookie] are also very useful.

    I hope it helps!

    Follow

    Get every new post delivered to your Inbox.

    Join 366 other followers