Multiple attachment columns in RadGrid

[Francois MARTIN]

Hi,

I would like to include multiple attachment columns in a RadGrid that is built dynamically.
All the attachments are comming from the same SQL table (ID, FileName, document).
Therefore my RadGrid data contains several columns generated by a stored procedure:

• ID1, FileName1
• ID2, Filename2
• ID3, Filename3
• ...

I am using SQL Server stored procedures, and specifically a proc called "getDocument" that has a single parameter: "@ID" declared as an "int" and returning "document" (the file data; varbinary). This procedure is delcared in an SqlDataSource (SqlDataSourceAttachment), with a single parameter "ID".

The problem is that in order to manage the attachment columns with several GridAttachmentColumn using that SqlDataSourceAttachment, I would have to map the @ID parameter of the SqlDataSourceAttachment with the RadGrid data columns i.e. ID1, ID2, ID3... and not just ID.

Any suggestions on how I can do that ?

François

[Francois MARTIN]

Well after I litle effort I managed to do it, so I figured out that I would share that small trick with you guys.
Please do post comments if I said anything innacurate or if you have a better solution!

Since the radgrid is built dynamically, but this applies to statically created radgrids as well, one would just have to add a litle code in the PageLoad or PageInit to define the SqlDataSources of the GridAttachmentColumns.
For example, I have added the following code during the Init phase:

// Create a specific datasource to allow multiple attachments by dynamically changing the ID of the SqlDataSource
SqlDataSource sqlDS = new SqlDataSource();
sqlDS.ID = "SqlDataSourceDocument" + TableColno;
sqlDS.ConnectionString = ConfigurationManager.ConnectionStrings["SqlData"].ConnectionString;
sqlDS.SelectCommand = "getDocument";
sqlDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
sqlDS.SelectParameters.Add(colName, DbType.Int32, null);
sqlDS.Selecting += new SqlDataSourceSelectingEventHandler(SqlDataSourceDocument_Selecting);
PlaceHolderDynamicData.Controls.Add(sqlDS);
gAttach.AttachmentKeyFields = new string[] { colName };
gAttach.AttachmentDataField = "document";
gAttach.DataSourceID = sqlDS.ID;

gAttach is a GridAttachmentColumn (there can be several columns of this type in the grid).TableColno is an integer representing the column number in the RadGrid. It is used to generate unique column names.
"getDocument" is the name of the stored procedure returning the Varbinary data of the attachment from the database. Here is the code of that proc:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDocument]') AND type in (N'P'))
DROP PROC [dbo].[getDocument]
go
create procedure getDocument @ID int
as
begin
    select ID, document
    from documents
    where ID = @ID
end
go

Now here is the trick: the dynamically created SqlDataSource intercepts the "Selecting" event with the following method:

protected void SqlDataSourceDocument_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters[0].ParameterName = "@ID";
}

This way, we can handle multiple GridAttachmentColumns, each using the same stored procedure or SQL statment.
All of this had to be done just because the GridAttachmentColumn uses a DataSource in wich the ID parameter HAS to have the same name that the ID parameter defined in the AttachmentKeyFields property of the GridAttachmentColumn itself.

Hope that it helps someone one day... :)
Francois