Customize the webpart properties for dynamic connection to SQL server and to enable paging in Grid view.
In Part 2 of the post, we looked at how to execute a SQL statement against a database and display the result in the web part as a gridview. Before going to Part 3 of developing a reusable SQL Data viewer web part for Sharepoint, please follow the steps in Part II.
In Part III of the post, we will enhance this web part and add properties to the web part so that connection to SQL Server and the SQL statement can be configured after the webpart is added to the page. We will also throw in an eventhandler for the gridview to handle paging. If you have lots of records, we want them to show in pages. We will also look at how to configure the page size through properties.
Note: The example below shows how to save configuration data in properties in plain text.If you feel such a method is not suitable for your security needs, you should find a way to use encryption on the data being saved in the properties and a method to decrypt that when accessing from code.
We will first create a webpart property called ServerName and also a method called ServerName
See Code Listing Below
public class DataViewer : WebPart
{
private string servername;
[Personalizable(), WebBrowsable(true),
WebDisplayName("ServerName"), Category("Data Properties")]
public string ServerName
{
get { return servername; }
set { servername = value; }
}
In the above code, we declared a private data member called servername and a get,set method called ServerName
We also created a webpart property called “ServerName” which will be displayed in a section called “Data Properties”
We will now create some more properties, see code listing below
public class DataViewer : WebPart
{
private string servername;
[Personalizable(), WebBrowsable(true),
WebDisplayName("ServerName"), Category("Data Properties")]
public string ServerName
{
get { return servername; }
set { servername = value; }
}
private string dbname;
[Personalizable(), WebBrowsable(true),
WebDisplayName("DatabaseName"), Category("Data Properties")]
public string DatabaseName
{
get { return dbname; }
set { dbname = value; }
}
private string username;
[Personalizable(), WebBrowsable(true),
WebDisplayName("UserName"), Category("Data Properties")]
public string UserName
{
get { return username; }
set { username = value; }
}
private string password;
[Personalizable(), WebBrowsable(true),
WebDisplayName("Password"), Category("Data Properties")]
public string Password
{
get { return password; }
set { password = value; }
}
private string innerpassword;
[Personalizable(), WebBrowsable(false),
WebDisplayName("InnerPassword"), Category("Data Properties")]
public string InnerPassword
{
get { return innerpassword; }
set { innerpassword = value; }
}
private string sqlquery;
[Personalizable(), WebBrowsable(true),
WebDisplayName("SQLQuery"), Category("Data Properties")]
public string SQLQuery
{
get { return sqlquery; }
set { sqlquery = value; }
}
private int datarows;
[Personalizable(), WebBrowsable(true),
WebDisplayName("RowsPerPage"), Category("Data Properties")]
public int DataRows
{
get { return datarows; }
set { datarows = value; }
}
All the properties are similar in declaration accept for a property called “InnerPassword”. You will notice that the WebBrowsable property for innerpassword is set to false. This property will not be visible to users and it is created so that the password can be stored internally in this property.
Build the project by clicking on Build>>Build Dataviewer
Once you have successfully build the assembly, go to the sharepoint site where the dataviewer webpart was originally added and refresh the page.
Modify the dataviewer webpart properties and you will see a section called “Data Properties” as shown below
The “Data Properties” section havs 6 text boxes.
· Server Name = [name of the SQL Server]
· DatabaseName= Name of the Database
· UserName= SQL server login account
· Password = SQL Server Password
· SQLQuery= T-SQL Statement to query the database
· RowsPerPage = number of rows to be displayed in the gridview for a page
In the next section, we will now add code to capture the values entered in these properties and then execute it in the webpart.
We will modify the CreateChildMethods and instead of using static SQL Strings or connections, we will read the connection and sql statement from the properties.
The following codes have been added
if (password != "******")
{
InnerPassword = password;
password = "******";
}
string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";
string sql = sqlquery;
objDB.StrConnection = strConnection;
DataTable dt = new DataTable();
dt = objDB.GetSQlResult(sql);
Let’s examine the code above. If the password ( this is the password property) is not equal to “******” then we will assign the password to Innerpassword and set the password to “******”
The reason for doing this is, if someone entered a SQL Server password on the webpart, we do not want to show the password to the user again, instead we will show “******” to prevent other people from accessing the password of SQL server by looking at the properties.
In the next line, we are building the SQL server connection string by reading the properties of the web part.
In the sql string we get the value from the sqlquery property of the webpart.
We are now dynamically reading the SQL connection string and the query from the webpart properties, next step is just execute the GetResult method of our DBAccess object and bind the datatable returned to the gridview.
We will now add code to enable paging in the gridview. We also need to specify the page size. We will set the allowpaging property of gridview to true and also add an eventhandler to enable paging for gridview. The pagesize will be assigned from the “datarows” properties.
gv.AllowPaging = true;
gv.PageSize = datarows;
gv.PageIndexChanging += new GridViewPageEventHandler(this.gv_PageIndexChanging);
We will need to manually create the method for the event handler as below
protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gv.PageIndex = e.NewPageIndex;
string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";
DbAccess objdb = new DbAccess();
objdb.StrConnection = strConnection;
DataTable dt = objdb.GetSQlResult(sqlquery);
gv.DataSource = dt;
gv.DataBind();
}
Note: The gridview should not be instantiated inside the CreateChildControls Method anymore because the gv_PageIndexChanging will not be able to access the object, we will therefore instantiate the gridview as a member of the class.
public class DataViewer : WebPart
{
GridView gv = new GridView();
Build the project. Go to the sharepoint page where the webpart has been added previously. Refresh the page.When the webpart reloads you may see an error message. This is expected because the database connections and SQL query has not been set.
You will now need to edit the dataview webpart properties to make the error go away and execute your SQL Statement.
Change the Title to : Employees
Expand the Dataproperties section and enter your connection information
Once the properties have been entered, click “Apply” and then click “OK”. You will now see that the web part displays the result of your sql query.
Important: The RowsPerPage must have a number greater than 0.
When the page loads, the page should similar to this, base on your data and your SQL Statement
You can now add more dataviewer webparts on the page and connect it to different SQL Servers or run different SQL Statements. Example below
You can now build a report dashboard based on this dataviewer webpart and re-using it multiple times on the same page by simply configuring the connection and query properties.