One of the shortcomings of the object datasource is that it doesn’t support relations properly. It only gives you a single table view of an object if that object is a dataset or datatable.
But what about the relationships etc. Well my first choice was to create specific views etc. But I found it to be a real hassle combined with stored procedures to maintain once the project starts growing a bit.
From the dataset I want a structure that looks like my database. Nothing more nothing less. The same relations and constraints there etc.
How do you accomplish that ? Well it’s going to be a bit of a lengthy post this one because it’s a lot of code.
This is the schema I’ll be talking about :
I have a series of objects in my business layer .. all wrappers around tableadapters. to give you an idea of the complexity of the other classes.
I should also mention that I made the Connection property public on the dataset so that I could open it at will in my business object and save some time opening and closing objects.
I use a base class for all the datasource objects (this is not production code and has now been completely changed to use base4)
This would be the base class :
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using NBlogr.Dal.NBlogrDataSetTableAdapters;
5 using NBlogr.Dal;
6 using System.ComponentModel;
7 using System.Web;
8 using System.Data.SqlClient;
9 using System.Configuration;
10 using System.Data;
11
12 namespace NBlogr.Bll.Base
13 {
14 publicabstractclassDataSourceObject
15 {
16 protectedHttpContext _context;
17 System.Web.Security.MembershipUser _user;
18
19 privateNBlogrDataSet dataSet;
20 privateSqlConnection connection;
21 privatebool autoPersist;
22 privateConnectionState previousState;
23
24 publicbool AutoPersist
25 {
26 get { return autoPersist; }
27 set { autoPersist = value; }
28 }
29
30
31 publicSqlConnection Connection
32 {
33 get
34 {
35 if (connection == null)
36 this.connection = newSqlConnection(ConfigurationManager.ConnectionStrings["NBlogr.DAL.Properties.Settings.NBlogrConnectionString"].ConnectionString);
37 returnthis.connection;
38 }
39 set
40 {
41 this.connection = value;
42 }
43 }
44
45 publicNBlogrDataSet DataSet
46 {
47 get
48 {
49 if (dataSet == null)
50 dataSet = newNBlogrDataSet();
51 return dataSet;
52 }
53 set { dataSet = value; }
54 }
55
56
57 public System.Web.Security.MembershipUser User
58 {
59 get {
60 if (_user == null)
61 _user = System.Web.Security.Membership.GetUser();
62 return _user; }
63 set { _user = value; }
64 }
65
66 publicGuid UserId
67 {
68 get
69 {
70 return (Guid)User.ProviderUserKey;
71 }
72 }
73
74 public DataSourceObject()
75 {
76 _context = HttpContext.Current;
77 }
78
79 protectedvoid OpenConnection(){
80 previousState = this.Connection.State;
81 if (this.Connection.State != ConnectionState.Open)
82 Connection.Open();
83 }
84
85 protectedvoid CloseConnection(bool force)
86 {
87 if (previousState == ConnectionState.Closed||force)
88 {
89 Connection.Close();
90 }
91 }
92 protectedvoid CloseConnection()
93 {
94 CloseConnection(AutoPersist);
95 }
96 }
97 }
And this is the object that gets the complete entries from the database and saves them again.
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using NBlogr.Dal.NBlogrDataSetTableAdapters;
5 using System.ComponentModel;
6 using NBlogr.Dal;
7 using System.Web;
8 using System.Transactions;
9
10 namespace NBlogr.Bll
11 {
12 [DataObject]
13 publicclassFullEntry : Base.DataSourceObject
14 {
15 Entry entryObject;
16 Comment commentObject;
17 Category categoryObject;
18 Attachment attachmentObject;
19 Blog blogObject;
20 EntryTableAdapter taEntry = newEntryTableAdapter();
21 CategoryTableAdapter taCategory = newCategoryTableAdapter();
22
23 EntryCategoryTableAdapter taEntryCategory;
24 EntryAttachmentTableAdapter taEntryAttachment;
25
26 public FullEntry()
27 {
28 entryObject = newEntry(false);
29 commentObject = newComment(false);
30 categoryObject = newCategory(false);
31 attachmentObject = newAttachment(false);
32 blogObject = newBlog(false);
33
34 taEntryAttachment = newEntryAttachmentTableAdapter();
35 taEntryCategory = newEntryCategoryTableAdapter();
36 taEntryCategory.ClearBeforeFill = false;
37 taEntryAttachment.ClearBeforeFill = false;
38 entryObject.Connection = commentObject.Connection = entryObject.Connection = categoryObject.Connection =
39 taEntryCategory.Connection = taEntryAttachment.Connection = this.Connection;
40 entryObject.DataSet = commentObject.DataSet = entryObject.DataSet = categoryObject.DataSet = this.DataSet;
41 }
42
43 publicNBlogrDataSet.EntryRow GetById(Guid Id)
44 {
45 OpenConnection();
46
47 attachmentObject.GetByEntry(Id);
48 commentObject.GetByEntry(Id);
49 categoryObject.GetByEntry(Id);
50 taEntryCategory.FillByEntry(DataSet.EntryCategory, Id);
51 taEntryAttachment.FillByEntry(DataSet.EntryAttachment, Id);
52
53 NBlogrDataSet.EntryRow entry = entryObject.GetRow(Id);
54
55 blogObject.FindById(entry.BlogId);
56
57 CloseConnection(true);
58
59 return entry;
60 }
61
62 [DataObjectMethod(DataObjectMethodType.Select)]
63 publicNBlogrDataSet.EntryDataTable GetEntryListByBlogId(Guid BlogId)
64 {
65 OpenConnection();
66
67 categoryObject.GetByBlog(BlogId);
68 taEntryCategory.FillByBlogId(DataSet.EntryCategory, BlogId);
69 entryObject.GetByBlog(BlogId);
70
71 CloseConnection(true);
72
73 return DataSet.Entry;
74 }
75
76 [DataObjectMethod(DataObjectMethodType.Select)]
77 publicNBlogrDataSet.EntryDataTable GetFrontpageEntryList(Guid BlogId)
78 {
79 OpenConnection();
80
81 categoryObject.GetByBlog(BlogId);
82 taEntryCategory.FillByBlogId(DataSet.EntryCategory, BlogId);
83 entryObject.GetFrontPage(BlogId);
84
85 CloseConnection(true);
86
87 return DataSet.Entry;
88 }
89
90 [DataObjectMethod(DataObjectMethodType.Select)]
91 publicNBlogrDataSet.EntryDataTable GetEntryListByMonthYear(decimal Month, decimal Year, Guid BlogId)
92 {
93 OpenConnection();
94
95 categoryObject.GetByBlog(BlogId);
96 taEntryCategory.FillByBlogId(DataSet.EntryCategory, BlogId);
97 entryObject.GetByMonthYear(BlogId, Month, Year);
98
99 CloseConnection(true);
100
101 return DataSet.Entry;
102 }
103
104 [DataObjectMethod(DataObjectMethodType.Select)]
105 publicNBlogrDataSet.EntryDataTable GetEntryListByCategory(string CategoryName, Guid BlogId)
106 {
107 OpenConnection();
108
109 Guid categoryId = categoryObject.GetIdByName(CategoryName);
110 categoryObject.GetById(categoryId);
111 taEntryCategory.FillByCategory(DataSet.EntryCategory, categoryId);
112 entryObject.GetByCategory(categoryId,BlogId);
113
114 CloseConnection(true);
115
116 return DataSet.Entry;
117 }
118
119 publicNBlogrDataSet.BlogRow GetDefaultBlog()
120 {
121 return blogObject.GetDefault()[0];
122 }
123
124
125 publicvoid AddComment(Guid Id, string Body, string AuthorName, string AuthorEmail, string AuthorHomepage, string AuthorIpAddress)
126 {
127 commentObject.Add(Id, Body, AuthorName, AuthorEmail, AuthorHomepage, AuthorIpAddress);
128 }
129
130 publicvoid AddFiles(HttpFileCollection files)
131 {
132 foreach (HttpPostedFile file in files)
133 {
134 attachmentObject.Save(file);
135 }
136 }
137
138 publicGuid AddEntry(Guid BlogId, string Title, string Body, bool IsPublic, bool Syndicated, bool ShowOnFrontPage, bool AllowComments, DateTime? CreateDate, List<Guid> Categories)
139 {
140 Guid Id = Guid.Empty;
141 OpenConnection();
142
143 try
144 {
145 Id = entryObject.Add(BlogId, Title, Body, IsPublic, Syndicated, ShowOnFrontPage, AllowComments, CreateDate);
146
147 entryObject.Persist();
148
149 foreach (Guid category in Categories)
150 {
151 taEntryCategory.Insert(category, Id);
152 }
153 }
154 catch { }
155 finally
156 {
157
158 CloseConnection(true);
159 }
160
161 return Id;
162 }
163
164 publicvoid UpdateEntry(Guid BlogId, string Title, string Body, bool IsPublic, bool Syndicated, bool ShowOnFrontPage, bool AllowComments, DateTime? CreateDate, List<Guid> Categories, List<Guid> RemovedCategories, Guid Original_Id)
165 {
166
167 OpenConnection();
168
169 try
170 {
171 entryObject.Update(BlogId, Title, Body, IsPublic, Syndicated, ShowOnFrontPage, AllowComments, CreateDate,Original_Id);
172
173 entryObject.Persist();
174
175 taEntryCategory.FillByEntry(DataSet.EntryCategory, Original_Id);
176
177 foreach (Guid category in Categories)
178 {
179 if (DataSet.EntryCategory.FindByCategoryIdEntryId(category, Original_Id) == null)
180 taEntryCategory.Insert(category, Original_Id);
181 }
182 foreach (Guid category in RemovedCategories)
183 {
184 if (DataSet.EntryCategory.FindByCategoryIdEntryId(category, Original_Id) != null)
185 taEntryCategory.Delete(category, Original_Id);
186 }
187 }
188 catch { }
189 finally
190 {
191
192 CloseConnection(true);
193 }
194 }
195
196 publicvoid DeleteEntry(Guid EntryId)
197 {
198 OpenConnection();
199
200 NBlogrDataSet.EntryRow entry = GetById(EntryId);
201
202 foreach (NBlogrDataSet.EntryAttachmentRow attachment in DataSet.EntryAttachment)
203 {
204 attachment.Delete();
205 }
206 foreach (NBlogrDataSet.EntryCategoryRow category in DataSet.EntryCategory)
207 {
208 category.Delete();
209 }
210 foreach (NBlogrDataSet.CommentRow comment in DataSet.Comment)
211 {
212 comment.Delete();
213 }
214
215 entry.Delete();
216
217 taEntryAttachment.Update(DataSet.EntryAttachment);
218 taEntryCategory.Update(DataSet.EntryCategory);
219 commentObject.Persist();
220 entryObject.Persist();
221
222 CloseConnection();
223 }
224
225 }
226 }
Then all that is left to do is show you the code from the page and you know how I did it with success
So this is the declarative part :
<%
@
Page
Language
=”C#”
MasterPageFile
=”~/NBlogrMaster.master”
AutoEventWireup
=”true”
CodeFile
=”Default.aspx.cs”
Inherits
=”_Default”
Title
=”NBlogr – Open Source Atlas Blogging”
%>
<
asp
:
Content
ID
=”Content2″
ContentPlaceHolderID
=”SideBarTop”
runat
=”Server”>
asp
:
Content
>
<
asp
:
Content
ID
=”Content1″
ContentPlaceHolderID
=”Main”
Runat
=”Server”>
<
asp
:
GridView
ID
=”gvEntries”
AutoGenerateColumns
=”false”
DataSourceID
=”odsFullEntry”
Width
=”100%”
GridLines
=”none”
runat
=”server”
ShowHeader
=”false”
OnRowDataBound
=”gvEntries_RowDataBound”
DataKeyNames
=”Id”
>
<
Columns
>
<
asp
:
TemplateField
>
<
ItemTemplate
>
<
asp
:
Image
SkinID
=”postbullet”
ID
=”postBullet”
AlternateText
=’
<%# Eval(“Title”) %>‘runat=”server”/><b><%# Eval(“Created”,”{0:dddd, dd MMMM yyyy}”) %>b>
<
hr
/>
<
div
>
<
h5
><
asp
:
HyperLink
ID
=”hlTitle”
NavigateUrl
=’
<%# Eval(“Id”,”~/Detail.aspx?entry={0}”) %>‘Text=’<%# Eval(“Title”) %>‘runat=”server”>asp:HyperLink>h5>
<%
# Eval(“Body”) %>
div
>
<
hr
/>
<
div
style
=”float:right”><
asp
:
Literal
ID
=”litCategories”
runat
=”server”>
asp
:
Literal
>
div
>
<%
# Eval(“Created”,”{0:r}”) %>
ItemTemplate
>
asp
:
TemplateField
>
Columns
>
<
EmptyDataTemplate
>
There are currently no posts available. Try again later.
EmptyDataTemplate
>
asp
:
GridView
>
<
asp
:
ObjectDataSource
ID
=”odsCategories”
runat
=”server”
InsertMethod
=”Add”
OldValuesParameterFormatString
=”original_{0}”
SelectMethod
=”GetAll”
TypeName
=”NBlogr.BLL.Category”>
<
InsertParameters
>
<
asp
:
ControlParameter
Name
=”Name”
Type
=”String”
ControlID
=”Category”
PropertyName
=”Text”
/>
InsertParameters
>
asp
:
ObjectDataSource
>
<
asp
:
ObjectDataSource
ID
=”odsFullEntry”
runat
=”server”
OldValuesParameterFormatString
=”original_{0}”
OnObjectCreated
=”odsFullEntry_ObjectCreated”
SelectMethod
=”GetFrontpageEntryList”
TypeName
=”NBlogr.Bll.FullEntry”>
<
SelectParameters
>
<
asp
:
SessionParameter
Name
=”BlogId”
SessionField
=”CurrentBlog”
Type
=”Object”
/>
SelectParameters
>
asp
:
ObjectDataSource
>
<
asp
:
ObjectDataSource
ID
=”odsFullEntryMonthYear”
runat
=”server”
OnObjectCreated
=”odsFullEntry_ObjectCreated”
OldValuesParameterFormatString
=”original_{0}”
SelectMethod
=”GetEntryListByMonthYear”
TypeName
=”NBlogr.Bll.FullEntry”
>
<
SelectParameters
>
<
asp
:
QueryStringParameter
Name
=”Month”
QueryStringField
=”month”
Type
=”Decimal”
/>
<
asp
:
QueryStringParameter
Name
=”Year”
QueryStringField
=”year”
Type
=”Decimal”
/>
<
asp
:
SessionParameter
Name
=”BlogId”
SessionField
=”currentBlog”
Type
=”Object”
/>
SelectParameters
>
asp
:
ObjectDataSource
>
<
asp
:
ObjectDataSource
ID
=”odsFullEntryCategory”
runat
=”server”
OnObjectCreated
=”odsFullEntry_ObjectCreated”
OldValuesParameterFormatString
=”original_{0}”
SelectMethod
=”GetEntryListByCategory”
TypeName
=”NBlogr.Bll.FullEntry”>
<
SelectParameters
>
<
asp
:
QueryStringParameter
Name
=”CategoryName”
QueryStringField
=”category”
Type
=”String”
/>
<
asp
:
SessionParameter
Name
=”BlogId”
SessionField
=”CurrentBlog”
Type
=”Object”
/>
SelectParameters
>
asp
:
ObjectDataSource
>
asp
:
Content
>
And here is the code behind. The bit you’re interested in, is the gvEntries_RowDataBound and setDataSource method:
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Collections;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11 using NBlogr.Bll;
12 using NBlogr.Dal;
13 using System.Collections.Generic;
14 using System.Collections.Specialized;
15 using NBlogr.Dal.NBlogrDataSetTableAdapters;
16
17 publicpartialclass_Default : System.Web.UI.Page
18 {
19
20 FullEntry entryObject = newFullEntry();
21
22
23 public _Default()
24 {
25
26 this.LoadComplete += newEventHandler(_Default_LoadComplete);
27 }
28
29 void _Default_LoadComplete(object sender, EventArgs e)
30 {
31 if (!IsPostBack)
32 {
33 setDataSource();
34 }
35 }
36
37 privateBlogListRetrieval displayMode()
38 {
39 if(!string.IsNullOrEmpty(Request.QueryString["category"])) returnBlogListRetrieval.ByCategory;
40
41 string year = Request.QueryString["year"];
42 string month = Request.QueryString["month"];
43 if (!string.IsNullOrEmpty(year) && !string.IsNullOrEmpty(month))
44 {
45 returnBlogListRetrieval.ByMonthYear;
46 }
47 returnBlogListRetrieval.HomePage;
48 }
49
50 protectedvoid Page_Load(object sender, EventArgs e)
51 {
52 }
53 privatevoid setDataSource()
54 {
55 gvEntries.DataSource = null;
56 switch (displayMode())
57 {
58 default:
59 caseBlogListRetrieval.HomePage:
60 gvEntries.DataSourceID = odsFullEntry.ID;
61 break;
62 caseBlogListRetrieval.ByMonthYear:
63 gvEntries.DataSourceID = odsFullEntryMonthYear.ID;
64 break;
65 caseBlogListRetrieval.ByCategory:
66 gvEntries.DataSourceID = odsFullEntryCategory.ID;
67 break;
68
69 }
70
71 }
72
73 protectedvoid gvEntries_RowDataBound(object sender, GridViewRowEventArgs e)
74 {
75 if (e.Row.RowType == DataControlRowType.DataRow)
76 {
77 if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
78 {
79 Literal litCategories = ((Literal)e.Row.FindControl(“litCategories”));
80 litCategories.Text = “”;
81 NBlogrDataSet.EntryCategoryRow[] entrycats = entryObject.DataSet.Entry[e.Row.DataItemIndex].GetEntryCategoryRows();
82 foreach (NBlogrDataSet.EntryCategoryRow entrycat in entrycats)
83 {
84
85 litCategories.Text += entrycat.CategoryRow.Name + ” | “;
86 }
87 if (entrycats.Length > 0)
88 litCategories.Text = litCategories.Text.Remove(litCategories.Text.LastIndexOf(” | “));
89
90 }
91 elseif(e.Row.RowState == DataControlRowState.Edit)
92 {
93 CheckBoxList lst = (CheckBoxList)e.Row.FindControl(“CategoryList”);
94 NBlogrDataSet.EntryCategoryRow[] entrycats = entryObject.DataSet.Entry[e.Row.RowIndex].GetEntryCategoryRows();
95 foreach (NBlogrDataSet.EntryCategoryRow entrycat in entrycats)
96 {
97 lst.Items.FindByValue(entrycat.CategoryId.ToString()).Selected = true;
98 }
99
100 }
101 }
102
103
104 }
105
106
107 protectedvoid odsFullEntry_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
108 {
109 e.ObjectInstance = entryObject;
110 }
111 }
