DEV Community

Ariel Reyes
Ariel Reyes

Posted on • Edited on

1 2

Making a dynamic Stored Procedure with SQL and C#

using Backend.Infrastructura.ProcedimientosAlmacenados.Command;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Reflection;
using System.Threading.Tasks;
using System.Web.Script.Serialization;

namespace Backend.Infrastructura
{
    public class SqlStoredProcedureServiceManager
    {
        public async Task<List<T>> Get<T>(string uri)
        {
            HttpClient client = new HttpClient();

            HttpResponseMessage response = await client.GetAsync(uri);
            bool responseWasWrong = !response.IsSuccessStatusCode;
            if (responseWasWrong)
            {
                return null;
            }

            string jsonString = await response.Content.ReadAsStringAsync(); 
            JavaScriptSerializer JSserializer = new JavaScriptSerializer();

            return JSserializer.Deserialize<List<T>>(jsonString);
        }

        private DataTable GetDataTable(string query)
        {

                SqlDataAdapter adapter = new SqlDataAdapter(query,SQLConfiguration.GetDbString());
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);

                SQLConfiguration.Close();
                return dataTable;

        }

        public DataTable GetDataByStoredProcedure(CommandSender cmdSnd)
        {

            using (SqlDataAdapter sda = new SqlDataAdapter(cmdSnd.GetCommand()))
            {

                DataTable dt = new DataTable();
                sda.Fill(dt);
                SQLConfiguration.Close();
                return dt;
            }

        }

        public List<T> GetAnyDataByCommand<T>(CommandSender cmdSender)
        {
            DataTable table = GetDataByStoredProcedure(cmdSender);
            return ConvertToList<T>(table);
        }


        public List<T> ConvertToList<T>(DataTable dataTable)
        {
            var columnNames = dataTable.Columns.Cast<DataColumn>()
                    .Select(c => c.ColumnName)
                    .ToList();
            var properties = typeof(T).GetProperties();

            return dataTable.AsEnumerable().Select(row =>
            {
                var objectType = Activator.CreateInstance<T>();
                foreach (var prop in properties)
                {
                    if (columnNames.Contains(prop.Name))
                    {
                        PropertyInfo propInfo = objectType.GetType().GetProperty(prop.Name);


                        prop.SetValue(objectType, row[prop.Name] == DBNull.Value ? null : Convert.ChangeType(row[prop.Name], propInfo.PropertyType));
                    }
                }
                return objectType;
            }).ToList();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay