Create the Data Access library and display query results in GridView
This is the Part II of the 3 Parts on how to create a reusable SQL Data VIewer Web Part. If you missed Part I, here is the link. Part I
In the part I of the post, we looked at how to create a simple webpart and deploy it to the bin directory. The webpart simply displayed a static text. We will now look at changing the webpart code and overriding some base class method so that instead of a static text, it will display data from SQL server database in a grid view.
In the part I of the post, we started a project called dataviewer, we will now continue with the same project.
Add a new class to the project and call it DBAccess
In the using section add the namespace
using System.Data.SqlClient;
The DBAccess.cs should look like this
We are now going to add some methods to the class DBAccess
Add a private data member called strConnection of string type. This will hold the connection information to the sql server database from where the query result will be fetched.
Create a get, set method for this data member so that we can set the connection string property through the assessors. Code snippet is shown below
public class DbAccess
{
private string strConnection;
public DbAccess()
{
}
public string StrConnection
{
get { return strConnection; }
set { strConnection = value; }
}
}
We need to create a method called GetSQLResult which returns a data table and takes a SQL statement as a parameter
public DataTable GetSQlResult(string sql)
{
DataTable dt = new DataTable();
try
{
SqlConnection cnn = new SqlConnection(strConnection);
SqlDataAdapter da = new SqlDataAdapter(sql, cnn);
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
return dt;
}
}
Build the project again. If the build fails, make sure all the namespaces are included. The namespaces required for this class are
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
After adding references, build the project again, make sure there are no errors.
You have successfully created the data access library.
Next step is to, use this DAL ( data access library) on the web part and display the data in a grid view.
Open the dataviewer.cs code file, we will now look at the method to display the output on a webpart. The method that displayed the output is shown below
protected override void RenderContents(HtmlTextWriter output)
{
output.Write("Data Viewer Test");
}
This is a base class method and we chose to override it in our class. The output is a HtmlTextWriter which gets displayed in the webpart UI.
We can also choose to override another base class method which will produce the same result, the method is called createchildcontrols.
See code listing below
protected override void CreateChildControls()
{
base.CreateChildControls();
}
If we are going to create controls like gridview, labels,textboxes etc and display it in webpart, it is much better to override the createchildcontrols method because it is easier to manage the order in which the user controls will be displayed in the webpart. We will therefore choose to override the createchildcontrols instead of rendercontents method
The next step is to create a gridview control and then bind the gridview to a datatable. We will then add the gridview to the controls collection so that the gridview will be displayed in the webpart with the result of the sql statement.
See code listing below
In the above listing, I have commented the RenderContents method because I want to use the CreateChildControls method to display the gridview in the webpart.
The strConnection string has the connection information to the sql server.
UID: name of the sql server account,
PWD: password for the user, I
nitial Catalog: Database to which you want to connect and finally
Data Source: The name of the server in which the SQL Server is running.
If you have multiple SQL Servers running on this machine, you have to specify the instance name as well Example: testserverSQLEngine1
Build the project, Build>>Build Data Viewer
If there are any build errors. Make sure that you have followed the code listings and included the namespaces correctly.
Go back to the sharepoint site on which you had added the DataViewer webpart in Part I of the blog. Simply refresh the page and you will now see that the web part loads with a gridview and a result of the SQL Statement.
See Screenshot below:
You have now successfully created a webpart that takes a SQL Statement, database connection string and executes the SQL statement and displays the result in the web part.
In part I, we set the assembly version to 1.0.0.0 and this makes it easy to test our changes by simply refreshing or reloading the page that contains the webpart. If we had not set the assembly to a fixed version, everytime we build our dataviewer project, it would create a new revision number and you would be required to edit the web.config and register the control as safe.
The problem with the above webpart is that the SQL statement and the connection information to the server are stored in the assembly.
In part III of the “Developing a reusable SQL Data viewer WebPart for Sharepoint”, we will look at how to store the SQL statement and connection string in the webpart properties, so that it can be configured easily. We will also look at how to set paging properties of the gridview.
sam