none
unable to connect to sql db from wcf service deployed in sharepoint 15 template layouts folder RRS feed

  • Question

  • i have created a  wcf service by using this ink [http://sharepointpals.com/post/Step-by-Step-Procedures-to-create-a-WCF-Service-Application-for-SharePoint-2013 ] thats deployed in my 15 template layouts folder . and i have tested with a simple method that accepts a string param, and returns a  string. and its working fine,

    now as per my requirement i need to connect to a sql db table and fetch few records  based on the parameter/primary key.

    so I wrote ado.net connectivity inside the service and  tried fetch the records . but ti failed to do so.

      
    sing System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    
    using Microsoft.Office.Server.Social;
    using Microsoft.Office.Server.UserProfiles;
    using Microsoft.Office.Server.Microfeed;
    using Microsoft.SharePoint;
    using Microsoft.Office.Server.ReputationModel;
    using Microsoft.Office.Server.SocialData;
    
    using System.Configuration;
    
    
    using System.Security.Principal;
    using System.Web;
    using Microsoft.Web.Hosting.Administration;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace SharePoint.WCFService.Sample.Code
    {
        [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
        public sealed class SampleService : ISampleService
        {
    
    
            /// <summary>
            /// This method is used to test the service whether it is been deployed properly or not.  This will not do anything functionally apart from the Logging.
            /// </summary>
            /// <param name="SampleValue"></param>
            /// <returns></returns>
            public string SampleServiceCall(string SampleValue)
            {
    
                //string strQueryString = HttpUtility.UrlEncode("DC07\\Administrator");
    
                //ULSLog.LogDebug("Entering into SampleServiceCall");
                //ULSLog.LogDebug("Leaving into SampleServiceCall");
                return "Success";
            }
    
    
            public string PayBill(string PayId)
            {
                return "Transaction having PayId " + PayId + " was successful";
            }

     the above code worked peRFectly worked.Now  i have added another method that targets sql db with fetches a custom class [ say products ==> list<products> ] .
     below code is the one causing the issue:

       public string GetProductPrice(string mproductID)
            {
                string strConn1 = @"Server=srvr11;Database=Products;User Id =mdomain\muserid11;Password=mypwd1+;";


                //Code Logic to get all students.
                SqlDataReader reader = null;
                SqlConnection myConnection = new SqlConnection();

                //Server = myServerAddress; Database = myDataBase; User Id = myUsername;      Password = myPassword;
                //USE [Products]
                /*  GO     INSERT INTO [dbo].[tblProducts]            ([ProductID]            ,[ProductName]            ,[ProductCategory]  ,[Price])    VALUES         (5,'racquet5','sports',104) GO SELECT *FROM tblProducts             */
                int prodprice = 0;
                int mproductIDINT = Convert.ToInt32(mproductID);
                myConnection.ConnectionString = strConn1;

                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandText = "Select * from tblProducts where ProductID=" + mproductIDINT + "";
                sqlCmd.Connection = myConnection;
                myConnection.Open();
                reader = sqlCmd.ExecuteReader();
                //   Products singleprod = null;
                while (reader.Read())
                {
                    // singleprod = new Products();
                    //singleprod.ProductID = Convert.ToInt32(reader.GetValue(0));
                    //  singleprod.ProductName = reader.GetValue(1).ToString();
                    //   singleprod.ProductCategory = reader.GetValue(2).ToString();

                    // singleprod.Price = Convert.ToInt32(reader.GetValue(3));
                    prodprice = Convert.ToInt32(reader.GetValue(3));
                }

                string strprodprice = Convert.ToString(prodprice);
                // return prodd;
                myConnection.Close();
     }

     am stuck with why my first 2 methods worked fine and the ado.net code failed to fetch the data in json/xml format! 

                               
    • Edited by SaMolPP Sunday, March 5, 2017 4:52 PM
    Sunday, March 5, 2017 2:46 PM

Answers

All replies