How to use stored procedure in 2010 Q3?

8 posts, 1 answers
  1. wg
    wg avatar
    30 posts
    Member since:
    Jul 2006

    Posted 09 Jan 2011 Link to this post

    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
  2. Answer
    PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 11 Jan 2011 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. wg
    wg avatar
    30 posts
    Member since:
    Jul 2006

    Posted 11 Jan 2011 Link to this post

    Hi Petar ,

    This help me a lot. Thank you so much.

    btw: OpenAccess ORM rocks!

    WG.
  5. Ted
    Ted avatar
    47 posts
    Member since:
    Jan 2008

    Posted 12 Jan 2011 Link to this post

    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?
  6. Ted
    Ted avatar
    47 posts
    Member since:
    Jan 2008

    Posted 12 Jan 2011 Link to this post

    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 -"));
           }
  7. wg
    wg avatar
    30 posts
    Member since:
    Jul 2006

    Posted 13 Jan 2011 Link to this post

    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,
  8. Ted
    Ted avatar
    47 posts
    Member since:
    Jan 2008

    Posted 14 Jan 2011 Link to this post

    It would really be nice to have an example of the proper way to use a stored Procedure SELECT with the latest version.  
  9. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 17 Jan 2011 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top
DevCraft banner