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的文章《[url=http://xgp1226.blogdriver.com/xgp1226/598244.html]Web部件访问SQL数据库--寻找收获的快乐[/url]》一文，深受启发，又参考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
 ...{

  webPart变量#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
   }

 }
}

 

 

 

转载于:https://www.cnblogs.com/downmoon/archive/2007/12/27/downmoon-webpart.html