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:
- public static void OpenSqlConnection(ref SqlConnection sqlConn, string webConfigConnectStringKey)
- NameValueCollection nvc =
- AppSettings as NameValueCollection;
- string connString = nvc[webConfigConnectStringKey];
- sqlConn = new SqlConnection(connString);
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:
- 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)
- // current site and web
- siteThis = SPControl.GetContextSite(Context);
- webThis = siteThis.OpenWeb();
- // reference the above connection method
- OpenSqlConnection(ref sqlConn);