Monday, 5 March 2012

Using ASP.NET 4.0 Chart Control With New Tooling Support for SQL Server CE 4.0 in VS 2010 SP1 and Entity Model

n this post I’m going to talk about  how we can use ASP.NET 4.0 Chart Control with SQL CE as back-end data base using Entity Framework. I will also show how Visual Studio 2010 SP1 provides new tooling supports for SQL Server CE 4.0. ASP.NET 4.0 introduced inbuilt chart controls features and Visual Studio 2010 SP1 Came up with nice tooling support for SQL Server CE. SQL CE is a free, embedded, lightweight  database engine that enables easy database storage. This does not required any installation and runs in memory.  Let’s see how we can place this together and create a small apps and deploy it using  new “Web Deployment Tool” which is available with Visual Studio 2010 SP1.


To demonstrate the complete flow  we will do the following steps.
  • Creating a New ASP.NET 4.0 Web Forms Application.
  • Create a SQL Server CE Database with new tooling of Visual Studio 2010 SP1.
  • Adding ASP.NET Chart Control to web forms.
  • Building an EF model layer to attaching with ASP.NET 4.0 Chart Control.
  • Using Same Model layer with ASP.NET GridView with Enabling  Edit / Delete support of records in SQL CE Data base.
  • Reflecting the changes in Chart Controls after editing records from GridView.
  • Dynamically changing ASP.NET 4.0 Chart Control Type.
  • Deploy using Web Deployment Tool.

After end of the above mentioned exercises we will achieve  something like below :
image
So Let’s start. First  create a new ASP.NET 4.0 Web Application and navigate to Solution Explorer (Solution Navigator) . In the Project solution hierarchy Right click on the “App_Data” folder  and select   “Add  > New Item” menu command.
Create New Web Application and Add New Item

This will open “Add Items Dialogs”  and then choose “SQL Server Compact 4.0 Local Database”,  Give the name “Students.sdf” and click on “Add

image
Above step will create an empty SQL Server Compact database for local data.
image

Double click on the “Student.sdf” it will brings up the “Server Explorer” with the same data base “Students.sdf” which we have already created. Visual Studio treats the “Students.sdf” as a new Data Base Connection.

image

 Tip : In this situation if you want to add a new SQL Server CE Connection or any chance you are not able to view the connection for created sdf file or got disconnect,  you can simply brings it back by doing followings
1. Click on “Connect To Data Base Icon

image

2.  This will brings up the “Add Connection Dialog” but default Data Source is set to “Microsoft SQL Server ( SqlClient) . Click on “Change..”

image

3. In “Change Data Source” dialog, select “Microsoft SQL Server Compact 4.0”  as Data Source and by it will have only single Data Provider “.NET Framework Data Provider for Microsoft SQL Server Compact 4.0

image
on click of “Ok” it will navigate back to “Add Connection Window”.
4. There you can “Create a New SQL CE DB” or can browse  existing “SQL CE DB
image
Above 4 steps talks about create a new connection and SQL CE DB from Server Explore or using Existing one.  But we have already created our “Students.sdf”. So lets back to action.
In Server Explorer , Expand Students.sdf and click on “Create Table”
image
This will brings up “Add New Table” window where you have to fill the Table name and corresponding column name.  Here We have used table name as “Student” with three column “Roll”, “Name”, “Marks
image
On click of “Ok”, you will see a new table with name “Student” has been created under the table folder .
image
Let’s have a look how we can enter data into the created table. There is two ways by which we  achieve the same.  In Server Explorer, right click on the table. From the context menu you can select either of “New Query” option where you have to write simple SQL Statement to store records or else you can select “Show Tables Data” where enter data in different column. Very straight forward.
image
below screen shows how we can insert data in SQL CE table using “New Query” mode.
image
Well, by using any of them just entered few data in Student table.
image
We will be displaying above data in ASP.NET Chart control. So lets build the ASP.NET Web Forms with Chart Control
ASP.NET 4 introduced inbuilt chart control which you can find in the “Data” section of toolbox .
image
Drag and Drop the Chart control in web page and choose any of the  Chart Type from Chart control Smart Tag.
image
Now we have to choose data source for the Chart Control.  So, let create a EF Model to read the data from SQL CE Data Base.
From Solution Explorer > Add > New Item, Select “ADO.NET Entity Data Model” and give the name “Students.edmx
image
Then Select “Generate from Database” option from “Entity Data Model Wizard
image
Continue with the Wizard, in the next screen, you have to provide connection and entity name.
imageimage
Click on “Finish”, you can see the generated “edmx” file
image
Just Build the solution once, and back to web page with Chart Control where we stopped earlier.
Selectoption from from Chart Control Smart Tag.
image
This will launch the “Data Source Configuration Wizard” , Select “Entity” type and specify the name “StudentEntityDataSource” and click on OK.
image
From the below screen select Named Connection and Default Container name. Both of these dropdown populate automatically as we have already created the entity model. In the next window, select all the column and Enable Automatic insert, updates and deletes.
imageimage
I just enabled the automatic insert, delete and update for future use with gridview. Click on Finish !
Once done with the data source configuration, provides the value for Chart Series Data Member .
image
That’s all. Run the application. You will get below output at first look.
image
Let’s make this stuff more interesting, First I will change the appearance to 3D, don’t worry. This is also inbuilt support !
image
On changes of above code snippet for the Chart control you will get below output.
image
Let me know bring one GridView in the seen so that you can see the Edit and update reflection to ASP.NET Chart Control that refers the same SQL Server CE Data Base.
I am referring the same Entity data source that used for Chart Control. This GridView also Enabled with Editing Rows that is the raised while creating the model I made the auto editable.
image
Below images is the first appearance after putting GridView.
image
Now, you can edit the records in grid and get the same reflection on Chart. Because  both are referring to same records.
01<asp:UpdatePanel ID="UpdatePanel1" runat="server">
02        <ContentTemplate>
03            <table border="0" cellpadding="0" cellspacing="0">
04                <tr>
05                    <td>
06                        <div>
07                            <asp:Chart ID="Chart1" runat="server" DataSourceID="StudentEntityDataSource" Palette="Chocolate"
08                                BorderlineColor="Window">
09                                <Series>
10                                    <asp:Series Name="Series1" XValueMember="Name" YValueMembers="Marks">
11                                    </asp:Series>
12                                </Series>
13                                <ChartAreas>
14                                    <asp:ChartArea Name="ChartArea1">
15                                        <Area3DStyle Rotation="15" Perspective="10" Enable3D="True" Inclination="15" IsRightAngleAxes="False"
16                                            WallWidth="1" IsClustered="False" />
17                                    </asp:ChartArea>
18                                </ChartAreas>
19                                <BorderSkin BackColor="Highlight" />
20                            </asp:Chart>
21                        </div>
22                        <div>
23                            <asp:Label ID="Label1" Text="Select Chart Type" runat="server" />
24                            <asp:DropDownList ID="ddlCharType" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlCharType_SelectedIndexChanged">
25                            </asp:DropDownList>
26                        </div>
27                    </td>
28                    <td valign="top">
29                        <asp:GridView ID="GridView1" runat="server" CellPadding="4" DataSourceID="StudentEntityDataSource"
30                            ForeColor="#333333" GridLines="None">
31                            <AlternatingRowStyle BackColor="White" />
32                            <Columns>
33                                <asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
34                            </Columns>
35                            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
36                            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
37                            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
38                            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
39                            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
40                            <SortedAscendingCellStyle BackColor="#FDF5AC" />
41                            <SortedAscendingHeaderStyle BackColor="#4D0000" />
42                            <SortedDescendingCellStyle BackColor="#FCF6C0" />
43                            <SortedDescendingHeaderStyle BackColor="#820000" />
44                        </asp:GridView>
45                    </td>
46                </tr>
47            </table>
48            <asp:EntityDataSource ID="StudentEntityDataSource" runat="server" ConnectionString="name=StudentsEntities"
49                DefaultContainerName="StudentsEntities" EnableDelete="True" EnableFlattening="False"
50                EnableInsert="True" EnableUpdate="True" EntitySetName="Students">
51            </asp:EntityDataSource>
52        </ContentTemplate>
53    </asp:UpdatePanel>
image
In the above picture I have shown two scenarios. Upper section is the chart before updating records and bottom section after updating the records.  You can also see the snaps of updated  SQL CE Data base. Yes, very simple !
To make it more interesting let’s implement the dynamically change the Chart Type.
image
If you are wondering how to  implement this, here we go. Let’s put a dropdown control in the web forms.
All the Chart Type for ASP.NET 4.0 Chart control are defined in a public enum SeriesChartType.  So first bind the SeriesChartType enum value in dropdown.  You can read one of my post on Generic Way to Bind Enum With Different ASP.NET List Controls .
01protected void Page_Load(object sender, EventArgs e)
02       {
03           if (!Page.IsPostBack)
04           {
05               BindEnumToListControls(typeof(SeriesChartType), ddlCharType);
06           }
07       }
08 
09       /// <summary>
10       /// Binds the enum to list controls.
11       /// </summary>
12       /// <param name="enumType">Type of the enum.</param>
13       /// <param name="listcontrol">The listcontrol.</param>
14       public void BindEnumToListControls(Type enumType, ListControl listcontrol)
15       {
16           string[] names = Enum.GetNames(enumType);
17           listcontrol.DataSource = names.Select((key, value) =>
18                                       new { key, value }).ToDictionary(x => x.key, x => x.value + 1);
19           listcontrol.DataTextField = "Key";
20           listcontrol.DataValueField = "Value";
21           listcontrol.DataBind();
22       }
Change the Chart type on Drop Down Selection index change.
1protected void ddlCharType_SelectedIndexChanged(object sender, EventArgs e)
2       {
3           this.Chart1.Series["Series1"].ChartType = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), ddlCharType.SelectedItem.Text);
4       }
Finally, let have a look into the deployment. Why I am talking deployment over here ? Yes the only reason of SQL Server CE. As I said, we do not need any installation of SQL Server CE DB if we want to move our data base, but we have to provide some associated dll that helps ASP.NET engine to interact with the DB. Visual Studio 2010 SP1 introduced one new features “Web Deployment Tool”.  Right Click on the Solution file, and select “Add Deployable Dependencies…” .
image
This is only available for Razor and SQL Server CE.
image
Select “Select SQL Server Compact”  checkbox and “Click on Ok” . This will add all required assembiles automatcially in your solution and your are good to deploy without any problem
image
Well, that’s all. To summarizes what I have discussed is using ASP.NET 4.0 Chart Control with SQL Server CE 4.0. I have also discussed how we can use power of SQL Server CE Tool support which is introduced in Visual Server 2010 SP1 along with deployment using Web Deployment Tool.
Hope this will help you !
Cheers !

reffer to http://abhijitjana.net
 

No comments :