Add column to SQL Server database when year changes

Question

I am developing a small project as an hobby and came to a dead end. I am using Visual Studio and SQL Server Management Studio.

I use Entity Framework model to be able to add data entries to a table with 4 columns. Two of those are supposed to log data entry taking place in 2017 like this:

if (CheckBoxBulb.Checked == true)
{
    string roomNo = "";
    roomNo = DropDownList1.SelectedValue.ToString();
    testtable2017 entry = new testtable2017();

    SqlConnection dataConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["iPadLoanConnectionString2"].ToString());

    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "UPDATE testtable2017 SET Last_Bulb_Replaced_2017 = '" + Calendar1.SelectedDate.ToString() + "' WHERE Room_No ='" + roomNo + "'";

    cmd.Connection = dataConnection;

    dataConnection.Open();
    cmd.ExecuteNonQuery();
    dataConnection.Close();

    iPadEntities db = new iPadEntities();
    db.testtable2017.Add(entry);
    db.SaveChanges();
}
else if(CheckBoxFilter.Checked == true) 
{
    string roomNo = "";
    roomNo = DropDownList1.SelectedValue.ToString();

    testtable2017 entry = new testtable2017();

    SqlConnection dataConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["iPadLoanConnectionString2"].ToString());

    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "UPDATE testtable2017 SET Last_Cleaned_Filter_2017 = '" + Calendar1.SelectedDate.ToString() + "' WHERE Room_No ='" +roomNo +"'";

    cmd.Connection = dataConnection;

    dataConnection.Open();
    cmd.ExecuteNonQuery();
    dataConnection.Close();

    iPadEntities db = new iPadEntities();
    db.testtable2017.Add(entry);
    db.SaveChanges();
}

So far so good and it is working perfectly but lets say that when the year changes to 2018 I want to create two more columns that will hold all the data logged within 2018. Is there any way of achieving that and if so how could I also manipulate the code in C# to then start save any data entries to the newly created columns?

Thank you

Don't do that, it's horrible design. Just save the date in a `date/datetime(2)` column. — HoneyBadger, Jun 06 '17 at 12:40
What do you think to gain making a separate column for the year? This will only give you more problems when you need to query that data — Steve, Jun 06 '17 at 12:41
UPDATE testtable2017 SET Last_Bulb_Replaced_2017 = '" + Calendar1.SelectedDate.ToString() + "' WHERE Room_No ='" + roomNo + "'"; <-- this is super dangerous, and open to SQL injection. Go and read https://stackoverflow.com/a/30367283/34092 . — mjwills, Jun 06 '17 at 12:42
@HoneyBadger I forgot to mention I am quite new to this so the probability of getting a bad design was pretty high from the starting point. The thing is i could log the date time in a separate column but I wouldn't want older entries to be overridden. — Jorge Guerreiro, Jun 06 '17 at 12:43
Why would older entries be overridden? If you log a new entry, you use `insert`, if you want to update an older entry, you'd use `update`. — HoneyBadger, Jun 06 '17 at 12:45
The datetime column will have year information too. New date will not override old values if you don't allow it to be. You can always query the table to retrieve data for a specific year. — Chetan, Jun 06 '17 at 12:47
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Jun 06 '17 at 12:48
You have combined ADO.NET and Entity Framework in your code. In any case the code will break when you add new column to table without changing the code. — Chetan, Jun 06 '17 at 12:49
You can add a column for year and use a dynamic pivot to query it with a column for each year. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Peter, Jun 06 '17 at 13:02

score 1 · Accepted Answer · answered Jun 06 '17 at 12:57

I agree with all the people who say this is a bad design and a horrible idea.

However, treating your question as academic, you could simply add some logic to your program to check what year it is today, and query the database to see if the column exists with the year in the name.

If the column doesn't exist, then run an ALTER TABLE statement to add it before proceeding with the rest of the script. You would also need to use the Year today to build your UPDATE query instead of hard-coding "2017" in it.

score 0 · Answer 2 · answered Sep 25 '17 at 15:41

Adding Column: In Object Explorer, right-click the table to which you want to add columns and choose Design. Click in the first blank cell in the Column Name column. Type the column name in the cell. The column name is a required value. Press the TAB key to go to the Data Type cell and select a data type from the dropdown. This is a required value, and will be assigned the default value if you don't choose one.

Add column to SQL Server database when year changes

2 Answers2