locked
WCF with complex types RRS feed

  • Question

  • Hi All,

    I found this article

    https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx

    The entity data model created this for my sp:

    public virtual int sp_TinUserManten(Nullable<int> pTypeLang, Nullable<int> pTypeOpe, ObjectParameter pUsers_id)
            {
                var pTypeLangParameter = pTypeLang.HasValue ?
                    new ObjectParameter("pTypeLang", pTypeLang) :
                    new ObjectParameter("pTypeLang", typeof(int));
        
                var pTypeOpeParameter = pTypeOpe.HasValue ?
                    new ObjectParameter("pTypeOpe", pTypeOpe) :
                    new ObjectParameter("pTypeOpe", typeof(int));
        
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sp_TinUserManten", pTypeLangParameter, pTypeOpeParameter, pUsers_id);
            }

    Where the pTypeLang is a database type. The idea is to pass a DataTable as parameter and the return can be error or the new id.

    And created this for table as (I added some fields):

     public partial class tin_user
        {
            public int users_id { get; set; }
            public string user_name { get; set; }
            public string surnames { get; set; }
            public string names { get; set; }
            public Nullable<short> type_document { get; set; }
            public string number_id { get; set; }
            public string email { get; set; }
            public string address { get; set; }

            //------------------------ NEW FIELDS  --------------------//
            [StringLength(20)]
            private string _desc_status_users { get; set; }
            private gen_StatusProcess _gen_StatusProcess { get; set; }
        }

    I called the sp from my WCF method as:

            public String addUser(string uID)
            {
                return insertUser(uID);
            }

            public String insertUser(string uID)
            {
                         DataTable uTbl = new DataTable("type_tin_user");
                uTbl.Columns.Add("users_id", typeof(Int32));
                uTbl.Columns.Add("user_name", typeof(string));
                uTbl.Columns.Add("surnames", typeof(string));
                uTbl.Columns.Add("names", typeof(string));
                uTbl.Columns.Add("type_document", typeof(Int32));
                uTbl.Columns.Add("number_id", typeof(string));
                uTbl.Columns.Add("email", typeof(string));
                uTbl.Columns.Add("address", typeof(string));

                uTbl.Rows.Add("all values here");

                SqlConnection conn;
                conn = ConnectionManager.GetConnection();
                conn.Open();

                SqlCommand newCmd = conn.CreateCommand();

                newCmd.CommandText = "sp_TinUserManten";
                newCmd.CommandType = CommandType.StoredProcedure;

                newCmd.Parameters.Add(new SqlParameter("@pReg", uTbl)); //pass table
                newCmd.Parameters.AddWithValue("@pTypeLang", 1);
                newCmd.Parameters.AddWithValue("@pTypeOpe", 1);
                newCmd.Parameters.Add("@pUsers_id", SqlDbType.Int).Direction = ParameterDirection.Output;

                newCmd.ExecuteNonQuery();

                String tempID = newCmd.Parameters["@pUsers_id"].Value.ToString();
               
                conn.Close();
                return tempID;
            }

    How can I use/configure the sp class to pass the values coming from outside and call from my WCF method using the complex type?

    Maybe create a business layer between the WCF and the Data Access Layer.

    Thanks!

    Wednesday, June 28, 2017 3:33 PM

All replies

  • This link gave me better ideas:

    https://www.youtube.com/watch?v=6g8Z82xoWHw

    • Proposed as answer by JFBconsul Saturday, September 9, 2017 11:25 AM
    Friday, June 30, 2017 8:21 PM