SQL-DMO and C#: retrieve the list of User-Defined Functions

[serge]

http://www.csharphelp.com/archives2/archive342.html

I am using the sample code from this link but I am
unable to figure out how to retrieve the list of
the User-Defined Functions. I am able to get the
count of the user defined functions correctly using:

db.ListObjects( SQLDMO.SQLDMO_O BJECT_TYPE.SQLD MOObj_UserDefin edFunction,
SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name ).Count

but I am unable to get to enumerate the function names.

Then I tried to see if I can achieve what I want using
SQLObjectList but I was unsuccessful.

Does someone know how I can do this using C#?

Thank you

This is the full code I have:

private void linkLabel5_Link Clicked(object sender,
LinkLabelLinkCl ickedEventArgs e)
{
this.Cursor = Cursors.WaitCur sor;
SQLDMO.SQLServe r srv = new SQLDMO.SQLServe rClass();
srv.Connect(thi s.cboServers.Se lectedItem.ToSt ring(), this.txtUser.Te xt,
this.txtPasswor d.Text);
for (int i = 0; i < srv.Databases.C ount; i++)
{
if (srv.Databases. Item(i + 1, "dbo").Name ==
this.cboDatabas e.SelectedItem. ToString())
{
SQLDMO._Databas e db = srv.Databases.I tem(i + 1, "dbo");
this.lstObjects .Items.Clear();
SQLDMO.SQLObjec tList sqludf;
sqludf =
db.ListObjects( SQLDMO.SQLDMO_O BJECT_TYPE.SQLD MOObj_UserDefin edFunction,
SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name );
for (int j = 0; j < sqludf.Count; j++)
{
//this.lstObjects .Items.Add(db.L istObjects(SQLD MO.SQLDMO_OBJEC T_TYPE.SQLDMOOb j_UserDefinedFu nction,
SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name ).Item(j + 1, "dbo").Name );
}
this.Cursor = Cursors.Default ;
return;
}
}
this.Cursor = Cursors.Default ;
}

[Andrea Montanari]

Re: SQL-DMO and C#: retrieve the list of User-Defined Functions

hi,
serge wrote:[color=blue]
> http://www.csharphelp.com/archives2/archive342.html
>
> I am using the sample code from this link but I am
> unable to figure out how to retrieve the list of
> the User-Defined Functions. I am able to get the
> count of the user defined functions correctly using:
>
> db.ListObjects( SQLDMO.SQLDMO_O BJECT_TYPE.SQLD MOObj_UserDefin edFunction,
> SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name ).Count
>
> but I am unable to get to enumerate the function names.
>
> Then I tried to see if I can achieve what I want using
> SQLObjectList but I was unsuccessful.
>
> Does someone know how I can do this using C#?
>
> Thank you
>
> This is the full code I have:
>
> private void linkLabel5_Link Clicked(object sender,
> LinkLabelLinkCl ickedEventArgs e)
> {
> this.Cursor = Cursors.WaitCur sor;
> SQLDMO.SQLServe r srv = new SQLDMO.SQLServe rClass();
> srv.Connect(thi s.cboServers.Se lectedItem.ToSt ring(),
> this.txtUser.Te xt, this.txtPasswor d.Text);
> for (int i = 0; i < srv.Databases.C ount; i++)
> {
> if (srv.Databases. Item(i + 1, "dbo").Name ==
> this.cboDatabas e.SelectedItem. ToString())
> {
> SQLDMO._Databas e db = srv.Databases.I tem(i + 1, "dbo");
> this.lstObjects .Items.Clear();
> SQLDMO.SQLObjec tList sqludf;
> sqludf =
> db.ListObjects( SQLDMO.SQLDMO_O BJECT_TYPE.SQLD MOObj_UserDefin edFunction,
> SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name );
> for (int j = 0; j < sqludf.Count; j++)
> {
>
> //this.lstObjects .Items.Add(db.L istObjects(SQLD MO.SQLDMO_OBJEC T_TYPE.SQLDMOOb j_UserDefinedFu nction,
> SQLDMO.SQLDMO_O BJSORT_TYPE.SQL DMOObjSort_Name ).Item(j
> + 1, "dbo").Name ); } this.Cursor = Cursors.Default ;
> return;
> }
> }
> this.Cursor = Cursors.Default ;
> }[/color]

try using the SQLDMO.Database 2 interface, as the original one does not
provide functionnalitie s to access SQL Server 2000 new features..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

[Chris Fulstow]

Re: SQL-DMO and C#: retrieve the list of User-Defined Functions

Hi Serge,

As well as using SQLDMO, you could also query the built-in sysobjects
table to get the names of your UDFs, e.g.

SELECT [name] AS [UDF Name]
FROM sysobjects
WHERE xtype = 'FN' or xtype = 'IF'
ORDER BY [name]

FN = Scalar function
IF = In-lined table-function

HTH,

Chris

[serge]

Re: SQL-DMO and C#: retrieve the list of User-Defined Functions

Thank you Chris.

I would still hope someone knows how to achieve this using SQL-DMO.
Obviously using SQL-DMO I am able to get the count of the user-defined
functions so I am hoping someone who knows C# and/or SQL-DMO would
be able to tell me what I need to write to make this code show me the
function name or the SQL object name using SQLObjects..

[serge]

Re: SQL-DMO and C#: retrieve the list of User-Defined Functions

Thank you Andrea, I will look at the SQLDMO.Database 2 interface.

P.S.
I am not sure why my I didn't get your post downloaded to my news
reader. I read your email through Google.