asp.net solutions: 2014

Tuesday, December 23, 2014

WPF - Set Startup Window Based on Some Condition

Here I will like to explain you that how to set startup form in WPF window application based on non-conditional and conditional.

choose startup window without any condition

look into App.xaml
Change default xaml startuoUri with your file : StartupUri="MainWindow.xaml"

<Application x:Class="YourProject.App"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           Startup="MainWindow.xaml">

choose startup window with some condition

look into App.xaml
remove StartupUri="MainWindow.xaml"
add Startup="Application_Startup" new event Handler

<Application x:Class="YourProject.App"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           Startup="Application_Startup">

From code behind App.xaml.cs create Application_Startup event as follow:

private void Application_Startup(object sender, StartupEventArgs e)
    {
        //add some startup logic
        var identity = AuthService.CheckLogin();
        if (identity == null)
        {
            //if identity is null then show login window
            //LoginWindow is your win form(Xaml File)
            LoginWindow login = new LoginWindow();
            login.Show();
        }
        else
        {
            //if identity is not null then show main window
            MainWindow mainView = new MainWindow();
            mainView.Show();
        }
    }

Sunday, December 14, 2014

Delete Multiple Rows in Gridview using Checkbox in Asp.net

Here I will explain how to delete multiple rows in gridview using checkbox in asp.net in c# with confirmation message box.

Before use this example first design one table UserData in your database as shown below.

Column Name	Data Type	Allow Nulls
UserID	Int(IDENTITY=TRUE)	No
UserName	varchar(100)	Yes
FirstName	varchar(100)	Yes
LastName	varchar(100)	Yes

Once above table created in database then enter some data to test application after that write the following code in your aspx page.

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>delete Multiple Rows from gridview in asp.net using checkbox</title>
<script type="text/javascript">
function Confirmation() {
var result = confirm('Are you sure you want to delete selected User(s)?');
if (result) {
return true;
}
else {
return false;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvUserData" DataKeyNames="UserID" AutoGenerateColumns="false" CellPadding="5" runat="server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="UserID" DataField="UserID" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="FirstName" DataField="FirstName" />
<asp:BoundField HeaderText="LastName" DataField="LastName" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<asp:Button ID="btnDelete" Text="Delete Records" runat="server" Font-Bold="true" OnClientClick="javascript:return Confirmation();" onclick="btnDelete_Click" />
</div>
</form>
</body>
</html>

Now goto code behind file(press F7 key) and write the following code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class DeleteRows : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindUserData();
}
}
protected void BindUserData()
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection("Data Source=vibhavdb;Integrated Security=true;Initial Catalog=SampleDB");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from UserData", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvUserData.DataSource = dt;gvUserData.DataBind();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
foreach (GridViewRow gvrow in gvUserData.Rows)
{
//Finiding checkbox control in gridview for particular row
CheckBox chkdelete = (CheckBox)gvrow.FindControl("chkSelect");
//Condition to check checkbox selected or not
if (chkdelete.Checked)
{
//Getting UserId of particular row using datakey value
int usrid = Convert.ToInt32(gvUserData.DataKeys[gvrow.RowIndex].Value);
SqlConnection con = new SqlConnection("Data Source=vibhavdb;Integrated Security=true;Initial Catalog=SampleDB");
con.Open();
SqlCommand cmd = new SqlCommand("delete from UserData where UserID=" + usrid, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
BindUserData();
}
}

Monday, November 24, 2014

Display ASP.Net Ajax UpdateProgress in the middle of the Page

Here I will explain how to display ASP.Net AJAX UpdateProgress control in middle of the page with a modal background covering the whole screen.

Until the AJAX call is in progress, the screen will freeze and user will not able to perform any action.

Example:
Follow the below steps:

1. Add AjaxControlToolkit to your application, and register the same in your .aspx page.

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" 
TagPrefix="asp" %>

2. Add a ScriptManager to your .aspx page.

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

3. Add the below CSS in tag in .aspx page or in external CSS file.

.divWaiting{
   
position: absolute;
background-color: #FAFAFA;
z-index: 2147483647 !important;
opacity: 0.8;
overflow: hidden;
text-align: center; top: 0; left: 0;
height: 100%;
width: 100%;
padding-top:20%;
}

   4. Add UpdateProgress to your page. Provide an Image and a text message in >progresstemplate<.

       With DisplayAfter property, you can modify the time after which the message should displaying.
       The value should be in microseconds.
       AssociatedUpdatePanelId is the Id of the update panel which contains the controls.

<asp:UpdateProgress ID="UpdateProgress1" DisplayAfter="10" 
runat="server" AssociatedUpdatePanelID="upTest">
<ProgressTemplate>
  <div class="divWaiting">            
 <asp:Label ID="lblWait" runat="server" 
 Text=" Please wait... " />
 <asp:Image ID="imgWait" runat="server" 
 ImageAlign="Middle" ImageUrl="~/Images/wait.gif" />
  </div>
</ProgressTemplate>
</asp:UpdateProgress>

5. Add the controls in UpdatePanel which is associated with UpdateProgress. Whenever any event fire from a control inside associated UpdatePanel, the progress image and message will be shown in the center of screen.

 <asp:UpdatePanel ID="upTest" runat="server">
    <ContentTemplate>
      <asp:Button ID="btnSubmit" OnClick="btnSubmit_Click" Text="Submit"  
       runat="server" /> 
    </ContentTemplate>
</asp:UpdatePanel>

Now, if you want to check the above functionality, then use the below code in btnSubmit_Click event.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    Thread.Sleep(2000); 
}

Sunday, April 13, 2014

Simple Example of Cursor in SQL Server

Here I will explain how to create cursor in Sql Server 2008 using sql query.

What is cursor in SQL Server?

A cursor is a set of rows with a pointer that identifies a current row(for example if you have set of rows together and wants to perform some action like insert update or delete repeatedly with different values that is store in rows set format and pointer is pointing to row one by one until reached at last row).

In other word, Cursor is a database object used by many applications to easly manipulate data in a set on a row-by-row basis, its like recordset in the ASP.

Example:

Declare @ID as int
Declare @Name as varchar(50)
Declare @Email as varchar(20) 
Declare @Location as varchar(20) 

  --declare the UserData cursor   
  Declare UserData CURSOR FOR

  Select Name ,Email, Location from  tblUser

     --now we need to open cursor and tell it to go
     OPEN UserData

     --now fetch first row from table tblUser 
      FETCH NEXT FROM UserData INTO @Name ,@MOBILE ,@Location

      WHILE @@FETCH_STATUS = 0
          BEGIN 
                  --action 
                   Insert Records @Name ,@MOBILE , @Location

      --fetch row values until end of the row 
      FETCH NEXT FROM UserData INTO @Name ,@MOBILE ,@Location
        END

   --close cursor

  CLOSE UserData

--Deallocate cursor

DEALLOCATE UserData

Wednesday, March 26, 2014

OPENXML Function - Read XML file in SQL Server 2008

Here I will explain how to read data from xml document and insert it into SQL table in SQL Server 2008.

The examples in this post show how OPENXML function is used to create a rowset view of an XML document.

Example:

For reading data from the xml document, like row pattern is used to identify the nodes(its also identify same name nodes) in the XML document. For example, if the pattern(nodes like '/Customer/order') ends in an element or an attribute, a row is created for each element or attribute node that is selected by row pattern.

The OPENXML statement represents the following:

Row pattern (/Customer/order) identifies the nodes to process.

For attribute-centric flags parameter value is set to 1. As an output, the XML attributes map to the columns in the rowset defined in Schema Declaration.

The XML document in this example is contain of <customer>, <order>, and <orderdetail> elements.

Example 1: Use of OPENXML function in select statement

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

The SELECT statement is used to retrieves all the columns in the rowset provided by OPENXML.

Output:

From this result you can insert all that data into your SQL table using below query you can just need to put insert query above select query with all that columns that you need to be insert into the table.

Example 2 : Use of OPENXML function to insert data into SQL table

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Insert data from SELECT statement using OPENXML.
INSERT INTO CustomerOrder(OrderID,CustomerID,ContactName,OrderDate,

ProductID,Qty,Amount,Comment)

SELECT OrderID, CustomerID, ContactName, OrderDate, ProductID, Qty,

Amount, Comment
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

After execute above query you get your data into you SQL table.

Examlpe 3 : Read XML file and use of OPENXML function in select statement

DECLARE @DocHandle int
Declare @XML XML

--Read XML file from you local and insert your data very easy and fast(bulk)

 SET @XML = 
(SELECT * FROM OPENROWSET(BULK 'd:\test.xml',
 SINGLE_BLOB) AS x)
--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

Output:

test.xml
<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer>
<Customer CustomerID="3" ContactName="shivani bhavsar">
   <Order OrderID="103" CustomerID="3" OrderDate="2014-03-01">
      <OrderDetail ProductID="21" Quantity="18" Amount="280$"/>
      <OrderDetail ProductID="9" Quantity="5" Amount="80$"/>
   </Order>
</Customer>
</ROOT>

I have give above file location in SQL query to read data of this file and main benefit of using xml file is that you can give big xml file(about 150MB) to get execute easily and very fast.

-Thank you

Sunday, March 23, 2014

Export Data from Gridview to Excel in ASP.NET using C#

Here I will like to explain how to export data from Gridview control to Excel file in ASP.NET using C# programming language.

Following is code we need to write for export data from gridview to excel

<title>Export Excel in Asp.net</title>

</head>

<body>

<asp:Button ID="btnExport" runat="server" Text="Export to Excel"

onclick="btnExportExcel_Click" />

<div>

<asp:GridView ID="gvUserInfo" AutoGenerateColumns="false" CellPadding="8" runat="server">

<asp:BoundField HeaderText="UserId" DataField="UserId" />

<asp:BoundField HeaderText="First Name" DataField="First Name" />

<asp:BoundField HeaderText="Last Name" DataField="Last Name" />

<asp:BoundField HeaderText="Address" DataField="Address" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Now in code behind add following code

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridview();

}

protected void BindGridview()

{

DataTable dt = new DataTable();

dt.Columns.Add("UserId", typeof(Int32));

dt.Columns.Add("First Name", typeof(string));

dt.Columns.Add("Last Name", typeof(string));

dt.Columns.Add("Address", typeof(string));

dt.Rows.Add(1, "Vibhav", "Bhavsar", "Gujarat");

dt.Rows.Add(2, "Mahesh", "patel", "Pune");

dt.Rows.Add(3, "Geeta", "shah", "Chennai");

dt.Rows.Add(4, "Meet", "Rathod", "Nagpur");

dt.Rows.Add(5, "Akshay", "Trivedi", "Mumbai");

gvDetails.DataSource = dt;

gvDetails.DataBind();

}

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

protected void btnExportExcel_Click(object sender, EventArgs e)

{

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment;filename= UserList.xls");

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvDetails.AllowPaging = false;

BindGridview();

//Header Row back to white color

gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

//gridview header cells

for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)

{

gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#4384FD");

}

gvDetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Whenever we run application in ASP.NET we will see the screen like as below

Once we click on Export to Excel button we will see data in excel file like as below

Saturday, March 22, 2014

Using TRY CATCH in SQL Server to Handling Exception

Here I will explain that how to handle exceptions or exception handling in SQL using try catch block in SQL Server.

Try and catch block is very similar to what we use in other.net languages.

For handling exceptions in SQL Server we can use TRY CATCH blocks. To use TRY CATCH blocks in stored procedure we have to write the query like as below

TRY CATCH Block Syntax

BEGIN TRY

--Write Your Query for which we need to do the error handaling

END TRY

BEGIN CATCH 

--Write code for handle error while occure during query excection in try block 

END CATCH

In TRY block we need to write our query and in CATCH block we need to
write code to handle error occur during query execution.
In our SQL query if error occurs automatically it will move to CATCH block in that we can handle error.
For handling error messages we have some defined Error Functions in CATCH block those are

1. ERROR_LINE() - function will return error line number of SQL query which cause to occure error.

2. ERROR_NUMBER() - function will return error number which is unique and assigned to it.

3. ERROR_SEVERITY() - function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

4. ERROR_STATE() - function will return state number of error message which cause to occure error.

5. ERROR_PROCEDURE() - function will return name of the procedure where an error occurred.

6. ERROR_MESSAGE() - function will return the complete error message which cause to ouccer error.

Check below query for handling errors in stored procedure

BEGIN TRY

   -- ocure error, as UserID is an IDENTITY column

   -- we can't specify a value for this column.

   INSERT INTO tbl_user(UserID, UserName)

   VALUES(1, 'Test')

END TRY 

BEGIN CATCH 

   SELECT ErrorNumber = ERROR_NUMBER(),

   ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE()

   ErrorProcedure = ERROR_PROCEDURE(), ErrorLine =

   ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()

END CATCH

If we run above query then we will get output like as shown below