|
Dear All,
Im reading some data from a excel sheet and converting it to a datatable.
Now i have to update my database table on the basis of this datatable:
i want old records to be updated and new record inserted in my db table.
What is the best and fast way to do that?
Thanks in advance.
|
|
|
|
|
How are you going to identify if an entry already exists? i.e. what is unique about the rows?
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
my db table has the same columns as in the excel sheet
|
|
|
|
|
Is there any primary key in your table?
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
yes i have two columns:
id and name.
id is the pk
|
|
|
|
|
Use that key to check if record exists in database and update if they do. If not, insert them. You can send the entire datatable to the stored procedure by converting that into an XML.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
If you have an ID value in the excel spreadsheet this will be useful to check for existing entries. I assume this field will be empty for new entries? (I say that as you may have trouble adding, if its an auto incremet column)
I would...
1. Search all rows in existing database, pulling only the ID field data.
2. Create a list of ID values
3. Loop all rows in datatable (from excel)
4. Check if datatable row ID is in list of ID values, if so then do an UPDATE query, if not do an INSERT query
...that's it
Life goes very fast. Tomorrow, today is already yesterday.
modified on Thursday, November 19, 2009 6:07 AM
|
|
|
|
|
Hi Friend,
This is normal but want to know how it's done.
I have multiple drop down on one form which are related to each other.
Country Drop down
City Drop down
Location Drop down
If user select Country it will fill drop down and so on, but when user again select new country system should clear the related drop down it's working fine but because of that its taking time which is issue need to know how to speed this.
thanks in advance.
Sasmi
|
|
|
|
|
Are you hitting database everytime country selection is changed? That will slow things down. Unless you show some code about how you are doing this, it would be tough to help.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Just beat me to it 
|
|
|
|
|
This has happenned with me a few times earlier as well. Fortunately, I was lucky enough to come out as a winner every single time. 
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
thanks for your quick response
me using following query to fill dropdown
"select CountryName, CountryID from CountryTable"
"select CityName, CityID from CityDetails where CountryID = 1"
"select LocationName, LocationID from LocationDetails wherer CityID = 1"
this way the data has been retrive from database
but before that
i want to clearify
at page Load it initialize each control
DropDown1.Items.Clear()
ListItem _Item = new ListItem("-Select Country-",0)
DropDown1.Items.Insert(_Item, 0)
DropDown2.Items.Clear()
ListItem _Item = new ListItem("-Select City-",0)
DropDown2.Items.Insert(_Item, 0)
DropDown3.Items.Clear()
ListItem _Item = new ListItem("-Select Location-",0)
DropDown3.Items.Insert(_Item, 0)
when i change selection of Country drop down
again
DropDown2.Items.Clear()
ListItem _Item = new ListItem("-Select City-",0)
DropDown2.Items.Insert(_Item, 0)
DropDown3.Items.Clear()
ListItem _Item = new ListItem("-Select Location-",0)
DropDown3.Items.Insert(_Item, 0)
this way its coded..
sasmi
|
|
|
|
|
It seems you are hitting the database everytime a country or city is changed, that's why it is so slow. Load your lists in a JavaScript array and rewrite your logic using JavaScript.
|
|
|
|
|
You seem to hitting database all the time. Get the list in one go and cache it. Then, all you need is to filter the datasource for second and third dropdowns.
BTW give your controls a descriptive name.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Check valid field when pass to Controls
Công ty Đất Quảng - Cong ty Dat Quang - http://datquangda.com.vn
Bất động sản, mua bán nhà đất khu đô thị mới Nam An Khánh Hà Nội.
Trụ sở: Phòng 303 nhà N6E – Trung Hoà Nhân Chính – Thanh Xuân – Hà Nội - Vietnam
Văn phòng giao dịch: Số nhà 10, ngách 23, ngõ 61, Trần Duy Hưng, Trung Hòa, Cầu Giấy, Hà Nội, Vietnam
Điện thoai: 04.3556 2516 - 3556 2552 - 2247 5733
Fax: 04.3556 2566 - 3556 2140
Email:datquangda@gmail.com
Website: http://www.datquangda.com.vn
|
|
|
|
|
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Which code is populating the dropdown boxes? What part of the code is slow? If you're searching through a database, have you used the WHERE clause, or are you just iterating over the results when the list is populated?
|
|
|
|
|
thanks for your quick response
me using following query to fill dropdown
"select CountryName, CountryID from CountryTable"
"select CityName, CityID from CityDetails where CountryID = 1"
"select LocationName, LocationID from LocationDetails wherer CityID = 1"
this way the data has been retrive from database
but before that
i want to clearify
at page Load it initialize each control
DropDown1.Items.Clear()
ListItem _Item = new ListItem("-Select Country-",0)
DropDown1.Items.Insert(_Item, 0)
DropDown2.Items.Clear()
ListItem _Item = new ListItem("-Select City-",0)
DropDown2.Items.Insert(_Item, 0)
DropDown3.Items.Clear()
ListItem _Item = new ListItem("-Select Location-",0)
DropDown3.Items.Insert(_Item, 0)
when i change selection of Country drop down
again
DropDown2.Items.Clear()
ListItem _Item = new ListItem("-Select City-",0)
DropDown2.Items.Insert(_Item, 0)
DropDown3.Items.Clear()
ListItem _Item = new ListItem("-Select Location-",0)
DropDown3.Items.Insert(_Item, 0)
this way its coded..
hope you get what i coded on my form
sasmi
|
|
|
|
|
hello,
im looking for a solution with a slight problem that im sure it is possible, but i cant find anything about it on the web.
In my code, im calling a leave event out of a control that check's if the control has made any changes (txtBox, combo Box...) raising a question telling the user that changes weren't saved.
if the user click's on Cancel button, i want him to stay in the control and not actually leave like he initially did.
How can i do it ?
Thanks
private void TabControlChars_Leave(object sender, EventArgs e)
{
DataTable charChanges = null;
if (charsTypeDT != null)
{
charChanges = charsTypeDT.GetChanges();
}
else if (charsDT != null)
{
charChanges = charsDT.GetChanges();
}
if (charChanges != null)
{
DialogResult result;
result = MessageBox.Show("Changes were made without saving !!\n"
+ "All Changes will be lost ...", "Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (result == DialogResult.Cancel)
{
???
}
}
}
|
|
|
|
|
Try setting focus back to the textbox.
You can also place the code in the Validating event and then use Cancel property of the CancelEventArgs there to remain in the textbox.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Your logic should ideally be placed in Cancel button's Click event.
|
|
|
|
|
Sorry, but i didnt get you..
the message Box is created inside the Leave event so how can i put the event in the cancel button click event ?
|
|
|
|
|
I'm sorry, I got your question wrong.
In your case, instead of using the Leave event, use the Validating event and if the user chooses cancel, set e.Cancel = true (e is the ValidatingEventArgs passed to the method).
|
|
|
|
|
i have some data in grid which i want to write to database table on clicking a comand button...tell me esay way to do that?
|
|
|
|
|
Member 590310 wrote: i have some data in grid which i want to write to database table on clicking a comand button...tell me esay way to do that
What is the source of data that you are showing in gridview ? Read those data and put into the database. Or your problem is something else ?
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
