• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Passing DataTable to SP with SQL Server 2008 & .NET 1.1

Hey experts,

we have a SP on SQL Server 2008 that takes a user-defined table type (a 2008 feature). Of course from .NET 4.0 (Visual Studio 2010), I'm able to call it normally passing to it a .NET DataTable.
Now the issue is that I need to call it from a .NET 1.1 application (VS 2003); is there anyway this can be done using some add-in?
0
mte01
Asked:
mte01
  • 4
1 Solution
 
Pratima PharandeCommented:
have you tried passing .net datatable . I think that will work

Found a different way to go about doing it. This way uses the System.Data.SqlClient libraries to create a connection to the database, specify the stored procedure name, and then pass a parameter in as a DataTable that serves as the SQL Server user-defined table type.

using (SqlConnection conn = new SqlConnection(connStr)) {
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "dbo.usp_Test";
    cmd.Parameters.AddWithValue("@ids", dt);
    conn.Open();

    using (SqlDataReader sqlReader = cmd.ExecuteReader()) {
        DataTable retTbl = new DataTable();
        retTbl.Load(sqlReader);
    }
refer
http://stackoverflow.com/questions/10624394/sql-server-user-defined-table-type-and-net
0
 
mte01Author Commented:
>>pratima_mcs

.NET 1.1 does not have AddWithValue; it has an overload of .Add that takes name & value (without dbtype); we tried this, but it errored out at the .Add command with:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list
0
 
mmsiCommented:
You could try:

SqlParameter param = New SqlParameter();
param = cmd.Parameters.Add("@ids", SqlType.Structured);
param.Value = "your dataTable";

If you receive the message "sqlDBType.Structured" is not part of System.Data.SqlDbType

Then you may need to install a more recent version the the .net framework, because .Structured was added after 2003.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mte01Author Commented:
>>mmsi

This was my point in the 1st place :) I cannot upgrade my current project from 1.1 because it's very large, and I need - if possible - some add-on library that would allow me to do this SP call to SQL Server 2008
0
 
mte01Author Commented:
Apparently, there's no way to do it, but using .NET 3.5 (VS 2008) - it can't be done using .NET 1.1
0
 
mte01Author Commented:
Came down to it after research
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now