Using LINQ to SQL in Visual Basic [Best Practice]

[FarligOpptreden]

I don't know what it is but the level of over-engineered systems I've been getting to work on recently is insane. Recently took over a project with a massive solution containing something like 50+ projects. The data layer was modelled to the nth degree with each field in it's own class file, as well as every database operation. Multi-tiered with WCF and dependency injection and using customised Log4Net configuration (in fact every kind of customised/overridden modification you can think of). It contains whole projects dedicated to just interface classes. And what does it do? A couple of screens that maintain a small database of CV's. As a result it takes about a day to change one field. NASA level engineering for something that you could belt out in a day using straightforward tools. I'm starting to think devs are doing this to justify billing time.

Sounds exactly like something Dariël would build. No, really. I had the displeasure of working on one of the projects they initiated and there was a lot of unnecessary code - to put it in simple terms, the code-base (sans data or database) was in excess of 1.5GB in size.

It seemed like the developers felt it necessary to implement every pattern they could sniff out on the internet, just because they could. It took me a day just to get the thing running.

 

[Hamster]

Ok, since we're throwing numbers around. Here at the bank we have a c# solution with +-210 projects in it. 13 years, 4 different teams/"regimes".

If you want a proof that inheritance is bad and want an interface nightmare all wrapped in 7-8% test coverage then we've got you covered.

...and that's why a rewrite is happening. The really shocking part is that it took us over a year to convince them that they should "have the idea" to start said rewrite.

 

[DominionZA]

Ok, since we're throwing numbers around. Here at the bank we have a c# solution with +-210 projects in it. 13 years, 4 different teams/"regimes".

If you want a proof that inheritance is bad and want an interface nightmare all wrapped in 7-8% test coverage then we've got you covered.

...and that's why a rewrite is happening. The really shocking part is that it took us over a year to convince them that they should "have the idea" to start said rewrite.

What bank you with? Just curious.

 

[Hamster]

What bank you with? Just curious.

One with a lot of blue tape

 

[DA-LION-619]

One with a lot of blue tape

The one that lost R300 million

 

[Hamster]

The one that lost R300 million

Indeed

 

[Kosmik]

Recommendation 1: Drop Link to SQL and use Dapper.

So much win in such a tiny statement and it goes whoosh with a lot of folks

 

[Solarion]

I like this sort of thing and it works perfectly. Stick this in a class:



and simply fill a combo box.

 

[FarligOpptreden]

I like this sort of thing and it works perfectly. Stick this in a class:

View attachment 364038

and simply fill a combo box.

View attachment 364040

Convert that to an extension method for even more awesomeness.

 

[Hamster]

Convert that to an extension method for even more awesomeness.

Make use of using statements and score browny points

 

[Solarion]

Convert that to an extension method for even more awesomeness.

Alright let me re-work it.

 

[DominionZA]

Not a very efficient helper method if you have more than 1 combo to populate.
I would pass in the SQL connection and not have the helper method have anything to do with opening or closing it at all.

What if you have something else to do with the DB? Open another connection to do whatever else needs to be done?

But that's just me...

 

[DominionZA]

You don't need to instantiate a list in the fill method. GetStylesList is already returning a list.

 

[Solarion]

Not a very efficient helper method if you have more than 1 combo to populate.
I would pass in the SQL connection and not have the helper method have anything to do with opening or closing it at all.

What if you have something else to do with the DB? Open another connection to do whatever else needs to be done?

But that's just me...

Thanks Mike that is good advice. I don't want to re-invent it, just fix it and as you pointed out, there are some issues with it.

 

[Solarion]

You don't need to instantiate a list in the fill method. GetStylesList is already returning a list.

Ja no I deleted that post. Took one look at all of it and decided that it's late and I'm tired. Will carry on tomorrow.

 

[DominionZA]

Ja no I deleted that post. Took one look at all of it and decided that it's late and I'm tired. Will carry on tomorrow.

you're getting there man.

Try coding pissed. Quite an experience the next morning when you look at the code churned out - and quite amazing actually if it actually works.

 

[Solarion]

you're getting there man.

Try coding pissed. Quite an experience the next morning when you look at the code churned out - and quite amazing actually if it actually works.

Thanks man I'm striving ahead, you guys have all been super.

 

[semaphore]

I should add the best practise is not to use that abomination called linq-2-sql.

 

[Necropolis]

Also, wherever possible use Using {} blocks.

So you don't have to worry about cleaning up after yourself.

 

[Solarion]

Much prefer this sort of methodology. To have my entire project with this sort of structure is how I would want to go forward.

https://msdn.microsoft.com/en-us/library/6sh2ey19(v=vs.110).aspx

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
public class Part : IEquatable<Part>
{
	public string PartName {
		get { return m_PartName; }
		set { m_PartName = value; }
	}

	private string m_PartName;
	public int PartId {
		get { return m_PartId; }
		set { m_PartId = value; }
	}

	private int m_PartId;
	public override string ToString()
	{
		return "ID: " + PartId + "   Name: " + PartName;
	}
	public override bool Equals(object obj)
	{
		if (obj == null) {
			return false;
		}
		Part objAsPart = obj as Part;
		if (objAsPart == null) {
			return false;
		} else {
			return Equals(objAsPart);
		}
	}
	public override int GetHashCode()
	{
		return PartId;
	}
	public bool Equals(Part other)
	{
		if (other == null) {
			return false;
		}
		return (this.PartId.Equals(other.PartId));
	}
	// Should also override == and != operators.

}

public List<String> GetCountryList(string sql)
{

	try {
		List<String> parts = new List<String>();

		//returns an array of results into a combobox
		SqlConnection SQLConn = new SqlConnection(sqlConnParams);
		SqlCommand SQLCommand = new SqlCommand(sql, sqlConnParams);

		SQLConn.Open();

		SqlDataReader sqlDataReader = SQLCommand.ExecuteReader();

		while (sqlDataReader.Read == true) {
			// Add parts to the list.
			parts.Add(new Part {
				PartName = sqlDataReader.Item("PartName"),
				PartId = sqlDataReader.Item("PartID")
			});


		}

		sqlDataReader.Close();
		SQLConn.Close();

		return parts;

	} catch (Exception ex) {
		MessageBox.Show(ex.ToString());

	} finally {
	}

}