locked
C# and SQL Queries Best practices RRS feed

  • Question

  • I'm creating couple application web and desktop and was wondering what is the best way to code queries. Should that be in XML file or inside the code ?

     

     

    Friday, October 26, 2007 2:02 PM

All replies

  •  

    If you want my opinion, niether of the approaches you suggested are the best. It is best to work with stored procedures and call the stored procedures from your code. Stored procedures work much faster and if needed you can modify you procedures without recompiling your code (in some cases).

     

    Corby Nichols

    Flying Elephant Software

     

    Friday, October 26, 2007 2:19 PM
  • Hi i am completely agree with stored procedure approach. you should use them, other then this you should use layerd appraoch towords your application.

     

    i mean you should use DAO pattren which consist of Data Access layer. Business Logic layer and Interface between them for communication. if you need any more help regarding this. please let us know.

     

    Thanks

     

    Friday, October 26, 2007 3:06 PM
  • I think it depends on the usage.  First, though, not in the XML.

     

    I write a lot of desktop applications and most are database related.  Most are also a small number of users (<20) accessing the same database.  All users are also in the same floor of a building. I code my SQL directly in the program. 

     

    Currently, I also have all my database SQL calls in one class in each application.  This makes it easier for modifying SQL stuff as it's all in one class and I don't have to look through each form for SQL.

     

    Nothing against stored procedures.  Unless you have a DBA that insists on checking / testing all your stored procedures before moving it into production.  In that case, write your SQL in your code so it's one less person hassling you.  [please let's skip the pro-DBA/anti-DBA discussions].

    Friday, October 26, 2007 3:13 PM
  • Hi

     

    I work with a flexible business-layer. So the queries will be built automatically within the source-code this can handle all simple queries like "SELECT * FROM Orders WHERE ID = '123'". For special or complex queries I would also suggest stored procedures.

     

    Regards

    Flo

     

    Friday, October 26, 2007 3:20 PM
  • Previously I have used NHibernate to do things, but my customer this time is using Godaddy servers and they don't allow reflection.

     

    What is the way to do it using Data Access Layer ?

     

    Friday, October 26, 2007 3:54 PM
  •  

    I am not pushing my program here, but I have a free source code, object generator, stored procedure generator, data tier creator (From a Multi Project Template) program on my web site, and it demonstrates how to create a multi tier 100% stored procedure driven framework.

     

    The program is call Flying Elephant Software RAD Studio

     

    www.flyingelephantsoftware.com

     

    or watch a movie about it here:

     

    www.flyingelephantsoftware.com/Products/RADStudio.aspx

     

    The program is 100% free even for commercial purposes, but it creates a stored procedure driven enviroment and you can update your code similiar to NHibernate - myobject.Save(ref object); but my program is all stored procedures and it creates C# class objects based on each table in your SQL Server or Access database table for you.

     

    The program is free it is my gift to the C# development world so hopefully this is not considered spam. If it is I apologize.

     

    Thanks,

     

    Corby Nichols

    President

    Flying Elephant Software

    Friday, October 26, 2007 4:10 PM
  • Another pull for stored procedures. Easy to manage, easy to update, easier to check for errors. But the big reason is protecting parameters from SQL injection.

    Friday, October 26, 2007 5:46 PM
  • I prefer to use stored procedures becasue to me they are much easier to test and are much more secure than trying to run them in your code everytime that you want to make a change. For Data Access Layers, I like to make a separate DAL for each table in the database and then from that I create a private variable for each stored procedure in that DAL, so that the only part of a project that knows anything about a certain table is that DAL. Oh yeah and I use DAO (Data Access Object) instead of DAL For instance:

    public static class DAO_Alumni
    {

        #region Stored Procedure Names
        /// <summary>
        ///Name of stored procedures that act on this
        ///table and are only used by the methods in this class
        /// </summary>
        private static string _GetAlumni = "GetAlumni";
        private static string _GetAlumnus = "GetAlumnus";
        private static string _GetDetailedAlumniInfo = "GetDetailedAlumniInfo";
        private static string _GetClassYears = "GetClassYears";
        private static string _SearchAlumniInfo = "SearchAlumniInfo";
        #endregion

        #region Methods
        public static List<TO_Alumnus> GetAlumni()
        {
        }

        public static List<TO_Alumnus> GetDetailedAlumni()
        {
        }

        public static TO_Alumnus GetDetailedAlumnus(int? parID)
        {
        }

        public static List<TO_Alumnus> SearchAlumni(TO_AlumniSearch TO_AlumniSearchObject)
        {
        }

        public static void ModifyAlumnus(TO_Alumnus parTO_Alumni)
        {
        }

        public static void AddAlumnus(TO_Alumnus parTO_Alumni)
        {
        }

        public static List<string> GetClassYears()
        {     
        }

        private static TO_Alumnus FillAlumnus(DataRow DataRowObject)
        {
        }

        private static TO_Alumnus FillDetailedAlumnus(DataRow DataRowObject)
        {
        }
        #endregion
    }
    Saturday, October 27, 2007 7:15 PM