How to create C# objects from the SQL Server tables

Introduction

We can come across a moment when we need the data of the tables as objects to be used in the code for multiple discrete uses.

The use cases are as below:

1. We need these objects in database seeding, where we list the C# objects in the code and make it insert or update while the app starts.

   

2. We need these objects in the test cases where we need to arrange some dummy data mocked before we act to verify assertions.

   

Possible Solutions

We use the two methods listed below based on our time, complexity, and expertise.

1. Manually write the values beside the data as we create an object with static data for all the records we need in the code.
   This method is very exhausting, and we might also run into issues if we paste the wrong data to values as we will be hopping between the screens of SQL and our code.

2. If we know about the formula of spreadsheets and have some time to spare, we can create the object code.
   Firstly, we need to paste all the desired data into the spreadsheet. Now, we can write the formula to join the values of the cells with static text and format like a c# object.
   Though this is efficient, and we can easily create objects, this method can be tricky since we need to play with spreadsheet formulas.
   The major disadvantage I can see is its uniqueness. We cannot use the formula created for other tables. We need to create a new spreadsheet formula for each table we need.

What can we do here?

We can use the script I have created and added to the GitHub repo.

Link: https://github.com/mahadikrahul/sql-scripts#c-object-creator-csharpobjectcreatorsql

CSharpObjectCreator.sql

I have created this script using the system tables that store the details of the tables in our database.
We first query the system tables and use the data to populate the objects in the format we need.

Usage

1. Add the target table name as the value of the @TableName variable at line 3.

2. Add any desired condition to the table that you want to filter the result. However, we can keep it blank for all records to populate.

3. Add any field in @SkipField to skip populating in the object.
   

4. Execute the query and find the result in the messages tab.
   

Advantages

Since we use the system tables, we can create objects for all the tables in the DB and populate the real-time data from the tables.

Pro Tip

If you want to modify the formatting or language of the object for other languages, you can update the script and get the task done.

Author

Rahul Mahadik

I started my career journey in 2015 in an IT company. Currently, I am a full-stack team leader at The One Technologies.
I love to solve complex issues while taking care of modernizing and optimizing. I am fond of listening to music, spending time with family & friends, and watching movies.

Comments

[Aaron Reese]

Nice on Rahul.
It would be quite useful if you could also identify the PK and FK links and generate the nested collections in the class. E.g. customer {
ID: int,
Name: String
Orders: [order]
}
Order {
ID: Int
Customer: Customer
OrderDate: Date
Lines: [OrderLine]
}

[Rahul Mahadik]

Thank you for reading my post! I hope it will help in your tasks.

Yes, this might also be a useful feature to have. I will definitely look into it once I get a chance.

I had created to mostly use it for seeding data in DB and I am not sure if nesting of objects will insert data as we want. So, I need to check that aspect as well. Kindly let me know if you have done it and it worked as expected.