Results 1 to 8 of 8

Thread: Query SQL Server from within a Class

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2007
    Posts
    523

    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?

  2. #2
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    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

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2007
    Posts
    523

    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

  4. #4
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    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

  5. #5
    New Member
    Join Date
    Nov 2013
    Location
    India
    Posts
    9

    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.

  6. #6
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    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

  7. #7
    New Member
    Join Date
    Nov 2013
    Location
    India
    Posts
    9

    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

  8. #8
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    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..
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width