Crystal Reports in vs 2010

This tutorial focuses on creating a crystal report in Visual studio 2010. The report uses a stored procedure to get its data from an Sql server 2008 R2 database. In order to create the report you will need to download and install crystal reports for Visual Studio 2010. This is because VS 2010 does not ship with crystal reports. However you can download crystal reports from SAP websitehttp://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0.exe

1. Start by running the following scripts in sql database to create a test table, insert some records and also create a procedure that will retrieve the records.
   --===========================================
--create a test customer table
CREATE TABLE t_Customers
(
    CustomerID VARCHAR(20),
    FirstName VARCHAR(25),
    LastName VARCHAR(25)
)
--===========================================
--insert dummy data into the test table
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('12345','Evans','Amenya')
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('324132123','ASas','ASasASas')
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('234234','evans','asdasdasd')
--===========================================
--create procedure to retrieve the records
CREATE PROCEDURE sp_getCustomers
AS
    SELECT CustomerID,FirstName,LastName FROM t_Customers
--=========================================== 
2. Create a new Visual Studio 2010 project
3. Right-click on the project on the solution explorer and click on Add New Item on the pop-up menu as shown below
   
4. The Add New Item dialogue box will appear as shown below. Select Crystal Reports in the middle pane then enter the name as “CustomersReport.rpt” in the Name field.
   
5. Click the Add button.
   Crystal Reports Gallery dialogue box will appear as shown below
   
   Select As a Blank Report in Create a New Crystal Report Document options. Then click the OK button
6. A blank report will appear. Right-click on any blank area on the report, then point to Database on the pop-up menu that appears. Click on Database Expert as shown below
   
7. The Database Expert dialogue box will appear as shown below. Expand the Create New Connection node. Then expand the OLE DB (ADO) node.
   
8. OLE DB Provider dialogue box will appear as shown below. Scroll down and select SQL Server Native Client 10.0 from the list of Providers. Then click the Next button.
   
9. OLE DB Connection information dialogue box will appear as shown below. Provide necessary information to log on and click Next.
   
10. The Advanced Information dialogue box will appear as shown below. Click the Finish button.
    
11. The connected database will appear as shown in the screen below. Expand the Database name node.
    Expand the dbo node. Expand the Stored Procedures node. Select the stored procedure to use,
    “sp_getCustomers”, then click the right-pointing single arrow to move the procedure to the right-hand pane.
    
12. Click OK button.
    The Database Expert dialogue box closes and the blank report is visible.
    Expand the Database Fields node on the Field Explorer box. Expand procedure name node ("sp_getCustomers") as shown below.
    
13. Drag and drop the fields from the Field Explorer box onto the blank report just below the Details
    section. After you release the mouse key, the heading should automatically appear as shown below.
    
14. The linking of our crystal report to sql server database is complete. Now we can create a .aspx page that will call it.
    Add web form to the project and choose C# as the programming language. Also make sure the option to place code in separate file is selected. Name the page "CustomerReportViewer.aspx"
15. Drag and drop a CrystalReportViewer control from the Reporting tab onto the CustomerReportViewer.aspx page. Your page should appear as shown below,
    
16. Add the following code on the code file
    ...
using System.Reflection;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.Reporting;
using CrystalDecisions;
using System.Data.SqlClient;
public partial class CustomerReportViewer : System.Web.UI.Page
{
    ReportDocument doc;
    protected void Page_Unload(object sender, EventArgs e)
    {
        if (doc != null)
            doc.Close();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        ShowReport(Server.MapPath(".\\CustomersReport.rpt"), "sp_getCustomers");
    }
    protected void ShowReport(String fileName, String strProcedureName)
    {
        TableLogOnInfo crTableLogOnInfo = new TableLogOnInfo();
        ConnectionInfo crConnectionInfo = new ConnectionInfo();
        CrystalDecisions.CrystalReports.Engine.Database crDatabase;
        CrystalDecisions.CrystalReports.Engine.Tables crTables;
        doc = new ReportDocument();
        doc.Load(fileName);
        crConnectionInfo.ServerName = "HP-PC";
        crConnectionInfo.DatabaseName = "ProgrammingSamples";
        crConnectionInfo.UserID = "xxxxxxx";
        crConnectionInfo.Password = "yyyyyyyy";
        crConnectionInfo.Type = ConnectionInfoType.SQL;
        crConnectionInfo.IntegratedSecurity = false;
        crDatabase = doc.Database;
        crTables = crDatabase.Tables;
        SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
        SqlCommand cmd = new SqlCommand("dbo." + strProcedureName, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adpt = new SqlDataAdapter(cmd);
        DataSet dataSet = new DataSet();
        adpt.Fill(dataSet, "Customers");
        foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
        {
            crTableLogOnInfo = crTable.LogOnInfo;
            crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
            crTable.ApplyLogOnInfo(crTableLogOnInfo);
        }
        doc.SetDataSource(dataSet.Tables[0]);
        CrystalReportViewer1.ReportSource = doc;
    }
} 
17. Set "CustomerReportViewer.aspx" as the startup page and run the project.
    The report will be displayed as shown below.

Download the complete source code for the example project

click here

jQuery dynamic textbox example

	<html>
	<head>
	<title>jQuery add / remove textbox example</title>
	<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
	<style type="text/css">
	div{
	padding:8px;
	}
	</style>
	</head>
	<body>
	<h1>jQuery add / remove textbox example</h1>
	<script type="text/javascript">
	$(document).ready(function(){
	var counter = 2;
	$("#addButton").click(function () {
	if(counter>10){
	alert("Only 10 textboxes allow");
	return false;
	}
	var newTextBoxDiv = $(document.createElement('div')).attr("id", 'TextBoxDiv' + counter);
	newTextBoxDiv.after().html('<label>Textbox #'+ counter + ' : </label>' +
	'<input type="text" name="textbox' + counter +
	'" id="textbox' + counter + '" value="" >');
	newTextBoxDiv.appendTo("#TextBoxesGroup");
	counter++;
	});
	$("#removeButton").click(function () {
	if(counter==1){
	alert("No more textbox to remove");
	return false;
	}
	counter--;
	$("#TextBoxDiv" + counter).remove();
	});
	$("#getButtonValue").click(function () {
	var msg = '';
	for(i=1; i<counter; i++){
	msg += "\n Textbox #" + i + " : " + $('#textbox' + i).val();
	}
	alert(msg);
	});
	});
	</script>
	</head><body>
	<div id='TextBoxesGroup'>
	<div id="TextBoxDiv1">
	<label>Textbox #1 : </label><input type='text' id='textbox1' >
	</div>
	</div>
	<input type='button' value='Add Button' id='addButton'>
	<input type='button' value='Remove Button' id='removeButton'>
	<input type='button' value='Get TextBox Value' id='getButtonValue'>
	</body>
	</html>

LINQ To MySQL

LINQ to SQL is a great tool that saves a lot of time. However it has one disadvantage – it supports only SQL Server. Anyway it’s possible to write your own LINQ provider. Sure, there is one for MySQL. It is called DbLinq, in fact in supports many databases:

• MySQL
• Oracle
• PostreSql
• Ingress
• SqlLite

It’s not finished yet, however you can still try to use it. I’m going to explain how to use it in this article; we’ll go through 3 steps:

1. Installation
2. Using DbLinq in a console application
3. Using DbLinq in a ASP.NET application

Installation

1. Go here and download the ZIP archieve
2. Unpack the archive
3. Open the Visual Studio solution - DbLinq
4. Build the solution

Using DbLinq in a console application

Ok, let’s try it in action, we are going to create a console application that will retrieve, insert, update and delete records from the database.

First, we need to generate the data context and the table classes. Unfortunately you cannot do this in Visual Studio, but there is a command line utility called DbMetal and its visual analogue – Visual Metal that is written in WPF, by the way.

Go to the \dblinq2007\DbMetal\ folder, there are several BAT files, open in your favourite text editor run_myMetal.bat, since we are dealing with MySQL, and change the necessary parameters, save and run it. You must have got a generated CS file, copy it to your console application project.
Then, it’s time to add the references, you should add the following:

• System.Data.Linq
• DbLinq.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
• DbLinq.MySql.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
• MySql.Data.dll (located in \dblinq2007\lib\)

Since the table is called Products, I want the class that represents a stored entity to be called Product, not Products, as it was called by DbMetal. So, I change the generated code.

Compile the project to check if there are no errors. If you get any error, check if there are all the necessary references.

Let’s write some code; basically we want just to display the data:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient; //Don't forget to add this line

namespace MySqlLinqTest
{
    class Program
    {
        static void Main(string[] args)
        {
            LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));

            var products = from p in db.Products
                           where p.Category == "Sport cars"
                           select p;

            foreach (var product in products)
            {
                Console.WriteLine(product.Name);
            }
        }
    }
}

That works. What about inserting a new record? No pro blems!

db.Products.InsertOnSubmit(new Product { Name = "Some car", Category = "Some category" });
db.SubmitC hanges();

var products = from p in db.Products
               orderby p.Name
               select p; 

foreach (var product in products)
{
    Console.WriteLine(product.Name);
}

Ok, let’s remove the newly added record:

Product someCar = (from p in db.Products
                      where p.Name == "Some car"
                      select p).First();

db.Products.DeleteOnSubmit(someCar);
db.SubmitChanges();

Let’s update something:

Product bentley = (from p in db.Products
                   where p.Name == "Bentley"
                   select p).First();

bentley.Name = "Bentley Continental";
db.SubmitChanges();

Using DbLinq in an ASP.NET application

It was funny enough, but the console applications are not very useful when dealing with the database, let’s write something that is closer to a real world – an ASP.NET application.

Create a C# web site; copy the generated file we used in the previous example.

Don’t forget to add the necessary references.

We are going to add a GridView and bind the data retrieved from the database.

Default.aspx

<asp:GridView ID="gridProducts" runat="server">
</asp:GridView>

Default.aspx.cs 

protected void Page_Load(object sender, EventArgs e)
{
    LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));

    gridProducts.DataSource = from p in db.Products
                              orderby p.Name
                              select p;
    gridProducts.DataBind();
}

It works.

What about LinqDataSource, can we use it with DbLinq? Yes, we can, but it’s a bit tricky. If we add a LinqDataSource, we’ll get an error saying that there’s no parametless constructor in our DataContext. That is a thing that hopefully will be fixed in the new versions, but now we have to write a wrapper around a generated DataContext.

App_Code/Products.cs

public class MyDataContext
{
    private LinqTest _db;

    public MyDataContext()
    {
        _db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));
    }

    public Table<Product> Products { get { return _db.GetTable<Product>(); } }
}

Then, we can use LinqDataSource as usual:

<asp:LinqDataSource ID="dsProducts" runat="server" ContextTypeName="MysqlLinqTest.MyDataContext"     TableName="Products"></asp:LinqDataSource> <asp:GridView ID="gridProducts" runat="server" DataSourceID="dsProducts">  </asp:GridView>

Conclusion

As you see you can use LINQ to work with MySQL, unfortunately sometimes it’s not very convenient and we have to wait for the new versions or wait for a LINQ to MySQL provider from MySQL.

Resizable asp.net Gridview columns using Jquery

.aspx.cs code


 public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Designation { get; set; }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        var employees = new List<Employee>()
    {
        new Employee(){ Id = 1, Name = "Ms. Nancy Davolio",     Address = "507 - 20th Ave. E.  Apt. 2A",    Designation = "Sales Representative"},
        new Employee(){ Id = 2, Name = "Dr. Andrew Fuller",     Address = "908 W. Capital Way",             Designation = "Vice President Sales"},
        new Employee(){ Id = 3, Name = "Ms. Janet Leverling",   Address = "722 Moss Bay Blvd.",             Designation = "Sales Representative"},
        new Employee(){ Id = 4, Name = "Mrs. Margaret Peacock", Address = "4110 Old Redmond Rd.",           Designation = "Sales Representative"},
        new Employee(){ Id = 5, Name = "Mr. Steven Buchanan",   Address = "14 Garrett Hill",                Designation = "Sales Manager"},
        new Employee(){ Id = 6, Name = "Mr. Michael Suyama",    Address = "Coventry House  Miner Rd.",      Designation = "Sales Representative"},
        new Employee(){ Id = 7, Name = "Mr. Robert King",       Address = "Edgeham Hollow  Winchester Way", Designation = "Sales Representative"},
        new Employee(){ Id = 8, Name = "Ms. Laura Callahan",    Address = "4726 - 11th Ave. N.E.",          Designation = "Inside Sales Coordinator"},
        new Employee(){ Id = 9, Name = "Ms. Anne Dodsworth",    Address = "7 Houndstooth Rd.",              Designation = "Sales Representative"}
    };

        GridView1.DataSource = employees;
        GridView1.DataBind();
    }



.aspx code


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery.js" type="text/javascript"></script>
    <script src="Scripts/colResizable-1.3.min.js" type="text/javascript"></script>
    <script src="Scripts/jquery.cookie.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            if ($.cookie('colWidth') != null) {
                var columns = $.cookie('colWidth').split(',');
                var i = 0;
                $('.GridViewStyle th').each(function () {
                    $(this).width(columns[i++]);
                });
            }

            $(".GridViewStyle").colResizable({
                liveDrag: true,
                gripInnerHtml: "<div class='grip'></div>",
                draggingClass: "dragging",
                onResize: onSampleResized
            });

        });

        var onSampleResized = function (e) {
            var columns = $(e.currentTarget).find("th");
            var msg = "";
            columns.each(function () { msg += $(this).width() + ","; })
            $.cookie("colWidth", msg);
        };
    </script>
</head>
<body>
    <form id="Form1" runat="server">
    <asp:Label ID="Label1" runat="server"></asp:Label>
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" CssClass="GridViewStyle">
    </asp:GridView>
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Test Postback" />
    </form>
</body>
</html>