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 :

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.

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

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.

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 !

reffer to http://abhijitjana.net
 

Custom Script Quickly with SSMS sql server 2008

Run Your Custom Script Quickly with SSMS

Suppose you have to do a task repeatedly with different objects of database, for example, you have to generate code or stored procs of table in the specific format, you created your custom SQL script which takes table name and generate the code. But each time, you have to execute query and pass table name.
What’ll happen If you right click on the table and select your script to execute. It’ll save bunch of time as well as stop you getting irritated Let’s see how to do using SSMS Tools Pack.

SSMS Tools Pack:

It is the best free Sql Server Management Studio(SSMS)’s add-in having lots of features like Execution Plan Analyzer, SQL Snippets, Execution History, Search Table, View or Database Data, Search Results in Grid Mode, Generate Insert statements from result sets, tables or database, Regions and Debug sections, CRUD stored procedure generation, New query template..etc.
Website Link
Download Link

Steps:

Suppose you want to see total number of records in different tables.
1. Click on SSMS Tools > “Run Custom Script” > Options

2. Left side of dialog, Enter Shortcut ‘Total Records‘ and check Run checkbox

3. Right Side, Click on ‘Enabled On..‘ option and select ‘table’. It means this will appear on each table.
4. Enter following script
Select Count(*) from |SchemaName|.|ObjectName|
Here |SchemaName| and |ObjectName| are replacement texts. You can see available replacements options on ‘Replacement Texts‘ click.

Click on Save and OK button.
5. Now, Right click on any table > “Select SSMS Tools” > Run Custom Scripts > Total Records

You will get Total Records in result and no need to pass table name in query again and again.




Similarly, you can run your other custom scripts quickly. It is very helpful as a code generation tool. Let me know how you are using it.

DataBinding in WPF Browser Application using SQL Server Compact

• Download source - 50.76 KB

Background

After completing my first blog post (nearly 6 months back ;-)), I had plans to create a browser application for the same. This article will help you to create a WPF browser application with basic technique of data binding using SQL Server compact 3.5 SP1, so without waiting for any more time, let’s start our first WPF browser application.

Pre-Requisite

• Visual Studio 2008/2010
• SQL Server 2008

Using the Code

Once you have both the pre-requisites installed on your machine, launch “Visual Studio 2010” --> Select “New Project” to create a new WPF Browser Application as shown in the below screenshot:
Select “Windows” --> Select “WPF Browser Application” and provide a name for the application, Click “Ok” to create a new WPF browser application.
So now, we have created a new application, our next agenda is to create a GUI (Graphics User Interface). Please find below the controls I have used:

Label	Control	Control Name
First Name	Text Box	FirstName_txt
Last Name	Text Box	LastName_txt
Date Of Birth	DatePicker	DOB_txt
City	ComboBox	City_txt
	DataGrid	Details_Grid
New	Button	New_btn
Add	Button	Add_btn
Delete	Button	Del_btn
Update	Button	Update_btn

I have used ADO.NET for data binding to Datagrid, it's very simple to bind the data very quickly. Find below the XAML code for the DataGrid:

Collapse | Copy Code

<DataGrid AutoGenerateColumns="False" Height="181" 
 HorizontalAlignment="Left" Margin="12,188,0,0" Name="Details_Grid" 
 VerticalAlignment="Top" Width="518" ItemsSource="{Binding Path=MyDataBinding}" 
 CanUserResizeRows="False" Loaded="Details_Grid_Loaded" 
 SelectedCellsChanged="Details_Grid_SelectedCellsChanged">
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Path=fName}" 
  Header="First Name" Width="120" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=lName}" 
  Header="Last Name" Width="120" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=DOB}" 
  Header="Date Of Birth" Width="150" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding Path=City}" 
  Header="City" Width="120" IsReadOnly="True" />
            </DataGrid.Columns>
</DataGrid> 

In the above code, I have binded the data to the Datagrid by giving the binding path name to “ItemsSource” attribute as “MyDataBinding” which will refer to the dataset name which I have declared in “BindGrid()” method. To have a customized view of the data in the datagrid, here I used DataGrid columns so that we can have our own order of displaying data as shown in the below screenshot:
For the first column, I have used “DataGridTextColumn” and referred to the database column name “fname” for “First Name” so that the value directly binded to this column.
So we are all set to write the code for the functionalities, will start with “Add” a data to the database. Just double click on “Add” button, it will create a new click event and put the code as shown below:

Collapse | Copy Code

private void Add_btn_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlCeConnection Conn = new SqlCeConnection(Connection_String);

                // Open the Database Connection
                Conn.Open();

                string Date = DOB_txt.DisplayDate.ToShortDateString();

                // Command String
                string Insert_Cmd = @"insert into Details(fName,lName,DOB,City) Values
  ('" + FirstName_txt.Text + "','" + LastName_txt.Text +"','" + 
  Date.ToString() + "','" + City_txt.Text + "')";

                // Initialize the Command Query and Connection
                SqlCeCommand cmd = new SqlCeCommand(Insert_Cmd, Conn);

                // Execute the Command
                cmd.ExecuteNonQuery();

                MessageBox.Show("One Record Inserted");
                FirstName_txt.Text = string.Empty;
                LastName_txt.Text = string.Empty;
                DOB_txt.Text = string.Empty;
                City_txt.Text = string.Empty;

                this.BindGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

To get the shortdate, I have used “ToShortDateString()” method for DatePicker control. There are many methods available with this control and you can use it according to your requirement.
In the above code, I used “sqlCeConnection” to establish connection to SQL Server compact database. To do this, you need to add reference for SQL Server Compact as shown in the below screenshots:
Right-Click on References --> Select “Add Reference”.
Browse to “C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop” --> Select “System.Data.SqlServerCe.dll” and Click “Ok”. Now you can see “Sql Server Compact” listed in the references as shown below:
To use the methods of this namespace, call it in the header as we do for SQL Server.

Collapse | Copy Code

using System.Data.SqlServerCe;

I have used App.Config file to store the database connection details and I have called the connection string name in the code to establish the database connection.

App.Config File

Collapse | Copy Code

<configuration>
  <connectionStrings>
    <add name="ConnectionString1" 
 connectionString="Data Source=<Location of the Database file Goes here>
 \DatabindusingWPF.sdf; Password=test@123; Persist Security Info=False;"/>
  </connectionStrings>
</configuration>

The benefit of using App.Config file to store all the application configurations in one file or one place, similar to Web.Config in ASP.NET applications.
To get the connection string value from the app.config file, we have to create a reference in our code, so I have used “ConfigurationManager” of the namespace “System.Configuration” as shown in the below code:

Collapse | Copy Code

string Connection_String = ConfigurationManager.ConnectionStrings
    ["ConnectionString1"].ConnectionString; 

To know more about System.Configuration class, refer to this link.
To bind data to Datagrid, I have created a method namely “BindGrid” and I have called the method in all the other events to refresh the Datagrid data.

Collapse | Copy Code

public void BindGrid()
        {
            try
            {
                SqlCeConnection Conn = new SqlCeConnection(Connection_String);

                //Open the Database Connection
                Conn.Open();

                SqlCeDataAdapter Adapter = new SqlCeDataAdapter
     ("Select * from Details", Conn);

                // Bind Data to DataSet
                DataSet Bind = new DataSet();
                Adapter.Fill(Bind, "MyDataBinding");

                // Bind Data to DataGrid
                Details_Grid.DataContext = Bind;

                // Close the Database Connection
                Conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

So we need to write code for other functionalities like New, Update and Delete (Please refer to the attached project).
Now we are done with the design and coding part of our first WPF Browser project and we need to test our project, to do so hit “F5”,
OOPs, I got the above error!!! (If you didn’t see this error and our project is executed … Wow .. you are lucky ;-)).
I searched this error on the internet and referred to many links, but nothing worked for me. In one forum, I found deleting “app.manifest” and create new “app.manifest” will work. Wow, it worked for me. :-)
I know this is not the right solution for this issue, so I am still working on it. If anyone comes across any other solution for this issue, please do post your finding as comments in this space. Even I will do the same if I come across any ;-) Ok deal …
Cool, so now it’s launching without any error.
I found that browser title as “Databinding_WPF_Browser.xbap” which I want to change so I have added “WindowTitle” attribute in Page tag in XAML file as "DataBinding in WPF using SQL Server Compact".
The browser title has been changed now :-) and I have tested the functionalities by adding, deleting and updating the data in the datagrid. So we have successfully created and tested our project.
Happy programming :-).