How to use stored procedure in 2010 Q3?

[wg]

Hi,

I am new to Open Access 2010 Q3. When connecting to SQL server, I can create a Entity Diagrams like Entity Framework. I also can add the stored procedure. 

But how can I use the stored procedure? I have made some researches with tutorials and video. Looks I need find a Mapping Wizard. Howerver, I can not find option like "Reverse Mapping Wizard" in 2010 Q3.

After installed Open Access package, there is only certain menus like "Add Domain Model", "Add Domain Services" and disabled "Update reference". Did I do something wrong?

btw: The project was created by "Silverlight Business Application" template with Visual Studio 2010.

Thanks,
Bill

[PetarP]

Hi wg,

 When working with OpenAccess you are given two possibilities. One is to work with our new Entity Diagrams approach much like Entity framework. The other is the old reverse mapping or the reverse mapping wizard to which you are referring. The two approaches are completely different and cannot be used together.
As to how to switch between the two approaches I advice you to have a look at this blog post as it describes the entire process in details.
Should you decide to use the Entity Diagrams approach I would advice you to have a look into this help topic as it describes the process of importing a stored procedure.
A good starting point for working with stored procedures and the old reverse mapping would be this help topic.
I hope that my answer has been useful to you. Should you have any further questions please do not hesitate to contact us.

Regards,
Petar
the Telerik team

[wg]

Hi Petar ,

This help me a lot. Thank you so much.

btw: OpenAccess ORM rocks!

WG.

[Ted]

I'm still not understanding it.  I'm new to OpenAccess and used to using LINQTOSQL.

we have the "EntityDiagrams.rlinq file in a DAL Project and a reference set from the web application to that project but all I see is the tables in the diagram.  Under the Model Schema Explorer I see the Stored Procedures listed there.  can you tell me what I am supposed to do with them to be able to call them from my code and pass parameters to them to return a List?

[Ted]

OK.  nevermind.  It's just been a long week already.  I'm getting it but I would like to know the proper way to handle the object that is returned?  it's not a table that I have defined so for now I defined a seperate class

public class Packages
    {
        public int PackageID { get; set; }
        public string PackageName { get; set; }
    }

but I have to manually build that when it is returned.  is there a way to tell it what type it is returning?  what I am returning is actually a table from another DB that I just query for this result set.

ProDocDataContext pdContext = new ProDocDataContext();
       string sPackages = "";
       protected void Page_Load(object sender, EventArgs e)
       {
           Session["sub_package_id"] = "7105";
           if(Session["sub_package_id"] != null)
               sPackages = Session["sub_package_id"].ToString();
           if (!Page.IsPostBack)
           {
               GetPackages();
           }
       }
       private void GetPackages()
       {
           List<DAL.Packages> packs = new List<Packages>();
           var a = pdContext.UspPackagesGet(sPackages).ToList();
           foreach(object[] c in a)
           {
               DAL.Packages p = new Packages();
               p.PackageID = Convert.ToInt32(c[0]);
               p.PackageName = c[1].ToString();
               packs.Add(p);
           }
           rcbPackage.DataSource = packs;
           rcbPackage.DataValueField = "PackageID";
           rcbPackage.DataTextField = "PackageName";
           rcbPackage.DataBind();
           //insert the first item
           rcbPackage.Items.Insert(0, new RadComboBoxItem("- Select a Package -"));
       }

[wg]

Hi Ted,

That's exactly what I have done. To create another class like a DTO to transform the Object Data.

public partial class InventoryDomainService
{
    public IQueryable<CallDTO> GetCallDTOs()
    {
        var vvv = this.DataContext.GetQuantityInStorehouse(100000, "eee003b6-691a-4044-8547-8d0bd10ce08d-883");
        var list = new List<CallDTO>();
        for (int i = 0; i < vvv.Count(); i++)
        {
            object[] arr = vvv[i] as object[];
            list.Add(new CallDTO { ID = i + 1, S1 = arr[0].ToString(), S2 = arr[1].ToString() });
        }

        var qqq = from p in list
                  select p;
        return qqq.AsQueryable<CallDTO>();

    }
}

I am wondering as well that if there is a right way to use object data coming from Stored Precedure.

Thanks,

[Ted]

It would really be nice to have an example of the proper way to use a stored Procedure SELECT with the latest version.  

[PetarP]

Hello wg,

Currently we do not have a way to automatically assign a result type to a stored procedure. We do plan to have some functionality added here however this is still subject to implementation.
At the moment the only possible way would be to rewrite the stored procedure declaration in a partial class and manually change its return type. This is an example for a stored procedure returning an array of Products:

publicProduct[] TenMostExpensiveProducts(int? productID)
        {
            SqlParameter parameterProductID = newSqlParameter("ProductID", OpenAccessType.Int32);

            List<SqlParameter> sqlParameters = newList<SqlParameter>()
            {
                parameterProductID
            };

            Product[] queryResult = this.ExecuteStoredProcedure<Product>("'Ten Most Expensive Products' ?", sqlParameters, productID);
            returnqueryResult;
        }

Greetings,
Petar
the Telerik team