|
Hello !
I'm using vb.net 2017 with EF 6 and Sql Server 2008 R2.
I'm writing a code to read data from an excel file and after to save on the database.The excel file will have very large number of records. ( maybe 50.000 or even more ).
When is better to call context.savechanges :
- After adding each record
- After adding a number of n records ( !! If yes , what is the best number )
- After adding all records
Thank you !
|
|
|
|
|
desanti wrote: If yes , what is the best number
42.
Ask yourself what happens if you don't save; if the thing crashes, you have to start anew. Saving means rewriting the Excel-file, since it is not a DB - and that will take some time.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Quote: Saving means rewriting the Excel-file, since it is not a DB
Sorry ,i'm reading from excel file not writing.
|
|
|
|
|
In a single transaction 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
As always, it depends on business rules.
Should all the records in the Excel sheet be treated individually? Should they be treated as one unit? What happens if you're app crashed or is otherwise interrupted during the write to the database? What do you track, if anything, to recover? Do you start over and rewrite the records you already wrote in the database? Do you try to resume from the last successfully written record?
You COULD save after every record. It'll slow things down but you'll get a verified write on each and every one.
What's the "best" number of records? It's impossible for anyone to say. Again, it depends on a ton of factors. There is no "one value" to rule them all.
You COULD save after the entire set is created. That will result in a long save operation that'll return a single error if there is a problem with any record in the operation.
It's impossible for anyone, other than yourself, to answer your question.
|
|
|
|
|
The problem is that or i should write all the records from Excel to database , or if a single record has problems no records will be saved.
I will make this proces in 2 steps :
1. I read one by one all the records from Excel and test them if they are correct and can be saved to database.If one of the record is ok , I add it on entity. If one record has problems , the context is disposed , and the user has to correct the problems and to start testing again the excel data.
2.If the test procedure goes without error , I call savechanges.
So , i call savechanges after all objects are added to entity.
But i don't know if this is safe , so i want your opinion and i should changes the code that I've described before.
|
|
|
|
|
|
I have an application that runs access database with about 50 tables but i want to search all the tables with Refno since the tables all have it, and i don't want to use the inner join because if it do and some tables don't have data it wont populate, so how do i search all the tables and display on crystal report the code i use for the search is below
thank you
Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Public Class Qmatereportplus
Private Sub btqgetreport_Click(sender As Object, e As EventArgs) Handles btqgetreport.Click
Getreportdisplay.ReportSource = Application.StartupPath + "\Qmateplusreport.rpt"
Getreportdisplay.SelectionFormula = "{excavationbkfill.Refno} = '" & txtdisplaydetails.Text.ToString() & "'"
Getreportdisplay.Refresh()
Getreportdisplay.RefreshReport()
End Sub
End Class
|
|
|
|
|
Something like below would be a starting place;
SELECT RefNo, "TableName1Here" FROM TableName1Here
UNION
SELECT RefNo, "TableName2Here" FROM TableName2Here
UNION
SELECT RefNo, "TableName3Here" FROM TableName3Here
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Thanks for your respond but Please am new and confused with your reply
|
|
|
|
|
You can't select every field from every table; so you'd be only searching the RefNo and the table that it belongs to.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
There is a one-line subject, and then a complete text-field for your post. Still you put a vague question in the subject and no details whatsoever in the large text-field.
The answer will be "no". It's the default-answer for every question I don't understand. The DGV of WinForms is well-documented, and there's a lot of examples on MSDN.
DataGridView Class (System.Windows.Forms) | Microsoft Docs[^]
If you scroll down a bit, you'll find links to "howto" articles, which explain how to do the most common tasks using this control. They are step-by-step guides.
If you still have a question after that, then please try to use at least five sentences to explain your problem; what are you trying to do, what is the desired result, that kind of stuff.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi Guys,
I have 583 rows at Excel but I need to order them in a specific way.
Below is first 4 row:
1 10197700 DA0518 2 DA0532 1 DA0539 1 DA0546 2 DA0557 2 DA0550 0
2 10197701 DA0005 2 DA0532 1 DA0539 1 DA0040 2 DA0113 2 DA0550 0
3 10197702 DA0005 3 DA0075 1 DA0059 1 DA0040 2 DA0107 2 DA0091 0
4 10197703 DA0005 4 DA0077 1 DA0061 1 DA0040 2 DA0109 2 DA0093 0
And it needs to be sorted like below:
10197700
DA0518 2
DA0532 1
DA0539 1
DA0546 2
DA0557 2
10197701
DA0005 2
DA0532 1
DA0539 1
DA0040 2
DA0113 2
10197702
DA0005 3
DA0075 1
DA0059 1
DA0040 2
DA0107 2
How can I solve this? Thanks.
modified 13-Feb-19 21:02pm.
|
|
|
|
|
|
hi.
I dont have sqldmo.dll.
possible to list all sqlserver on my lan?
Tks
|
|
|
|
|
Yes. Get SqlDmo and VB.NET.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hello,
I'm having trouble figuring out the correct search path to find the Appointment:
where CompanyKey='Mr. Key' and Date='11/11/2018 12:00:00 AM'.
I've tried numerous formats in sFmt2 and can't find the magic combination.
sFmt1 works to find the first occurance of 'Mr. Key'.
Can someone help?
Thank you
<?xml version="1.0" encoding="UTF-16"?>
-<MyCalendar xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
-<Appointments>
-<Appointment CompanyKey="Mr. Key">
<Date>11/11/2018 12:00:00 AM</Date>
<Details>Add Appointment through XmlSerializer</Details>
</Appointment>
-<Appointment CompanyKey="Mr. Key">
<Date>11/01/2018 12:00 AM</Date>
<Details>Add Appointment through XmlDocument</Details>
</Appointment>
</Appointments>
</MyCalendar>
Dim sFmt1 As String = "/MyCalendar/Appointments/Appointment[@CompanyKey='Mr. Key']"
Dim sFmt2 as string = "/MyCalendar/Appointments/Appointment[@CompanyKey='Mr. Key' and Date='11/11/2018 12:00 AM']"
'Dim sFmt2 as string = "/MyCalendar/Appointments/Appointment[@CompanyKey='Mr. Key'][Date='11/11/2018 12:00 AM']"
Dim node As XmlNode = Me._XmlDoc.DocumentElement.SelectSingleNode(sFmt2)
If IsNothing(node) = True Then
Debug.WriteLine("Failed - " + sFmt)
Else
Debug.WriteLine("Found - " + sFmt)
End If
|
|
|
|
|
Do you need an @ in front of Date ?
|
|
|
|
|
Stupid, Stupid, Stupid me.
I can't believe I worked for hours on this and it turns out to be a data error.
"12:00" verses "12:00:00"
Thanks to a test program I found, XPath Visualizer, I found the
problem immediately
Sorry for my Stupidness 
Thanks
-- modified 12-Nov-18 6:58am.
|
|
|
|
|
Public Sub incrPR()
Dim curValue As Integer
Dim result As String
Dim s As String = "PR"
Dim yr As String = Now.Year.ToString()
Using con As SqlConnection = New SqlConnection(ConString)
con.Open()
Dim cmd = New SqlCommand("Select MAX(SpecialOrderNo) FROM SpecialOrder", con)
result = cmd.ExecuteScalar().ToString()
cmd.Parameters.Clear()
If String.IsNullOrEmpty(result) Then
result = "PR000"
End If
result = result.Substring(3)
Int32.TryParse(result, curValue)
curValue = curValue + 1
result = s + curValue.ToString("D3")
txtno.Text = result
End Using
End Sub
|
|
|
|
|
curValue is the number (if I read that right), so you just need to construct your string from that followed by the year followed by "PR". Each field separated by the '-' character. What is the problem with doing that?
|
|
|
|
|
allukasan wrote:
You don't.
You divide them over the three atomic fields that they are, and put them together only when being formatted for viewing. That way "001" is a simple autoincrement, the other two are simply added to it.
The "Id" that you are assigning to it should also not be used as a key.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I want calculate chord in midi with vb6
Can you help me?
thanks
|
|
|
|
|
Nope. You have to define exactly what you mean by "calculate chord in midi".
And VB6 has long been dead. You have absolutely no reason for using on on new projects. The Community edition of Visual Studio is free from here[^]. It supports VB.NET, C#, C/C++, ...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
