For starters, YES… you can use SqlDataSource to attach to any database you want from a DotNetNuke module… I do it everyday. I made a little example module to show how easy it is.
Here is the ascx page: BRAssociates_Comment_View.ascx
<%@ Control Language="VB" AutoEventWireup="false" CodeFile="BRAssociates_Comment_View.ascx.vb" Inherits="BRAssociates.Modules.Comment.View" %>
<table id="Main" runat="server">
<tr>
<td>
<asp:Label ID="lbName" runat="server" Text="Name" CssClass="Normal" />
</td>
<td>
<asp:TextBox ID="tbName" runat="server" CssClass="NormalTextBox" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="lbEmail" runat="server" Text="Email" CssClass="Normal" />
</td>
<td>
<asp:TextBox ID="tbEmail" runat="server" CssClass="NormalTextBox" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="lbComment" runat="server" Text="Comment" CssClass="Normal" />
</td>
<td>
<asp:TextBox ID="tbComment" runat="server" CssClass="NormalTextBox" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Submit" runat="server" Text="Submit" CssClass="Normal" />
</td>
</tr>
</table>
<asp:SqlDataSource ID="sdsComment"
runat="server"
ConnectionString="Data Source=(local);Initial Catalog=Test;Integrated Security=True"
SelectCommand="SELECT * FROM dbo.Comments"
SelectCommandType="Text"
InsertCommand="INSERT INTO dbo.Comments (Name, Email, Comment) VALUES (@Name, @Email, @Comment)"
InsertCommandType="Text">
<SelectParameters>
<asp:QueryStringParameter Name="articleID" QueryStringField="id" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="tbName" PropertyName="Text" Type="String" />
<asp:ControlParameter Name="Email" ControlID="tbEmail" PropertyName="Text" Type="String" />
<asp:ControlParameter Name="Comment" ControlID="tbComment" PropertyName="Text" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
Here is the code behind page: BRAssociates_Comment_View.ascx.vb
'
' Web Site Makers® - http://www.websitemakers.com
' Copyright (c) 2001-2007
' by Barnes, Rover & Associates, Inc. ( http://www.brassociates.net )
'
Imports DotNetNuke
Imports DotNetNuke.Common
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Collections.Generic
Imports System.Reflection
Imports System.Data.SqlClient
Imports DotNetNuke.Entities.Modules
Namespace BRAssociates.Modules.Comment
Partial Class View
Inherits Entities.Modules.PortalModuleBase
Private Sub Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Submit.Click
sdsComment.Insert()
End Sub
End Class
End Namespace
Now, my DB table looks like this:
|
Column Name
|
Data Type
|
Allow Nulls
|
Notes
|
|
CommentID
|
int
|
No
|
This column is the identity column
|
|
Name
|
nvarchar(50)
|
No
|
|
|
Email
|
nvarchar(50)
|
No
|
|
|
Comment
|
nvarchar(MAX)
|
Yes
|
|
One more note, I can’t stress enough how absolutely, incredibly, terribly important it is that you use a stored procedure to handle this. Not to mention that it’s just a good idea/programming practice to separate you business logic layer from you presentation layer. If you already know how to do this forgive me but for the sake of anyone who doesn’t already know this stuff and might have stumbled across this post in a search here is how you can change the above code to support stored procedures.
First you must make a stored procedure… like so:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Nathan Rover
-- Create date: 3/31/08
-- Description: This Sproc will handle the inserting
-- of data into the dbo.Comments table.
-- =============================================
CREATE PROCEDURE [dbo].[Comments_Insert_Sproc]
@Name nvarchar(50),
@Email nvarchar(50),
@Comment nvarchar(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Comments([Name], Email, Comment)
VALUES (@Name, @Email, @Comment);
END;
Next, you must modify the line InsertCommand in the sqlDataSource "sdsComment" To Read:
InsertCommand="dbo.Comments_Insert_Sproc"
Next, you must modify the line InsertCommandType in the sqlDataSource "sdsComment" To Read:
InsertCommandType="StoredProcedure"
Next, save and recompile. Test it out it should work… and now you can sleep easy at night knowing you have added a level of protection form a SQL injection attack.
I hope this helps… let me know if you have any problems or if you have any other questions.
Nathan Rover