|
Hello !
Is there any library to compare 2 SQL server databases inside a vb.net program ? ( I mean to compare the structure and relationships, no data)
Thank you !
|
|
|
|
|
|
About the SMO , can you specify some more information , because on that link I can't find any way to do this.
|
|
|
|
|
It's a big library so you need to navigate through the documentation. To have something to start from , have a look at:
|
|
|
|
|
Have a look at OpenDBDiff[^]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
This doesn't seems to be used from inside a vb.net application , but as a external tool.
|
|
|
|
|
You may or may not be able to integrate the supplied source code
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
For this I need some help
|
|
|
|
|
You know how this site works, right? You start yourself and if you run into a specific problem you can ask
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
No , I don't know. Thank you for telling me.
But what if I don't know how to start ?
Can't this forum help someone how to start ?
|
|
|
|
|
I wrote this piece of code in the event of a textbox LEAVE where I enter the city automatically brought in CAP PR and the results of a query ....
Making debugging line by line VS 2010 jumps the line Dim laws as OdbcDataReader and therefore it does not perform the query and gives me error as if there were no data.
I made a few mistakes in the code?
Private Sub txtPaese_Leave_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPaese.Leave
Try
Dim cn As New OdbcConnection("dsn=PHOENIXDB;uid=SYSDBA;pwd=masterkey;")
Dim com As String = txtPaese.Text
Dim strCn As String = "Select distinct * from TBL_COMUNI where COMUNE='" & com & "'"
Dim cmd As OdbcCommand = New OdbcCommand(strCn)
Dim leggi As OdbcDataReader
Dim daSelPaese As New OdbcDataAdapter
Dim dsSelPaese As New DataSet
cn.Open()
daSelPaese.SelectCommand = cmd
daSelPaese.SelectCommand.Connection = cn
daSelPaese.Fill(dsSelPaese, "TBL_COMUNI")
leggi = cmd.ExecuteReader
Me.txtCAP.Text = leggi.Item(11)
Me.txtPR.Text = leggi.Item(4)
leggi.Close()
cn.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
End Try
End Sub
|
|
|
|
|
Your code is vulnerable to SQL Injection[^].
NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
You're executing your command twice - once to fill a DataTable , which you then throw away, and once with the ExecuteReader method.
You've then tried to access columns from the returned OdbcDataReader object without first calling its Read method. This will throw an exception.
Your query is selecting multiple rows from the table, but you're only using the first row. You should change it to only select a single row instead.
You're using SELECT * FROM ... , which returns every column in the table. You're then only using two columns. Change your query to return only the columns you need.
You should also wrap any objects that implement IDisposable in a Using block.
Private Sub txtPaese_Leave_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPaese.Leave
Try
Using cn As New OdbcConnection("dsn=PHOENIXDB;uid=SYSDBA;pwd=masterkey;")
Dim query As String = "SELECT TOP 1 * FROM TBL_COMUNI WHERE COMUNE = ?"
Using cmd As New OdbcCommand(query, cn)
cmd.Parameters.AddWithValue("p0", txtPaese.Text)
cn.Open()
Using reader As OdbcDataReader = cmd.ExecuteReader(CommandBehiavor.CloseConnection)
If reader.Read() Then
Me.txtCAP.Text = reader.Item(11)
Me.txtPR.Text = reader.Item(4)
Else
End If
End Using
End Using
End Using
Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
End Try
End Sub
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Grazie tantissimo.... Ora funziona tutto....
Thank you so much .... Now everything works ....
|
|
|
|
|
Hello Everyone,
I am Working on SCADA project in which I am writing real time data to excel worksheet. Now I want to apply a check on data that if worksheet is updating continuously or not if not then I will restart my windows form Application. I try following code but it doesn't work.
Private EventDel_CellsChange As Excel.DocEvents_ChangeEventHandler
Public Sub UseDelegate()
xlBook = excelWB
xlSheet1 = xlBook.Worksheets(2)
CType(xlSheet1, Excel._Worksheet).Activate()
EventDel_CellsChange = New Excel.DocEvents_ChangeEventHandler( _
AddressOf CellsChange)
AddHandler xlSheet1.Change, EventDel_CellsChange
xlApp.Visible = True
xlApp.UserControl = True
End Sub
Private Sub CellsChange(ByVal Target As Excel.Range)
MsgBox("Restrating Of Application Required")
End Sub
Any Help Please ................ thanks in advance.
|
|
|
|
|
I am trying to read a CLOB value from an Oracle database query using the GetChars method. I am doing this so that I can buffer very large CLOB values and write the results out to an XML file.
However, there seems to be an issue where the GetChars method of an OracleDataReader is only buffering half the characters it should. This causes me to continue buffering in a loop until GetChars returns 0, but I think this is very inefficient and is slowing down my program.
Does anyone know why this is happening? Here is the fragment of code that I am using:
Dim reader As Data.OracleClient.OracleDataReader
Dim bufferSize As Integer = 1024 * 2000
Dim outChar(bufferSize - 1) As Char
Using connection As New Data.OracleClient.OracleConnection(connectString)
Dim command As New Data.OracleClient.OracleCommand(statement)
command.Connection = connection
connection.Open()
reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
Do While reader.Read()
startIndex = 0
Array.Clear(outChar, 0, bufferSize)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
rawStr = New String(outChar)
rawStr = rawStr.Replace(Chr(0), "")
myxml.WriteRaw(rawStr)
myxml.Flush()
startIndex += rawStr.Length
Array.Clear(outChar, 0, bufferSize)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
Loop
End Using
|
|
|
|
|
Aaron Rosenthal wrote: This causes me to continue buffering in a loop until GetChars returns 0 You are reading it in blocks of 1024 * 2000 chars. If you know the size of the blob, then you could see if you can fetch it completely.
Aaron Rosenthal wrote: I think this is very inefficient and is slowing down my program Why do you think that?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I can't assume to know the size of the CLOB in my program because it may vary. Also, if I simply buffered the entire CLOB, what would be the point of buffering? I'm trying to avoid doing that so I don't run into memory issues for very large CLOBs.
What I don't get is why doesn't GetChars fill the entire outChar array? I know that it can fill more than it does. For example, on a further iteration of my loop GetChars only fetches FOUR characters, and there are still 3 more to fetch, so I have to continue iterating. Why does it do that?
|
|
|
|
|
Aaron Rosenthal wrote: Why does it do that? I don't know the exact implementation details of the method, despite having it open in ILSpy.
The buffer will be filled to its maximum with the available data. Did you set the commandbehaviour to sequential on the reader?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
What type is the myxml variable? If it's an System.Xml.XmlWriter , you should probably be using the WriteRaw overload[^] that takes a char[] , rather than creating two new strings every time:
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
myxml.WriteRaw(outChar, 0, retval)
myxml.Flush()
startIndex += retval
Array.Clear(outChar, 0, retval)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I don't think the problem is with the WriteRaw method.
When GetChars first returns, the value of retval is 1265918. However, the outChar array is only filled up to half of that amount (up to index 632958). I know this because all the values past that index are null (0x00). Also, when I convert the outChar char array to a string and remove the null values, the length of the resulting string is 632959.
On the second call to GetChars, the value of retval is 632959, but the outChar array is only filled up to index 316478.
And so on and so forth.
So again, my question is why does GetChars not fill the entire outChar array?
|
|
|
|
|
No idea why it's not filling the buffer, but creating two new strings on every pass through the loop is going to have a noticeable impact on your method's performance.
Since the real problem seems to be the performance of the code, it's got to be worth trying!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Possibly, but how would I determine the index in the outChar array where the null values begin? I don't feel comfortable assuming that it will always be half what retval returns. That seems to be asking for trouble. And looping through the entire outChar array until I encounter a null (0x00) value would probably be just as inefficient as converting the outChar array to a string and then stripping the null values, right?
|
|
|
|
|
Are you expecting to get null characters returned from the database?
As far as I can see, the only reason you're getting null characters in your string is because you're ignoring the retval value, and creating a string that represents the entire array. Since all of the array entries beyond retval will be set to the null character, you're ending up with a long list of nulls on the end, which you're then having to remove.
With your current code, you could use:
rawStr = New String(outChar, 0, retval)
which would avoid adding those null characters to the string in the first place.
With my version, the line:
myxml.WriteRaw(outChar, 0, retval)
also avoids writing null characters to the output.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You've touched upon the source of my problem, though. The value of retval is incorrect.
For example, the first time GetChars returns, it puts the value 1265918 into retval. However, if I actually try to access the value of the outChar array at index 1265917, I get a null value. This is because GetChars does not actually seem to be buffering as much as it says it does. In fact, it is only buffering up to index 632958.
GetChars is only filling my buffer array to half of what it says it did. The value that GetChars returns seems to be the actual size of the entire CLOB, not the number of characters that it filled the buffer with.
|
|
|
|
|
OK, so try an Array.IndexOf call to find the index of the first null character:
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Do Until retval = 0
Dim nullIndex = Array.IndexOf(outChar, Chr(0), 0, retval)
If nullIndex = -1 Then nullIndex = retval
myxml.WriteRaw(outChar, 0, nullIndex)
myxml.Flush()
startIndex += retval
Array.Clear(outChar, 0, retval)
retval = reader.GetChars(0, startIndex, outChar, 0, bufferSize)
Loop
That will perform a linear search on the array, but it will be much faster than creating a string containing a complete copy of the array, then iterating through every character in the string, copying every non-null character to a new string.
If the retval value is incorrect, you might also need to adjust the increment of the startIndex variable; otherwise, you might be skipping characters. You'd need to compare the final output with the database column to see if that's the case.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|