"Serializing" to the SqlServer Database

Hi.

I was wondering, if there is an easy way to insert and retrieve objects from SqlServer database? Basically this is what i want to do:

- I define a class, which has several dozen members.
- I want to insert many of these objects to the database.
- All classes members should have its own column in the database table.

Do I have to manually constuct a database tables and sql inserts, so I could save the objects to the database? Or is there a way to tell the .NET to construct me a the sufficient database tables and sql statements? Can DataSets used somehow to achieve this result?

I'm not sure what version of the .NET framework you are using. If you are using 1.1, you might want to take a look at the Microsoft Enterprise Library:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

Specifically look at the Configuration Application Block which allows you to serialize objects that can be stored in XML, the registry, SqlServer, or in a custom location if you wish to implement your own custom storage provider.

In the case of Sql Server, defined stored procedures are called to store and retrieve the data from Sql server. The object is serialized when it is stored and deserialized when it is retrieved. The sample code includes scripts that can be used to create the underlying tables and stored procedures.

There is a January 2006 release of the 2.0 Enterprise Library that I'm not as familiar with as I am with the 1.1 version.

|||I am using the .NET Framework 2.0.

But since the Enterprise Library contains this kind of functionality, it'll be good assumption that the CompactFramework itself doesen't contain one?

I thought that .NET has the functionality, because in a Microsoft event, where a MS-Evangelist demostrated some new features of .NET 2.0, this kind of DB-based settings handling cought my eye at some point (sorry Aali, I was a little sleepy;)), I think it had something to do with the browser based ASP.NET website management console. I don't remember the details though...

But it could be the case, that the services of the very same Enterprice Platform have been used with the management console.

Thanks for You reply! (You folks can of course give me more info if you please;))
|||

You can do all the things with .NET (1.1 or 2.0) that the Enterprise Library is doing. The advantage of using the Enterprise Library is that Microsoft has already done the work for you (as well as the testing) and wrapped it up in a nice framework.

Here is an outline of the steps you'd need to take

To save an object:
1. Make sure your class has the [Serializable] attribute applied. Your class must support (either implicitly or explicitly) a no parameter constructor.
2. Popluate an object made from the class with data
3. Call the Serialize() method of an XmlSerializer object (for example). You pass in to this method your object and the stream that the serialized output will be sent to.
4. Construct the proper SQL statements to insert the stream data into a custom database table. The data type of the SQL column the stream data is saved to must be able to accept the character range of the stream as well as it's length. NText would be a good choice.

To retrieve an object:
1. Construct the proper SQL statements to populate a Stream with the data that was stored in #4 above.
2. Call the Deserialize() method on the same type of formatter used in #3 above passing in the Stream object. The method returns an object that must be cast to the class defined in #1 above.

Hope this helps.