convert mssql data table to list of objects in c#
This is a C# example code that shows you how to convert a ms-sql command or stored procedure to a list of objects in C#
Let's create a sample of select statement and stored procedure:
SELECT Id, FirstName, LastName, Sex, DateofBirth FROM ExampleTableCREATE PROCEDURE [dbo].[usp_GetListofPeople]
AS
BEGIN
--Declare Local Variables
DECLARE @Procedure_Name varchar(100),
@Error_Num int,
@Error_Message varchar(1000)
BEGIN TRY
SELECT
Id AS Id
, FirstName AS FirstName
, LastName AS LastName
, Sex AS Sex
, DateofBirth AS DateofBirth
FROM [dbo].[ExampleTable]
ORDER BY CAST(Id AS INT)
END TRY
BEGIN CATCH
SET @Error_Num = ERROR_NUMBER()
SET @Error_Message = ERROR_MESSAGE()
RAISERROR(@Error_Message,15,1,@Procedure_Name) WITH NOWAIT, SETERROR
END CATCH
ENDThen, we are going to create a object Person.cs in c#:
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public char Sex { get; set; }
public DateTime DateofBirth { get; set; }The function to select a stored procedure or sql command text and return it as data table:
public DataTable Select(string storedProcedureorCommandText, bool isStoredProcedure = true)
{
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection("ConnectionString"))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
if (!isStoredProcedure)
{
command.CommandType = CommandType.Text;
}
command.CommandText = storedProcedureorCommandText;
connection.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
return dataTable;
}
}
}The function for converting the data table to a list of objects:
public IEnumerable<T> ExcuteObject<T> (string storedProcedureorCommandText, bool isStoredProcedure = true)
{
List<T> items = new List<T>();
var dataTable = Select(storedProcedureorCommandText, isStoredProcedure); //this will use the DataTable Select function
foreach (var row in dataTable.Rows)
{
T item = (T)Activator.CreateInstance(typeof(T), row);
items.Add(item);
}
return items;
}List<Person> people = new List<Person>();
people = ExcuteObject<Person>("SELECT Id, FirstName, LastName, Sex, DateofBirth FROM ExampleTable", false).ToList();
//do something with the result - people
List<Person> people = new List<Person>();
people = ExcuteObject<Person>("[dbo].[usp_GetListofPeople]", true).ToList();
//do something with the result - people