Re: what's your opinion on the Visual Studion 2005 data designer? - Database Discussion Boards

I wouldn't consider that any big advantage - its only a couple of line of code. I would consider an understanding of what happens in the background is far more important.

As I said originally, if you look at how many questions are posted on CP where the real cause of the problem is lack of understanding.

Bob
Ashfield Consultants Ltd

Have you used the Data Designer before? If so, what happened?

Yes I did try it, and spent many hours trying to make it fit in with OO principles and struggled to get it working with mutli-table transactions.

I now use a class generator that I wrote myself, which I just point at a stored proc and it will generate a class, or point it at a table and it will generate a class, and the stored procs for CRUD. It also generates a strongly typed collection for each class.

All in all I found it much more time consuming to use the gui data designer and trying to figure out work arounds for when it didn't do what I needed.

I suspect anyne who has been around since before .net is of the same opinion - at least they are where I am currently contracting.

Bob
Ashfield Consultants Ltd

That's very valuable advice. Thanks a bunch.

Thats OK, you are welcome.

Bob
Ashfield Consultants Ltd

After working extensively with visual studio 2005 data designer I came to the conclusion that it is evil. Don't use it if you can. I'd suggest using ORM packages like nhibernate to make mapping from DB to object oriented code more easy.

The problem is that VS data designer work at the table level. It simply maps a data table to a data class. This is a gross mistake because many times you have data that is scattered across many tables but is still owned (logically) by the same class. What happens next is that you have to work very hard to provide this mapping yourself.

Tools like nhibernate can help you alleviate this problem somewhat but now you have another headache to maintain (XML mapping files). Although I think this is better, it is still far from optimal.

The best solution IMHO is that we'll finally have a good, open-source and free (not GPL) object oriented database engine. With such a database you wouldn't need to handle these darn mapping issues at all.

Liron

Thank you for your reply by the way. You gave an honest critique of the data designer and it's drawbacks. I am having trouble understanding what you mean by mapping. Doesn't the DD provide it with your table relations that it pulls from your DB?

I'll give you an example from real life.

I have a Customer class that has a list of contact persons inside it. Something like:

public class Customer
{
public List<contactperson> Contacts;
}

This is a simple clean data class, but when you model it in the database you'll end up with two tables (of course this is just my interpretation - you can use different representation but this is a valid one in my case):

CREATE TABLE Customer
(
int CustomerID PRIMARY KEY,
nvarchar(50) FirstName NOT NULL,
nvarchar(50) LastName NOT NULL,

... more fields here
)

CREATE TABLE ContactPersons
(
int CustomerID, -- foreign key to the customer table
nvarchar(50) HomePhone,

.. more fields here
)

When you work with Visual Studio designer it will create a CustomerRow class and a ContactPersonsRow class to represent the rows of these two tables. However - you can forget about having it creating a single class like I showed you above. If you want such a class you'll have to write it yourself (relations will not help in this case).

Another problem is that the visual studio designer writes the autogenerated code around the concept of data sets and data tables. What do you do if you want to build a separate server process and than pass these objects between the client and the server? (you guess - more classes and more mapping code to map from data rows to your pure data classes and vice-versa). One of the tenets of good object oriented design is to separate the data classes from external DB specific interfaces (like ADO.NET) and Microsoft doesn't help at all in this regard.

All in all - the visual studio data designers are probably meant for building quick-n-dirty database solutions and not for providing the infra-structure that you need when building real-world applications. And I say this with a lot of grief because I'm currently stuck with their solution due to a bad decision on my part Frown | :(

I don't even mention that there is no explicit support for retrieving data using both the CustomerTableAdapter class and the ContactTableAdapter class in the same transaction (you have to roll your own solution here because Microsoft did not think that such thing is common enough to warrant having it in their auto generated classes, another gross mistake IMHO).

In the end, you can either break object oriented encapsulation and go with Microsoft in their half-baked solution, use their designer generated code as a middle-ground for building real object oriented support to your data (my fate unfortunately) or use a mapping layer that will relieve you from having to write most of this code yourself (like NHibernate).

Personally I hate all of these solutions. For god sake we are in 2008 and there is no reason why we must use DB technologies from the 60s plus having to break our neck trying to interface with it. The relational DB model and the object oriented models are simply inconsistent with each other and a good, free object oriented database will solve the problem from its root.

In short - use Hibernate or something like it if you can. With regards to DB technology I sometimes feel like I came from the future and nobody knows what I'm talking about Poke tongue | ;-P

Good Luck

Firstly can't you code it yourself. I would see that relational problem as something that exists with our without the designer. Here is where I am coming from. There is no chance albeit poor design that I will require separation of processes. The reality is however that I end up saving a tremendous amount of time with the designer because while the anticipated amount of data will be small as far as rows are concerned, however I do plan on having an enormous amount of columns of data, and the designer seems to streamline that process. I believe that the best way would be to avoid the designer but when I have a client who is paying me by the hour and also really wants to see the app as they are in violation of the law without this app I am not so sure the correct decision is to hand code it. You know what I am saying??? As far as your idea of an object orientated DB, why wouldn't you just make objects, and then add them to a collection class, and then serialize the collection class. I started with such a model, but the code was up to 7000 lines of code and I decided that this wasn't the best way. I have however written programs in such a way and works well.

If you have a small number of tables and a simple data model + you don't need to handle client/server interactions or any of that stuff + the amount of data is small + you don't need transaction support or concurrent data access then using serialized data seems like a very good solution to me. I don't exactly know what caused this solution of yours to become a 7000 LOC monster but I bet that using the DB will grow you another one just as easily so be careful.

You may save time using VS data designers but only if you go with their solution and forget about object oriented encapsulation. It can work and it WILL save you time this way. If your application is small and your data requirements are simple it will work out just fine. However - if your application has a complex data schema and is expected to grow (in structure and/or data) than your attempt to save time will actually cost you more, a lot more (as I've already found in the hard way).

The reason that using serialized objects doesn't work in general is that it is not scalable for large amounts of data. You can't search according to specific fields when the data is serialized. You can't update the database every time a single property of some object changes. You can't handle concurrent access well and you don't have transaction support. If the file is small (<=1MB) this may work OK but it fails when the amount of data grows beyond this point due to excessive disk trashing etc.

Liron

Could you tell me what happened when you tried to change your dataset with the wizard because I actually had to do just that, and while it was kind of painful, after I knew how to do it, I did it again with no problem. You see I am kind of trying to see into the future, and if this is going to cost me or not. I also have working against me that I have code structured on that dataset from the designer and I will obviously have to redo that code if I don't go with the designer.

modified on Tuesday, September 2, 2008 1:00 PM

You have the investor's dilemma. Since you are already invested in the visual studio data designer you will view everything I say here with pink glasses. I will say this bluntly - the visual studio data designer suck big time, UNLESS you are doing a very small and simple application.

Otherwise I'd suggest you stay away from it if you can.

Good luck
Liron

Well you are right in that respect. My decision is not coming from a neutral standpoint. I am trying to establish if I can use it so you are right. I have a conflict of interest you could say. However with that being said, I really would like to establish for myself what kind of changes in the future really will be literally mnpossible. I have been able to accomplish the following;

change column order in a table (helped me some looping)
change column names and properties
change or add relations
add columns to existing tables
add tables

Now really these kinds of things are really the only changes that I can foresee although I am sure that there are other things that I can't foresee which is why I am asking you what you ran into. Also I have heard that there are many tools that do the same thing (generate and edit typed datasets)as the dataset designer except a lot better. If you have used such tools I would also really appreciate what you used or use today and what you think of them. I want you to know that I really appreciate your advice on this one.

You are not looking at the problem from the right angle IMO.

The problem of changed DB schema is a separate problem (and a headache) altogether. You will need to write some kind of DB upgrade framework to solve upgrade issues and you will need to change existing data classes to incorporate new fields and (sometimes) eliminate obsolete ones.

What bothers me most is the impedance mismatch between the relational DB and the object oriented data classes. Here I spend lots of time writing boring repetitive and error prone data access code when most of it can be done automatically or eliminated altogether using tools like hibernate/nhibernate. This is where my biggest pain exists. This is where you'll end up if you are not careful enough.

I'd also recommend you stay away from tools that auto-generate classes based on DB schema. You should never design the DB schema first and than write classes to represent it. It should always work the other way around - do a well designed object model and than find the best DB modeling to represent this model. This is why I don't like tools like SubSonic and prefer tools like Hibernate.

If you have the choice I'd recommend using object oriented DB instead of a relational one (that is - if you don't need Oracle performance..). I've played some time with the db4o database and it looks pretty straight forward to work with. Such a database eliminates all the headache of writing data access code. You simply save and read whole class instances. Much much easier to work that way.

If I was not so stubborn when it comes to NOT using commercial libraries (I always prefer to work with open-source modules due to source code availability in case of errors) I would have probably saved tens to hundreds hours of wasted effort writing data access code to map from the relational DB model to object oriented classes.

Liron

Does Oracle offer an OO solution? I saw a little bit on it but it seemed like it was still in it's early stages. I think I am going go with the db4o like you said but I have another question related to something I mentioned before. Basically I had 7000 line sof code going just because I had a very large object. Is that a problem?? It's just that I have an item that holds lot's of different types of data. It's patient data as you can imagine can get large.

Oracle does not offer any OO solution that I know of.. Oracle is too heavily invested in relational technology to offer anything different.

With db4o you'll simply save the entire object with a single line of code. I don't think you'll have problem with that but maybe you'll need to find other things to do in your spare time (see a movie, play with kids, get a life Smile | :)

)

Good luck with your endeavor
Liron

Hi all,
I have different characters in a string, My string may contain CHAR(10) or CHAR(13) + CHAR(10).
If my string contain only CHAR(10) then I want to replace it with CHAR(13) + CHAR(10). And if my string contain CHAR(13) + CHAR(10) then I don't want to replace it.

Can any body give me a solution to this.

Thanks & Regards
Mishra

Find the characters in string like this...

DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)), 
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END
GO

You may modify/build a new string upon your requirements...

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

We can do this in two cascaded steps:
1. Replace all occurrences of Char(10) with Char(13)+Char(10)
2. Replace all occurrences of Char(13)+Char(13)+Char(10) with Char(13)+Char(10)

Note that Step 1 replaces some unwanted occurrences also (i.e. Char(13)+Char(10) occurrences), but the second step fixes those unwanted wrong replaces.

Have fun with this piece of code:

declare @myTestString varchar(50)

set @myTestString = char(10)+char(13)+char(10)+char(10)+char(13)+char(13)+char(13)+char(10)

set @myTestString = replace( @myTestString, char(10), char(13)+char(10) )
set @myTestString = replace( @myTestString, char(13)+char(13)+char(10), char(13)+char(10) )

Cheers,
Syed Mehroz Alam

My Blog
My Articles

Hi,
I hv a query which retrives the data frm data base and also calculates sm value for few colums i get the value as infinity.so how can i hv it in my query so that it should display zero and find the sum of that column.
Here is my query...Pls sm one help...

OleDbCommand cmdMtdDays = new OleDbCommand("with member COSt as sum({[Dimension Time].[Day Text].[" + FromFilter + "]},[Measures].[Out Standing Amt]) member AV_MTD as sum(" + QueryFilterMTD + ",[Measures].[Inv Amt]) member COSMTD as (AV_MTD/" + day + ") member MTDDAYS as (iif(COSt=0,null,(COSt/COSMTD))) select {MTDDAYS} on columns, nonempty([DLOMAS].[OMVALU].[OMVALU]) on rows from COS", con);

Regards,
Annu

Already answered in another forum

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

HI
I have a table (ContactDetails) with 2000 records. I have an application receiving an XML file from an external web service. Most time the data received from the web service is similar to the data in my table. Occasionally the data returned from the web service will have few differences. I want to compare the data received from the web service with the data in my local database and generate a xml file (based on the same schema) to send to another application . So how do i go about doing that?
Thanks

modified on Thursday, August 28, 2008 12:13 PM

I would approach this problem by breaking it down into phases.
1) Read the XML file
2) Fetch data from the database and compare to XML data
3) Write a resulting XML file

Below should get you some idea how to loop through an XML file.

<br />
Imports System.Xml<br />
<br />
Dim xdTemplate As New XmlDocument<br />
Dim xnRoot As XmlNode<br />
<br />
xdTemplate.Load("c:\temp\FP_TAK.xml")<br />
<br />
xnRoot = xdTemplate.SelectSingleNode("/myPath/mySub/etc")<br />
<br />
'  This code show some simple code to loop through an XML file<br />
'  you will need to modify this based on your layout, but at least it shows some<br />
'  functionality.<br />
<br />
While (Not (IsNothing(xnRoot))<br />
  If (xnRoot.Name = "FSFORMULATPALL") Then<br />
    If (xnRoot.Item("PARAM_CODE").InnerText = "R.B.D_SOY_ALLERGEN") Then<br />
      Debug.Print(CStr(i) + ") " + xnRoot.Item("PARAM_CODE").InnerText)<br />
      xnRoot.Item("PVALUE_LABEL").InnerText = "No"<br />
    End If<br />
  End If<br />
<br />
  xnRoot = xnRoot.NextSibling<br />
End While<br />
<br />
xdTemplate.Save("c:\temp\dave.xml")<br />
<br />

Hello every one

I have created small application which can save and delete images from sql database it's works fine but only problem is when ever I add or delete image from sql database it dosent refresh I mean If I delete on image so in picture or database count it should show that database is empty or if there is two images in database then it should shows that one image in database

but if i close application and restart it then it shows all the changes so could you please help me with this

this is the code for save,delete and load image

Private Sub LoadImages()
        Try
            conImage = GetDBConnection()
            dsImage.Clear()
            Me.ImagesTableAdapter.ClearBeforeFill = True
            Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
            daImage.Fill(dsImage, "Images")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conImage.Close()
        conImage.Dispose()
        End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        'get sql connection
        Try
            conImage = GetDBConnection()
            Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & _
                           "@pic, @title, @itype, @iheight, @iwidth)"
            commImage = New Data.SqlClient.SqlCommand(sSQL, conImage)
            Call GetImage()
            commImage.ExecuteNonQuery()
            MessageBox.Show("Image successfuly saved in database", "Image Load")
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        commImage.Dispose()
        ' commImage = Nothing
        conImage.Close()
        conImage.Dispose()
        Call LoadImages()
    End Sub

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        SelectedImage = ("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text)
        conImage = GetDBConnection()
        Try
            commImage = New Data.SqlClient.SqlCommand(SelectedImage, conImage)
            commImage.ExecuteNonQuery()
            MsgBox("Image successfuly deleted from database", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        commImage.Dispose()
        commImage = Nothing
        conImage.Close()
        conImage.Dispose()
        Call LoadImages()
    End Sub

please help me with this issue Confused | :confused:

Hi,

Is the row really being deleted: Modify the following to see that one record is really being deleted

SelectedImage = ("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text)        
conImage = GetDBConnection()        
Try            
   commImage = New Data.SqlClient.SqlCommand(SelectedImage, conImage)            
   recordsAffected = commImage.ExecuteNonQuery()            
   MsgBox("Deleted count: " & recordsAffected)
   MsgBox("Image successfuly deleted from database", MsgBoxStyle.Information)

If recordsAffected is 0 then your condition isn't correct

Mika