July 2003 - Posts
I've completed my playing with the MS Data Access Application Block for now. Its time to start saving time and writing cleaner code with it now. Here is a ZIP containing a webform and codebehind containing calls to Sqlhelper for binding to a grid. The guts of the thing are below, intended to be uncommented by sample to demo various Sqlhelper methods. The .ASPX contains a label and datagrid. That's it. Also included is a SQL script to create a couple of test procs in PUBS.
// shared connection and query strings
string cn = "server=localhost;uid=sa;pwd=;database=pubs";
string sql = "select au_id, au_lname from authors where au_lname like 'm%' ";
string sql2 = "select pub_id, pub_name from publishers where pub_name like '%pub%' ";
string sql3 = "select title_id, title from titles where title like '%computer%' ";
// SAMPLE ONE: using FillDataSet with multiple query strings in a single pass
// DataSet ds = new DataSet();
// SqlHelper.FillDataset(cn,CommandType.Text, sql + sql2 + sql3, ds, new string[]{"authors","pubs","titles"});
// lblTableName.Text = ds.Tables[2].TableName.ToString();
// dgtest.DataSource = ds.Tables[2].DefaultView;
// See my DAAB, Part 2 post below on updating Sqlhelper.cs to use tablenames, i.e., ds.Tables["titles"].DefaultView;
// SAMPLE TWO: using ExecuteDataSet with multiple sql statements. Returns Table0, Table1, etc.
// lblTableName.Text = SqlHelper.ExecuteDataset(cn, CommandType.Text, sql + sql2).Tables[1].TableName;
// dgtest.DataSource = SqlHelper.ExecuteDataset(cn, CommandType.Text, sql + sql2).Tables[1].DefaultView;
// SAMPLE THREE: using ExecuteDataSet with stored procedure and two string param input values
// string lname_initial = "m";
// string state = "CA";
// dgtest.DataSource = SqlHelper.ExecuteReader(cn, "p_authors", new String[]{lname_initial, state});
// SAMPLE FOUR: using ExecuteDataSet with stored procedure and one string param, one integer input values
// string stor_id = "8042";
// int qty = 15;
// dgtest.DataSource = SqlHelper.ExecuteReader(cn, "p_sales", new String[]{stor_id, qty.ToString()});
|
How's it go? This posting and all associated code is provided "AS IS" with no warranties, and confers no rights. Use at own risk and have fun.
I was reading Jim Edelen's post on running Sharepoint on a notebook and freeing up memory and decided to see where my 512MB of memory was being spent at the moment. I went through and shut down non-essential services like SQL Server and SQL Server Agent (these freed up @20MB, as was Jim's observation), then I exited Adobe Photoshop 6.0. I do a lot of utility graphic work through my day and so I keep Photoshop open but minimized for quick use. I couldn't believe what I saw when over 200MB of memory was freed by closing Photoshop! Hey, I'm a big Adobe Photoshop and Illustrator fan, but this was one of those times I am reminded that they aren't the most efficiently designed Windows apps out there. I will certainly make a practice of exiting Photoshop when I'm not using it in the future.
In response to an earlier post on using msdab v2.0 with vs.net 1.0, here is the requested port I made to VS.NET 1.0 and have been working without problem. I am not yet completely through testing it (and note from another post that I changed SqlHelper.cs by adding a for() {loop} to update table names on lines 1848-1851), but so far it works fine with VS.NET 1.0. Unzip and go, baby.
I always enjoy Roy Osherove's writings on ADO.NET and other .NET subjects and so I studied his Filling Typed datasets posts which included a reported DAB bug, originally reported by Mark Brown on the dotnetjunkies blog site.
The bug, as excellently described by Mark in his blog post had to do with the SqlHelper.FillDataset() method, which creates a dataset with multiple tables. I applied "the fix" but still received
<ds>
<correcttable0name></correcttable0name>
<table1></table1>
<table2></table2>
</ds>
instead of
<ds>
<correcttable0name></correcttable0name>
<correcttable1name></correcttable1name>
<correcttable2name></correcttable2name>
</ds>
when using the sweet one-pass code found on Roy's post provided by Lior Rozner:
public ApartmentDataset GetDataset()
{
ApartmentDataset ds = new ApartmentDataset();
SqlHelper.FillDataset(CON_STRING,CommandType.Text,
"select * from Apartments " +
"select * from Cities " +
"select * from Statuses ",
ds,
new string[]{"Apartments",
"Cities",
"Statuses"});
return ds;
}
So I more closely examined the fix, and it didn't make sense, that is, the tables beyond the 0-index string array passed to SqlHelper.FillDataset were still going to be named Table+index# and that the fix did nothing to correctly name the tables (unless I'm missing something, which is always a possibility...) I tried several alterations to the fix until I realized that the sure way to name my dataset tables correctly was to iterate through and rename them after the dataset was populated in the FillDataset method a couple of lines below (@ 1847):
dataAdapter.Fill(dataSet);
for (int index=0; index < tableNames.Length; index++)
{
dataSet.Tables[index].TableName = tableNames[index].ToString();
}
That did the trick. Now when I use the one-pass approach I can use ds.tables["tablename"].whatever instead of ds.tables[2].whatever.
In review, if you want to use a single pass approach and have multiple tables correctly named, leave the code in SQLHelper.cs as it is and add the for loop after the da.Fill(ds) statement.
I'm just getting into the Microsoft Data Access Application Block and am going through each of the methods. I know the data app block will save me time and my employer money. I look forward to investigating all of the app blocks.
First of all, our IT shop is moving to Visual Studio 2003 and Windows Server 2003 over the next few weeks, but I wanted to get started with the data app block (v.2.0), even though it "requires" VS.NET 1.1 and I'm using 1.0. I created a new VS.NET 1.0 project called Microsoft.ApplicationBlocks.Data, and essentially copied-n-pasted the SQLHelper.cs and AssemblyInfo.cs text contents into corresponding .cs files and after using it for a couple of hours, everything compiles and works great. So unless I'm missing something anyone waiting to get into the data access block v2.0 until they get VS.NET 1.1 don't have to wait.
Secondly, I'm re-discovering how great the 4guysfromrolla site is. Here are two articles which were very helpful in getting up and running with the data block: Examining the dab and Introduction to MS app blocks, both authored by John Jakovich.
I have a third issue with the data app regarding a reported bug with I'll post separately.
Man, its great to be back to work after a 2-week vacation! Two weeks from the work is a week too long, and I don't understand people who think its such a wonderful thing to get away from work for 2 or more weeks at a stretch. They must not be Microsoft .NET developers! :-) The first week was swell, but once into the second week I was going nuts waiting in Casablanca. (You may recall that on our last episode my laptop's hard-drive died the day before we left for vacation, so I was determined to go analog cold turkey. It was enough for me to do double-shots of 4mg nicorettes again.) Here are pics from the beach.
I'll be offline for the next two weeks and in a North Carolina beachfront house with the family. I had planned on studying the ASP.NET Quickstarts and the MSDN Application Blocks among a bunch of other archived goodies I loaded on my laptop: DevTeach and TechEd presentations, code with books I've read, etc. So I had everything loaded and yesterday AM my laptop's hard drive dies. I'm pretty sure it was a sign from God telling me to get away from curly brackets and semi-colons for a couple of weeks. So I figure its a good time to read Harry Potter instead of, say, Applied XML Programming for Microsoft .NET. Oh wait, I already read that.
I am looking forward to what you .net bloggers have been up the next two weeks.
Peace.
I wanted to copy a dataview to a datatable this afternoon and it proved an interesting case. The data consisted of file and folder info, and I needed to group by folder name with an onitembound call to determine the count of files by folder. The dataview was sorted, filtered, and ready to go, yet I couldn't make the leap.
A dotnet newsgroup post to "Neil" read:
Neil,
You could use something like that
1. Use RowFilter property of your DataView to filter your rows.
2. Copy all rows to array using CopyTo method of DataView.
3. Use ImportRow or LoadDataRow method of DataTable to load rows from array into DataTable
--
Val Mazur
Microsoft MVP
(Thanks Val!) Which made great sense to me, but I kept getting datarow to datarowview mistype errors. So I wanted to know more about this DataView CopyTo method. The VS.NET help page was one of those lamers you occasionally encounter at
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/
frlrfSystemDataDataViewClassCopyToTopic.htm
And further Googling made me think that it was a phantom method that no one really uses. So I ended up with the following. The cool thing was that I discovered the datatable.select() method. Didn't know about that at all!
| Okay, so it isn't true Visual Inheritance, but it seems pretty cool to be nevertheless. The circled menu-blocks on the screenshot consists of 3 different .ASCX files with each using the same code-behind .CS file. Why do this? Each menu-block control uses a different stylesheet, has different dimensions, and other unique qualities which are presentation-specific. So instead of taking the time to pass each of these properties in a single .ASCX/.CS, since the logic is exactly the same for each menu-block, why duplicate it? This approach saved me a lot of time, though I can hear coders conversing like Seinfeld and Castanza with, "Not that there's anything wrong with that..." |
 |
Each ASCX file starts the same:
<%@ Control Language="c#" AutoEventWireup="false" Codebehind="_genplace.ascx.cs" Inherits="proj.C_Genplace" %>
The control .ASCXs are distinguished in the .ASPX page file by giving each a different tagname when registering, as in
<%@ Register TagPrefix="PROJ" TagName="C_Genplace" Src="/controls/ii/home/_genplace.ascx" %>
<%@ Register TagPrefix="PROJ" TagName="C_Gencenplace" Src="/controls/ii/home/_gencenterplace.ascx" %>
when placing them in the .ASPX with their specific TagName and id, as in
<PROJ:C_Gencenplace id="uc_gencenclients" runat="server"></PROJ:C_Gencenplace>
<PROJ:C_Genplace id="uc_genrightlocs" runat="server"></PROJ:C_Genplace>
and finally in the code-behind with the single class name and ids from the .ASPX
protected proj.C_Genplace uc_gencenclients, uc_genrightlocs;
In search of a Discovery of the Day, I again must describe a Stupidity o.t.d. experience. At least I am a mature-enough developer to first ask the question: What am I doing wrong? No one will learn much from this post, but can at least empathize with being momentarily stuck and finding humor in the problem once discovered.
I inherited a project (the long naming style ain't mine) and had to fix a bug in one of its data layer components. I kept receiving the familiar (well, not that familiar) "There is no row at position 0" error. The problem should be immediately obvious to most dotnetweblogs readers. (DataAdapter sqlAdapter1 was created earlier in method, so the dataset was being populated from another sqlStatement.)
I would step through the code repeatedly, and every line would happily step through. Yet the "no row at position 0" error persisted. Had I written the code from scratch rather than copy-paste from surrounding lines within the same method, I would have quickly seen that I was using a data adapter created earlier with a different sqlStatement than the one I had intended.
Moral of the story: We developers do awesome things with code, yet can encounter momentary lapses writing basic processes. I for one would rather be responsible for understanding these basic processes and occasionally finding humor in doing something stupid than to use a code generator approach and not have a sound understanding in .net coding principles.
More Posts