As stated in the documentation for that constructor: When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object. Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, … Read more
This is how you explicitly set nvarchar(max): cmd.Parameters.Add(“@JobNumbers”, SqlDbType.NVarChar, -1); If you’re really concerned with performance you might want to consider passing a table of integers: https://stackoverflow.com/a/10779593/465509
For part 2 of your question, see my answer to Stored procedure: pass XML as an argument and INSERT (key/value pairs) for an example of how to use XML within a stored procedure. EDIT: Sample code below is based on the specific example given in the comments. declare @MyXML xml set @MyXML = ‘<booksdetail> <isbn_13>700001048</isbn_13> … Read more
You can’t (unfortunately) do that. A Sql Parameter can only be a single value, so you’d have to do: WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3…) Which, of course, requires you to know how many building ids there are, or to dynamically construct the query. As a workaround*, I’ve done the following: WHERE buildingID IN (@buildingID) … Read more
Go for a white list. There can only be a fixed set of possible correct values for the table name anyway – at least, so I’d hope. If you don’t have a white list of table names, you could start with a whitelist of characters – if you restrict it to A-Z, a-z and 0-9 … Read more
The problem is that the ?: operator cannot determine the return type because you are either returning an int value or a DBNull type value, which are not compatible. You can of course cast the instance of AgeIndex to be type object which would satisfy the ?: requirement. You can use the ?? null-coalescing operator … Read more
You should either use this method: SqlCommand cmd = new SqlCommand(“dbo.workScheduleDataGrid”, sqlcon); or this method SqlCommand cmd = sqlcon.CreateCommand(); to create the command, but not both (the second assignment in your code overwrites the first one). With the second options, you need to specify the command to execute separarely: cmd.CommandText = “dbo.workScheduleDataGrid”; Also, do not … Read more