XSD DataSets and ignoring foreign keys

Question: I have a pretty standard table set-up in a current application using the .NET XSD DataSet and TableAdapter features. My contracts table consists of some standard contract information, with a column for the primary department. This column is a foreign key to my Departments table, where I store the basic department name, id, notes. This is all setup and functioning in my SQL Server.
When I use the XSD tool, I can drag both tables in at once and it auto detects/creates the foreign key I have between these two tables. This works great when I'm on my main page and am viewing contract data.
However, when I go to my administrative page to modify the department data. I typically do something like this:

Dim dtDepartment As New DepartmentDataTable()
Dim taDepartment As New DepartmentTableAdapter()

taDepartment.Fill(dtDepartment)

However, at this point an exception is thrown saying to the effect that there is a foreign key reference broken here, I'm guessing since I don't have the Contract DataTable filled.
How can I fix this problem? I know I can simply remove the foreign key from the XSD to make things work fine, but having the additional integrity check there and having the XSD schema match the SQL schema in the database is nice.

Solution:  You can try turning Check-constraints off on the DataSet (it's in its properties), or altering the properties of that relationship, and change the key to a simple reference - up to you.

How to export data from SQL Server 2005 to MySQL

Question: I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated, so you can't deal with any row that has a string with a comma in it (or whatever you use as a delimiter) and I would still have to hand write all of the create table statements, as obviously CSV doesn't tell you anything about the data types.

What would be better is if there was some tool that could connect to both SQL Server and MySQL, then do a copy. You lose views, stored procedures, trigger, etc, but it isn't hard to copy a table that only uses base types from one DB to another... is it?
Does anybody know of such a tool? I don't mind how many assumptions it makes or what simplifications occur, as long as it supports integer, float, datetime and string. I have to do a lot of pruning, normalising, etc. anyway so I don't care about keeping keys, relationships or anything like that, but I need the initial set of data in fast!

Solution: Using MSSQL Management Studio i've transitioned tables with the MySQL OLE DB. Right click on your database and go to "Tasks->Export Data" from there you can specify a MsSQL OLE DB source, the MySQL OLE DB source and create the column mappings between the two data sources.

You'll most likely want to setup the database and tables in advance on the MySQL destination (the export will want to create the tables automatically, but this often results in failure). You can quickly create the tables in MySQL using the "Tasks->Generate Scripts" by right clicking on the database. Once your creation scripts are generated you'll need to step through and search/replace for keywords and types that exist in MSSQL to MYSQL.
Of course you could also backup the database like normal and find a utility which will restore the MSSQL backup on MYSQL. I'm not sure if one exists however.

How would you access Object properties from within an object method?

Question: What is the "purist" or "correct" way to access an object's properties from within an object method that is not a getter/setter method?
I know that from outside of the object you should use a getter/setter, but from within would you just do:
Java:

String property = this.property;

PHP:

$property = $this->property;

or would you do:
Java:

String property = this.getProperty();

PHP:

$property = $this->getProperty();

I am talking about private or protected variables/properties only. When I learned OO I was taught to use getters/setters for every single property even if it was public (and actually I was told never to make any variable/property public). So, I may be starting off from a false assumption from the get go. It appears that people answering this question are maybe saying that you should have public properties and that those don't need getters and setters, which goes against what I was taught, and what I was talking about, although maybe that needs to be discussed as well. That's probably a good topic for a different question though...

Solution: This has religious war potential, but it seems to me that if you're using a getter/setter, you should use it internally as well - using both will lead to maintenance problems down the road (e.g. somebody adds code to a setter that needs to run every time that property is set, and the property is being set internally w/o that setter being called).

Java lib or app to convert CSV to XML file?

Question: Is there an existing application or library in Java which will allow me to convert a CSV data file to XML file?
The XML tags would be provided through possibly the first row containing column headings

Solution:  OpenCsv for parsing CSV (small, simple, reliable and easy to use)
Xstream to parse/serialize XML (very very easy to use, and creating fully human readable xml) Using the same sample data as above, code would look like:

import java.io.FileReader;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;

import com.thoughtworks.xstream.XStream;

public class CsvToXml {     

    public static void main(String[] args) {

        String startFile = "./startData.csv";
        String outFile = "./outData.xml";

        try {
            CSVReader reader = new CSVReader(new FileReader(startFile));
            String[] line = null;

            String[] header = reader.readNext();

            List out = new ArrayList();

            while((line = reader.readNext())!=null){
                List<String[]> item = new ArrayList<String[]>();
                    for (int i = 0; i < header.length; i++) {
                    String[] keyVal = new String[2];
                    String string = header[i];
                    String val = line[i];
                    keyVal[0] = string;
                    keyVal[1] = val;
                    item.add(keyVal);
                }
                out.add(item);
            }

            XStream xstream = new XStream();

            xstream.toXML(out, new FileWriter(outFile,false));

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Producing the following result: (Xstream allows very fine tuning of the result...)

<list>
  <list>
    <string-array>
      <string>string</string>
      <string>hello world</string>
    </string-array>
    <string-array>
      <string>float1</string>
      <string>1.0</string>
    </string-array>
    <string-array>
      <string>float2</string>
      <string>3.3</string>
    </string-array>
    <string-array>
      <string>integer</string>
      <string>4</string>
    </string-array>
  </list>
  <list>
    <string-array>
      <string>string</string>
      <string>goodbye world</string>
    </string-array>
    <string-array>
      <string>float1</string>
      <string>1e9</string>
    </string-array>
    <string-array>
      <string>float2</string>
      <string>-3.3</string>
    </string-array>
    <string-array>
      <string>integer</string>
      <string>45</string>
    </string-array>
  </list>
  <list>
    <string-array>
      <string>string</string>
      <string>hello again</string>
    </string-array>
    <string-array>
      <string>float1</string>
      <string>-1</string>
    </string-array>
    <string-array>
      <string>float2</string>
      <string>23.33</string>
    </string-array>
    <string-array>
      <string>integer</string>
      <string>456</string>
    </string-array>
  </list>
  <list>
    <string-array>
      <string>string</string>
      <string>hello world 3</string>
    </string-array>
    <string-array>
      <string>float1</string>
      <string>1.40</string>
    </string-array>
    <string-array>
      <string>float2</string>
      <string>34.83</string>
    </string-array>
    <string-array>
      <string>integer</string>
      <string>4999</string>
    </string-array>
  </list>
  <list>
    <string-array>
      <string>string</string>
      <string>hello 2 world</string>
    </string-array>
    <string-array>
      <string>float1</string>
      <string>9981.05</string>
    </string-array>
    <string-array>
      <string>float2</string>
      <string>43.33</string>
    </string-array>
    <string-array>
      <string>integer</string>
      <string>444</string>
    </string-array>
  </list>
</list>

ASP.NET Site Maps

Question: Has anyone got experience creating SQL-based ASP.NET site-map providers?

I've got the default XML file web.sitemap working properly with my Menu and SiteMapPath controls, but I'll need a way for the users of my site to create and modify pages dynamically.
I need to tie page viewing permissions into the standard ASP.NET membership system as well.

Solution:  Here's my fix in BuildSiteMap():

SiteMapNode node = GetSiteMapNodeFromReader(reader);
string url = node.Url;
if (url.Contains(":"))
{
    string garbage = Guid.NewGuid().ToString();  // SiteMapNode needs unique URLs
    node.Url = "~/dummy_" + garbage + ".aspx";
    AddNode(node, _root);
    node.Url = url;
}
else
{
    AddNode(node, _root);
}

SQLDependency caching is cool, but if you don't want to make a trip to the DB everytime your menu loads (to check to see if the dependency has changed) and your menus don't change very often, then why not use HttpRuntime.Cache instead?

public override SiteMapNode RootNode
{
    get
    {
        SiteMapNode temp = (SiteMapNode)HttpRuntime.Cache["SomeKeyName"];
        if (temp == null)
        {
            temp = BuildSiteMap();
            HttpRuntime.Cache.Insert("SomeKeyName", temp, null, DateTime.Now.AddHours(1), Cache.NoSlidingExpiration);
        }
        return temp;
    }
}

Decoding T-SQL CAST in C#/VB.NET

Question: Recently our site has been deluged with the resurgence of the Asprox botnet SQL injection attack. Without going into details, the attack attempts to execute SQL code by encoding the T-SQL commands in an ASCII encoded BINARY string. It looks something like this:

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500...06F007200%20AS%20NVARCHAR(4000));EXEC(@S);--

I was able to decode this in SQL, but I was a little wary of doing this since I didn't know exactly what was happening at the time.
I tried to write a simple decode tool, so I could decode this type of text without even touching SQL Server. The main part I need decoded is:

CAST(0x44004500...06F007200 AS
NVARCHAR(4000))

I've tried all of the following commands with no luck:

txtDecodedText.Text =
    System.Web.HttpUtility.UrlDecode(txtURLText.Text);
txtDecodedText.Text =
    Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.Unicode.GetString(Encoding.Unicode.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.ASCII.GetString(Encoding.Unicode.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.Unicode.GetString(Convert.FromBase64String(txtURLText.Text));

What is the proper way to translate this encoding without using SQL Server? Is it possible? I'll take VB.NET code since I'm familiar with that too.

Okay, I'm sure I'm missing something here, so here's where I'm at.
Since my input is a basic string, I started with just a snippet of the encoded portion - 4445434C41 (which translates to DECLA) - and the first attempt was to do this...

txtDecodedText.Text = Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(txtURL.Text));

...and all it did was return the exact same thing that I put in, since it converted each character into is byte.
I realized that I need to parse each two characters into a byte manually since I don't know of any methods yet that will do that, so now my little decoder looks something like this:

while (!boolIsDone)
{
    bytURLChar = byte.Parse(txtURLText.Text.Substring(intParseIndex, 2));
    bytURL[intURLIndex] = bytURLChar;
    intParseIndex += 2;
    intURLIndex++;

    if (txtURLText.Text.Length - intParseIndex < 2)
    {
        boolIsDone = true;
    }
}

txtDecodedText.Text = Encoding.UTF8.GetString(bytURL);

Things look good for the first couple of pairs, but then the loop balks when it gets to the "4C" pair and says that the string is in the incorrect format.
Interestingly enough, when I step through the debugger and to the GetString method on the byte array that I was able to parse up to that point, I get ",-+" as the result.
How do I figure out what I'm missing - do I need to do a "direct cast" for each byte instead of attempting to parse it?

Solution: did some more poking and realized that I did need to do a double conversion, and eventually worked out this little nugget:

Convert.ToString(Convert.ToChar(Int32.Parse(EncodedString.Substring(intParseIndex, 2), System.Globalization.NumberStyles.HexNumber)));

From there I simply made a loop to go through all the characters 2 by 2 and get them "hexified" and then translated to a string.

Anatomy of a “Memory Leak”

Question: In .NET perspective:

• What is a Memory Leak?
• How can you determine whether your application leaks? What are the effects?
• How can you prevent a memory leak?
• If your application has memory leak, does it go away when the process exits or is killed? Or do memory leaks in your application affect other processes on the system even after process completion?
• And what about unmanaged code accessed via COM Interop and/or P/Invoke?

I have some answers for these questions myself, but they are incomplete. What do you think?

Solution: The best explanation I've seen is in Chapter 7 of the free Foundations of Programming ebook.

Basically, in .NET a memory leak occurs when referenced objects are rooted and thus cannot be garbage collected. This occurs accidentally when you hold on to references beyond the intended scope.
You'll know that you have leaks when you start getting outofmemoryexceptions or your memory usage goes up beyond what you'd expect (perfmon has nice memory counters).
Understanding .NET's memory model is your best way of avoiding it. Specifically, understanding how the garbage collector works and how references work (again, I refer you to chapter 7 of the ebook). Also, be mindful of common pitfalls, probably the most common being events. If object A registered to an event on object B, then object A will stick around until object B disappears because B holds a reference to A. The solution is to unregister your events when you're done.
Of course, a good memory profile will let you see your object graphs and explore the nesting/referencing of your objects to see where references are coming from and what root object is responsible (red-gate ants profile, JetBrains dotMemory, memprofiler are really good choices, or you can use the text-only windbg and sos, but I'd strongly recommend a commercial/visual product unless you're a real guru).
I believe unmanaged code is subject to typical memory leaks of unamanged code, except that references shared between the two are managed by the garbage collector. Could be wrong about this last point.