|
|
I need to load a dataset with data from a list of items.
That is to say, I have an excel spreadsheet with a list of category id numbers. I need to load all data into a dataset that matches that list of categories. What is the best way to go about this?
Right now it is easy enough to do, load the spreadsheet into memory, load the dataset for the first category id, process, and the move to the next item in the spreadsheet, but I don't want to reload the data set 60 times. I want something that looks like this for a datatable:
select item from itemsTable
where intCategoryId in @categories
What is the best way to do this? Do I need to loop through my spreadsheet and reload the dataset for each item? Or should I build my spreadsheet information into a comma separated list or something of the sort.
Thoughts?
Cheers, --EA
|
|
|
|
|
Traversing an Excel worksheet, cell by cell is very slow to start with. It is a lot quicker to read a range of cells into memory and then work from there.
And you only need to read to values once and store the DataTable as a static variable so you can continue to reference the DataTable instead of continuously reading from the worksheet.
Hope that helps.
Architecture is extensible, code is minimal.
|
|
|
|
|
It is a good point about the actual datasheet / table, but it actually is outside the scope of my question. The bigger question is one of how to load information into my data table based on the values in the spreadsheet.
Example:
Spreadsheet might look like this
Column1
---------
123
125
129
133
135
And the query for the datatable should look something like this:
select category, item from itemsTable
where itemId = @itemId
At this point I could loop through the values in Column1, whether they are loaded into a datatable, looping through a spreadsheet, whatever. The end result is that I need a datatable that contains the category and item from the items table for every itemId value in the spreadsheet. So the question is whether it is acceptable to use the IN keyword in my dataset query and supply it a list of values as a variable (e.g. instead of where itemId = @itemId, where itemId in (@itemId)) maybe a variable comma separated list?
Or do I need to loop through the source (datatable, excel spreadsheet, whatever) and reload the items datatable for each distinct value in the spreadsheet and process? I am not sure I am getting the point across very well. I guess the real question here is whether you can use the IN keyword and supply an array or comma separated string when loading data with a table adapter or whether you can only use equality with a single value.
|
|
|
|
|
Sorry I got focused on the fact you were reading from excel exclusively. I'm still not quite sure what you are after but that's probably just me
Are you looking for something more along the lines of:
select category, item from itemsTable
where itemId IN (select distinct itemId from sourceTable)
Architecture is extensible, code is minimal.
|
|
|
|
|
That is the general idea. Normally when you load a datatable you feed it an argument like so:
this.tableadapter.fillbyarg(this.dataset.datatable, arg1);
That would load all of the items with category = arg1. Well, I want to load all items from items where category is = arg1 where arg1 is (1,2,3,4,5, etc...). So yes, the IN keyword would be what I would use in a SQL query. My question is twofold. 1) Can you use In and supply the argument like so:
int[] myInt = new int[3];
myInt[0] = 1;
myInt[1] = 2;
myInt[2] = 3;
this.tableadapter.fillbyarg(this.dataset.datatable,myInt);
You see the crux of my dilemma at this point I think?
The second question is whether using In is a good approach or whether there is something I am not considering here.
An alternative is to load the myInt values into a datatable and do something like this:
this.tableAdapter.fillbyarg(this.dataset.datatable, valuefromtable);
Adding that line in a loop through the datatable from the Excel spreadsheet.
|
|
|
|
|
I now understand but my knowledge of TableAdapter these days is vague at best. Sorry, hopefully someone else can help
Architecture is extensible, code is minimal.
|
|
|
|
|
First off, DataSets are rubbish and you shouldn't use them, but...
Isn't there a Filter property you can set after loading all the data?
What are you trying to accomplish?
|
|
|
|
|
I could filter the datatable, yes. Why do you say that datasets (ADO.Net) are rubbish? What would you suggest in alternate? Keep in mind I am using a MSSQL 2000 server, so EF and nHibernate are not good options.
|
|
|
|
|
eddieangel wrote: Why do you say that datasets (ADO.Net) are rubbish?
First off, ADO.net in general is not rubbish. DataSets, DataAdapters, and DataGridViews are. I just use DataTables and DataReaders.
eddieangel wrote: EF and nHibernate
I've never used them.
|
|
|
|
|
An array is defined to be a Jeffian array if it contains at least one prime number and the minimum prime number in the array is equal to the count of all the prime numbers in the array. Recall that a prime number is an integer greater than 1 whose only factors are 1 and itself. For example, the array {3, 6, 7, 10, 5} is a Jeffian array because:
1. it contains at least one prime number
2. its minimum prime number is 3 and that is the count of the number of primes (3, 7, 5) in the array
modified on Wednesday, January 26, 2011 5:20 PM
|
|
|
|
|
Well, good luck. I'm pleased that you've posted this for us to share in. Although, I am confused as to why you are wasting your time posting here when you should be doing your exam. Oh wait, you aren't asking us to write this for you are you? That would be, what's the word I'm looking for, oh yes - cheating and I'm sure that an honest chap like yourself wouldn't condone cheating.
Anyway. Good luck with your exam.
|
|
|
|
|
dude, that's pretty simple, but we're not here to do your homework for you.
you need to try and post what you tried and maybe we can give you pointers.
given the description you should first write a function that answers whether a number is prime or not.
then another function isJeffian that iterates over the given array and counts the number of primes in the array.
if that count is equal to the first argument of the array (and the first argument is prime) then it's a Jeffian array, so return true, otherwise return false.
"If your actions inspire others to dream more, learn more, do more and become more, you are a leader." - John Quincy Adams
|
|
|
|
|
Sorry.
I can't speak for any of the others here, but I personally could not see helping someone cheat their way into better grades, perhaps thus earning a degree (and job) they don't deserve, and can't sustain without relying on others to do their work for them.
It may be "tough love", but we're doing you a favor by NOT doing it for you.
|
|
|
|
|
It's alright. He only has 10 minutes left before he fails. Harsh I know, but I will not condone cheating.
|
|
|
|
|
not cheating
but i ended the exam
i can't answer three question
now i want to know the code ... and wait the answer tomorro or after tomorrow not now
calculating in programing difficult
|
|
|
|
|
Mohamed Nabawy wrote: not cheating
but i ended the exam
Suuuuure. Is that why you deleted the message you just had posted that said you had 30 minutes to go on your exam? If you have all this free time now, you would do yourself some good by solving this yourself. Perhaps you should go to your teacher if you are unable to provide a solution. I'm sure he/she would be happy to help you so that you don't fail again in the future.
|
|
|
|
|
thaaanks
but the exam ended .. and i will do my best in other exams
|
|
|
|
|
just remember: cheaters never prosper, unless you work for WallStreet
"If your actions inspire others to dream more, learn more, do more and become more, you are a leader." - John Quincy Adams
|
|
|
|
|
The OP is trying to cheat on an online exam and is wasting time asking here rather than attempting to work it out for himself.
|
|
|
|
|
This is quite simple really:
- Take the inverse cosine of the dot product of the array and its transverse.
- Once you have this result, compare it to the sum of the edges of the numbers arranged into the indentity matrix multiplied by the primes of the original array arranged diagonally into a matrix.
- Once you perform that comparison, realize that it does you no good and that you should really focus on the assignment at hand rather than asking strangers on a forum to complete it for you. That's dishonest and stupid.
|
|
|
|
|
Oh for goodness sake. I didn't expect anybody to actually post the solution here. BTW, it's the log of the inverse cosine otherwise you end up with a nullity array.
|
|
|
|
|
Ah, but you see the nullity array is cancelled out during the substitution phase. It's also important to keep the nullity array to preserve the equation's covariance, otherwise you end up with an unprovable postulate.
|
|
|
|
|
Ah. I see you are applying Huffman's theory of indeterminate coexistence. An interesting theory, but as I only program in L space, completely useless in the 11th dimension. Still, it should suffice in a four dimension Euclidean universe.
|
|
|
|
|
I find that Capo Ferro cancels Agrippa.
|
|
|
|