erode

This is a tricky subject and its a bit complex to search - so I apologize for improper placement of this thread. I do believe it is best suited here for now.

My latest endeavor includes WPF, SQL, and a UI created in Blend. Ive spent a good deal of time doing basic examples of controls and XML binding.. really easy and SO powerful it blew my mind! I have a project I wish to implement my SQL Express 2005 database to.. I was not able to create the SQL connection within Blend - so I had to make the data source connection in VS2005, then load up the project in Blend. I was then presented with the opportunity to make a CLR binding to a dataset. Let this dataset be known as 'sampleDataSetDS'.

When in Blend, I can bind to many parts of the dataset because that is the beauty of databinding in this new technology. I could bind to the int32 Count of the array of Relations of my dataset. All I want to do is bind a listbox to my table and let it display all of the rows from a certain column. Is that so much to ask I cannot find a solid confirmation on how to do this and all of my efforts leave me with garbled junk. I am only familiar with ADO.NET in winforms 2.0 .. drag and drop OR using Data /text/tag binding properties. I am lost in this new world and nobody covers old fashioned SQL connections..just XML.

Appreciate the views,

erode




Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

enf_Elliot

Hey Erode,


I'm not 100% sure if this is what you're looking for, but it might help.

The way I've managed to do the databinding to elements is mainly though the code behind file. What I end up doing is:

1: Set up database functions
2: Run it
3: Save it to a datatable
4: Bind that data table to an element

5: Set that element to display the bound data

6: Make it so the listbox is formatted the way you want using a template

eg:
1 - Set up database functions

Code Snippet - Database functions in a class "DataAccess.cs"

/**************************************************************
* SQLSelect_Generic(String, String) *
**************************************************************
* Recieves a string of SQL code and a ConnectionString. *
* Then it goes and tries to connect to the database, and *
* executes the querey. The function returns a DataTable *
* containing the data returned from the querey's execution. *
* *
* Example of a ConnectionString: TestResultsConnectionString *
* L--> Gotten from app.config as the string used to connect*
**************************************************************/
public DataTable SQLSelect_Generic(String SQL, String ConnectionString)
{
try
{
//If no connection string is passed in just break out
//and return an empty DataTable
if (ConnectionString == String.Empty) return null;
//Set up a data table to hold the querey results
DataTable dt = new DataTable();
SqlConnection Connect = ConnectToDB(ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(SQL, Connect);
adapter.Fill(dt);
DisconnectFromDB();
return dt;
}
catch (Exception e)
{
sErrorMessage += e.Message;
throw e;
}
}//End SQLSelect_Generic(String, String) public DataTable SQLSelect_Generic(String SQL, String ConnectionString)
{
try
{
//If no connection string is passed in just break out
//and return an empty DataTable
if (ConnectionString == String.Empty) return null;
//Set up a data table to hold the querey results
DataTable dt = new DataTable();
SqlConnection Connect = ConnectToDB(ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(SQL, Connect);
adapter.Fill(dt);
DisconnectFromDB();
return dt;
}
catch (Exception e)
{
sErrorMessage += e.Message;
throw e;
}


/**************************************************************
* ConnectToDB(String) *
**************************************************************
* Recieves a string that contains a ConnectionString. *
* Then it goes and tries to connect to the database, and it *
* returns a SqlConnection object (conn) with the connection *
* whether it is null or full. If there is an error it will *
* the error message, and then return null. *
* *
* Example of a ConnectionString: TestResultsConnectionString *
* L--> Gotten from app.config as the string used to connect*
**************************************************************/
public SqlConnection ConnectToDB(String ConnectionString)
{
try
{
//Format a connection using the passed in ConnectionString (FOUND IN APP.CONFIG!!)
//Then use it to open the connection
conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString);
conn.Open(); //Open the connection to the DB
return conn; //return the connection object
}
catch (Exception ex)
{
//If no connection can be made
//return an error message and an empty object
sErrorMessage += ex.Message;
return null;
}
}//End ConnectToDB(String)


/**************************************************************
* DisconnectFromDB() *
**************************************************************
* Disconnects from the database. Invokes the .Close() *
* method of the SqlConnection object. If it can't close the *
* connection it will throw an error, and add its message to *
* the sErrorMessage string. *
**************************************************************/
public void DisconnectFromDB()
{
try
{
//close the db
conn.Close();
}
catch (Exception e)
{
//If object is NULL or the database cannot be
//closed, pick up on it and deal with it
sErrorMessage += e.Message;
throw e;
}
}//End DisconnectFromDB()



2/3- In codebehind file... (some event calls up function that returns the datatable)

Code Snippet

public DataTable getDataTableFor(String sql)
{
//Data table to hold data
DataTable myDataTable;
//Set up DataAccess object
DataAccess da = new DataAccess(strConnection);
myDataTable = null;
//1.3 Execute Query and fill in myDataTable with query data
myDataTable = da.SQLSelect_Generic(sql);
return myDataTable;
}


4/5/6 - Set the datacontext of the element you're binding to that datatable you just pulled (If you want to throw it in at startup put it in the init() of your mainwindow.cs code behind file after it initializes all the other components)

Code Snippet - Bind in the .cs file

lbMyListBox.DataContext = getDataTableFor(strMySqlQuery);


Code Snippet - Bind in the .xaml file


<Window.Resources>

<!-- Specify a name(x:Key) to bind the template by -->

<DataTemplate x:Key="tempMyListBoxTempate">

<Border BorderBrush="AliceBlue" BorderThickness="1" Padding="2" Margin="2">
<TextBlock>
<TextBlock Text="Title: " FontWeight="Bold"/>
<!-- When you bound the datacontext before you are now dealing with the table column specified in the "Binding Path = tableColumn" So, you want to make sure that you know your column names.-->

<TextBlock Text="{Binding Path=Title}" />
</TextBlock>
<TextBlock>
<TextBlock Text="Date: " FontWeight="Bold"/>
<!-- Once again the binding path is the table column name -->

<TextBlock Text="{Binding Path=Date}"/>
</TextBlock>
</StackPanel>
</Border>

</DataTemplate>

</Window.Resources>


...


<!-- Your list box, located in the window somewhere. The Binding Path may not have to be set(I'm not sure, because it is in the codebehind). The template was specified in the window/page/whatever's resources. This will fill the list box in with the template specified above. Note that the x:Name "lbMyListBox" is what we used in the codebehind file to set the datacontext -->

<ListBox
IsSynchronizedWithCurrentItem="True"
x:Name="lbMyListBox"
ItemsSource="{Binding Path=''}"
ItemTemplate="{StaticResource tempMyListBoxTempate
}" />


This is about as far as I got with it. The data binding isn't too bad. You might want to look at binding it to a CLR object , and creating your own class. That might be easier for what you're doing. Sorry if this didn't help, but if anything it'll be a stepping stone.. just hopefully not a jagged one!!

~Elliot


PS: Feel free to contact me if you need to, my email address is in my profile.




Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

erode

That was very helpful! I was really hoping for more of a CLR object approach. I am always open to anything that will help me achieve my goal.

If possible I'd like to contact you via email or maybe some sort of instant messenger if you've got some time to explain your code a little. I did not see your email in your profile - feel free ping me at mine. eric.rode at gmail.com

Appreciate it, this could work!






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

enf_Elliot

Yeah, not a problem. The email address should be in there now. The CLR approach is a good choice, but I haven't really used it that much because what I'm trying to do tends to deal with objects with many different kinds of data members. I have a post on here about what to do to pull items from a dataset (that contained a bunch of tables), and I ended up scrapping the idea and using an asynchronous approach using the queries to be executed only during the user's request.
Here is a pretty good resource on using CLR objects for binding (really simplistic):
http://blogs.msdn.com/danteg/archive/2007/03/07/databinding-to-clr-objects.aspx

Here is probably the most helpful spot on MSDN for data binding **my opinion**:
http://msdn2.microsoft.com/en-us/library/ms752039.aspx

Here's another for data templates (important when actually showing the data you bind):
http://msdn2.microsoft.com/en-us/library/ms742521.aspx

Here are a bunch of data binding example links, there are a few that use CLR objects:
http://msdn2.microsoft.com/en-us/library/ms771419.aspx

~Elliot




Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

erode

Here is my solution.. not completely ideal, I would have preferred to use Blend and using my CLR dataset to bind..

Connecting to Database / Filling the dataset

Dataset flixster3;

private void OnInit(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("Data Source=PCNAME\\SQLEXPRESS;Initial Catalog=flixster;Integrated Security=True");

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM filmInfo", conn);

flixster3 = new DataSet();

adapter.Fill(flixster3, "filmInfo");

listBox1.DataContext = flixster3;

}

XAML databinding to ListBox & ItemTemplate Example

<StackPanel >

<StackPanel.Resources>

<DataTemplate x:Key="titleTemplate">

<Grid>

<Grid.ColumnDefinitions>

<ColumnDefinition MinWidth="200" Width="250" />

<ColumnDefinition Width="*" />

</Grid.ColumnDefinitions>

<TextBlock Text="{Binding Path=title}" Grid.Column="0" FontSize="20" VerticalAlignment="Center" Foreground="White" FontWeight="Bold">

<TextBlock.BitmapEffect>

<OuterGlowBitmapEffect GlowColor="Black" GlowSize="7" />

</TextBlock.BitmapEffect>

</TextBlock>

<TextBlock Text="{Binding Path=tagline}" Grid.Column="1" VerticalAlignment="Center" />

</Grid>

</DataTemplate>

</StackPanel.Resources>

<ListBox Margin="10,150,10,10" Height="200" Name="listBox1" ItemsSource="{Binding Path=filmInfo}" ItemTemplate="{StaticResource titleTemplate}"/>

</StackPanel>






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

erode

Here is a quick app to show an example of what it looks like...

(IMG: http://img.photobucket.com/albums/v424/eric_r/databinding_SQL_WPF.jpg)






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

enf_Elliot

Hehe.. I decided to revisit this, because I really didn't help you that much. Here the same App using CLR objects. (Without the styling.. I'm not that great of an artist)

1. Make your class and populate the data
2. Make the UI components
3. Bind (Blend, or hardcode)

1: Here is a class that I made (Based off an example I found on MSDN)
Code Snippet - Movie.cs

/******************************************************
* Author: Elliot Franford
* Date: 6/19/2007
* I/O: None
* Comments: Declares classes for Movie and MovieList
* Revisions: none
******************************************************/
using System;
using System.Collections.Generic;
using System.Text;

namespace flickster
{
public class Movie
{
//Full dataset constructor
public Movie(string title, string description)
{
_title = title;
_description = description;
}
//Default constructor
public Movie()
{
_title = null;
_description = null;
}
//Title and description
private string _title;
private string _description;

//Getter for title and description
public string Title { get { return _title; } }
public string Description { get { return _description; } }

//List of movies getter and declaratino
public List<Movie> _movies;
public List<Movie> Movies { get { return _movies; } }
}

//New class MovieList that is simply a list of Movies
public class MovieList : List<Movie>
{
//Make the list
public MovieList()
{
/*********************************************
* USE DB HERE TO POPULATE DATA IF YOU NEED TO
* THIS EXAMPLE IS HARDCODED!!
*********************************************/
//put data in the list
Add( new Movie( "Apocalypto", "When the end comes, not everyone is ready to go."));
Add( new Movie( "Grandma's Boy", "Sex. Drugs. Nakedness. Rude language... And proud of it!"));
Add( new Movie( "300", "Prepare for glory!"));
Add( new Movie( "A.I Artificial Intelligence", "David is 11 years old. And he's a robot."));
Add( new Movie( "Blade Runner", "Man has made his match... Now it's his problem."));
Add( new Movie( "Hott Fuzz", "Big cops. Small town. Moderate violence."));
}
//Returns each Movie in the list collection
public Movie this[string title]
{
get
{
foreach (Movie m in this)
if (m.Title == title)
return m;

return null;
}
}
}
}


2. Make the listview (I put a gridview in)
3. In blend right click on the gridview and hit "bind items source to data"
-> Click on "+CLR Object"
-> Select "MovieList" and hit "OK"
-> Hit "OK" on the next screen
-> Right click on your gridview and hit "View XAML"
-> Modify the GridViewColumn tag to show all of our data for each column

Here's the end code XAML code for this solution. All the binding related items are bolded.
Code Snippet - Window1.xaml

<Window
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2006"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
xmlns:flickster="clr-namespace:flickster"
x:Class="flickster.Window1"
x:Name="Window"
Title="Flickster"
Width="865" Height="414"
>

<Window.Resources>
<ObjectDataProvider x:Key="MovieListDS" d:IsDataSource="True" ObjectType="{x:Type flickster:MovieList}"/>
</Window.Resources>
<Viewbox>
<Canvas Width="906.236" Height="406.604">
<ListView Width="832.46" Height="209.379" ItemsSource="{Binding Mode=OneWay, Source={StaticResource MovieListDS}}" Canvas.Top="162.844" IsSynchronizedWithCurrentItem="True" FontSize="14" Canvas.Left="21.961" >
<ListView.View>
<GridView >
<GridViewColumn Width="200" Header="Title" DisplayMemberBinding="{Binding Path=Title}" />
<GridViewColumn Width="800" Header="Description" DisplayMemberBinding="{Binding Path=Description}" />
</GridView>
</ListView.View>
</ListView>
</Canvas>
</Viewbox>
</Window>


There ya go!! Hope that answered some questions!!!
:-)
~Elliot




Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

erode

That is also a very useful way to get the job done.

However, with that, am I limited to hardcoding all of the data Or can I still fill a datatable with my SQL server That is the whole point of the exercise after all Smile

Thanks Elliot!






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

enf_Elliot

Hello again,
Yeah. You can still get stuff from the database.. Here's an example. I'm going to assume you have a database set up with a table called "Movies" and columns "Title" and "Description." Here is what this revised Movies class constructor would look like:

Movie List - Modified for DB Access
...
//New class MovieList that is simply a list of Movies
public class MovieList : List<Movie>
{
//Make the list
public MovieList()
{
//Set up the query to get the information
String sql = "Select Title, Description from Movies";

//DataAccess object for getting the connection
DataAccess da = new DataAccess("Connection String");

//DataTable to hold the movies
DataTable dtMovies = da.SQLSelect_Generic(sql);

//Step through each row in the dtMovies table and pull out the title/description from each row, and add them to the list
foreach(DataRow row in dtMovies.Rows)
{
Add( new Movie(row["Title"].ToString(), row["Description"].ToString()));
}

}
//Returns each Movie in the list collection
public Movie this[string title]
{
get
{
foreach (Movie m in this)
if (m.Title == title)
return m;

return null;
}
}
}
...

This can be accomplished using my DataAccess class. This should work just fine.
:-)
~Elliot




Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

Harish Suhanda

Hi All,

Untill now, I am unable to find out any method to only and only Expression Blend to bind my Controls to SQL Server. Are we bound to use CodeBehind Isn't there something like we have in Visual Studio 2005 to "Add Project DataSource" and simply follow the Wizard to select the DataSource, along with the SQL statement and blow... you are connected

Is it necessary to declare class and hard code the DataBinding using DataContext or bla bla...

I hope many of you will agree with me that Expression Blend needs to be more user friendly in context of working with database....

-Harish






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

JDPeckham

if you use a custom data table instead of the regular data table then you can bind to it as a collection because it's IEnumerable<YourCustomDataRow>. SInce using custom datasets is probably the preferred method for a 1 or 2 tier app that is why it works and what you're wanting to do doesn't. (they're type safe and regular datasets aren't)






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

erode

Harish Suhanda wrote:

Hi All,

Untill now, I am unable to find out any method to only and only Expression Blend to bind my Controls to SQL Server. Are we bound to use CodeBehind Isn't there something like we have in Visual Studio 2005 to "Add Project DataSource" and simply follow the Wizard to select the DataSource, along with the SQL statement and blow... you are connected

Is it necessary to declare class and hard code the DataBinding using DataContext or bla bla...

I hope many of you will agree with me that Expression Blend needs to be more user friendly in context of working with database....

-Harish

I couldn't agree with you any more, Harish. I believe this functionality will come in due time, but right now is an awkward time. These development tools are not finalized yet and in the next year I would think everything will be implemented.

The moment those functions become implemented, everybody will know.. it'll be a big deal I think.






Re: Windows Presentation Foundation (WPF) Databinding SQL in Expression Blend

Harish Suhanda

Thank you very much for your reply Eric.

It is really a good news to all who are seeking to make Expression Blend as their favorite tool. :-)

For time being I am struggling to figure, how to move to next and previous record on the button click if I bound my Text box to a DataContext, which actually stores a DataTable from my SQLExpress database. Here is my code snippet:

Dim theDataTable As New DataTable

Dim Str As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)\sqlexpress"

Dim qry As String = "Select FirstName,LastName from Employees"

Dim Con As New SqlConnection(Str)

Dim adp As New SqlDataAdapter(qry, Con)

adp.Fill(theDataTable)

DataContext = theDataTable

XAML:

<Grid>

<TextBox Text="{Binding Path=FirstName}" Height="18" Margin="106,50,94,0" Name="TextBox1" VerticalAlignment="Top" ></TextBox>

<Label Height="23.2766666666667" HorizontalAlignment="Left" Margin="28.37,46.7233333333333,0,0" Name="Label1" VerticalAlignment="Top" Width="67.63">First Name</Label>

<Label Height="23.2766666666667" HorizontalAlignment="Left" Margin="34.37,101.723333333333,0,0" Name="Label2" VerticalAlignment="Top" Width="66.63">Last Name</Label>

<TextBox Text="{Binding Path=LastName}" Height="20" Margin="108,103,92,0" Name="TextBox2" VerticalAlignment="Top"></TextBox>

<Button Height="23" Content="Prev" HorizontalAlignment="Left" Margin="49,0,0,109" Name="Button1" VerticalAlignment="Bottom" Width="75" />

<Button Height="23" Content="Next" Margin="0,0,73,109" Name="Button2" VerticalAlignment="Bottom" HorizontalAlignment="Right" Width="75"/>

</Grid>