In Part 1 of this article we compared the system function fn_listextendedproperty of SQL Server 2005 with the similar function of SQL Server 2000, wrote a few queries for SQL Server 2005 to test the function. In part 2, I am going to describe using the function for .NET applications.
The best "place" to store text for labels or/and "header text" of columns (see Part 1) is the MS_Description. This property specifies informational text for every column of the table. For example, you have some table. The name of this table is "Request". The table has eleven columns, one of the columns is "Reason_Closing". You write into "Description" property of this column: "Reason of closing". Since this moment each label for this field (I mean "Reason_Closing") should have the same name : "Reason of closing". Well ! In a few month you should "translate" your .NET application into the Spanish language. There is no problem! You don't touch forms of your application , you just change the property to "La razon de cierre". Then, in one month - into the French language: "La raison de fermer", etc.
Let's name our stored procedure, with the help of which we are going to get data of a table, "usp_TableData" ( "usp" stands for "user stored procedure"). Now we should define input parameters which allow the caller to pass a data value to the stored procedure. These parameters are used to exchange data between our stored procedure and our .NET application. In Part 1 we saw in order to get some data of columns of some table we have to define for the function
fn_listextendedproperty (arg_name, arg0_type, arg0_name, arg1_type, arg1_name,
arg2_type, arg2_name)
at least the following arguments : arg0_type, arg0_name, arg1_type, arg1_name. If we want "to choose" any certain column, we have to define also : arg2_type, arg2_name. Well! Now we are sure that our stored procedure has to have three input parameters :
@Table varchar(50), @Schema varchar(20), @Cols varchar(500) = ''.
Pay attention , that @Cols is an optional parameter . When the stored procedure is executed, the default value (@Cols = '') is used if no other value has been specified. In this case we get data for all columns. But if , for example, @Cols = 'Col_1,Col_2,Col_3' - we get data only for the following columns : Col_1,Col_2,Col_3.
From our "SELECT ..." SQL statement we are going to get such data as : name, description, type, length of the column. Now we are ready for the code :
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'usp_TableData'
AND type = 'P')
DROP PROCEDURE dbo.usp_TableData
GO
-----------------------------------------------------------
CREATE PROCEDURE dbo.usp_TableData
@Table varchar(50),
@Schema varchar(20),
@Cols varchar(500) = ''
AS
DECLARE @Select nvarchar (800)
BEGIN
set @Select = ''
set @Select = @Select +
' select syscolumns.[Name] as ColName,[value] as ColDescr, ' +
' xtype,xusertype,length from syscolumns, ' +
'::fn_listextendedproperty (default, ''schema'', ''' + @Schema +
''', ''table'',''' + @Table + ''',''column'',null) ' +
' where syscolumns.id = ' +
' (select sysobjects.id from sysobjects where type=''U'' ' +
' and [name] = ''' + @Table + ''') ' +
' and fn_listextendedproperty.objname = syscolumns.[name] ' +
' and fn_listextendedproperty.[name] = ''MS_Description'' '
if ltrim(rtrim(@Cols)) = ''
begin
set @Select = @Select + ' order by syscolumns.colid '
end
else
begin
set @Select = @Select + ' and syscolumns.Name in (''' +
replace (@Cols,',',''',''') + ''')' + ' order by syscolumns.colid '
end
exec sp_executesql @Select
END
GO
When executing this stored procedure you can receive an error : "Cannot resolve collation conflict..." . In this case you just should changed the line
' and fn_listextendedproperty.objname = syscolumns.[name] ' +
to the following :
' and fn_listextendedproperty.objname COLLATE Latin1_General_CI_AS =' + 'syscolumns.[name] ' +
You can test the stored procedure with the help of the following code:
--for the columns EmployeeID,MaritalStatus,Gender,ModifiedDate
--of "Employee" table
exec dbo.usp_TableData 'Employee','HumanResources',
'EmployeeID,MaritalStatus,Gender,ModifiedDate'
--for all columns of "Employee" table
exec dbo.usp_TableData 'Employee','HumanResources'
The result is shown in figure 9.
![Fig_9.gif]()
Figure 9.
Of course, for your own table you will use shorter descriptions (as you will use descriptions for label's text, etc). For example, instead of "Date and time the record was last updated." you will write just "Last update" and so on.
Now you can use this stored procedure by any way which you like more.
As for me I like to execute the usp_TableData stored procedure inside another one and get some DataSet which consists at least of two tables : the first table is select from the table itself and the second - data of the columns of this table. For example, if I want to select EmployeeID, MaritalStatus, Gender, ModifiedDate columns from Employee table I will use the follows T-SQL code inside some stored procedure:
----------------------------
select
EmployeeID,MaritalStatus,Gender,ModifiedDate
from
HumanResources.Employee
exec dbo.usp_TableData 'Employee','HumanResources', 'EmployeeID,MaritalStatus,Gender,ModifiedDate'
----------------------------
The result is shown in figure 10.
![Fig_10.gif]()
Figure 10.
Now we are ready to pass to some tips ( in C#) , which can be useful for .NET application.
I recommend , if you are going to fill "automatically" labels and/or headers for your grid, first of all to add to your code page some objects , which hold value of such columns as "ColName", "ColDescr", "length" under the order which is defined by our stored procedure usp_TableData.
#region "forClass"
//Creates the arr_ColName object that holds value of
//the ColName column
ArrayList arr_ColName = new ArrayList ();
//Creates the arr_ColDescr object that holds value of
//the ColDescr column
ArrayList arr_ColDescr = new ArrayList ();
//Creates the arr_ColLength object that holds value of
//the Length column
ArrayList arr_ColLength = new ArrayList ();
//...something else for the class
#endregion
In order to fill our arr_... objects we can write some method fillArrayNameDesc which has an input parameter of the type of DataSet (the first table in this DataSet is select from the table itself and the second - data of the columns of this table; see above):
//The method fillArrayNameDesc initialize
//the arr_ColName , arr_ColDescr and
//arr_ColLength objects.
private void fillArrayNameDesc(DataSet ds)
{
DataTable dtNameDescr = new DataTable () ;
int iCount =0;
int iMax ;
dtNameDescr = ds.Tables[1];
iMax = dtNameDescr.Rows.Count ;
arr_ColName.Clear ();
arr_ColDescr.Clear ();
arr_ColLength.Clear ();
if (iMax > 0)
{
for (iCount=0;iCount < iMax ; iCount++)
{
try
{
arr_ColName.Add (dtNameDescr.
Rows[iCount]["ColName"].ToString().Trim());
}
catch(Exception ex)
{
arr_ColName.Add(ex.Message);
}
try
{
arr_ColDescr.Add(dtNameDescr.
Rows[iCount]["ColDescr"].ToString().Trim());
}
catch(Exception ex)
{
arr_ColDescr.Add (ex.Message);
}
try
{
arr_ColLength.Add(dtNameDescr.
Rows[iCount]["length"].ToString().Trim());
}
catch(Exception ex)
{
arr_ColLength.Add (ex.Message);
}
}
}
}
Let's assume there are four labels in your form : labelName0, labelName1, labelName2, labelName3. You can use the following method to change the text property of these labels:
private void setLabelText()
{
labelName0.Text = arr_ColDesc[0];
labelName1.Text = arr_ColDesc[1];
labelName2.Text = arr_ColDesc[2];
labelName3.Text = arr_ColDesc[3];
//....................................
}
Now let's assume that you named labels according to "formula" : label[Name of Column].
For example, label for column "ID" is named "labelID" and for column "Value_1" - "labelValue_1" . In this case you can use the following method:
private void setLabelText()
{
//....................................
int iControls = Controls.Count;
int iLabel = arr_ColDescr.Count;
for (int i =0; i< iControls ; i++ )
{
if (Controls[i].GetType() == typeof(Label) )
{
for(int j=0; j< iLabel ;j++)
{
if((string)arr_ColName[j]==
Controls[i].Name.Replace("label",""))
{
Controls[i].Text =
arr_ColDescr[j].ToString () ;
}
}
}
}
}
Or for ASPX-version :
private void setLabelText()
{
//....................................
int iControls = Controls.Count;
int iLabel = arr_ColDescr.Count;
for (int i =0; i< iControls ; i++ )
{
if (Controls[i].GetType() == typeof(Label) )
{
for(int j=0; j< iLabel ;j++)
{
if((string)arr_ColName[j]==
((Label)Controls[i]).ID.Replace("label",""))
{
((Label)Controls[i]).Text =
arr_ColDescr[j].ToString () ;
}
}
}
}
}
For some kind of grid you can use code like that:
private void setGrid(DataGrid myDG)
{
//..............................
int iCol = arr_ColDescr.Count;
for (int i =0; i< iCol ; i++ )
{
myDG.Columns[i].HeaderText = arr_ColDescr[i].ToString();
//..............................
}
//..............................
//if there is such property as "Width" you
//can define width of the column
// for (int i =0; i< iCol ; i++ )
// {
// myDG.Columns[i].Width = arr_ColLength[i].ToString()*6;
// }
//..............................
//and so on
}
CONCLUSION
I hope that this series of these articles will help you to 'facilitate' creation of your .NET application when you use SQL Server as database.
Good luck in programming !