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

S

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_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_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_LinkClicked(object sender,
LinkLabelLinkClickedEventArgs e)
{
this.Cursor = Cursors.WaitCursor;
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(), this.txtUser.Text,
this.txtPassword.Text);
for (int i = 0; i < srv.Databases.Count; i++)
{
if (srv.Databases.Item(i + 1, "dbo").Name ==
this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");
this.lstObjects.Items.Clear();
SQLDMO.SQLObjectList sqludf;
sqludf =
db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name);
for (int j = 0; j < sqludf.Count; j++)
{
//this.lstObjects.Items.Add(db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Item(j + 1, "dbo").Name);
}
this.Cursor = Cursors.Default;
return;
}
}
this.Cursor = Cursors.Default;
}
 
A

Andrea Montanari

hi,
serge said:
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_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_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_LinkClicked(object sender,
LinkLabelLinkClickedEventArgs e)
{
this.Cursor = Cursors.WaitCursor;
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),
this.txtUser.Text, this.txtPassword.Text);
for (int i = 0; i < srv.Databases.Count; i++)
{
if (srv.Databases.Item(i + 1, "dbo").Name ==
this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");
this.lstObjects.Items.Clear();
SQLDMO.SQLObjectList sqludf;
sqludf =
db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name);
for (int j = 0; j < sqludf.Count; j++)
{

//this.lstObjects.Items.Add(db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,
SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Item(j
+ 1, "dbo").Name); } this.Cursor = Cursors.Default;
return;
}
}
this.Cursor = Cursors.Default;
}
Click to expand...

try using the SQLDMO.Database2 interface, as the original one does not
provide functionnalities 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
 
C

Chris Fulstow

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
 
S

serge

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..
 
S

serge

Thank you Andrea, I will look at the SQLDMO.Database2 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.
 
Top