Datatype

[Guest]

1. I've a column in a log table in my SQL Server that's of SmallDateTime
datatype. This column (LogonTime) stores the particular Time, e.g. 12:00:00,
that the user logs on. I created a class called SystemLog and 1 of its
attribute is called LogonTime. I wish to represent values that LogonTime
column by this LogonTime attribute. What should this attribute's datatype be?
DateTime?

2. I've another column called LastUpdate in every column. This column is of
rowversion type. I've this column to prevent Concurrency problem. I'll check
if values of this LastUpdate columns are the same before and during updates.
Again, I'll like to represent it in a class. This is how I declare it:

Private _LastUpdate(8) As Byte

Public Sub New(ByVal newUserID As String, ByVal newPassword As String, ByVal
newLastUpdate() As Byte)

_UserID = newUserID
_Password = newPassword
Dim n As Integer
For n = 0 To 7
_LastUpdate(n) = newLastUpdate(n)
Next n

End Sub

Public Property LastUpdate() As Array
Get
Return _LastUpdate
End Get
Set(ByVal Value As Array)
Dim n As Integer
For n = 0 To 7
_LastUpdate(n) = Value(n)
Next n
End Set
End Property

Is this fine or are there better methods?

3. Say if I want to execute a SQL Select query statement, and I want to
parse the value every column of each row to a variable in my codes, how
should I do?

Dim strConnection as String = "server=(local);database=myDatabase;User
Id=sa;password=;"
Dim strSQL as string = "SELECT * FROM Users;"

Dim objDataSet As New DataSet()
Dim objConnection As New SqlConnection(strConnection)
Dim objDataAdapter As New SqlDataAdapter(strSQL, objConnectionj)
objDataAdapter.Fill(objDataSet, "Users")

then?

Sorry I'm a beginner... please help. Thank you.

 

[Cor Ligthert]

Wrytat,

1.
You have in VBNet only the possibility to use a datetime type for dates and
times. Beside a string and a long as ticks however that I would absolutly
not use.

2.
About the concurrency errors I would not try to make your own routines.
First it is very dangerous for yourself because you are full responsible and
second they exist already.

There are enough samples on MSDN in my opinion to help you.
http://msdn.microsoft.com/library/d...on/html/vbwlkHandlingConcurrencyException.asp

3.
A dataset is an object that exist from a collection of datatables and
relations
A datatatable is an object that exist from a collection of datarows and
datacolumns
A datarow is an object that exist from a collection of items
A datacolumn describes the item in the datatable.

In other words the first item from a dataset is (ds, dt, and dr are a kind
of standard abbreviations in this newsgroup).
ds.Tables(0).Rows(0)(0) is the first item in the first row in the first
table of the dataset.

When you do this.
dim dr as datarow = ds.Tables(0).Rows(0)
Than you have set a reference to that dr. You did not copy the values,
before you make that (often made) misunderstanding.

4. When you next time have a question, seperate them than in more messages.
You have now the change that somebody knows one answer however not all and
is than not responding.

I hope this helps,

Cor

 

[Guest]

Thank you very much.

Does that mean that I don't have to keep a LastUpdate rowversion column in
my tables because ADO.NET will automatically help me to track if a
concurrency error had happened? I just need to catch the
DBConcurrencyException when updating the table, and do what I need to do. I'm
so glad.

And for ds.Tables(0).Rows(0)(0) is a SmallDateTime, and
ds.Tables(0).Rows(0)(1) is an integer in the SQL Server, that means I can do
something like:
Dim Row1Col1 As DateTime
Dim Row1Col2 As Integer
Row1Col1 = ds.Tables(0).Rows(0)(0)
Row1Col2 = ds.Tables(0).Row(0)(1)
Right? So, I can create a lot of objects and store them in an arraylist and
then bind a table with the arraylist for my web applcation. Am I right?

Thank you~

 

[Cor Ligthert]

Wrytat,

Just try it yourself first.

I see not directly something wrong in what you wrote.

Maybe there are more simple ways, however that you have to learn step by
step in my opinion. (While the way that you understand it is not slow in my
opinion)..

Cor

 

[Guest]

Thank you.

I think I was just confused after reading the "Introduction to Data
Concurrency in ADO.NET". Because under "Concurrency Control in ADO.NET and
Visual Studio .NET | Version Number Approach", it said that one way to handle
concurrency is by,

Update Table1 SET column1 = @newvalue1, ...
WHERE RowVersion = @origRowVersionValue

And SQL Server Books Online said that I must supply a column name when
specifying rowversion. So I thought I've to specify a rowversion column on
every table that I have to handle concurrency.

But after reading the article that you lead me to and "Handling Concurrency
Errors", I notice about this DBConcurrencyException. So I assume that I don't
need a rowversion column now.

But then, how do ADO.NET knows that a DBConcurrencyException has occurred?
Can it possibly be that a rowversion column is created automatically when we
create a table although it's not shown?

 

[Guest]

Thanks.

The reason why I had a rowversion column in every of my tables previously
was the Introduction to Data Concurrency in ADO.NET article. Under
Concurrency Control in ADO.NET and Visual Studio .NET | Version Number
Approach, it says that one way to handle concurrency is by:

UPDATE Table1 SET Col1 = @newvalue1, ...

WHERE RowVersion = @origRowVersionValue

So, I thought I need to have a rowversion column in every of my tables in
the SQL server to do concurrency control.

DBConcurrencyException is thrown by the DataAdapter during the update
operation if the number of rows affected equals zero. Just curious. How would
the DataAdapter knows that someone else had modified the data while the user
is trying to modify it?

Can it be that:

1. a rowversion column is created automatically when I create a table in MS
SQL Server, but it's hidden and is used for concurrency control? or

2. the row is locked and so the DataAdapter can't update and returned this
error? or

3. others...

In the 2nd case, how do I lock a row? Or is it lock automatically by MS SQL
Server when someone updates a row. Do I need to manually lock it or configure
anything from the Enterprise Manager?

Thanks anyway. I'll go and try.

 

[Cor Ligthert]

Wrytat,

When you fill a datarow using a select than there is an option to set the
fill with acceptchanges (when you want the excact name message than back).

So in that situation a row is added to a table and the acceptchanges is
directly done. The name acceptchanges gives a lot of misunderstandings.

It means that it accept all changes that has done before and in fact it does
this.
With datarows with a rowstate delete it removes the row and set from all
datarows the rowstate to unchanged.

With a change the rowstate is set to changed
With a remove the datarow is removed and will therefore never be updated in
a database
With a new the rowstate is set to inserted (or something)
With a delete from a datarow with rowstate "inserted" the datarow is removed
With a delete with not the rowstate "inserted" the rowstate is set to
deleted.

I hope that this gives an idea.

Cor