Developing A Form WebPart – Part 3 – Setup the Connection to the SQL Server with ADO.NET 2.0

In order to manage the connection to the database and perform relevant database operations, use ADO.NET functionality out of the System.Data.SqlClient namespace. For the Forms WebPart, ADO.NET provides the crux of the operations for database logic.

In order to open a connection to the SQL database, the Forms WebPart can harvest the connection information out of a configuration file by using WebConfigurationManager out of the System.Web.Configuration namespace. By exposing the AppSettings public property, you can return key-value pairs from the configuration file, allowing the exposure of the connection string information. By returning the connString argument from the Connection String data in the configuration file, you can then use the argument to pass into the SqlConnection object, which can then open a connection to the database by using the Open() method.

Here is the code for the SQL connection method:

[csharp]

public static void OpenSqlConnection(ref SqlConnection sqlConn, string webConfigConnectStringKey)
{
      NameValueCollection nvc =
      AppSettings as NameValueCollection;
      string connString = nvc[webConfigConnectStringKey];
      sqlConn = new SqlConnection(connString);
      sqlConn.Open();
}

[/csharp]

Once the OpenSqlConnection method has been declared, you can then call it in the OnInit method in order to intalize the connection to the database when the WebPart is firstly instantiated. In the OnInit method, there must be a call to base.OnInit(e) in order to raise the method’s associated events, which will directly impact inherent WebPart functionality such as WebPart menus provided that allow interaction with the WebPart such as closing, minimizing, modifying the WebPart properties, and managing WebPart connections . Also, in the OnInit is a good time to establish the site context and establish a reference to the SPWeb object using the OpenWeb() method as a best practice.

Here is the code for the OnInit method In the Form WebPart:

[csharp]

protected override void OnInit(EventArgs e)
{
       // must include for WebPart integrity
       // (menu registration items such as closing, minimizing, modifying
       // the WebPart properties, and managing WebPart connections)
       base.OnInit(e);
       // current site and web
       siteThis = SPControl.GetContextSite(Context);
       webThis = siteThis.OpenWeb();
       // reference the above connection method      
       OpenSqlConnection(ref sqlConn);

[/csharp]

Share

Developing A SharePoint Form WebPart – Part 1 – Set Up the WebPart Class

Before accomplishing any custom development, the necessary foundational code must be added to the WebPart in order to set it up for inclusion in the SharePoint environment. You must establish the following references (shown in the code listing) in the main WebPartclass file. Doing so allows WebPart class derivation out of the WebPart base class, procures SQL connectivity using ADO.NET 2.0, allows for definition of WebPart attributes, and tolerates the binding of the appropriate user interface controls for final output by an overridden RenderWebPart method.

[csharp]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.Xml.Serialization;
using System.Xml;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebControls;
using System.Collections;

[/csharp]

Supply the appropriate WebPart base class inheritance derivation for the parent class either through System.Web.UI.WebControls.WebParts or Microsoft.SharePoint.WebPartPages. For the Form WebPart, it is advisable to use the new ASP.NET 2.0 base class derivation, therefore set the inheritance to System.Web.UI.WebControls.WebParts.WebPart. Once the class has been defined, call the relevant XML decoration attributes in the XmlRoot element provided by the System.Xml.Serialization namespace by defining a default XML namespace in order to control the serialization of the WebPart properties.

Optionally, define the ToolBoxData attribute with the default Typename so that the control class is concretely defined when using it within an IDE. After modifications, your top level class definition should look like the following:

[csharp]

    [DefaultProperty(“Text”)]
    [ToolboxData(“<{0}: FormsWebPart runat=server></{0}: FormsWebPart >”)]
    [XmlRoot(Namespace = ” ProfessionalSharePoint2007.FormsWebPart “)]
    public class FormsWebPart : System.Web.UI.WebControls.WebParts.WebPart
    {
    }

[csharp]

Since you have now appropriately modified the class in order to assimilate into the WebPart framework and made the necessary references to draw on ADO.NET 2.0 functionality in order to submit user entered items to the backend SQL data store, the relevant child controls need to be instantiated in order to capture user input.

Share

PersistenceManager

The PersistenceManager class is a static helper class that manages the commonly used objects like the connection string, the ADO.NET connection, and the current transaction.

[csharp]
//*****************************************************************************
// This file is part of the data access layer example to the ASP.NET 2.0 provider database
// This file was written by Adam Buenz [WSS MVP] of ARB Security Solutions, LLC
// http://www.sharepointsecurity.com
//
// This file and its parts is free for re-distribution, for use in both free
// and commercial applications, however this header must remain intact for legal
// use. The data access layer example is distributed in the hope that it will
// be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
//*****************************************************************************
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Aspnet.Provider.Datalayer.Commands;
using Aspnet.Provider.Datalayer.DataTransferObjects;

namespace Aspnet.Provider.Datalayer
{
///
/// The AspnetApplications class.
///
public partial class AspnetApplications : IPersistable
{
#region Members

private bool isNew;
private bool _isNew;
private string applicationname;
private string loweredapplicationname;
private Guid applicationid;
private string description;

#endregion

#region Properties

///
/// The Applicationname.
///
[StringInfo(256, true)]
public virtual string Applicationname
{
get { return this.applicationname; }
set { this.applicationname = value; }
}

///
/// The Loweredapplicationname.
///
[StringInfo(256, true)]
public virtual string Loweredapplicationname
{
get { return this.loweredapplicationname; }
set { this.loweredapplicationname = value; }
}

///
/// The Applicationid.
///
public virtual Guid Applicationid
{
get { return this.applicationid; }
set { this.applicationid = value; }
}

///
/// The Description.
///
[StringInfo(256, false)]
public virtual string Description
{
get { return this.description; }
set { this.description = value; }
}

#endregion

#region ColumnNames

///
/// The corresponding schema name.
///
internal const string SchemaName = “dbo”;

///
/// The corresponding table name.
///
internal const string TableName = “aspnet_Applications”;

///
/// The column names.
///
internal class ColumnNames
{
///
/// The column name of the Applicationname property.
///
public const string Applicationname=”ApplicationName”;
///
/// The column name of the Loweredapplicationname property.
///
public const string Loweredapplicationname=”LoweredApplicationName”;
///
/// The column name of the Applicationid property.
///
public const string Applicationid=”ApplicationId”;
///
/// The column name of the Description property.
///
public const string Description=”Description”;
}

#endregion

///
/// The default constructor.
///
public AspnetApplications()
{
this.isNew = true;
PersistenceManager.InvokeInstanceMethod(this, “CreationComplete”, null);
}

///
/// The constructor of the required fields.
///
///
The Applicationname. ///
The Loweredapplicationname. ///
The Applicationid. public AspnetApplications(string applicationname, string loweredapplicationname, Guid applicationid)
{
this.applicationname = applicationname;
this.loweredapplicationname = loweredapplicationname;
this.applicationid = applicationid;

this.isNew = true;
PersistenceManager.InvokeInstanceMethod(this, “CreationComplete”, null);
}

///
/// The constructor from IDataReader.
///
///
An initalized IDataReader. internal AspnetApplications(IDataReader reader)
{
if ((reader[“ApplicationName”] != null) && (reader[“ApplicationName”] != DBNull.Value))
this.applicationname = (string)reader[“ApplicationName”];
if ((reader[“LoweredApplicationName”] != null) && (reader[“LoweredApplicationName”] != DBNull.Value))
this.loweredapplicationname = (string)reader[“LoweredApplicationName”];
if ((reader[“ApplicationId”] != null) && (reader[“ApplicationId”] != DBNull.Value))
this.applicationid = (Guid)reader[“ApplicationId”];
if ((reader[“Description”] != null) && (reader[“Description”] != DBNull.Value))
this.description = (string)reader[“Description”];

this.isNew = false;
}

///
/// Creates an IDbCommand to insert an object into the database.
///
/// An initialized IDbCommand object.
internal virtual IDbCommand CreateInsertCommand()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “insert into [dbo].[aspnet_Applications] ([ApplicationName], [LoweredApplicationName], [ApplicationId], [Description]) values (@applicationname, @loweredapplicationname, @applicationid, @description)”;
cmd.Parameters.AddWithValue(“@applicationname”, this.applicationname);
cmd.Parameters.AddWithValue(“@loweredapplicationname”, this.loweredapplicationname);
cmd.Parameters.AddWithValue(“@applicationid”, this.applicationid);
if (String.IsNullOrEmpty(this.description))
cmd.Parameters.AddWithValue(“@description”, DBNull.Value);
else
cmd.Parameters.AddWithValue(“@description”, this.description);

cmd.Connection = PersistenceManager.Connection;
return cmd;
}

///
/// Creates an IDbCommand to update an object in the database.
///
/// An initialized IDbCommand object.
internal virtual IDbCommand CreateUpdateCommand()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “update [dbo].[aspnet_Applications] set [ApplicationName]=@applicationname, [LoweredApplicationName]=@loweredapplicationname, [Description]=@description where ([ApplicationId]=@applicationid)”;
cmd.Parameters.AddWithValue(“@applicationname”, this.applicationname);
cmd.Parameters.AddWithValue(“@loweredapplicationname”, this.loweredapplicationname);
if (String.IsNullOrEmpty(this.description))
cmd.Parameters.AddWithValue(“@description”, DBNull.Value);
else
cmd.Parameters.AddWithValue(“@description”, this.description);
cmd.Parameters.AddWithValue(“@applicationid”, this.applicationid);

cmd.Connection = PersistenceManager.Connection;
return cmd;
}

///
/// Creates an IDbCommand to delete an object in the database.
///
/// An initialized IDbCommand object.
internal virtual IDbCommand CreateDeleteCommand()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “delete from [dbo].[aspnet_Applications] where ([ApplicationId]=@applicationid)”;
cmd.Parameters.AddWithValue(“@applicationid”, this.applicationid);

cmd.Connection = PersistenceManager.Connection;
return cmd;
}

///
/// Persists the object.
///
public virtual void Persist()
{
PersistenceManager.InvokeInstanceMethod(this, “PrePersist”, null);

IDbCommand cmd;

if (this.isNew)
cmd = this.CreateInsertCommand();
else
cmd = this.CreateUpdateCommand();

cmd.Transaction = PersistenceManager.Transaction;
PersistenceManager.RegisterInTransaction(this);

bool connWasClosed =
PersistenceManager.Connection.State.Equals(ConnectionState.Closed) ||
PersistenceManager.Connection.State.Equals(ConnectionState.Broken);

try
{

if (connWasClosed)
PersistenceManager.Connection.Open();

cmd.ExecuteNonQuery();

this.isNew = false;

if (connWasClosed)
PersistenceManager.Connection.Close();
}
catch (Exception ex)
{
if (connWasClosed)
PersistenceManager.Connection.Close();
throw ex;
}
}

///
/// Deletes the object.
///
public virtual void Delete()
{
PersistenceManager.InvokeInstanceMethod(this, “PreDelete”, null);

IDbCommand cmd = this.CreateDeleteCommand();
cmd.Transaction = PersistenceManager.Transaction;

bool connWasClosed =
PersistenceManager.Connection.State.Equals(ConnectionState.Closed) ||
PersistenceManager.Connection.State.Equals(ConnectionState.Broken);

try
{
if (connWasClosed)
PersistenceManager.Connection.Open();

cmd.ExecuteNonQuery();

if (connWasClosed)
PersistenceManager.Connection.Close();
}
catch (Exception ex)
{
if (connWasClosed)
PersistenceManager.Connection.Close();
throw ex;
}
}

///
/// Retrieves a AspnetApplications object by its primary key (Throws System.DataException).
///
///
The Applicationid. /// The AspnetApplications object.
public static AspnetApplications Get(Guid applicationid)
{
AspnetApplications aspnetapplications;
if (AspnetApplications.TryGet(applicationid, out aspnetapplications))
return aspnetapplications;
else
throw new DataException(“‘AspnetApplications’ object not found.”);
}

///
/// Tries to retrieve a AspnetApplications object by its primary key.
///
///
The Applicationid. ///
The found AspnetApplications or null if the primary key value does not exist. /// True if the AspnetApplications exists, else false.
public static bool TryGet(Guid applicationid, out AspnetApplications aspnetapplications)
{
SqlCommand cmd = new SqlCommand(“select * from [dbo].[aspnet_Applications] where ([ApplicationId]=@applicationid)”);
cmd.Parameters.AddWithValue(“@applicationid”, applicationid);

IList list = AspnetApplications.Query(cmd);

if (list.Count == 0)
{
aspnetapplications = null;
return false;
}
else
{
aspnetapplications = list[0];
return true;
}
}

///
/// For internal use only.
///
public virtual void SaveState()
{
this._isNew = this.isNew;
}

///
/// For internal use only.
///
public virtual void RestoreState()
{
this.isNew = this._isNew;
}

///
/// Refreshes the internal state of the object.
///
/// This method should be called after xml deserialization to refresh internal flags.
public virtual void RefreshState()
{
AspnetApplications aspnetapplications;

if (AspnetApplications.TryGet(this.applicationid, out aspnetapplications))
{
this.isNew = false;
}
else
{
this.isNew = true;
}
}

///
/// Returns a Data Transfer Object of this AspnetApplications.
///
/// A Data Transfer Object of this AspnetApplications.
public virtual AspnetApplicationsDTO GetDTO()
{
AspnetApplicationsDTO dto = new AspnetApplicationsDTO();

dto.Applicationname = this.Applicationname;
dto.Loweredapplicationname = this.Loweredapplicationname;
dto.Applicationid = this.Applicationid;
dto.Description = this.Description;

return dto;
}

///
/// Applies a Data Transfer Object data to this AspnetApplications.
///
///
The Data Transfer Object. public virtual void SetDTO(AspnetApplicationsDTO dto)
{
this.Applicationname = dto.Applicationname;
this.Loweredapplicationname = dto.Loweredapplicationname;
this.Applicationid = dto.Applicationid;
this.Description = dto.Description;
}

///
/// Perfoms a query on AspnetApplications objects.
///
///
An IDbCommand containing the select statement. /// A result list of AspnetApplications objects.
internal static IList Query(IDbCommand command)
{
command.Connection = PersistenceManager.Connection;
command.Transaction = PersistenceManager.Transaction;

bool connWasClosed =
PersistenceManager.Connection.State.Equals(ConnectionState.Closed) ||
PersistenceManager.Connection.State.Equals(ConnectionState.Broken);

try
{
if (connWasClosed)
PersistenceManager.Connection.Open();

List list = new List();
IDataReader reader = command.ExecuteReader();

using (reader)
{
while (reader.Read())
list.Add(new AspnetApplications(reader));
}

if (connWasClosed)
PersistenceManager.Connection.Close();

return list;
}
catch (Exception ex)
{
if (connWasClosed)
PersistenceManager.Connection.Close();
throw ex;
}
}

///
/// Finds all AspnetApplications objects with a certain Applicationname value.
///
///
The Applicationname value (‘*’ can be used as a wildcard). /// All AspnetApplications objects with a certain Applicationname value.
public static IList FindByApplicationname(string applicationname)
{
return AspnetApplications.Query(AspnetApplicationsCommands.FindByApplicationname(applicationname));
}

///
/// Finds all AspnetApplications objects with a certain Loweredapplicationname value.
///
///
The Loweredapplicationname value (‘*’ can be used as a wildcard). /// All AspnetApplications objects with a certain Loweredapplicationname value.
public static IList FindByLoweredapplicationname(string loweredapplicationname)
{
return AspnetApplications.Query(AspnetApplicationsCommands.FindByLoweredapplicationname(loweredapplicationname));
}

///
/// Finds all AspnetApplications objects with a certain Description value.
///
///
The Description value (‘*’ can be used as a wildcard). /// All AspnetApplications objects with a certain Description value.
public static IList FindByDescription(string description)
{
return AspnetApplications.Query(AspnetApplicationsCommands.FindByDescription(description));
}

}
}

[/csharp]

Share