-
Nov 20th, 2013, 01:19 AM
#1
Thread Starter
Fanatic Member
Query SQL Server from within a Class
I have an application where I am trying to create a Content Management System for a set of newsletters. I use a SQL server to store all content, and have the connection string in the Web.Config. I use forms to allow users to enter articles and register to receive the newsletters. The app regularly generates the newsletters and emails them out. Since the processing of the newsletters does not need a form, I was considering using a class.
I am having trouble getting the datasets connected to the SQL database, and using the stored connection string. I am thinking it is something like:
Code:
Imports System.Data
Imports System.Data.SqlClient
Public Class clsProcessNewsletters
Dim strSql As String = "SELECT * FROM Users"
Dim dtb As New DataTable
' pull connectionstring from Web.config << not sure how to do this correctly
Using cnn As New SqlConnection(connectionString)
cnn.Open()
Using dad As New SqlDataAdapter(strSql, cnn)
dad.Fill(dtb)
End Using
cnn.Close()
First of all is this an appropriate approach? And if so, any pointers on cleaning up the code?
-
Nov 20th, 2013, 06:14 AM
#2
Re: Query SQL Server from within a Class
Hello,
To access the contents of the web.config file, you should make use of the WebConfigurationManager, which you can find details of here:
http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx
In your case, you would want to use the ConnectionStrings collection, and then index into the one you want.
Segregating your code into classes, and limiting these classes to a specific task is definitely a good idea, and I think that most people would agree that this is a best practice, so you are definitely going down the right lines.
One question though, what triggers the sending of the emails? Although it doesn't need a form to do this work, and it can be contained within a class, you are still going to need to call the method that you create.
Gary
-
Nov 20th, 2013, 07:35 AM
#3
Thread Starter
Fanatic Member
Re: Query SQL Server from within a Class
Gary,
Thanks for you input.
My intent is to trigger the emails based on a timer of some sort (I have not investigated that yet). I am trying to automate the email process. Basically every time period (say every day / week) something would fire off to trigger the emails. I was planning for something at the application level (i.e., Global.aspx) triggering this. Any suggestions or pointers would be welcome.
John
-
Nov 20th, 2013, 08:49 AM
#4
Re: Query SQL Server from within a Class
Hey,
Since websites are stateless by their nature, there really isn't anything that you can do within the Global.ascx, or anywhere else, to trigger something to happen. You would need to look at something external to the site to actually do the triggering, or instance a scheduled task running on the machine that is hosting the website. This could be made to open a web page on your site which would initiate the email sending for instance. You could then build into your administration pages in the site a mechanism to manually trigger the same process. If you go down this route it would probably make sense to expose this functionality as a webservice method, which could then be called from a web page manually, from a scheduled task, from a mobile device, etc.
Gary
-
Nov 27th, 2013, 06:02 AM
#5
New Member
Re: Query SQL Server from within a Class
Hi as your conversation is going on you seems to be in search of some component that automatically trigger some event in you app. If you were on linux environment corn job would be the best idea to do those things. But as you are using asp.net so it has to be windows environment. You can use quartz library This is kind of job scheduler for your application. It automatically triggers some events that will allow you to send newletter on certain time interval.
Thanks,
Darshan.
-
Nov 28th, 2013, 11:01 AM
#6
Re: Query SQL Server from within a Class
Interesting, I wasn't aware of this library...
Do you know if this works within an ASP.Net scope, if so, I am very curious as to how this would work, since it isn't state based.
Gary
-
Nov 29th, 2013, 07:15 AM
#7
New Member
Re: Query SQL Server from within a Class
Well it works with .NET framework, so should work with asp.net. I have dive dipper into it but what I know about it is, it works on systems clock tick events and based on that it schedule the job. For further info this example may give some idea on how to use it with asp.net
Darshan
-
Nov 29th, 2013, 10:19 AM
#8
Re: Query SQL Server from within a Class
How about ajax timer control, would this be possible?
Ajax Timer Control
So, if your sending emails every 9:00am in the morning, you just have to add some codes in the
tick event like calling a webservice or something.
In our WPF and Winforms projects, we trigger sending of bulk mails through Timer/Dispatcher objects.
Im quite interested in using quartz though..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|