I have a data table which is filled within my application with some values that user has entered them via an excel file. My Application targer .net framework is 2.0 and I can NOT change it to 3.0 or 3.5 in order to use LINQ feature.
So, I have to send my data table values to a stored procedure and contribute them in a join operation.
Is it a good solution or not? If yes, How can I send my Data table to Stored procedure as an input parameter?
Thank you
By using table-valued parameters you can send data to the SQLServer stored procedure. This user-defined type represents the definition of a table structure and is compatible with SQLServer 2008 and next versions.
You can find example and more information referring to this msdn aritcle
Large Complex: For large complex data I'd probably get your data into a *.csv file (if it's not already that way in Excel), use .Net to BCP it into #temp tables, and then on that same connection call the proc and have the proc always know to look for the data in the #temp tables. BCP is the fasted way to get large chunks of data into SQLServer.
Medium Size: If the size of the data is small then you could format it as XML and send that to the proc. Here's a quick example using C# http://www.a2zdotnet.com/View.aspx?Id=107
Small Delimited: For small list type data you might be able to get away with sending it as comma delimited string of values. This is very handy when sending a list of ID's to a proc (http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx)
