Get data

[HrckoV]

How to retrieve data from sql server?

I'm using this code :

.WithRetrieveDataMethod((options) => {

                var result = new QueryResult<StrukturaObrok>();
                using (var db = new  ) **<-- what to put in here ?**
                {
                    result.Items = db.People.Where(p => p.Employee).ToList();
                }
                return result;
            })

[michaeltaylor1329]

Good question! I hope someone who knows answers....

[michaeltaylor1329]

This seems to work - using the Northwind database, Orders table: //PUT IN VIEW: @Html.MVCGrid("Orders")

        MVCGridDefinitionTable.Add("Orders", new MVCGridBuilder<OrdersModel>()
            .WithAuthorizationType(AuthorizationType.AllowAnonymous)
            .AddColumns(cols =>
            {
                // Add your columns here
                cols.Add().WithColumnName("OrderID")
                    .WithHeaderText("OrderID")
                    .WithValueExpression(i => i.OrderID); // use the Value Expression to return the cell text for this column

                cols.Add().WithColumnName("CustomerID")
                    .WithHeaderText("CustomerID")
                    .WithValueExpression(i => i.CustomerID);

                cols.Add().WithColumnName("EmployeeID")
                    .WithHeaderText("EmployeeID")
                    .WithValueExpression(i => i.EmployeeID.ToString());

                cols.Add().WithColumnName("OrderDate")
                    .WithHeaderText("OrderDate")
                    .WithValueExpression(i => i.OrderDate.ToString());

                cols.Add().WithColumnName("RequiredDate")
                    .WithHeaderText("RequiredDate")
                    .WithValueExpression(i => i.RequiredDate.ToString());

                cols.Add().WithColumnName("ShippedDate")
                    .WithHeaderText("ShippedDate")
                    .WithValueExpression(i => i.ShippedDate.ToString());

                cols.Add().WithColumnName("ShipVia")
                    .WithHeaderText("ShipVia")
                    .WithValueExpression(i => i.ShipVia.ToString());

                cols.Add().WithColumnName("Freight")
                    .WithHeaderText("Freight")
                    .WithValueExpression(i => i.Freight);

                cols.Add().WithColumnName("ShipName")
                    .WithHeaderText("ShipName")
                    .WithValueExpression(i => i.ShipName);

                cols.Add().WithColumnName("ShipAddress")
                    .WithHeaderText("ShipAddress")
                    .WithValueExpression(i => i.ShipAddress);

                cols.Add().WithColumnName("ShipCity")
                    .WithHeaderText("ShipCity")
                    .WithValueExpression(i => i.ShipCity);

                cols.Add().WithColumnName("ShipRegion")
                    .WithHeaderText("ShipRegion")
                    .WithValueExpression(i => i.ShipRegion);

                cols.Add().WithColumnName("ShipPostalCode")
                    .WithHeaderText("ShipPostalCode")
                    .WithValueExpression(i => i.ShipPostalCode);

                cols.Add().WithColumnName("ShipCountry")
                    .WithHeaderText("ShipCountry")
                    .WithValueExpression(i => i.ShipCountry); 

            })
            .WithRetrieveDataMethod((context) =>
            {
                System.Data.DataSet ds = new System.Data.DataSet(); 

                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=4NZ45Y2;Database=Northwind;Persist Security Info=false;Integrated Security=True;User ID=michaelt;Password=xxxxx");
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Northwind].[dbo].[Orders]";
                da.SelectCommand = cmd;

                conn.Open();
                da.Fill(ds);
                conn.Close();

                int totalRecords = ds.Tables[0].Rows.Count;

                List<OrdersModel> lst = new List<OrdersModel>(); 

                System.Collections.Generic.List<OrdersModel> list = new System.Collections.Generic.List<OrdersModel>();

                foreach (var row in ds.Tables[0].AsEnumerable()) //using System.Data;
                {
                    OrdersModel obj = new OrdersModel();

                    foreach (var prop in obj.GetType().GetProperties())
                    {
                        try
                        {
                            System.Reflection.PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                            propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                        }
                        catch
                        {
                            continue;
                        }
                    }

                    list.Add(obj);
                }
                var items = list;

                return new QueryResult<OrdersModel>()
                {
                    Items = items,
                    TotalRecords = totalRecords
                };

            })

[michaeltaylor1329]

The OrdersModel: // OrdersModel.cs using System; using System.Collections.Generic; using System.Linq; using System.Web; public class OrdersModel { public string OrderID { get; set; } public string CustomerID { get; set; } public int EmployeeID { get; set; } public DateTime OrderDate { get; set; } public DateTime RequiredDate { get; set; } public DateTime ShippedDate { get; set; } public int ShipVia { get; set; } public string Freight { get; set; } public string ShipName { get; set; } public string ShipAddress { get; set; } public string ShipCity { get; set; } public string ShipRegion { get; set; } public string ShipPostalCode { get; set; } public string ShipCountry { get; set; }

public System.Collections.Generic.List<OrdersModel> OrdersList { get;set; }

//pagination
public int TotalCount { get; set; }
public int PageSize { get; set; }
public int PageNumber { get; set; }
public int PagerCount { get; set; }

}

[JRyanJones]

If you are using Owin and have registered a Create method for your DB context in IAppBuilder.CreatePerOwinContext, then:

MyDbContext dbContext = context.CurrentHttpContext.GetOwinContext().Get<MyDbContext>();