Function to return List<t>

[Nefertiti]

Use Dapper

ooh, nice. See Stackoverflow is using it so it must be good.

 

[Kilgore_Trout_Redux]

Dapper will change your fu king life. I have someone on MyBB to thank for turning me onto it and I will be eternally grateful to them.

 

[.QQ.]

ooh, nice. See Stackoverflow is using it so it must be good.

They wrote it.

 

[Pho3nix]

Dapper will change your fu king life. I have someone on MyBB to thank for turning me onto it and I will be eternally grateful to them.

Probably Hamster. Know he has recommended it a couple times to me as well.

 

[Pho3nix]

You picked up that? Besides the rest of that abomination called code.

Use Dapper

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Konsole
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var customerNumbers = GetCustomerNumbers(DateTime.Now, "abc");

            Console.ReadKey();
        }

        public static List<string> GetCustomerNumbers(DateTime transactionDate, string submissionId)
        {
            string myConnString = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            using (IDbConnection conn = new SqlConnection(myConnString))
            {
                string sql = "select distinct CustomerNumber from tblCustDetails where transaction_date = @transactionDate and submission_id = @submissionId";
                var param = new { transactionDate = transactionDate, submissionId = submissionId };
                var results = conn.Query<string>(sql, param, commandType: CommandType.Text);
                return results.ToList();
            }
        }
    }
}

Very nice !

 

[animal531]

How about using a SqlDataAdapter to fill a datatable then do this:
dataTable.AsEnumerable().Select(r => r.Field<string>(columnName)).ToList();

Or even better, use EntityFramework or LinqToSql:
db.YourTable.Where(w => w.SomeColumn == "WhatEva").Select(s => s.YourColumn).ToList();

Yeah, you're better off using an ORM instead of writing SQL commands by yourself (for a large percentage of an average project). I've worked with both EntityFramework and LinqToSQL and you'll need to learn some Linq to use them, but it's not that hard and once you do it's also very handy for easily manipulating non DB list based data (such as lists, arrays, text etc).

There are others such as nHibernate and netTiers that I've used on projects, but I'm not a fan.


I'll go have a look at Dapper when I get a chance.

 

[Kilgore_Trout_Redux]

Yeah, you're better off using an ORM instead of writing SQL commands by yourself (for a large percentage of an average project). I've worked with both EntityFramework and LinqToSQL and you'll need to learn some Linq to use them, but it's not that hard and once you do it's also very handy for easily manipulating non DB list based data (such as lists, arrays, text etc).

There are others such as nHibernate and netTiers that I've used on projects, but I'm not a fan.


I'll go have a look at Dapper when I get a chance.

I seriously do not like entity framework. It's an incredibly slow, bloated mess.

I really don't believe that your code should be changing your DB schema so code first doesn't sit well with me and the models it generates from the DB aren't the best. I won't mention the fact that it doesn't support a number of MS SQL data types.

Dapper is the sweetspot for simplifying DB access while still requiring you to design the models you need.

 

[Mr.Jax]

I seriously do not like entity framework. It's an incredibly slow, bloated mess.

I really don't believe that your code should be changing your DB schema so code first doesn't sit well with me and the models it generates from the DB aren't the best. I won't mention the fact that it doesn't support a number of MS SQL data types.

Dapper is the sweetspot for simplifying DB access while still requiring you to design the models you need.

I completely agree with you.

Do it the old school way and be in total control (of your destiny ), but then also using something like Dapper that eases the conversion between datasets and your objects is the right way to do it!

Big ORM frameworks are "nice"...but really not so nice for big, forever changing in little bits, performance sensitive systems...

 

[Hamster]

Yeah, you're better off using an ORM instead of writing SQL commands by yourself (for a large percentage of an average project).

That is very debatable and very dependent on your definition of average. For all the nice things ORMs give you they also give anybody access to change queries/statements.

A database is probably the one thing that can most easily bottleneck or crash a system. When very large amounts of data or performance is a factor I much prefer having a DBA or somebody control and safeguard the stored procs etc. than give some lazy n00b the ability to shortcut his way into a table lock for the sake of convenience.

IMO ORMs like nHibernate should die and be forgotten and developers should stop being lazy.

 

[Kilgore_Trout_Redux]

IMO ORMs like nHibernate should die and be forgotten and developers should stop being lazy.

I wouldn't go that far. Linq and Entity Framework have their place for rapid prototyping and PoCs.

 

[Hamster]

I wouldn't go that far. Linq and Entity Framework have their place for rapid prototyping and PoCs.

Hehe, I would. Although I do see the appeal for smaller projects like prototyping or something that updates user records etc. with repetitive CRUD functions.

That said though, using Dapper I doubt I'd lag far behind somebody using Entity Framework and the like.