String.Format???
-
Jörgen Andersson wrote:
list as a parameter
Indeed, you have me wondering whether or not a DataTable may be passed as a parameter. Though I'm sure that if so, that only SQL Server would support it, so it wouldn't be a general solution. :sigh: P.S. This[^] looks interesting. P.P.S. And this[^].
modified on Friday, July 9, 2010 5:21 PM
Well, it seems that you can pass an array as a parameter to a stored procedure in both SQL serever[^] and Oracle[^]
"When did ignorance become a point of view" - Dilbert
-
I would imagine this would work:
string query = "select * from table where ID in (@value1, @value2, @value3)";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add("@value1", 123);
cmd.Parameters.Add("@value2", 124);
cmd.Parameters.Add("@value3", 125);If not, this surely would:
string query = "select * from table where (ID = @value1 or ID = @value2 or ID = @value3)";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add("@value1", 123);
cmd.Parameters.Add("@value2", 124);
cmd.Parameters.Add("@value3", 125);It does as long as your list isn't dynamic, but assume your list has a varying number of values...
"When did ignorance become a point of view" - Dilbert
-
Well, it seems that you can pass an array as a parameter to a stored procedure in both SQL serever[^] and Oracle[^]
"When did ignorance become a point of view" - Dilbert
Eureka! In SQL Server 2008 (Express): I created an Account table with ID (int) and Name (nvarchar) fields. I populated the Account table with some records. I created an IDdef User Defined Table Type with an ID (int) field. In C# I instantiated a DataTable, added an ID (int) column. Added some rows to the DataTable. Then set up the following (db is an instance of one of my DALs, dt is the DataTable):
db.Command.CommandText = "SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )" ;
System.Data.SqlClient.SqlParameter p =
new System.Data.SqlClient.SqlParameter
( "@IDs" , System.Data.SqlDbType.Structured ) ;p.TypeName = "dbo.IDdef" ;
p.Value = dt ;
db.Command.Parameters.Add ( p ) ;
db.Open() ;
System.Data.IDataReader dr = db.Command.ExecuteReader
( System.Data.CommandBehavior.CloseConnection ) ;And it works! :-D I then changed the statement to
SELECT * FROM Account INNER JOIN @IDs IDs ON Account.ID=IDs.IDand that works too! :jig: :jig: :jig: :jig: -
Eureka! In SQL Server 2008 (Express): I created an Account table with ID (int) and Name (nvarchar) fields. I populated the Account table with some records. I created an IDdef User Defined Table Type with an ID (int) field. In C# I instantiated a DataTable, added an ID (int) column. Added some rows to the DataTable. Then set up the following (db is an instance of one of my DALs, dt is the DataTable):
db.Command.CommandText = "SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )" ;
System.Data.SqlClient.SqlParameter p =
new System.Data.SqlClient.SqlParameter
( "@IDs" , System.Data.SqlDbType.Structured ) ;p.TypeName = "dbo.IDdef" ;
p.Value = dt ;
db.Command.Parameters.Add ( p ) ;
db.Open() ;
System.Data.IDataReader dr = db.Command.ExecuteReader
( System.Data.CommandBehavior.CloseConnection ) ;And it works! :-D I then changed the statement to
SELECT * FROM Account INNER JOIN @IDs IDs ON Account.ID=IDs.IDand that works too! :jig: :jig: :jig: :jig:My wife is going to kill me, I'm supposed to put up new wallpaper in the livingroom tomorrow. Not sit in front of the computer again.
"When did ignorance become a point of view" - Dilbert
-
My wife is going to kill me, I'm supposed to put up new wallpaper in the livingroom tomorrow. Not sit in front of the computer again.
"When did ignorance become a point of view" - Dilbert
It'll keep. The wallpaper, that is. :-D
-
Did you look at the code in the OP? You can't do SQL injection if your parameter data is strongly typed Int32 and DateTime values. If it was a string that's a different story, but if you are doing extra code to make sure no one is slipping SQL keywords into your ints then you are wasting a lot of time way overarchitecting.
I was making general observation about in-code SQL. I believe there are some valid causes for using in-code SQL, but it is generally a bad practice. But that is just my opinion which may have something to do with the fact, that I don't need to put SQL statements in code in my line of work, but I do recognize that sometimes there is no other way.
-
My wife is going to kill me, I'm supposed to put up new wallpaper in the livingroom tomorrow. Not sit in front of the computer again.
"When did ignorance become a point of view" - Dilbert
I finally remembered to try
SELECT * FROM Account WHERE EXISTS ( SELECT ID FROM @Param0 IDs WHERE IDs.ID=Account.ID ), it works. It appears that one of the differences between IN/EXISTS and JOIN is that the JOIN sorts (or maybe the others do). -
While working with a client to help them clean up their code I found this little gem. They absolutely never knew string.Format existed :wtf:
string sql = "select * from table where id={0} and date={1}";
string cmdText = sql.replace("{0}", id.Tostring())
.replace("{1}", DateTime.Now.ToShortDateString());
I know the language. I've read a book. - _Madmatt
String.Format is a nice feature, but slower than just appending.
"I do not know with what weapons World War 3 will be fought, but World War 4 will be fought with sticks and stones." Einstein "Few things are harder to put up with than the annoyance of a good example." Mark Twain
-
String.Format is a nice feature, but slower than just appending.
"I do not know with what weapons World War 3 will be fought, but World War 4 will be fought with sticks and stones." Einstein "Few things are harder to put up with than the annoyance of a good example." Mark Twain
VectorX wrote:
just appending
String concatenation? X| X|
I know the language. I've read a book. - _Madmatt
-
String.Format is a nice feature, but slower than just appending.
"I do not know with what weapons World War 3 will be fought, but World War 4 will be fought with sticks and stones." Einstein "Few things are harder to put up with than the annoyance of a good example." Mark Twain
It's (almost) the right tool for this job.
-
How would you add the parameter for a query like:
SELECT * FROM Table WHERE ID IN (123,124,125);? Lists are a bit tougher to handle in a proper way..."When did ignorance become a point of view" - Dilbert
That is true, things get a bit problematic when you have many or a variable amount of parameters. First, I would simply try to avoid the situation if that is possible. Perhaps it is possible to replace the list with a nested select statement. It really depends on the case at hand. If that's not possible, I would put together the SQL command in my code. With a variable amount of parameters, I would simply use a loop to add the parameter placeholders to the SQL string and the parameters themselves to the command object. Taken to the extreme, this can become very ugly and results in absolutely unmaintainable code. In my current project here at work some genius tried to replace the complete data access layer with one single function which constructs every needed SQL statement in a few thousand (!) lines of spaghetti code. But, like with everything, there is no problem as long as it is used sparingly and with at least some thought. Edit: Here is the beginning of that monster function, a worthy candidate for this board as well:
public static DataSet ArtikelHelpListe(int auswahl, string artnr, string artbez, string lnam,
string lnr, string lartnr, string wg, string mkenz,
string ean, string gab, bool ges, bool bug, bool bau,
bool eks, int stanort, string uid)
{
// Here be dragons ( thousands of lines of totally worthless code)
....
}A while ago he asked me what he should have printed on my business cards. I said 'Wizard'. I read books which nobody else understand. Then I do something which nobody understands. After that the computer does something which nobody understands. When asked, I say things about the results which nobody understand. But everybody expects miracles from me on a regular basis. Looks to me like the classical definition of a wizard.
-
Did you look at the code in the OP? You can't do SQL injection if your parameter data is strongly typed Int32 and DateTime values. If it was a string that's a different story, but if you are doing extra code to make sure no one is slipping SQL keywords into your ints then you are wasting a lot of time way overarchitecting.
T M Gray wrote:
overarchitecting
X|
-
Admittedly a SP would be much a nicer solution, but in real life you may have to work with databases where you are nowhere near getting authorized to implement a SP. Think of implementing a reporting system for at large financial institution as a consultant. What do you think their reply would be if you came saying "I need a dozen new stored procedures in your central DB2-database"? The polite answers would be something along the lines of "I'm sorry but that won't be possible", "Are you quite sure this is needed?" etc, etc. The impolite answer would be to find someone else to do the job.
So you're working in a large financial institution where they won't let you create a stored procedure but give you direct update access to tables !!!!!!!! Tell me which one so I can change my accounts :) seriously - the most sensible advantage of SPs over inline code is security - you can give the application(s) access ONLY to stored procedures (and only to certain stored procs if you want) and then easily manage the changing of those stored procedures to avoid someone accidentally or deliberately stuffing something up. In any decently secured DB the application should never have direct access to the tables (certainly not to update the tables) - as this doesn't prevent the simplest mistake
Update AccountBalance Set Balance = 0;for example - oops, forgot the 'where' Making all updates tothe DB go via a stored procedure allows you not only to monitor changes to the processes, but also so make modifications without changing the application and redeploying, and allows you to add (for example) logging easily. Granted your example was for implementing a reporting system - in that case you may have read-only access to tables and I guess it's a matter of taste as much as anything as to whether you code sql inline or not. that said, you can unit test an SP, you can check it works independently, you can add logging if it is doing something strange, all without having to redeploy the damn application - so it can be very useful indeed to use them all the time. Oh, and a nicely formatted SP is much easier to read than inline code - especially where that code is build up out of many strings, so the only way to work out what it actually dies is to run in debug and look at the runtime value... Hmm - I sound like a SP evangelist, don't I?___________________________________________ .\\axxx (That's an 'M')
-
So you're working in a large financial institution where they won't let you create a stored procedure but give you direct update access to tables !!!!!!!! Tell me which one so I can change my accounts :) seriously - the most sensible advantage of SPs over inline code is security - you can give the application(s) access ONLY to stored procedures (and only to certain stored procs if you want) and then easily manage the changing of those stored procedures to avoid someone accidentally or deliberately stuffing something up. In any decently secured DB the application should never have direct access to the tables (certainly not to update the tables) - as this doesn't prevent the simplest mistake
Update AccountBalance Set Balance = 0;for example - oops, forgot the 'where' Making all updates tothe DB go via a stored procedure allows you not only to monitor changes to the processes, but also so make modifications without changing the application and redeploying, and allows you to add (for example) logging easily. Granted your example was for implementing a reporting system - in that case you may have read-only access to tables and I guess it's a matter of taste as much as anything as to whether you code sql inline or not. that said, you can unit test an SP, you can check it works independently, you can add logging if it is doing something strange, all without having to redeploy the damn application - so it can be very useful indeed to use them all the time. Oh, and a nicely formatted SP is much easier to read than inline code - especially where that code is build up out of many strings, so the only way to work out what it actually dies is to run in debug and look at the runtime value... Hmm - I sound like a SP evangelist, don't I?___________________________________________ .\\axxx (That's an 'M')
I'm an anti-SP evangelist. I have been ever since I had some SPs disappear and bring a system down (granted that was SQL Server 6, but I still don't know the cause). Having said that, I do see that SPs (at least with SQL Server) offer the things you mention -- but I wonder how many companies actually implement the security and logging features that are available. And mightn't that only be appropriate for in-house systems where you can enforce it, and not so much for software for sale, where the buyer may demand "it's my database and I want access". My primary concern with SPs is the ease of changing them that you mention -- I see that as a Very Bad Thing. I want all code changes to go through change control, testing, and a proper deployment. Certainly you may have a process for that in place, but in my opinion SPs are too easy to change in the field. They are too open to a properly-authorized but malicious (or inexperienced) employee. Additionally, what happens to changes to SPs when a database is restored from backup? Don't you have to reapply any changes you made since the backup? That sounds like additional maintenance to me. As a bonus, not being able to implement a change instantaneously allows a cooling-off period -- you may be able to convince the customer that the change they want is a bad idea.
-
I'm an anti-SP evangelist. I have been ever since I had some SPs disappear and bring a system down (granted that was SQL Server 6, but I still don't know the cause). Having said that, I do see that SPs (at least with SQL Server) offer the things you mention -- but I wonder how many companies actually implement the security and logging features that are available. And mightn't that only be appropriate for in-house systems where you can enforce it, and not so much for software for sale, where the buyer may demand "it's my database and I want access". My primary concern with SPs is the ease of changing them that you mention -- I see that as a Very Bad Thing. I want all code changes to go through change control, testing, and a proper deployment. Certainly you may have a process for that in place, but in my opinion SPs are too easy to change in the field. They are too open to a properly-authorized but malicious (or inexperienced) employee. Additionally, what happens to changes to SPs when a database is restored from backup? Don't you have to reapply any changes you made since the backup? That sounds like additional maintenance to me. As a bonus, not being able to implement a change instantaneously allows a cooling-off period -- you may be able to convince the customer that the change they want is a bad idea.
PIEBALDconsult wrote:
some SPs disappear and bring a system down (granted that was SQL Server 6, but I still don't know the cause).
I can understand your point - but what if a DLL had disapeared without explanation? There's nothing magical about SPs thery are just Database objects.
PIEBALDconsult wrote:
software for sale, where the buyer may demand "it's my database and I want access".
I've found that it depends onthe size and computer-savviness of the customer. small customers, yes, customers with any sort of IT dept really hate having their users have access, but demand full access to the IT staff. If all access to your data is through SPs, then you can give execute only access to everyone (and even require a password as a parameter if you're paranoid) but restrict changing of SPs to only those few.
PIEBALDconsult wrote:
all code changes to go through change control, testing, and a proper deployment.
sure - in fact most large sites are much more diligent about this in DB than in code. You can put SP scripts in source control, you can set up tests to test them, you can do source compare and versioning etc. etc. You can do all that you can do with any other source code because the source code for an SP is just that - source code.
PIEBALDconsult wrote:
They are too open to a properly-authorized but malicious (or inexperienced) employee.
.. who has open slather to the entire database, in your model, tables data and all! Sure a sufficiently irate tachie could change a SP to regularly do womething - equally they could just change the data, write a tigger etc.
PIEBALDconsult wrote:
when a database is restored from backup? Don't you have to reapply any changes you made since the backup?
No - you usually back up databases as a whole - SPs, views, tables, data, keys, foregin keysm constraints etc.etc.etc. and restore them the same - so no additional work at all.
PIEBALDconsult wrote:
As a bonus, not being able to implement a change instantaneously allows a cooling-off period -- you may be able to convince the customer that the change they want is a bad idea.
it's rare to make a change to a live site without some thought and process -
-
PIEBALDconsult wrote:
some SPs disappear and bring a system down (granted that was SQL Server 6, but I still don't know the cause).
I can understand your point - but what if a DLL had disapeared without explanation? There's nothing magical about SPs thery are just Database objects.
PIEBALDconsult wrote:
software for sale, where the buyer may demand "it's my database and I want access".
I've found that it depends onthe size and computer-savviness of the customer. small customers, yes, customers with any sort of IT dept really hate having their users have access, but demand full access to the IT staff. If all access to your data is through SPs, then you can give execute only access to everyone (and even require a password as a parameter if you're paranoid) but restrict changing of SPs to only those few.
PIEBALDconsult wrote:
all code changes to go through change control, testing, and a proper deployment.
sure - in fact most large sites are much more diligent about this in DB than in code. You can put SP scripts in source control, you can set up tests to test them, you can do source compare and versioning etc. etc. You can do all that you can do with any other source code because the source code for an SP is just that - source code.
PIEBALDconsult wrote:
They are too open to a properly-authorized but malicious (or inexperienced) employee.
.. who has open slather to the entire database, in your model, tables data and all! Sure a sufficiently irate tachie could change a SP to regularly do womething - equally they could just change the data, write a tigger etc.
PIEBALDconsult wrote:
when a database is restored from backup? Don't you have to reapply any changes you made since the backup?
No - you usually back up databases as a whole - SPs, views, tables, data, keys, foregin keysm constraints etc.etc.etc. and restore them the same - so no additional work at all.
PIEBALDconsult wrote:
As a bonus, not being able to implement a change instantaneously allows a cooling-off period -- you may be able to convince the customer that the change they want is a bad idea.
it's rare to make a change to a live site without some thought and process -
_Maxxx_ wrote:
No - you usually back up databases as a whole - SPs, views, tables, data, keys, foregin keysm constraints etc.etc.etc. and restore them the same
That's my point, you would lose recent code changes and have to reapply them.
_Maxxx_ wrote:
+ " and active = true".
I've never written code like that. Currently I use C# verbatim strings.
cmd.CommandText = @"
SELECT
blah
, blah
, blah
FROM table
WHERE something
AND something else
" ;Having linefeeds in the SQL makes them easier to read when logged with error messages. Lately I've been thinking about storing SQL statements as resources.
_Maxxx_ wrote:
keep an open mind and look at the pros and cons.
As I have, but also remember that many of the features we've discussed are unique to SQL Server. I use a lot if different database systems and try to stay as database-agnostic as I can.
-
So you're working in a large financial institution where they won't let you create a stored procedure but give you direct update access to tables !!!!!!!! Tell me which one so I can change my accounts :) seriously - the most sensible advantage of SPs over inline code is security - you can give the application(s) access ONLY to stored procedures (and only to certain stored procs if you want) and then easily manage the changing of those stored procedures to avoid someone accidentally or deliberately stuffing something up. In any decently secured DB the application should never have direct access to the tables (certainly not to update the tables) - as this doesn't prevent the simplest mistake
Update AccountBalance Set Balance = 0;for example - oops, forgot the 'where' Making all updates tothe DB go via a stored procedure allows you not only to monitor changes to the processes, but also so make modifications without changing the application and redeploying, and allows you to add (for example) logging easily. Granted your example was for implementing a reporting system - in that case you may have read-only access to tables and I guess it's a matter of taste as much as anything as to whether you code sql inline or not. that said, you can unit test an SP, you can check it works independently, you can add logging if it is doing something strange, all without having to redeploy the damn application - so it can be very useful indeed to use them all the time. Oh, and a nicely formatted SP is much easier to read than inline code - especially where that code is build up out of many strings, so the only way to work out what it actually dies is to run in debug and look at the runtime value... Hmm - I sound like a SP evangelist, don't I?___________________________________________ .\\axxx (That's an 'M')
-
While working with a client to help them clean up their code I found this little gem. They absolutely never knew string.Format existed :wtf:
string sql = "select * from table where id={0} and date={1}";
string cmdText = sql.replace("{0}", id.Tostring())
.replace("{1}", DateTime.Now.ToShortDateString());
I know the language. I've read a book. - _Madmatt
I also found in several codes (written by some experts :^) though) using
string.Replace()to replace the text "{0}" with proper string values. Not only that, I found some code where they used:string myStr2 = myStr1.ToString();
What is the significance of that code? :confused: Why do you need to
ToString()a string? If experts are doing like that, then what the freshers will do, who follows that expert who has several years of experience in that field!!!Don't forget to Click on [Vote] and [Good Answer] on the posts that helped you.
Regards - Kunal Chowdhury | Software Developer | Chennai | India | My Blog | My Tweets | Silverlight Tutorial
-
_Maxxx_ wrote:
No - you usually back up databases as a whole - SPs, views, tables, data, keys, foregin keysm constraints etc.etc.etc. and restore them the same
That's my point, you would lose recent code changes and have to reapply them.
_Maxxx_ wrote:
+ " and active = true".
I've never written code like that. Currently I use C# verbatim strings.
cmd.CommandText = @"
SELECT
blah
, blah
, blah
FROM table
WHERE something
AND something else
" ;Having linefeeds in the SQL makes them easier to read when logged with error messages. Lately I've been thinking about storing SQL statements as resources.
_Maxxx_ wrote:
keep an open mind and look at the pros and cons.
As I have, but also remember that many of the features we've discussed are unique to SQL Server. I use a lot if different database systems and try to stay as database-agnostic as I can.
PIEBALDconsult wrote:
That's my point, you would lose recent code changes and have to reapply them.
? I don't follow? You release a change to the database, which is backed up that night. Unless you want to roll back the change before the next backup, I don't seee the problem - or am I missing something?
PIEBALDconsult wrote:
Lately I've been thinking about storing SQL statements as resources.
Good idea. You could write them as text and then save them to the database - call them, erm, I dunno, well, they're SQL procedures and you' re storing them - hey call them Stored Procedures :)
___________________________________________ .\\axxx (That's an 'M')
-
PIEBALDconsult wrote:
That's my point, you would lose recent code changes and have to reapply them.
? I don't follow? You release a change to the database, which is backed up that night. Unless you want to roll back the change before the next backup, I don't seee the problem - or am I missing something?
PIEBALDconsult wrote:
Lately I've been thinking about storing SQL statements as resources.
Good idea. You could write them as text and then save them to the database - call them, erm, I dunno, well, they're SQL procedures and you' re storing them - hey call them Stored Procedures :)
___________________________________________ .\\axxx (That's an 'M')
I mean add or modify a stored procedure, then have to restore from an earlier backup. (Consider a client who doesn't have a well-run shop.) If I stored my SQL in a database, it wouldn't be in the database they act upon; just as I have a database that contains nothing but user-defined functions which I can use on any of my databases. Plus it would allow for multiple versions of a statement. At any rate, I meant resources within the application.