|
how to export the data from stored procedure to excel?
|
|
|
|
|
What have you tried?
Where are you stuck?
What help do you need?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
NB: You can't use Office Interop in an ASP.NET application:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
Use a tool like EPPlus[^], ClosedXML[^], or The OpenXML SDK[^] to generate an Excel file.
For example, EPPlus provides both LoadFromDataReader and LoadFromDataTable methods which you could use to load the data from your query into the Excel file.
DataTable dt = LoadDataFromStoredProcedure();
var pck = new ExcelPackage();
var wsDt = pck.Workbook.Worksheets.Add("FromDataTable");
wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
pck.SaveAs(Response.OutputStream);
Response.Flush();
Response.SuppressContent = true;
Context.ApplicationInstance.CompleteRequest();
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Export as CSV.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I can not use int j =Convert.Int32(ReadLine()); in Visual Stdio 2019
|
|
|
|
|
Don't post the same question all over the site either: you have the answer in your QA post, so spreading the same thing all over the place just wastes even more time that reposting it in QA repeatedly does. And that really annoys people - and annoyed people are generally not as helpful as happy people.
All it does is make you look rude, arrogant, and pretty desperate. You want to come over like that? Be my guest - but don't expect help from volunteers with other things to do ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Yes you can. Why can't you?
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Hi
I'm quit new to C#, I moved from Autoit to C# mostly due to most AV-vendors keep flagging Autoit as virus :/
I've been googling for the better part of a week, but haven't found any answer.
What I looking for is an equivalent to Autoit's code stripper.
What the stripper does is, at compile time it strips all comments from the code, and renames all vars and functions to "short code", it also removes all unused functions.
An Autoit example:
If GUICtrlRead($idCb_Edit_Chosen_Lot) = $GUI_CHECKED Then
; We updates the database, with the data from the array
_SQLite_Exec(-1, "UPDATE tbl_data SET date='" & $sDateTime & "', lot='" & $aData[1] & "', packing='" & $aData[2] & _
"', sort_number='" & $aData[3] & "', sort_name=" & _SQLite_FastEscape($aData[4]) & ", type='" & $aData[5] & "', grower='" & $aData[6] & _
"', customer=" & _SQLite_FastEscape($aData[7]) & ", comment=" & _SQLite_FastEscape($aData[8]) & " WHERE date='" & $g_SDateTime & "';")
; Then we update the Listview
; In the list view we don't have seperate filds for sort number and name, so we need to combine thoes
$aData[3] &= ' ' & $aData[4] ; Combine sort number and name in to $aData[3]
_ArrayDelete($aData, 4) ; then we removes (deletes) the 4'th index from the array $aData
; Now we creates a loop to update the listview with the data from the array we just changed
For $i = 0 To 7 Step 1 ; Sins we deletede on index from the array, we only run from 0 to 7
; Insert the data into listview
_GUICtrlListView_SetItemText($idListView_Overview, $g_iIndex, $aData[$i], $i)
Next
GUICtrlSetData($idLbl_LotNr, StringFormat("%06d", $g_iLot - 1)) ; Set the last used lot -1 (we add + 1 later in the script)
$g_iLot = Null ; Clear the global lot value
GUICtrlSetState($idCb_Edit_Chosen_Lot, $GUI_UNCHECKED) ; Unchecks the Checkbox
Else ; If it's not a lot edit then
; We Adds the data to the database
_SQLite_Exec(-1, "INSERT INTO tbl_data VALUES ('" & $aData[0] & "', '" & $aData[1] & "', '" & $aData[2] & _
"', '" & $aData[3] & "', " & _SQLite_FastEscape($aData[4]) & ", '" & $aData[5] & "', '" & $aData[6] & _
"', " & _SQLite_FastEscape($aData[7]) & ", " & _SQLite_FastEscape($aData[8]) & ");")
; Creating an array we can use to adde the data to the listview
Local $aListViewAdd[1][8]
; Combine the Sort number and name
$aData[3] &= ' ' & $aData[4]
; Update the array, by remove/delete the 4'th index (sortname)
_ArrayDelete($aData, 4)
; Creating a loop to add data to the array
For $i = 0 To 7 ; We only run from 0 to 7 course we did delete one index in the array
$aListViewAdd[0][$i] = $aData[$i] ; Add the data to the new array
Next
; Add the data to the listview using the new array, we just created
_GUICtrlListView_AddArray($idListView_Overview, $aListViewAdd)
; If the user have choosen to reuse the previus lotnumber
If $g_bReuse = True Then
$g_bReuse = False
GUICtrlSetState($idCb_Reuse_Pre_Lot, $GUI_UNCHECKED)
EndIf
EndIf
After stripping:
If GUICtrlRead($idCb_Edit_Chosen_Lot) = 1 Then
_SQLite_Exec(-1, "UPDATE tbl_data SET date='" & $sDateTime & "', lot='" & $aData[1] & "', packing='" & $aData[2] & "', sort_number='" & $aData[3] & "', sort_name=" & _SQLite_FastEscape($aData[4]) & ", type='" & $aData[5] & "', grower='" & $aData[6] & "', customer=" & _SQLite_FastEscape($aData[7]) & ", comment=" & _SQLite_FastEscape($aData[8]) & " WHERE date='" & $g_SDateTime & "';")
$aData[3] &= ' ' & $aData[4]
_ArrayDelete($aData, 4)
For $i = 0 To 7 Step 1
_GUICtrlListView_SetItemText($idListView_Overview, $g_iIndex, $aData[$i], $i)
Next
GUICtrlSetData($idLbl_LotNr, StringFormat("%06d", $g_iLot - 1))
$g_iLot = Null
GUICtrlSetState($idCb_Edit_Chosen_Lot, 4)
Else
_SQLite_Exec(-1, "INSERT INTO tbl_data VALUES ('" & $aData[0] & "', '" & $aData[1] & "', '" & $aData[2] & "', '" & $aData[3] & "', " & _SQLite_FastEscape($aData[4]) & ", '" & $aData[5] & "', '" & $aData[6] & "', " & _SQLite_FastEscape($aData[7]) & ", " & _SQLite_FastEscape($aData[8]) & ");")
Local $aListViewAdd[1][8]
$aData[3] &= ' ' & $aData[4]
_ArrayDelete($aData, 4)
For $i = 0 To 7
$aListViewAdd[0][$i] = $aData[$i]
Next
_GUICtrlListView_AddArray($idListView_Overview, $aListViewAdd)
If $g_bReuse = True Then
$g_bReuse = False
GUICtrlSetState($idCb_Reuse_Pre_Lot, 4)
EndIf
I have tried a lot of obfuscatore, but non worked correctly they did to0 much, leaving my code not working.
Is there some way one could do the same with a C# code, as the code above?
Not exactly obfuscation but more a slim down, and make the code a bit more unreadable.
Cheers
LR
|
|
|
|
|
Before you even start removing comments, learn what is safe code and what isn't. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood' The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable; Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x'; A perfectly valid SELECT
DROP TABLE MyTable; A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Then, you need to know that what you are doing - stripping out comments, unused methods and so on - is a bad idea in C#, and unnecessary.
Autoit is a scripting language, so when you release it to users you send them the "source code", so making it hard to follow is - kinda - an advantage. But C# is compiled, which means that what gets released is not in source, not "human readable" - it's an EXE file, and that's binary code. So taking out comments is pointless as they don't go into the release material anyway!
C# EXE files can be reworked to produce "source code" that isn't identical to the input, but can be close: so if that is a problem for you, then look for what is called an Obfuscator - it's a tool that makes the code you can get from an EXE file much, much harder to read than your "stripper" can do.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Hi OriginalGriff
The example I gave is a part of an old Autoit program, that uses Sqlite and have no need for secure code.
Removing unused methods in C# is unnecessary yes, but not the strip comments and rename part (for me that is).
I know what Autoit is, been using it for 15+ years and would had continued to do so if it wasn't for those damn scriptkidds who have used Autoit for virus/malware/cryptoware etc. thous making the av-vendors flag almost every program written in Autoit as virus.
And Autoit is just as C# released (can be anyway) in exe format, and both can (C# even more easy) be decompiled to plain source code.
As I said I have already tried to use lots of different obfuscate tools, and non of them worked with my codes  .
But a simple renaming would (should), sins the code won't be "scrambled" just "trimmed".
If I compile a library, and throw the dll into dotpeek I have my entire source code with comments and all, and that's what I'm trying to prevent .
If I could get my library to work with one of those obfuscators, it would be perfect but for some reason unknown to me I can't  .
Cheers
LR
|
|
|
|
|
If you're trying to stop people from decompiling the .exe or .dll in a readable form, obfuscators are the only thing that's going to do that.
Not all code can be obfuscated. If you're using Reflection, using a library that uses it, in your code, you can't use an obfuscator on it or else the code examining your code will no longer work.
If you can't get the code working after obfuscation, contact the vendor of the obfuscator for support on it.
|
|
|
|
|
Hi Dave
Nope I'm not trying to prevent decompiling, if I could though that would be cool - but with those protection softwares I have tried, de4dot decompiles them all just fine.
I'm only trying to make my code less readable.
But after reading the answer from OriginalGriff I think that I might have used the obfuscator wrong, so I tried once more, and instead of protecting my dll's for them self before they was included into my program, I did it on the whole release, and that worked.
Cheers
LR
|
|
|
|
|
Lupu5R3x wrote: the strip comments and rename part (for me that is).
As OriginalGriff already told you, that is a waste of time, since you will not be sending the source code to the people who use your code. I assume this is your first step into working with a compiled language, so you need to understand the difference between source and object code. You are welcome to remove all the comments from your source before compiling it, but that will make no difference to the final executable, since comments are not carried over.
|
|
|
|
|
Hi Richard
As I wrote to Dave, after reading the last answer from OriginalGriff, I found out what went on and what I did wrong 
Cheers
LR
|
|
|
|
|
That's not strictly true - it's picking up the comments and such from the PDB file, which it detects from the App GUID (IIRC). Try this: create a new project, comment it, and build it.
copy the Release EXE to a safe place, and delete the original solution folder (as if it was just installed on a new machine).
Open the exe with dotPeek, and it can't find the PDB, so it uses just what's in the file. No comments! 
As for readability, use an obfuscator and it should be pretty horrible to look at!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Hi OriginalGriff
And that my friend was just the correct answer (sort of), it did clue me in onto what I did wrong
Now I only need to rob a bank, so I can afford to by the full edition of the obfuscator 
But besides that, I now know why the obfuscation failed, and for that I thank you 
Cheers
/LR
|
|
|
|
|
 I know that feeling ...
You're welcome!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I've written my own "comment cleaners". It's not that hard if you're consistent.
- Read all lines as strings
- look for leading "//", "/", "", "*/", etc.
- LastIndex of "//" for trailing comments.
- Write clean / accepted lines.
Do a directory at a time; copying from one to the other. Dump what was dropped / clipped for a verification.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Hi Gerry
My problem is/was that I didn't know where to start.
Cheers
LR
|
|
|
|
|
The problem is you don't really want to do that.
The comments never make it into the code. BUT, if you decompile the executable on the machine that has the project still on it, you'll the the comments from the original source, not from the decompiled executable.
|
|
|
|
|
I didn't follow the "compiled comments" part either; I use my cleaner on (my) source code when "comments" weren't part of the deal (and I'm in "that" mood) when turning it over.
I usually zero on those parts that say "do not change this code even if it does not make sense because it will only work this one way because I tried everything else already".
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
hi, I have my
dictionary1
key values
1 {item 1, 10; item 2, 20; item 3, 40}
2 {item 1, 15; item 2, 28; item 3, 40}
3 {item 1, 15; item 2, 28; item 3, 40}
dictionary2
key values
1 {item 1, 12; item 2, 20; item 3, 40}
2 {item 1, 15; item 2, 25; item 3, 40}
3 {item 1, 15; item 2, 28; item 3, 40}
i need to compare the 2 dictionary and result should be; item 3 dont need to display since there is no changes.
dictionary3
key values
1 {item 1, 12}
2 {item 2, 25}
this is what i used.
var dictionary3 = dictionary2.Where(entry => dictionary1[entry.Key] != entry.Value)
.ToDictionary(entry => entry.Key, entry => entry.Value);
and return
1 {item 1, 12; item 2, 20; item 3, 40}
2 {item 1, 15; item 2, 25; item 3, 40}
thank you
modified 18-Jan-20 13:05pm.
|
|
|
|
|
Easiest way is to write a loop. What have you tried?
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.
|
|
|
|
|
|
Your design in not in "third normal form". This app will always be a pain.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
