Re: XML and XSD into to a SQL Database - .NET (Core and Framework) Discussion Boards

May I give my two cents?

You are clearly not enjoying coding, otherwise you would not contradict yourself in almost every statement. You see a potential way out of your current life situation in technology, and believe that coding is the way to go.

Ask yourself this, and use your Internet connectivity to expand your answers:

WHat is it that you really like to do for the rest of your life? If you answered programming, what is your second choice? and your third?

Now, what is common among those three? And what do you have in your personality that will fit into each one?

You may find out that by moving down to your other choices, you can make yourself fit in better. Coding may look simple, but as you have found out, sometimes it is not. It takes not only perseverance, but also productivity, two things that are compeltely contradictory unless you are willing to devote yourself fully to it. It does not matter how many days/months/years/decades of experience you have, as unless you push yourself to the next level, with the same problem f productivity/perseverance, you will not enjoy it.

And whatever you decide to do, learn to do one thing well, and that is to communicate well. More than finding bugs, or coding in assembler, you will be compensated by your ability to communicate with all around you.

I have to say I think this is romanticized bullshit. Sorry to be so blunt, but I do. There is no requirement that you enjoy coding in order to be successful at it. As with *any* job, if you enjoy it that's good, because you'll spend an awful lot of time on the job, but in fact you may enjoy a job and be awful at it or you may dislike it and be really good at it.

So as always it comes down to choosing among alternatives, paying the opportunity cost of not doing alternative A and do B, or vice versa. And in that big equation job enjoyment is an important variable but far from the only one. If you have a sh*t job you hate that pays badly and you can switch to a boring-ass job that pays well it's worth thinking about it.

I'm sure this will invite a lot of emails from people who think personal experience is some sort of proof for anything, but I doubt *anyone* will be able to present any solid proof that there's anything more than a vague correlation between being happy at work and being successful.

Based on how you describe your approach I would say that you're probably not what I would call a programmer. If you *understand* any of the stuff you're copying from around the web then perhaps you will some day become a programmer, but as long as you are simply stubborn and copying various things you find online until something appears to do something similar to what you wanted that doesn't really count.

I do however salute your ambition and curiosity and figure there is no reason why you cannot become a programmer. You should consider a different approach though - learning by doing is great, but it's much more powerful if combined with theoretical studies. Buy some books or at least bookmark good articles that are NOT hands-on labs or tutorials but theoretical treatments on computer science subjects. What exactly you wish to learn is really up to you, all have their use but few are essential. I personally enjoyed learning how computer hardware works and understand the many layers of abstraction piled on top of that, but then again it's perfectly possible to learn OOP and make excellent use of it without knowing about stack-based programming or even know what a pointer is. That, after all, is the whole point with all this abstraction: To make it completely unnecessary to deal with the low-level detail.

Using the internet and either using other people's code or at least being heavily inspired by it is simply part of any efficient programmer's toolkit nowadays. It's not in itself any sign of lack of ability. So what really matters is to understand. Also, few developers are as good at taking a long term view when designing technical solutions as they ought to be, and this is an area where anyone who decides to be conscious about this can gain a strategic competitive advantage, because it often isn't very complex but just gets overlooked.

Good luck!

If you write code, you are (by definition) a programmer. You aren't necessarily a great programmer, but that comes with practice, perserverance and a whole heap of skill. Nearly everybody here, will have learned from others - so, learning from others code isn't necessarily a bad thing. The only thing you need to watch out for, is that you don't learn BAD coding habits from substandard applications.

When I started writing MFC code back in 92, I couldn't get my head round it. I bought a Que book, and spent months figuring out what the code did - a lot of time was spent just watching variables as I stepped over sections of code, but I emerged a stronger coder. When I wanted to learn WPF, I read a lot of stuff, didn't understand a lot of it, but I perservered with it. Now I'm a member of a group called the WPF Disciples, which has some scarily clever guys in it - that's because of perserverance.

Bottom line - stick at it if you enjoy it. Don't set your sites too high too early otherwise you'll end up disheartened, and less likely to stick at it.

Good luck.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

Hello, through all my searching I can't seem to go about finding the best way to do something that hopefully shouldn't be as hard I am finding it to be.

I have a set of open schema specifications (SportsML to be specific), an open database architecture (SportsDB), and lots of XML files that fit the schema which should intuitively map to SportsDB.

What I want to do is to load all the XML into the DB.

I don't know the quickest/simplest route for me to have all this XML loaded as mapped rows and columns in the database.

Here is the path I am on: Generate typed datasets using XSD.exe (which is in itelf a challenge since I am having troubles with XSD on multiple schema files), and hand-code all the mappings from all the DataSets to the DB. Then hand-code a SQL OPENXML command for each dataset. There are 124 individual tables in SportsDB. Is there an easier way? Is there a better way? I am interested in any feedback or ideas.

Here is what I am using: Visual Studio 2008, SQL Server 2008. I am writing CS but I don't mind receiving any suggestions with VB.

Also, to make it easier for people in the future I plan on documenting all this, making it open source, and putting it up as an article on this site.

Many Thanks!

It sort of depends doesn't it? I would probably go down a dynamic code generation route, partly because it's fun to do (it's real easy in .net to compile code at run time and then run it), but also because it results in the fastest possible execution while also being very very flexible.

For example, you could take a reflection-based approach. What matters to you isn't so much the database types used, but the .net types they map to. As far as I know there's no simple way to query this mapping from the ado.net providers, so the easy way is to do a "select top 0 * from [table]" and fill a datatable, then loop through it's columns and check the DataType property, which is the .net type and not the native database type.

Then you can generate an sql command, such as an INSERT statement, knowing little more than the names of the columns and the table:

INSERT [table] (col1, ..., colN) VALUES (@col1, ..., @colN);

Of course, for this to work you do need to know about any identity or computed columns that cannot be inserted.

Then, generate C# code to create an SQL command, use the SQL as the CommandText and add parameters to it for all the columns. And generate a *separate* method that takes an XmlNode and assigns the parameters of the command object. This will allow you to insert N records creating only one command instance. What I've done before is have the C# code generator use reflection to see if the type used for each column has a Parse(string) method and also if it has the Parse(string, IFormatInfo) overload. If it has the latter I generate code to use it - passing CultureInfo.InvariantCulture as I want all our files to be culture-independent; only *presentation* should depend on this. (I've had enough of Microsoft's French "CSV" (comma separated values) files. Because the comma is decimal point in the French culture they changed the *file* format and use semicolon (or whatever is "list separator" in Windows Regional Settings) instead, meaning users of the English Excel version cannot open files saved by the French version unless you go and change the default settings...)

Obviously the generation itself is not super quick, depending on reflection and having to compile the results into an in-memory ("dynamic") assembly before use. But it's quick enough that I find it's perfectly acceptable not to pre-compile the generated code, just cache the compiled type so it can be reused for the rest of the apps lifetime.

Other alternatives exist of course. If you have or want to have a business layer you may want to look at Entity Framework. If not, and if you want to statically create the data access code at design-time rather than on the fly at run-time, you may wish to check out CodeSmith. Typed datasets should certainly work too, but personally I find the absolutely awful naming conventions used and the nested classes so inconvenient and confusing that it totally ruins them for me. I just cannot quite getmyself to code like this:

AdventureWorksDataSet.AdventureWorksProductsTable t = new AdventureWorksDataSet.AdventureWorksProductsTable();
AdventureWorksDataSet.AdventureWorksProductsTableRow r = ds.AdventureWorksProductsTable.NewAdventureWorksProductTableRow();

It may not be exactly like that - it's been a long time since I used this - but whatever the differences it was in fact just that awful. If you don't mind putting a bunch of using aliases at the top of each file where you use these types you can resolve some of it though (type names, not method names). You could derive your adapters from the generated ones and add methods with sensible names that just call the insanely-named ones, but it's getting involved already.

And if you target framework 3.x you can of course use type inference for locals as well.

using ProductTab = AdventureWorksDataSet.AdventureWorksProductsTable;
using ProductRow = AventureWorksDataSet.AdventureWorksProductTableRow;
...
// nice :)
var t = new ProductTab();
// not so nice
var r = t.NewAdventureWorksProductTableRow();

Just my two cents.

Thanks for the response! I know what you mean about the nested classes and the look of the code. I can target 3.x so that is definitely good to know. I had used generation before and there are some good tools out there, but none for free that solved this specific problem Smile | :)

I had written some reflection before for a different problem in which I had only the XML and not any official XSD nor the DB. I might cave and buy an XMLSpy or similar license, but then I'll miss out on learning the trials and tribulations of this whole process. I have used MyGeneration and XSLT for going the other way (SQL DB to XML) and that was fine. But I think the problem this time is that I already have both the schema and the data model which strangely enough seems harder than only having one of them! Getting them to fit together just seems a bit tedious.

Also, I don't know the SportsML schemas or the database, but you might want to check if the native ADO.NET datasets can be made to load these xml documents. Upon re-reading your original posting it seems to me that your primary goal is to minimize the development effort, and datasets might work quite well for this purpose.

Another interesting possibility is to make a SportsML reader that implements the IDataReader interface - then you can use SqlBulkCopy to bulk insert the data. This gives you the best performance and is fairly easy to do if you can base the object on an existing IDataReader implementation.

For example, we wanted to copy some data efficiently from one DB to another. The data included some parent-child relations, so a foreign key in table Child would refer to a row in Parent. And the keys were IDENTITY values provided by each database, so they would be different in each database.

To deal with this I wrote a simple TransformingDataReader class based on SqlDataReader, which already implements IDataReader. Since SqlDataReader is sealed I couldn't inherit from it, but I just embed a reader in my class and delegate most of the interface implementation to that object, like this:

...
        public void Close()
        {
            sqlDataReader.Close();
        }
        public DataTable GetSchemaTable()
        {
            return sqlDataReader.GetSchemaTable();
        }
        public bool NextResult()
        {
            return sqlDataReader.NextResult();
        }
        public void Dispose()
        {
            sqlDataReader.Dispose();
        }
...

The only thing I really wanted to change from SqlDataReader was that when it reads I'd like to be able to modify the data of the row just after it's read internally and before it's passed on to the consumer of the reader. This is easily done like this:

SqlDataReader sqlDataReader;
object[] currentRow;

abstract public void Transform(object[] currentRow);

public bool Read()
{
    if (sqlDataReader.Read())
    {
        sqlDataReader.GetValues(currentRow);
        Transform(currentRow);
        return true;
    }
    currentRow = null;
    return false;
}

I like this solution since it's now very easy to make a reader that does transformations on the fly. For example, you could load a "correspondance" table if you know that in system A values 1, 2, 3 actually mean the same as X, Y, Z does in another system, and by implementing just Transform() you'd be able to do the transformation on the fly during a bulk insert that streams data from one database to the other.

Just to give you some more ideas!

I connected with an Access 2.0 file with Server Explorer in VS2008.
It can list all table in this database but when I get all row's data of one table It throw the Exception :

"Record(s) cannot be read; no read permission on 'table's name'"
How can I fix this? Thanks for any help!

QuynhTD

The account you used to connect to the database doesn't have permissions to read the data in that table. You need to use a different account or change the security in the Access database to allow the account you used better permissions. Don't ask me exactly how to do it because I haven't used Access to store data in years.