SQLDataAdapter SP Generation

  • Thread starter Thread starter Ramil Domingo
  • Start date Start date
R

Ramil Domingo

Hi,

I have created an SQLDataAdapter component on my vb.net winform project and
stepped through the wizard. In my SQL Database I have a table with fields
using user defined data types and not the base data types. The problem is
that when the stored procedure gets created by the adapter the parameter's
data types did not follow the data type of the table I used in the wizard.

Is there any solution on how I could use my user defined data types in my
SP, autogenerated by the adapter, w/o actually modifying the SP after
running the wizard?

Appreciate any help.

Thanks.

Ramil Domingo
Developer
 
Thanks for the reply.

As I recal I have used the data type NVARCHAR. I created a User Defined
Data Type "Code nvarchar(10)" and "Desc nvarchar(100)". Then I created a
table with 2 fields and applied the 2 UDDT stated above to those fields.
After creating the SP from VS.NET using SQLDataAdapter I found out that the
Parameters created by the adapter from the table has a data type of
NVARCHAR(50) for both fields instead of "Code" and "Desc".

Have any idea?

Thanks.

Ramil Domingo
Developer
 
Hi Ramil,

I tested this scenarion and it shows the right length.
They type (nvarchar) is also correct since parameters use always basic types
only.
 
Hi Miha,

I beg to disagree. I tried it over and over but still the parameters are of
nvarchar(50) for both fields instead of nvarchar(30) and nvarchar(100)
respectively.

I have attached the script of the sample table and UDTs. If you want you
can run this script using ISQL under the Northwind database. Then open a
new VB.NET Project. Create a new component. Create an SQLConnection object
and connect to Northwind Database. Create an SQLDataAdapter object and run
the configuration wizard. Choose the "Create New Stored Procedure" as the
Query Type. Accept the defaults in the Advance Options. Open the Query
Builder and choose the table name [Ramil] from the attached script. Make
sure you've run the script first. Then you can either accept the default SP
name or change it. Then click finish. Go back to Enterprise Manager and
check the parameters from the created SP with respect to the Insert Command.
Take note of the data type length of that parameter against the table.

Appreciate the help.

Thanks.

Ramil Domingo
Developer
 
Hi Ramil,

A, I see the issue now. I was looking in different direction :)
Yes, it picks default nvarchar lengths.
The only suggestion I have - build your sps in some otherway.
As I've already recommended to Brad, there is an excellent free tool
CodeSmith that comes with templates that already build stored procedures. If
that doesn't suit you, modify them.
http://www.ericjsmith.net/codesmith/

(gee, i had to collect percentage :-) )

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Ramil Domingo said:
Hi Miha,

I beg to disagree. I tried it over and over but still the parameters are of
nvarchar(50) for both fields instead of nvarchar(30) and nvarchar(100)
respectively.

I have attached the script of the sample table and UDTs. If you want you
can run this script using ISQL under the Northwind database. Then open a
new VB.NET Project. Create a new component. Create an SQLConnection object
and connect to Northwind Database. Create an SQLDataAdapter object and run
the configuration wizard. Choose the "Create New Stored Procedure" as the
Query Type. Accept the defaults in the Advance Options. Open the Query
Builder and choose the table name [Ramil] from the attached script. Make
sure you've run the script first. Then you can either accept the default SP
name or change it. Then click finish. Go back to Enterprise Manager and
check the parameters from the created SP with respect to the Insert Command.
Take note of the data type length of that parameter against the table.

Appreciate the help.

Thanks.

Ramil Domingo
Developer
Miha Markic said:
Hi Ramil,

I tested this scenarion and it shows the right length.
They type (nvarchar) is also correct since parameters use always basic types
only.
Click to expand...
created
a that
the
Click to expand...
types
in
Click to expand...
Click to expand...
 
Hi Miha,

I hope MS could do something about this. It would be nice if they could add
this feature specially getting the UDT as is in the parameters of the SP.

Anyway thanks for all the help. Appreciate it alot.

Ramil Domingo
Developer
Miha Markic said:
Hi Ramil,

A, I see the issue now. I was looking in different direction :)
Yes, it picks default nvarchar lengths.
The only suggestion I have - build your sps in some otherway.
As I've already recommended to Brad, there is an excellent free tool
CodeSmith that comes with templates that already build stored procedures. If
that doesn't suit you, modify them.
http://www.ericjsmith.net/codesmith/

(gee, i had to collect percentage :-) )

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Ramil Domingo said:
Hi Miha,

I beg to disagree. I tried it over and over but still the parameters
Click to expand...
are
of
nvarchar(50) for both fields instead of nvarchar(30) and nvarchar(100)
respectively.

I have attached the script of the sample table and UDTs. If you want you
can run this script using ISQL under the Northwind database. Then open a
new VB.NET Project. Create a new component. Create an SQLConnection object
and connect to Northwind Database. Create an SQLDataAdapter object and run
the configuration wizard. Choose the "Create New Stored Procedure" as the
Query Type. Accept the defaults in the Advance Options. Open the Query
Builder and choose the table name [Ramil] from the attached script. Make
sure you've run the script first. Then you can either accept the
Click to expand...
default
SP
name or change it. Then click finish. Go back to Enterprise Manager and
check the parameters from the created SP with respect to the Insert Command.
Take note of the data type length of that parameter against the table.

Appreciate the help.

Thanks.

Ramil Domingo
Developer
created types
Click to expand...
Click to expand...
 
Back
Top