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文件中添加下列两行：
<SafeControl Assembly="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.Data.SqlClient" TypeName="*" Safe="True" />
<SafeControl Assembly="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文件中添加下面一行：
<SecurityClass Name="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的文章《
Web部件访问SQL数据库--寻找收获的快乐》一文，深受启发，又参考MSDN文档，终于成功！
方法：
然后在wss_minimaltrust.config文件（在Webconfig文件中查找文件的位置)的<SecurityClass>节点中加入子节点：
<SecurityClass Name="SqlClientPermission" Description="System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>，
<SecurityClass Name="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>
/// Description for WebPart1.
/// </summary>
[DefaultProperty("Text"),
ToolboxData("<{0}:WebPart1 runat=server></{0}:WebPart1>"),
XmlRoot(Namespace="NewDispData")]
public class NewDispData : Microsoft.SharePoint.WebPartPages.WebPart//,ICellConsumer//,IRowProvider
{
#region webPart变量
private const string defaultText = "";
private string text = defaultText;
[Browsable(true),
Category("Miscellaneous"),
DefaultValue(defaultText),
WebPartStorage(Storage.Personal),
FriendlyName("Text"),
Description("Text Property")]
public string Text
{
get
{
return text;
}
set
{
text = value;
}
}
const string connectionStr = "server=downmoon-hgh;database=NorthWind;uid=sa;pwd=sa;";//
protected Label ErrorLabel;
protected DataGrid OrdersGrid;
SqlConnection NorthWindConnection = new SqlConnection(connectionStr);
SqlDataAdapter NorthWindAdp = null;
DataSet NorthWindDataSet = null;
private int _connectionCount = 1;
#endregion
#region 创建子控件
protected override void CreateChildControls()
{
// This label is displayed only if
// an exception is thrown while attempting
// to load the data.
ErrorLabel = new Label();
ErrorLabel.Visible = false;
Controls.Add(ErrorLabel);
// Create the data grid
OrdersGrid = new DataGrid();
BoundColumn newColumn;
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;
// Define grid columns
newColumn = new BoundColumn();
newColumn.DataField = "OrderID";
newColumn.HeaderText = "Order ID";
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);
newColumn = new BoundColumn();
newColumn.DataField = "CustomerID";
newColumn.HeaderText = "Customer ID";
OrdersGrid.Columns.Add(newColumn);
newColumn = new BoundColumn();
newColumn.DataField = "OrderDate";
newColumn.HeaderText = "Order Date";
newColumn.DataFormatString = "{0:d}";
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);
newColumn = new BoundColumn();
newColumn.DataField = "RequiredDate";
newColumn.HeaderText = "Required Date";
newColumn.DataFormatString = "{0:d}";
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);
newColumn = new BoundColumn();
newColumn.DataField = "ShippedDate";
newColumn.HeaderText = "Shipped Date";
newColumn.DataFormatString = "{0:d}";
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);
newColumn = new BoundColumn();
newColumn.DataField = "Freight";
newColumn.HeaderText = "Freight Cost";
newColumn.DataFormatString = "{0:c}";
newColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
OrdersGrid.Columns.Add(newColumn);
Controls.Add(OrdersGrid);
}
///<summary>
/// OrdersLoad
/// handles OrdersGrid.Load.
///</summary>
///
public void OrdersLoad(
object sender, EventArgs e)
{
BindGrid();
}
private void BindGrid()
{
// Load schema and data into a DataSet.
DataSet ordersSet = new DataSet();
// Assume XML data file is in the wpresources folder.
// If File I/O permissions are not available,
// the following will throw a security exception.
try
{
NorthWindConnection.Open();
string sqlSelectStr = "select * from Orders";
NorthWindAdp = new SqlDataAdapter(sqlSelectStr,NorthWindConnection);
NorthWindDataSet = new DataSet();
NorthWindAdp.Fill(NorthWindDataSet,"Orders");
NorthWindConnection.Close();
}
catch(Exception ex)
{
ErrorLabel.Text =
ex.Message + "<br>" +
"Steps to correct this are included in" +
" the documentation for this sample.";
ErrorLabel.Visible = true;
return;
}
finally
{
NorthWindConnection = null;
NorthWindAdp = null;
}
// No error if we made it this far.
ErrorLabel.Visible = false;
// Use a DataView to filter orders.
string rowFilter="";
//// if(CustomerId!="")
//// {
//// rowFilter = "CustomerID = '" + CustomerId + "'";
//// }
//// if(CustomerId!="")
//// {
//// rowFilter = "CustomerID = '" + "CHOPS" + "'";
//// }
DataView ordersView = new DataView(NorthWindDataSet.Tables["Orders"]);
//// ordersView.RowFilter = rowFilter;
OrdersGrid.Enabled = true;
OrdersGrid.DataSource = ordersView;
OrdersGrid.DataBind();
}
#endregion
/// <summary>
/// Render this Web Part to the output parameter specified.
/// </summary>
/// <param name="output"> The HTML writer to write out to </param>
protected override void RenderWebPart(HtmlTextWriter output)
{
//output.Write(SPEncode.HtmlEncode(Text));
EnsureChildControls();
if (ErrorLabel.Visible == true)
{
ErrorLabel.RenderControl(output);
return;
}
//If connected then display a heading and the grid.
if (_connectionCount > 0)
{
output.RenderBeginTag("div");
output.Write("<br>");
output.AddStyleAttribute(HtmlTextWriterStyle.FontWeight, "bold");
output.RenderBeginTag(HtmlTextWriterTag.Span);
//// output.Write("<nobr> Orders for Customer ID: "
//// + 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> Orders for Customer ID: "
//// + System.Web.HttpUtility.HtmlEncode(CustomerId));
//// output.Write(" </nobr>");
output.RenderEndTag(); //span
BindGrid();
OrdersGrid.RenderControl(output);
output.RenderEndTag(); //div
}
}
}
本文引用通告地址： http://blog.csdn.net/downmoon/services/trackbacks/396481.aspx
