ASP.net declaring a SQL connection across multiple pages/ascx files

Discussion in 'Programming & Software Development' started by ACodingFettish, Jul 9, 2007.

  1. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Yeah, thats how i do it, ill show him what i mean when this video has rendered. Hopefully it isn't too big so i don't have to recompress it in virtual dub or something.

    The project i used to demo tho is pretty messy tho, havent tidied it but it gets the point accross none-the-less.
     
  2. Jaffa_Cree

    Jaffa_Cree Member

    Joined:
    Feb 26, 2002
    Messages:
    1,976
    Cool Elyzion. I am sure Mordy knows what we mean, just some confusion was apparent.

    I am sure a lot of people here will benefit from your video in how to create a DAL that promotes loose coupling and not be fussed by the implementation of it.
     
  3. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    If i had a mac so i had Final Cut i would make some proper tutorials.

    What the video does is show the web.config with connection string. A dataaccess class. Website with a Referenced Business layer. And using breakpoints on the Business layer when debugging while showing varibles passed in.
     
  4. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Ok i had to remove the web.config file piece. Because it contains Impersonate Windows User user/pass, and database user/pass, and mailserver user/pass.

    http://philliphaydon.com/jetsetpets2.rar

    Hope it works. I'll try blur the stuff out but its not proving easy :(


    For the record. My PC was laggy when i did this. (hadn't been rebooted in about 25 days) and i had a game running in the background (LastChaos).

    Theres a huge jump when i click on the web.config file and the dataaccess class because i cut about 15 seconds of video.

    Basically the webconfig file showed a line like this:

    <appSettings>
    <add key="PooledConnectionstring" value="server=000.000.000.000;database=JetSetPets;User id=***;Password=***;Connect Timeout=2;Min Pool Size=1;Max Pool Size=60;"/>
    <add key="NonPooledConnectionstring" value="server=000.000.000.000;database=JetSetPets;User id=***;Password=***;Pooling=false;Connect Timeout=45;"/>
    </appSettings>

    Oh and the video is 1280x1024.
     
    Last edited: Jul 10, 2007
  5. OP
    OP
    ACodingFettish

    ACodingFettish Member

    Joined:
    Feb 7, 2004
    Messages:
    6,652
    Location:
    Brisbane
    thanks alot for that, man your code is neat, you'd probably shit yourself and vomit at the same time if you saw mine. Hopefulyl once I get the hang of things a bit better I'll have it all cleaned up.

    How did you learn ASP.net, did you do a course? or self taught?
     
  6. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Clean? Thats messy for me dude!

    Most of what i know now is self taught. But i did a Diploma in eCommerce at Media Design school.

    Read below if you want, its just about me.
    I actually learnt VB.Net but any assignments i was given i would finish really early. There were 4 people in the class (NZ :)) and 2 dropped out and the teacher (good friend now) had nothing left to teach so he taught me C#.Net because i was more likely to get a job in C# than VB.

    My only down side is i have no one above me where i work so i have no one to learn from. So i have to teach myself. Which has its ups and down.

    The ups is that i have 110% creative control, i get to decide what we do and how we do it which means i can come up with new things, or steal ideas and find my own way of doing it, so im not doing the same stuff over and over.

    The downs are that i could be teaching myself bad habbits. But i just try to keep my code nice and clean so if there is a problem its easy to resolve. And considering my porn site got over 2m hits last month and only 31 internal server errors i think im doing pretty well.

    Hopefully my currently poor work enviroment wont hinder me getting a job in Australia in september when i move. (im looking to move to Sydney because the project im working on might not go though and im sick of living in NZ)

    If you want to know anything or want help just ask.
     
  7. OP
    OP
    ACodingFettish

    ACodingFettish Member

    Joined:
    Feb 7, 2004
    Messages:
    6,652
    Location:
    Brisbane
    cheers mate, I'm in a similar situation actually, except I'm at the very beginning.

    the place I work at gave me a job fixing PC's and redesigning their intranet, they wanted it in ASP.net, though I knew nothing about it. I knew a little asp classic, and php but not much.

    So I've just been teaching myself vb.net, and asp.net as I go, according to what I want to work. So my code is as similar to asp classic as I can get it. I hope to learn more and clean it up etc.

    The only other guy who knows IT stuff is a vbscript only guy. he's great for advice on that, but not on .net so I'm teaching myself, and have that creative control, but no one but OCAU to ask.

    Thanks for your help so far (I think you've pitched in on a few of my threads!)
     
  8. Bradzac

    Bradzac Member

    Joined:
    Aug 17, 2003
    Messages:
    1,744
    You should seperate your DAL from your BLL.

    Personally, I prefer web application over web site.
     
  9. Luke212

    Luke212 Member

    Joined:
    Feb 26, 2003
    Messages:
    10,013
    Location:
    Sydney
    Yes but how separate? There are no solid lines between layers. More time can spent working around layer boundary limitations than would have, if the layers were more closely coupled in the first place.

    Im generally happy to tie in the data access with the business logic. For example the SQL commands are never going to be changed to XML file based commands so you can integrate with your BLL.

    So:

    SQLDatabase.dll - executes select/insert/delete
    AccountLogic.cs - builds sql statements, generates business objects
    ShowAccount.aspx/.cs - shows the page

    It works for me :Paranoid:


    Edit: I would add that i dont actually build full sql in the normal sense. I have proprietry builders that save me time.
     
    Last edited: Jul 10, 2007
  10. Jaffa_Cree

    Jaffa_Cree Member

    Joined:
    Feb 26, 2002
    Messages:
    1,976
    Separation of responsibilities is always a grey issue as different developers have different needs. There is no one correct solution rather it depends on the complexity of the project and the time/resources given.

    I prefer to decouple my data layer from the business layer that way my data layer can be reused for other projects. My data layer generally either receives a SQL statement or a stored procedure name and returns a dataset, data reader, data table depending on the overloads and methods call. I let the business layer construct the entity business objects etc.

    Some people like this way others may pass around typed data sets, another method i have seen in use is another class library which defines all the business object classes, and the business; data; and UI layer all have a reference to this class and the data that passes through all tiers uses the custom business objects.

    There is no right or wrong way, it all comes down the to size of the project.
     
  11. Luke212

    Luke212 Member

    Joined:
    Feb 26, 2003
    Messages:
    10,013
    Location:
    Sydney
    So you are including your SQL statements in your BLL? SQL Server is a specific data storage format. By using SQL in the BLL, you can no longer replace the DAL with non SQL storage without huge changes.

    IMO to truely separate the two, you need to leave SQL in the DAL, and only communicate via BEOs (Business Entity Objects).

    This is why I say DONT separate the two under normal circumstances :p
     
  12. Jaffa_Cree

    Jaffa_Cree Member

    Joined:
    Feb 26, 2002
    Messages:
    1,976
    I generally put my custom business objects in the BLL and in these classes I used stored procedure names and the parameters. I also created generic a DAL using interfaces that way through a config setting it can connect to either an Oracle or MSSQL data source. Those are the 2 main data stores i deal with.

    For very simple CRUD applications where i know the data source wont change i *may* use sqlcommands and sql parameter objects in the BLL.

    But as i mentioned before, the way you implement these layers all depend on the project scope, complexity etc


    By using custom business objects you are tightly coupling it to some other library so it no longer is separated, because now your presentation, BLL and DAL are all dependant on this class. I have seen people use this method before but i am not too fond of it.

    But as a said earlier, there is no correct or incorrect way, more a matter of preference and the complexity of the solution.
     
  13. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Why do you perfer a Web Application over a Web Site?

    Both have their ups and downs.

    In a Web Application you have to declare all your web controls before you can use them.
    But when you build your project its compiled down to a single DLL file so you only have to upload the .dll + the .aspx pages.

    In a Web Site you don't have to declare any of the web controls, they are accessible right from the get-go.
    But you have to either upload your code behinds or Publish your website to compile all your code behinds/class's to .dll files.

    I'm not sure what i perfer, i'm using both really. The project im constantly updating im using a Web Site project, and the site im working on which once done, is done im using a Web Application.

    The reason im using the Web Site for the first is because if i update a single page i only upload the .aspx/.aspx.cs file and its done. I don't have to worry about any other pages breaking while im working on a page.

    The second project is a Retail website which once done, i don't need to work on anything else, and if i do it would be on a rare occassion.
     
  14. effekt26

    effekt26 Member

    Joined:
    Nov 2, 2006
    Messages:
    613
    Um, ok...

    any asp.net web application...whether it uses DLL files or .cs or.vb files in the App_Code directory, every website is compiled to a single DLL file regardless, and placed in an obscure directory that is read by IIS. So, if you make a single change to a file in your web directory (except the web.config file), the .NET engine will know a change has been made and recompile the application. This is while, on large asp.net web applications, after a change is made, there is a significant hit in time it takes to load the website, because it is being recompiled. if load the page the page again, without any changes, the load time is vastly improved, because the website is already compiled to the dll file, and read instantly...

    Yeah, seperating your applications into layers is a wise move (known as n-tier development), and make code reuse so much easier...however the inner workings is a bit beyond the scope of this thread. There are however lots of tutorials and articles about it on the net....

    If your are using the .NET 2.0 framework, your connection strings should go in the <connectionStrings> element of your web.config file...which sits as a sibling to <appSetting>...which is a child element of <system.web>...

    Code:
    <system.web>
    <connectionStrings>
      <add name="Connection1" 
        connectionString="Server=SERVER;Database=Database1;User ID=user;Password=pw" />
    </connectionStrings>
    <appSettings />
    </web.config>
    then, to use it across multiple pages, you should make it a class...this simple, and would advise against using this, and moving to a more n-tier design, but this should do what you want...

    Code:
    using System;
    //rest of using statements
    using System.Data.SqlClient;
    using System.Configuration;
    
    class SqlDataAccess {
    
        public SqlDataAccess() {}
    
        public static SqlConnection ReturnConnection() {
    
            string _connString = ConfigurationManager.ConnectionStrings["Connection1"];
            SqlConnection _conn = new SqlConnection(_connString);
    
            return _conn;
    
        }
    
    }
    then you would call it in your code like so...

    Code:
    using (SqlConnection _conn = SqlDataAccess.ReturnConnection()) {
    
        try {
    
            _conn.Open();
    
            //blah blah rest of code here
    
        } catch (Exception ex) {
            throw new Exception(ex.Message);
        }
    
    }
    always get into the habit of using the using{} and try {} catch{} blocks. The using block will ALWAYS release the object even in the event of an exception, and the try{} catch{} blocks will capture that exception...

    hope this info was usefull,
    Cheers,
    Justin
     
    Last edited: Jul 11, 2007
  15. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I think you go make a Web Application with a few class files, and a Web Site with a few class files and such. The Build both and see what is produced. Even porting one of your sites to a Web Site then Publishing it to see what is produced. I tried publishing one of my bigger sites and got 17 .dll files. Web Site and Web Application produce entirelly different builds.

    http://codebetter.com/blogs/grant.killian/archive/2005/01/27/48340.aspx
     
    Last edited: Jul 11, 2007
  16. effekt26

    effekt26 Member

    Joined:
    Nov 2, 2006
    Messages:
    613
    you clearly only know the language syntax, how to make it do stuff, but dont really understand what the language is ACTUALLY doing behind the scenes...

    do me a favour...Open up visual studio, and create a new website called website1..

    type something in the page...and run...(with or without debug...)

    browse to this folder...
    C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\website1\<random>\<random>

    and tell me what you see. yes, the dll file that is used to run your web application.

    Oh, and in ASP.NET, because every website is compiled just like this, they are all web applications, regardless of if you use .cs or .vb files in a App_Code folder, or create seperate projects, compiled to dll files, then referenced by your web app.

    does not even put your point across. that article is saying not to use try {} catch {} blocks when they are not needed. but once you dive into programming with unmanaged objects, (files, databases, registry etc...) you will find that debugging your application without will become useless, as it would be very hard to pinpoint the location of the problem.

    i never said use try{} catch{} for everything, but they are invaluable none the less...
     
  17. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Ohhh look at that, the same dll as using Publish, only when using Publish i don't have to surf through 3000 folders looking for the dll.

    :rolleyes:

    Sigh, can we stop talking about what a project builds and what it the .net compiler compiles it down to.

    You need to re-word what you originally said:

    You should try getting into the habit of not using try/catch/finally unless you actually need to catch an exception. Theres a time and place for using try/catch/finally. Your example is one of them. I was simply saying, without using any word cos im lazy. That you should't "always get into the habit" of using try/catch/finally.
     
    Last edited: Jul 11, 2007
  18. effekt26

    effekt26 Member

    Joined:
    Nov 2, 2006
    Messages:
    613
    probably, but i thought the second sentence would justify that use them always with unmanaged resources...
     
  19. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Oh yeah totally, i was referring to re-wording the first bit where you mentioned the try/catch stuff. All good :)
     
  20. Luke212

    Luke212 Member

    Joined:
    Feb 26, 2003
    Messages:
    10,013
    Location:
    Sydney
    ok OT but do you guys upload DLLs or do you upload cs files?

    i do cs files mostly. they are smaller and quicker to upload changes. only downside is visible plain text source code, and initial compile time.

    did i miss anything?
     

Share This Page

Advertisement: