SQL Data Display and Edit in Sharepoint

Question

I want to load data from SQL Table and edit the same and save in SQL DB. The tables has many records and all the records should be displayed and should be edited a single record at a time and should be saved.

What should I use?

SPGridView- I have tried here;

DataViewer Webpart - Displays the data but edit functionality is not done yet.

Any examples of the above two..

What is the best option for me.

Answer 1

If you are using SP2010 you can connect to a SQL Server table using BCS External Content types and read/write them pretty easily. What version of SharePoint are you using?

Answer 2

If you are not able to use BCS (because you are using MOSS 2007) then your only option is to write custom code to access the tables in SQL, or find a third-party control. From memory, the OOB data access tooling in MOSS is pretty much read-only.

Answer 3

I have successfully implemented this task using custom controls approach with the gridview.

You need to have a database by Name SkillsDB, Table-->Skills, Columns in it -->SkillId-Int, Skill-Varchar(50)

This is directory structure I have implemented.

This is the code in WebUserControl.ascx source

<%@ Control Language="C#" AutoEventWireup="true"  Inherits="SP2007SQLConnetorWebPart.WebUserControl, SP2007SQLConnetorWebPart,Version=1.0.0.0, Culture=neutral, PublicKeyToken=2c7b60f7a0f1a9bc" %>
<asp:GridView ID="GridView1" runat="server" CellPadding="3" GridLines="Horizontal"
   Font-Names="Verdana" Font-Size="10" DataKeyNames="SkillID" 
   AutoGenerateColumns="false" 
   onrowcancelingedit="GridView1_RowCancelingEdit" 
   onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating">

   <HeaderStyle BackColor="#336699" ForeColor="White" HorizontalAlign="Left" Height="25" />            

   <Columns>
      <asp:CommandField ButtonType="Button" ShowEditButton="true" ShowCancelButton="true" />

      <asp:BoundField DataField="SkillID" HeaderText="Skill ID" ReadOnly="true" />

      <asp:TemplateField HeaderText="Skill Name">
          <ItemTemplate>
            <%# Eval("Skill")%>
          </ItemTemplate>
          <EditItemTemplate>
            <asp:TextBox runat="server" ID="txtProductName" Text='<%# Eval("Skill")%>' />
          </EditItemTemplate>
      </asp:TemplateField>
      </Columns>            
</asp:GridView>

This is the Code Behind in WebUserControl.ascx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace SP2007SQLConnetorWebPart
{
    public partial class WebUserControl : System.Web.UI.UserControl
    {
        protected GridView GridView1;
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!Page.IsPostBack)
            {
                BindData();
            }
        }


        private void BindData()
        {
            string constr = @"Data Source=.;Initial Catalog=SkillsDB;Integrated Security=True";
            string query = "SELECT SkillID, Skill FROM Skills";
            SqlDataAdapter da = new SqlDataAdapter(query, constr);
            DataTable table = new DataTable();
            da.Fill(table);
            GridView1.DataSource = table;
            GridView1.DataBind();
        }
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            BindData();
        }
        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            e.Cancel = true;
            GridView1.EditIndex = -1;
            BindData();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            GridViewRow row = GridView1.Rows[e.RowIndex];
            TextBox txtProductName = (TextBox)row.FindControl("txtProductName");
            int productID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
            string productName = txtProductName.Text;
            UpdateProduct(productID, productName);
        }

        private void UpdateProduct(int skillID, string skillName)
        {
            try
            {
                string constr = @"Data Source=.;Initial Catalog=SkillsDB;Integrated Security=True";
                string query = "UPDATE Skills SET Skill = @Skill where SkillID=@SkillID";
                SqlConnection con = new SqlConnection(constr);
                SqlCommand com = new SqlCommand(query, con);
                com.Parameters.Add("@Skill", SqlDbType.NVarChar).Value = skillName;
                com.Parameters.Add("@SkillID", SqlDbType.Int).Value = skillID;
                con.Open();
                com.ExecuteNonQuery();
                con.Close();
                GridView1.EditIndex = -1;
                BindData();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Once the UserControl is done then Reference the User control in this format.

This is in SqlConnectorWebPart.cs

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Data;

namespace SP2007SQLConnetorWebPart
{
    [Guid("536db25d-f70e-480a-9755-44393ced89a8")]
    public class SqlConnectorWebPart : Microsoft.SharePoint.WebPartPages.WebPart
    {
        private bool _error = false;


        public SqlConnectorWebPart()
        {
            this.ExportMode = WebPartExportMode.All;
        }

        /// <summary>
        /// Create all your controls here for rendering.
        /// Try to avoid using the RenderWebPart() method.
        /// </summary>
        protected override void CreateChildControls()
        {
          Control cntrl= this.Page.LoadControl(@"\\_ControlTemplates\WebUserControl.ascx");
            this.Controls.Add(cntrl);
            }

        /// <summary>
        /// Ensures that the CreateChildControls() is called before events.
        /// Use CreateChildControls() to create your controls.
        /// </summary>
        /// <param name="e"></param>
        protected override void OnLoad(EventArgs e)
        {
            if (!_error)
            {
                try
                {
                    base.OnLoad(e);
                    this.EnsureChildControls();

                    // Your code here...
                }
                catch (Exception ex)
                {
                    HandleException(ex);
                }
            }
        }

        /// <summary>
        /// Clear all child controls and add an error message for display.
        /// </summary>
        /// <param name="ex"></param>
        private void HandleException(Exception ex)
        {
            this._error = true;
            this.Controls.Clear();
            this.Controls.Add(new LiteralControl(ex.Message));
        }



    }
}

Answer 4

If you don't want to write code, you can use common add-ons for this with full CRUD (create / update / delete) support:

You can download here: http://www.layer2.de/en/products/Pages/SharePoint-Business-Data-List-Connector.aspx

Hope that helps.