In 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.
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.
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”
Above step will create an empty SQL Server Compact database for local data.
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.
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”
2. This will brings up the “Add Connection Dialog” but default Data Source is set to “Microsoft SQL Server ( SqlClient) . Click on “Change..”
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”
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”
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”
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“
On click of “Ok”, you will see a new table with name “Student” has been created under the table folder .
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.
below screen shows how we can insert data in SQL CE table using “New Query” mode.
Well, by using any of them just entered few data in Student table.
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 .
Drag and Drop the Chart control in web page and choose any of the Chart Type from Chart control Smart Tag.
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”
Then Select “Generate from Database” option from “Entity Data Model Wizard”
Continue with the Wizard, in the next screen, you have to provide connection and entity name.
Click on “Finish”, you can see the generated “edmx” file
Just Build the solution once, and back to web page with Chart Control where we stopped earlier.
Selectoption from from Chart Control Smart Tag.
This will launch the “Data Source Configuration Wizard” , Select “Entity” type and specify the name “StudentEntityDataSource” and click on OK.
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.
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 .
That’s all. Run the application. You will get below output at first look.
Let’s make this stuff more interesting, First I will change the appearance to 3D, don’t worry. This is also inbuilt support !
On changes of above code snippet for the Chart control you will get below output.
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.
Below images is the first appearance after putting GridView.
Now, you can edit the records in grid and get the same reflection on Chart. Because both are referring to same records.
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.
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 .
Change the Chart type on Drop Down Selection index change.
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…” .
This is only available for Razor and SQL Server CE.
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
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,
Original Post by,
Abhijit Jana
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.
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.
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”
Above step will create an empty SQL Server Compact database for local data.
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.
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 followings1. Click on “Connect To Data Base Icon”
2. This will brings up the “Add Connection Dialog” but default Data Source is set to “Microsoft SQL Server ( SqlClient) . Click on “Change..”
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”
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”
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”
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“
On click of “Ok”, you will see a new table with name “Student” has been created under the table folder .
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.
below screen shows how we can insert data in SQL CE table using “New Query” mode.
Well, by using any of them just entered few data in Student table.
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 .
Drag and Drop the Chart control in web page and choose any of the Chart Type from Chart control Smart Tag.
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”
Then Select “Generate from Database” option from “Entity Data Model Wizard”
Continue with the Wizard, in the next screen, you have to provide connection and entity name.
Click on “Finish”, you can see the generated “edmx” file
Just Build the solution once, and back to web page with Chart Control where we stopped earlier.
Selectoption from from Chart Control Smart Tag.
This will launch the “Data Source Configuration Wizard” , Select “Entity” type and specify the name “StudentEntityDataSource” and click on OK.
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.
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 .
That’s all. Run the application. You will get below output at first look.
Let’s make this stuff more interesting, First I will change the appearance to 3D, don’t worry. This is also inbuilt support !
On changes of above code snippet for the Chart control you will get below output.
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.
Below images is the first appearance after putting GridView.
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> |
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.
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 .
01 | protected void Page_Load(object sender, EventArgs e) |
02 | { |
03 | if (!Page.IsPostBack) |
04 | { |
05 | BindEnumToListControls(typeof(SeriesChartType), ddlCharType); |
06 | } |
07 | } |
08 | /// <summary> |
09 | /// Binds the enum to list controls. |
10 | /// </summary> |
11 | /// <param name="enumType">Type of the enum.</param> |
12 | /// <param name="listcontrol">The listcontrol.</param> |
13 | public void BindEnumToListControls(Type enumType, ListControl listcontrol) |
14 | { |
15 | string[] names = Enum.GetNames(enumType); |
16 | listcontrol.DataSource = names.Select((key, value) => |
17 | new { key, value }).ToDictionary(x => x.key, x => x.value + 1); |
18 | listcontrol.DataTextField = "Key"; |
19 | listcontrol.DataValueField = "Value"; |
20 | listcontrol.DataBind(); |
21 | } |
Change the Chart type on Drop Down Selection index change.
1 | protected 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…” .
This is only available for Razor and SQL Server CE.
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
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,
Original Post by,
Abhijit Jana
No comments:
Post a Comment