Date: 06/22/06 (Asp Dot Net)    Keywords: database, sql

I posted this in another group but got like no responses.

Ok so now that I have my unions and memory issues resolved, now I need to fix this screwed up data that I have in my database.

I have a badly created Document Control system that stores all the documents in the database in 2 tables: Current and Versioned (those are aliases.) Current obviously holds all the current documents and the Versioned table has all the old versions. The problem is though that not all the information corresponds with the correct records. For example, if you have 5 versions of a document, the version notes (which we use for ISO) for the most recent version (5) actually are associated with Version 4, along with the date it was submitted. Also, the original document version (1) has version notes and a submitted date with it as well, but obviously the first version can't have version notes so those are actually referring to the next version (2). If this sounds confusing its because it is. I don't know who designed this damn thing, but as a lesson let me say to NOT use Tribune's BMS content Management system for your document control because they don't do good database design and they blatantly steal their code from Wrox's "How to build an Intranet" book.

So anyways I need to figure out how to get these records to correspond to the correct versions, so basically every record actually refers to the next record. Also it should be noted that SOME of the information does correspond, so I only need to move information from certain fields, not the entire row.


SqlDataAdapter da = new SqlDataAdapter("SELECT d.mdItemID AS CurrentID, d.mdFileDescription AS CurrentFile, d.mdFileName as CurrentFileName, tblUser.usUserName AS Creator, " +
"d.mdCreatedDate AS OriginalDocDate, tblMics_Docs_Versions.mvSubItemID AS OldID, " +
"tblMics_Docs_Versions.mvFileName as OldFileName, usr2.usUserName as ModifiedBy, tblMics_Docs_Versions.mvNotes AS VersionNotes, " +
"tblDepartment.dpNAME AS DepartmentName, tblMics_Docs_Versions.mvVersionedDate AS sort_date " +
"FROM tblMics_Docs d INNER JOIN tblDepartment ON d.mdDepartmentID = tblDepartment.dpID " +
"INNER JOIN tblUser On d.mdCreatedByUserID = tblUser.usUserID JOIN tblMics_Docs_Versions ON d.mdItemID = tblMics_Docs_Versions.mvItemID " +
"INNER JOIN tblUser usr2 on tblMics_Docs_Versions.mvCheckedInByUserID = usr2.UsUserID " +
"WHERE d.mdArchived = 0 UNION " +
"SELECT d.mdItemID AS CurrentID, d.mdFileDescription AS CurrentFile, d.mdFileName as CurrentFileName, " +
"tblUser.usUserName AS Creator, d.mdCreatedDate AS SubmittedDate, null, null, null, null, tblDepartment.dpNAME, getdate() as sort_date " +
"FROM tblMics_Docs d INNER JOIN tblDepartment ON d.mdDepartmentID = tblDepartment.dpID " +
"INNER JOIN tblUser On d.mdCreatedByUserID = tblUser.usUserID JOIN tblMics_Docs_Versions ON d.mdItemID = tblMics_Docs_Versions.mvItemID " +
"INNER JOIN tblUser usr2 on tblMics_Docs_Versions.mvCheckedInByUserID = usr2.UsUserID WHERE d.mdArchived = 0 ORDER BY mdItemID ASC, sort_date ASC", con);

try
{
da.Fill(ds);
}

catch (Exception j)
{
j.ToString();
}

ds.Tables[0].Columns.Add("Version");
ds.Tables[0].Columns.Add("FileLocation");

Int16 currID;
int lastID = 0;
int VerNum = 1;
//This code below is used to add the version numbers to the documents because the CM system
//Doesn't keep track and just uses a Count statement in its SQL code.

foreach (DataRow dr in ds.Tables[0].Rows)
{
currID = System.Convert.ToInt16(dr["CurrentID"]);
if ( currID == lastID )

{
dr["Version"] = VerNum;
lastID = currID;
}
else
{
VerNum = 1;
dr["Version"] = VerNum;
lastID = currID;
}
if (dr["oldID"] == DBNull.Value )

{
dr["FileLocation"] = "BMS Current\\" + dr["DepartmentName"] + "\\" + dr["CurrentID"] + " Version " +
dr["Version"] + " " + dr["CurrentFileName"];
}
else
{
dr["FileLocation"] = "BMS Archive\\" + dr["DepartmentName"] + "\\" + dr["CurrentID"] + " Version " +
dr["Version"] + " " + dr["OldFileName"];
}
VerNum ++;
}

dg1.DataSource = ds.Tables[0];
ds.Tables.Add("files");

con.Close();
//con.Dispose();
ds.Dispose();
da.Dispose();
}

I can try and post pictures of this in action if that'll help. Unfortunately my SQL skills aren't that good and I haven't had alot of experience with datasets. Thanks.

RESOLVED: I gave up on trying to do this in SQL and instead implemented an array to store the inconsistent values and then put them in their correct places.

Source: http://community.livejournal.com/aspdotnet/71275.html