Crystal Report in windows form application

Overview : Crystal reports are best used for printing document. Crystal reports provide a great printing experience. In this article I will tell you about how to add crystal report to your application and it's working.

Initially for this we need to take a DataSet from Add New Item in our application, and after adding the dataset we need to create a DataTable in our DataSet. I am creating a table with the name DataTable2. After adding table add some columns in your table as per your requirements. I am adding these columns as shown.
   

Now we need to add a crystalreport to our application from Add New Item in Reporting section as follows : 

After adding Crystal Report, a window named Database Expert appears, in this window expand Project Data, then expand ADO.NET Dataset then expand your dataset which you have created earlier and finally select your table and press OK.

Now you will see five sections in your crystal report as 

1. Section 1 (Report Header) : This section will be printed on the first page of the report.

2. Section 2 (Page Header) : This section will appear in all printed pages of your report.

3. Section 3 (Details) : This section will be repeated according to your data in DataTable.

4. Section 4 (Report Footer) : This section will be repeated once in a report.

5. Section 5(Page Footer) : This section will appear in footer of every page.

After placing data at appropriate place, your report is ready for print command, Now we will see the code to print the report.

The below shown code will be placed on the Click command of the button on which the report will be printed : 

First of all we will declare a DataTable with same name as in DataSet as follows : 

                            DataTable DataTable2 = new DataTable();

                            DataTable2.Columns.Add("date", typeof(string));

                            DataTable2.Columns.Add("agent", typeof(string));

                            DataTable2.Columns.Add("amount", typeof(string));

                            DataTable2.Columns.Add("receivedby", typeof(string));

now populate the DataTable with data as follows : 

                            DataRow dr = DataTable2.NewRow();

                            dr[0] = "column1";

                            dr[1] = "column2";

                            dr[2] = "column3";

                            dr[3] = "column4";

                            DataTable2.Rows.Add(dr);

After this the print command :

crystalreport1 crr1 = new crystalreport1();

crr1.Database.Tables["DataTable2"].SetDataSource((DataTable)DataTable2);

crystalReportViewer1.ReportSource = crr1;

This code will print the crystal report to your default printer.

NameSpace for this action : 

using System.IO;

using System.Drawing.Printing;

Happy Coding....

How to change SCHEMA name in SQL SERVER

Description:

 A Database schema is a described structure in a language supported by DBMS.

Example:

USE databasename;
GO
ALTER SCHEMA new_schema TRANSFER old_schema.TableName;
GO

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 :

Avoid Refreshing of Master Page

The refreshing of master page is a big problem for .NET developers, if you have placed a tree kind of menu in your website than if your master page refreshes every time than this is not a good thing for your website. So, to overcome this problem you can use frameset and iframe.

1  <frameset>

2       <div style="width:100%;">

3       <div style="width:35%; height:auto; float:left;">

4        <iframe src="Default2.aspx" height="auto" width="250px;"></iframe>

5        </div>

6        <div style="height:auto; width:65%; float:left;"></div>

7        <iframe id="iframe2" name="iframe2">

8         

9         </iframe>

1        </div>

1      </frameset>

If you a designing the menu in left side of your page than the first iframe should contain the src of the page which contains menu and in the page which contains menu will have target="iframe2" in anchor tag’s property like this :

<a href="Default3.aspx" id="dd" title="page" target="iframe2" style="color:Navy;">Title</a>

Master Page and IFrame

Master Page :

Master page reduces the code of a programmer because it comes with such features that we do not need to write the same code on many pages. If your website is using the same design on most of the pages than you can write the design code and programming on master page and place a content placeholder for other pages, by using this there is no need to write repetative codes. All the other pages are going to be shown in that content placeholder, you just simply need to call the master page on other pages.


IFrame :

Iframes are completely HTML part, it is also a very useful feature. If you are using the same kind of design an more than one page, than write the commom code on a separate page and call that page using iframe like this -

<iframe src="page.htm" height="55px" width="100%" frameborder="0"></iframe>

Note : Sometimes when we are using tree menu then we see that our master page gets refresh each time when we click on the menu and the menu goes in it's starting stage. To avoid these kind of problem it is usefull to use iframe by placing the menu in separate iframe.