Webpart中用DataGrid绑定SQL数据？ [downmoon原作]

自写了一个WebPart 用DataRead列出数据，准备
部署在SharePoint服务器上，想想很容易，一路顺利，可是将导入的WebPart拽到页面时，出来这个错误：

An Error has occurred: Request for the permission of type System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.

看来WebPart访问SQL有问题：
于是。在WSS的webConfig文件中添加下列两行：

< SafeControlAssembly = " System.Data,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089 " Namespace = " System.Data.SqlClient " TypeName = " * " Safe = " True " />
< SafeControlAssembly = " System.Data,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089 " Namespace = " System.Data " TypeName = " * " Safe = " True " />

还是不行，
又在C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/60/config/wss_minimaltrust.config文件中添加下面一行：

< SecurityClassName = " SqlClientPermission " Description = " System.Data.SqlClient.SqlClientPermission,System.Data,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089 " />

服务器　iisreset后　还是不行！
这可怪了！　再到服务器上一看，原来数据库采用Windows身份验证模式，而不是混合验证模式

于是，再次将服务器上的ASPNET帐号户设置为所有数据库访问权

还是不行
怀疑是.net运行权限不够
再次在服务器中"信任程序集"，将服务器上的System.dll的程序集权限设主完全信任！
还是不行！我晕

再捍源码中连接串如下：
"Data Source=sp-hgh;Initial Catalog=NorthWind;uid-sa;pwd=;"
马上改为：
"Data Source=sp-hgh;Initial Catalog=NorthWind;Integrated Security=SSPI;Trusted_Connection=yes;"

结果还是出不来数据
唉！整个一郁闷

最后捍了XGP的文章《[url=http://xgp1226.blogdriver.com/xgp1226/598244.html]Web部件访问SQL数据库--寻找收获的快乐[/url]》一文，深受启发，又参考MSDN文档，终于成功！
方法：

然后在wss_minimaltrust.config文件（在Webconfig文件中查找文件的位置)的<SecurityClass>节点中加入子节点：

< SecurityClassName = " SqlClientPermission " Description = " System.Data.SqlClient.SqlClientPermission,System.Data,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089 " /> ，

< SecurityClassName = " SharePointPermission " Description = " Microsoft.SharePoint.Security.SharePointPermission,Microsoft.SharePoint.Security,Version=11.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c " />

再在ASP.net(注意必须）的<PermissionSet>节点中加入子节点：

< IPermission
class = " SqlClientPermission "
version = " 1 "
Unrestricted = " true "
/>
< IPermission class = " SharePointPermission "
version = " 1 "
ObjectModel = " True "
/>

再重启IIS
iisreset 后，　一切OK！

我的源码如下：

using System;
using System.ComponentModel;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebPartPages;
using System.Data;
using System.Data.SqlClient;
namespace NewDispData
... {
/**/ /// <summary>
/// DescriptionforWebPart1.
/// </summary>
[DefaultProperty( " Text " ),
ToolboxData( " <{0}:WebPart1runat=server></{0}:WebPart1> " ),
XmlRoot(Namespace = " NewDispData " )]
public class NewDispData:Microsoft.SharePoint.WebPartPages.WebPart // ,ICellConsumer // ,IRowProvider
... {

webPart变量 #region webPart变量
private const string defaultText = "" ;
private string text = defaultText;
[Browsable( true ),
Category( " Miscellaneous " ),
DefaultValue(defaultText),
WebPartStorage(Storage.Personal),
FriendlyName( " Text " ),
Description( " TextProperty " )]
public string Text
... {
get
... {
return text;
}

set
... {
text = value;
}
}
const string connectionStr = " server=downmoon-hgh;database=NorthWind;uid=sa;pwd=sa; " ; //
protected LabelErrorLabel;
protected DataGridOrdersGrid;

SqlConnectionNorthWindConnection = new SqlConnection(connectionStr);
SqlDataAdapterNorthWindAdp = null ;
DataSetNorthWindDataSet = null ;
private int _connectionCount = 1 ;
#endregion

　　　　 创建子控件 #region 创建子控件
protected override void CreateChildControls()
... {
// Thislabelisdisplayedonlyif
// anexceptionisthrownwhileattempting
// toloadthedata.
ErrorLabel = new Label();
ErrorLabel.Visible = false ;
Controls.Add(ErrorLabel);

// Createthedatagrid
OrdersGrid = new DataGrid();
BoundColumnnewColumn;

OrdersGrid.Load += new EventHandler(OrdersLoad);
OrdersGrid.AllowPaging = false ;
OrdersGrid.HeaderStyle.Font.Bold = true ;
OrdersGrid.HeaderStyle.Wrap = false ;
OrdersGrid.GridLines = System.Web.UI.WebControls.GridLines.Both;
OrdersGrid.AutoGenerateColumns = false ;

// Definegridcolumns
newColumn = new BoundColumn();
newColumn.DataField = " OrderID " ;
newColumn.HeaderText = " OrderID " ;
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = " CustomerID " ;
newColumn.HeaderText = " CustomerID " ;
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();

newColumn.DataField = " OrderDate " ;
newColumn.HeaderText = " OrderDate " ;
newColumn.DataFormatString = " {0:d} " ;
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = " RequiredDate " ;
newColumn.HeaderText = " RequiredDate " ;
newColumn.DataFormatString = " {0:d} " ;
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = " ShippedDate " ;
newColumn.HeaderText = " ShippedDate " ;
newColumn.DataFormatString = " {0:d} " ;
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = " Freight " ;
newColumn.HeaderText = " FreightCost " ;
newColumn.DataFormatString = " {0:c} " ;
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);

Controls.Add(OrdersGrid);
}
/**/ /// <summary>
/// OrdersLoad
/// handlesOrdersGrid.Load.
/// </summary>
///
public void OrdersLoad( object sender,EventArgse)
... {
BindGrid();
}
private void BindGrid()
... {
// LoadschemaanddataintoaDataSet.
DataSetordersSet = new DataSet();

// AssumeXMLdatafileisinthewpresourcesfolder.
// IfFileI/Opermissionsarenotavailable,
// thefollowingwillthrowasecurityexception.
try
... {
NorthWindConnection.Open();
string sqlSelectStr = " select*fromOrders " ;
NorthWindAdp = new SqlDataAdapter(sqlSelectStr,NorthWindConnection);
NorthWindDataSet = new DataSet();
NorthWindAdp.Fill(NorthWindDataSet, " Orders " );
NorthWindConnection.Close();
}
catch (Exceptionex)
... {
ErrorLabel.Text =
ex.Message + " <br> " +
" Stepstocorrectthisareincludedin " +
" thedocumentationforthissample. " ;
ErrorLabel.Visible = true ;
return ;
}
finally
... {
NorthWindConnection = null ;
NorthWindAdp = null ;
}

// Noerrorifwemadeitthisfar.
ErrorLabel.Visible = false ;

// UseaDataViewtofilterorders.

string rowFilter = "" ;
/**/ /// /if(CustomerId!="")
/// /{
/// /rowFilter="CustomerID='"+CustomerId+"'";
/// /}
/// /if(CustomerId!="")
/// /{
/// /rowFilter="CustomerID='"+"CHOPS"+"'";
/// /}

DataViewordersView = new DataView(NorthWindDataSet.Tables[ " Orders " ]);
/**/ /// /ordersView.RowFilter=rowFilter;

OrdersGrid.Enabled = true ;
OrdersGrid.DataSource = ordersView;
OrdersGrid.DataBind();
}

#endregion
/**/ /// <summary>
/// RenderthisWebParttotheoutputparameterspecified.
/// </summary>
/// <paramname="output"> TheHTMLwritertowriteoutto </param>
protected override void RenderWebPart(HtmlTextWriteroutput)
... {
// output.Write(SPEncode.HtmlEncode(Text));
EnsureChildControls();
if (ErrorLabel.Visible == true )
... {
ErrorLabel.RenderControl(output);
return ;
}
// Ifconnectedthendisplayaheadingandthegrid.
if (_connectionCount > 0 )
... {
output.RenderBeginTag( " div " );
output.Write( " <br> " );
output.AddStyleAttribute(HtmlTextWriterStyle.FontWeight, " bold " );
output.RenderBeginTag(HtmlTextWriterTag.Span);
/**/ /// /output.Write(" <nobr> OrdersforCustomerID:"
/// /+System.Web.HttpUtility.HtmlEncode(CustomerId)+" </nobr> ");
output.RenderEndTag(); // span
output.Write( " <br> " );
output.Write( " <br> " );
OrdersGrid.RenderControl(output);
output.RenderEndTag(); // div
}
if (_connectionCount > 0 )
... {
output.RenderBeginTag( " div " );
output.Write( " <br> " );
output.AddStyleAttribute(HtmlTextWriterStyle.FontWeight, " bold " );
output.AddStyleAttribute(HtmlTextWriterStyle.Color, " #000000 " );
output.RenderBeginTag(HtmlTextWriterTag.Span);
/**/ /// /output.Write(" <nobr> OrdersforCustomerID:"
/// /+System.Web.HttpUtility.HtmlEncode(CustomerId));
/// /output.Write(" </nobr> ");
output.RenderEndTag(); // span
BindGrid();
OrdersGrid.RenderControl(output);
output.RenderEndTag(); // div
}

}
}