User control in ASP.NET

Usercontrols can be understood as a portion of web page created separately as some other page but can be called at any location withing a web page. The advantage of creating usercontrol in an application is that you need not to write the same code again and again.

Adding usercontrol to website :  

  

   Add usercontrol by simply adding new item to your site as shown in image : 

 

Adding usercontrol to a page : 

  To add a usercontrol to a page you need to write this code on top : 

<%@ Register src="~/usercontrol/mainheader.ascx" TagName="UserInformation" TagPrefix="UserInformation" %>

after adding the above code on top of the page, now you just need to place the code on the place you need to add the usercontrol

<UserInformation:UserInformation id="UserInformation" runat="server"/> 

As Simple As That   

Export data to EXCEL form ListView in Windows Application

Overview :  While working on windows form application sometimes we need to copy all data of listview to an EXCEL sheet. This is very important and needy task in most of the application because EXCEL provides as a great and easy data management platform for those who are not very much expert in handling large database, I am talking about the end user of the application.

Example: Suppose I have a listview in my form having columns as described below -

             listView1.Columns.Add("Item Code", 120);

            listView1.Columns.Add("Party Name", 100);

            listView1.Columns.Add("Brand", 80);

            listView1.Columns.Add("Model No", 100);

            listView1.Columns.Add("Item Name", 100);

            listView1.Columns.Add("Category", 80);

            listView1.Columns.Add("Size", 50);

            listView1.Columns.Add("Color", 70);

            listView1.Columns.Add("Quantity", 50);

            listView1.Columns.Add("Purchase Rate", 90);

            listView1.Columns.Add("Sale Rate", 90);

            listView1.Columns.Add("Disc(%)", 50);

            listView1.Columns.Add("Final Price", 70);

            listView1.Columns.Add("Branch", 100);

 I have a button placed somewhere on the form, so code for the button is as follows -

            copyAlltoClipboard(listView1);

            Microsoft.Office.Interop.Excel.Application xlexcel;

            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;

            xlexcel = new Excel.Application();

            xlexcel.Visible = true;

            xlWorkBook = xlexcel.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];

            CR.Select();

            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

function copyAlltoClipboard(ListView lv) used on buttins's click event as follows -

        public void copyAlltoClipboard(ListView lv)

        {

            StringBuilder buffer = new StringBuilder();

            for (int i = 0; i < lv.Columns.Count; i++)

            {

                buffer.Append(lv.Columns[i].Text);

                buffer.Append("\t");

            }

            buffer.Append("\n");

            for (int i = 0; i < lv.Items.Count; i++)

            {

                for (int j = 0; j < lv.Columns.Count; j++)

                {

                    buffer.Append(lv.Items[i].SubItems[j].Text);

                    buffer.Append("\t");

                }

                buffer.Append("\n");

            }

            Clipboard.SetText(buffer.ToString());

        }

NemaSpace used : 

using Excel = Microsoft.Office.Interop.Excel;

using fm = System.Windows.Forms;

Use of HAVING clause in SQL

Overview : 

Having clause in SQL is used to place aggregate functions  to filter data because where condition does not allow the use of aggregate function.

Syntax :

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Demo :

Suppose we have a database of items as shown below. 

Now if we need to find out if there is any duplicate bar-code in these items then we can use having clause.

Query :

select count(item_code), item_name, item_purchase_rate, item_sale_rate from item group by item_code, item_name, item_purchase_rate, item_sale_rate having count(item_code)  > 0

Using PIVOT in SQL

Overview : PIVOT turns the unique values from one column in the expression to multiple columns in result, and applies aggregation on remaining value found in resultant output.

Example: lets create a table for this as follows –

create table demo(id int identity(1,1), item nvarchar(max), category nvarchar(max), qty int)

now lets insert some items in this table –

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat02', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat02', 5)

Lets apply our PIVOT query on this –

select * from(select item, category, qty from demo

)datatable

 PIVOT

 (

   sum(qty) for category  IN ([cat01], [cat02], [cat03])

 )

 Pivottable

Result :