On my previous post, we have created a DataGrid with Insert functionality (Adding New Record Inside DataGrid (C#)). Today, we will create a user control for DotNetNuke exactly like that but with Data Access Layer and Business Logic Layer.
First, you will have to create your BLL and DAL. Please refer to this site for creating DotNetNuke BLL and DAL: http://adefwebserver.com/DotNetNukeHELP/DNN_Module4/DotNetNuke_module_3.htm
We will have to modify the codes from the above site to fit our needs, and we will also have to convert the codes to C#.
Create a new class library so we can compile the class separately from our DotNetNuke module. The first that we need is the Data Access Layer. Add a new class and name it DataProvider.cs. This is similar to the DataProvider.vb from the other tutorial. But here, I will post the codes in C#.
using System;
using DotNetNuke;
using System.Data;
using System.Xml;
using DotNetNuke.Framework;
namespace DotNetNuke.Modules.dnn
{
public abstract class DataProvider
{
private static DataProvider objProvider = null;
static DataProvider()
{
CreateProvider();
}
private static void CreateProvider()
{
objProvider = (DataProvider)Reflection.CreateObject("data", "DotNetNuke.Modules.dnn", "");
}
public static DataProvider Instance()
{
return objProvider;
}
public abstract void dnn_Insert(int ModuleId, string LastName, string FirstName, DateTime HireDate);
public abstract void dnn_Update(int ModuleId, int EmployeeID, string LastName, string FirstName, DateTime HireDate);
public abstract void dnn_Delete(int ModuleId, int EmployeeID);
public abstract XmlDocument dnn_GetAll(int ModuleId);
}
}
Next, add a new class and name it SqlDataprovider.cs.
using System;
using System.Data;
using System.Xml;
using System.Xml.XPath;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Data;
namespace DotNetNuke.Modules.dnn
{
public class SqlDataProvider : DataProvider
{
private const string ProviderType = "data";
private const string ModuleQualifier = "";
private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
private string _connectionString;
private string _providerPath;
private string _objectQualifier;
private string _databaseOwner;
SqlConnection _conn = null;
public SqlDataProvider(): base()
{
Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];
_connectionString = Config.GetConnectionString("conn");
if (_connectionString == "")
{
_connectionString = objProvider.Attributes["connectionString"];
}
_objectQualifier = objProvider.Attributes["objectQualifier"];
if (((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false)))
{
_objectQualifier = (_objectQualifier + "_");
}
_databaseOwner = objProvider.Attributes["databaseOwner"];
if (((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false)))
{
_databaseOwner = (_databaseOwner + ".");
}
}
public string ConnectionString
{
get { return _connectionString; }
}
public SqlConnection conn
{
get {
_conn = new SqlConnection(_connectionString);
return _conn;
}
}
public string ProviderPath
{
get { return _providerPath; }
}
public string ObjectQualifier
{
get { return _objectQualifier; }
}
public string DatabaseOwner
{
get { return _databaseOwner; }
}
private string GetFullyQualifiedName(string name)
{
return (DatabaseOwner + (ObjectQualifier + (ModuleQualifier + name)));
}
private object GetNull(object Field)
{
return Null.GetNull(Field, DBNull.Value);
}
public override void dnn_Insert(int ModuleId, string LastName, string FirstName, DateTime HireDate)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("dnn_Insert"), ModuleId, LastName, FirstName, HireDate);
}
public override void dnn_Delete(int ModuleId, int EmployeeID)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("dnn_Delete"), ModuleId, EmployeeID);
}
public override void dnn_Update(int ModuleId, int EmployeeID, string LastName, string FirstName, DateTime HireDate)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("dnn_Update"),ModuleId, EmployeeID, LastName, FirstName, HireDate);
}
public override XmlDocument dnn_GetAll(int ModuleId)
{
return xmlread(ModuleId);
}
public XmlDocument xmlread(int ModuleId)
{
string sRootName = "dnn";
XmlReader reader = SqlHelper.ExecuteXmlReader(conn, CommandType.StoredProcedure, "dbo.dnn_GetAll", new SqlParameter("@ModuleId", ModuleId));
XPathDocument xp = new XPathDocument(reader);
XPathNavigator xn = xp.CreateNavigator();
XmlDocument xd = new XmlDocument();
XmlNode root = xd.CreateElement(sRootName);
root.InnerXml = xn.OuterXml;
xd.AppendChild(root);
return xd;
}
}
}
Add another class and name it dnnInfo.cs (you can change the file name anyway you want, just make sure that you references and inherits them right on your codes).
using System;
using System.Configuration;
using System.Data;
namespace DotNetNuke.Modules.dnn
{
public class dnnInfo
{
private int _ModuleId;
private int _EmployeeID;
private string _LastName;
private string _FirstName;
private DateTime _HireDate;
public dnnInfo(): base()
{
}
public int ModuleId
{
get { return _ModuleId; }
set { _ModuleId = value; }
}
public int EmployeeID
{
get { return _EmployeeID; }
set { _EmployeeID = value; }
}
public string LastName
{
get { return _LastName; }
set { _LastName = value; }
}
public string FirstName
{
get { return _FirstName; }
set { _FirstName = value; }
}
public DateTime HireDate
{
get { return _HireDate; }
set { _HireDate = value; }
}
}
}
Add nother class. Rename this one with dnnController.cs.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Xml;
using System.Web;
using DotNetNuke;
using DotNetNuke.Common;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Entities.Modules;
using DotNetNuke.Services.Search;
namespace DotNetNuke.Modules.dnn
{
public class dnnController
{
[DataObjectMethod(DataObjectMethodType.Insert)]
public static void simple_Insert(dnnInfo objTest)
{
DataProvider.Instance().dnn_Insert(objTest.ModuleId, objTest.LastName, objTest.FirstName, objTest.HireDate);
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static void simple_Delete(dnnInfo objTest)
{
DataProvider.Instance().dnn_Delete(objTest.ModuleId, objTest.EmployeeID);
}
[DataObjectMethod(DataObjectMethodType.Update)]
public static void simple_Update(dnnInfo objTest)
{
DataProvider.Instance().dnn_Update(objTest.ModuleId, objTest.EmployeeID, objTest.LastName, objTest.FirstName, objTest.HireDate);
}
[DataObjectMethod(DataObjectMethodType.Select)]
public static XmlDocument simple_GetAll(int ModuleId)
{
return CBO.Serialize(DataProvider.Instance().dnn_GetAll(ModuleId));
}
}
}
Now compile your class library and copy the generated dll file to the bin directory of DotNetNuke.
Open the DotNetNuke project on visual studio. Inside the DesktopModules folder, create a new folder then create a new user control. Check the namespace in the code behind, it should match the namespace you used in your class: DotNetNuke.Modules.dnn.
Open the source code and check the directive on top of the page. The Inherits attribute should match the namespace.controlname: DotNetNuke.Modules.dnn.simple_editdelete. (The name of the user control is simple_editdelete.ascx.
Now, add a DataGrid and a Button. Remember, we will create the same project as the previous one except that now, we will use class for data access. If you have the previous project (Adding New Record Inside DataGrid (C#)), you can just copy and paste it on a user control and add the necessary codes like inheriting DotNetNuke.Entities.Modules.PortalModuleBase.
We will bind the DataGrid to a datasource, but first, we have to create a connection to the database in the web.config file.
DotNetNuke already has a default connection string. You don’t need to add a new connection if you want to use the default database of DotNetNuke.
Look at this line of code in the SqlDataprovider.cs:
_connectionString = Config.GetConnectionString("conn");
The GetConnectionString method accepts a string connection name to specify which connection to use.
Let’s go back to the binding of DataGrid:
XmlDocument doc = dnnController.simple_GetAll(ModuleId);
ds.ReadXml(new XmlNodeReader(doc));
Session["ds"] = ds;
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
btnAdd.Enabled = true;
Notice that dnnController.simple_GetAll(ModuleId) returns an XmlDocument. Why not use a DataReader instead? Let’s look at our dnnController.cs. simple_GetAll returns CBO.Serialize(DataProvider.Instance().dnn_GetAll(ModuleId)). CBO stands for Custom Business Object Helper and is included in DotNetNuke. It only returns either a DataReader or an XML document. To know more about CBO and DotNetNuke DataAccess, download the DotNetNuke documentation from the DotNetNuke site (www.dotnetnuke.com). Since we will insert a blank row in the DataTable for our insert function, we need to use the Serialize function and return an XML document.
Let us study the GetAll functions we created in our class.
dnnController.cs:
[DataObjectMethod(DataObjectMethodType.Select)]
public static XmlDocument simple_GetAll(int ModuleId)
{
return CBO.Serialize(DataProvider.Instance().dnn_GetAll(ModuleId));
}
The above code is the one we call from the user control.
SqlDataprovider.cs:
public override XmlDocument dnn_GetAll(int ModuleId)
{
return xmlread(ModuleId);
}
public XmlDocument xmlread(int ModuleId)
{
string sRootName = "dnn";
XmlReader reader = SqlHelper.ExecuteXmlReader(conn, CommandType.StoredProcedure, "dbo.dnn_GetAll", new SqlParameter("@ModuleId", ModuleId));
XPathDocument xp = new XPathDocument(reader);
XPathNavigator xn = xp.CreateNavigator();
XmlDocument xd = new XmlDocument();
XmlNode root = xd.CreateElement(sRootName);
root.InnerXml = xn.OuterXml;
xd.AppendChild(root);
return xd;
}
This one is where we access the database. We used the SqlHelper.ExecuteXmlReader to create an XmlDocument. But the parameters for ExecuteXml Reader accepts only an SqlConnection and not string connection. So I created a property for SqlConnection. (Dumb, I know, since we already have a connection, but I can’t find a way to get the active SqlConnection to pass as parameter. It is the only way I could think of).
public SqlConnection conn
{
get {
_conn = new SqlConnection(_connectionString);
return _conn;
}
}
From the XmlReader, we will create an XmlDocument which will be returned to the dnnController.
Before we forget, we also have to create our select stored procedure named dnn_GetAll and add FOR XML AUTO at the end of the statement so the query will return an xml.
Now, back to the DataGrid binding in our code behind, since the dnnController returned an XmlDocument, we need to read the XmlDocument to the DataSet, hence, the ds.ReadXml method. Then add the DataSet to Session variable to preserve it. Set the DataSet as the DataSource of the DataGrid and bind it.
Next is creating the Insert function in our DataGrid. We have already populated the DataSet and assigned it to a Session variable so when the page posts back to the server, we won’t need to query the database again, we will just have to assign the Session variable back to the DataSet. (If you know how to preserve the DataSet values in every postback, that would be really really great).
In the onClick event of the Add button, place these codes:
btnAdd.Enabled = false;
ds = (DataSet)Session["ds"];
ds.Tables[0].Rows.Add();
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
int index = DataGrid1.Items.Count - 1;
DataGrid1.EditItemIndex = index;
DataGrid1.DataBind();
This part is the same as the one in my previous post (Adding New Record Inside DataGrid (C#)).
On the ItemDataBound event of the DataGrid:
if (e.Item.ItemType == ListItemType.EditItem)
{
if (btnAdd.Enabled == false)
{
Button btnSave = (Button)e.Item.FindControl("btnUpdate");
btnSave.Text = "Save";
}
}
This is to change the Text Property of the Update button to “Save”.
On the Update_Grid event:
string LastName = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string FirstName = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
DateTime HireDate = Convert.ToDateTime(((TextBox)e.Item.Cells[3].Controls[0]).Text);
Button btn = (Button)e.Item.FindControl("btnUpdate");
dnnInfo info = new dnnInfo();
info.ModuleId = ModuleId;
info.FirstName = FirstName;
info.LastName = LastName;
info.HireDate = HireDate;
if (btn.Text == "Update")
{
int empId = Convert.ToInt32(DataGrid1.DataKeys[e.Item.ItemIndex]);
info.EmployeeID = empId;
dnnController.simple_Update(info);
}
else
{
dnnController.simple_Insert(info);
}
DataGrid1.EditItemIndex = -1;
BindData(DataGrid1);
You have to create an instance of dnnInfo object since the simple_Update and simple_Insert accepts only an object.
We used the if statement here to test whether we are inserting a new record or updating an existing record using the Update button’s text property.
And on delete_Grid event:
int empId = Convert.ToInt32(DataGrid1.DataKeys[e.Item.ItemIndex]);
dnnInfo info = new dnnInfo();
info.ModuleId = ModuleId;
info.EmployeeID = empId;
dnnController.simple_Delete(info);
DataGrid1.EditItemIndex = -1;
BindData(DataGrid1);
Before you test your project, you have to create your stored procedures for updating, inserting and deleting records.
And also, you have to include always the ModuleId as parameter (even on select statement) even if you have no use of it. I’m not really sure why but DotNetNuke generates an error and could not load the module when there is no ModuleId in the parameters passed on the class.
2/20/2008 11:36:41 AM
No comments:
Post a Comment