locked
need help to return Json data RRS feed

  • Question

  • User-557095619 posted

    Hi,

    My connectionstring is

    connectionString="Data Source=test\SVR1;Initial Catalog=ASimulate;User ID=sa;Password=12345"

    My SQL Server Table and Data as following, -

    api-001.png

    My Web API code so far,

    public class CountriesController : ApiController  
        {   
    
       // GET api/Country 
            public IEnumerable<Country> GetCountries(string searchText)  
            {  
                //how to query from database with parameter: DataText 
            }  
      
            // GET api/Country/3
            public Country GetCountry(string DataKey)  
            {  
               
    	   //how to query from database with parameter: DataKey
      
                return Country;  
            }   
    }

    Output expected is JSON Data as following,

    [
                     { DataText: "Algeria", DataKey: "flag-dz" }, { DataText: "Argentina", DataKey: "flag-ar" },
                    { DataText: "Armenia", DataKey: "flag-am" }, { DataText: "Brazil", DataKey: "flag-br" },
                    { DataText: "Bangladesh", DataKey: "flag-bd" }
    ]

    I need help - How my code looks like if query from SQL Server?

    Please help

    Thursday, February 20, 2020 4:40 AM

Answers

  • User475983607 posted

    You misunderstand the technology.  ASP.NET Web API 2 uses content negotiation.  The client, your browser, gets to tell the server what format to return.  Your browser is telling the server to return XML.  See the standard docs for more information.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/formats-and-model-binding/content-negotiation

    Use PostMan for testing to control the interaction.

    Keep in mind, this concept was covered in you other thread with the same subject.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 20, 2020 2:46 PM
  • User475983607 posted

    No you don't have to use jQuery, becuase the WebAPI's default data exchange is Json,  and it's sending Json to any client automatically.  You can see this if you use Postman(free) and give it a URL to a WebAPI controller method. The browser is showing the data in XML format its default even though the data that was actually sent to the browser was in a Json format.

    Please note, the statements above are incorrect for Web API 2.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 21, 2020 12:17 PM
  • User475983607 posted

    DA924

    I did two projects in WebAPI 2. One was for a C# solution and the other on was for a VB solution. I didn't have to Json serialize anything coming from the WebAPI. However, I always send back a DTO and not an EF entity.  Can you please explain why an  container object wouldn't be coming back as Json for any WebAPI no matter what version it is or who the client is?

    Web API 2 uses content negotiation and the default is XML.  This configuration can be changed and newer frameworks like ASP.NET Core return JSON by default.  There is simply no way to force the browser to request JSON when using the address bar and the default config.

    Please read the linked documentation. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 21, 2020 4:34 PM

All replies

  • User1064314092 posted

    DBConnection class

     public class DBConnection
        {
            private readonly SqlConnection _sqlConnection;
            private readonly SqlCommand _sqlCommand;
            private SqlDataReader _reader;
            private int _sqlCommandTimeout = 60000000;
    
            public bool HasTransaction { get; set; }
    
            #region Constructor and Initialization
            public DBConnection()
            {
                _sqlConnection = new SqlConnection { ConnectionString = "Server=;Database=;User Id=;Password = ; " };
    
                _sqlCommand = new SqlCommand { Connection = _sqlConnection };
            }
            #endregion
    
            /// <summary>
            /// Gets of sets the Transact-SQL statement, table name or stored procedure to execute at the data source.
            /// </summary>
            public string CommandText
            {
                get { return _sqlCommand.CommandText; }
                set { _sqlCommand.CommandText = value; }
            }
    
            /// <summary>
            /// Gets of sets a value indicating how the CommandText property is to be interpreted.
            /// </summary>
            public CommandType CommandType
            {
                get { return _sqlCommand.CommandType; }
                set { _sqlCommand.CommandType = value; }
            }
    
            /// <summary>
            /// Provides a way of reading a forward-only stream of rows from a database.
            /// </summary>
            public SqlDataReader DataReader
            {
                get { return _reader; }
            }
    
            /// <summary>
            /// Gets a value that indicates whether the DataReader contains one or more rows.
            /// </summary>
            public bool HasRows
            {
                get { return _reader.HasRows; }
            }
    
            #region Parameter Functions
            /// <summary>
            /// Adds a value to the end of the ParameterCollection.
            /// </summary>
            /// <param name="parameterName"></param>
            /// <param name="value"></param>
            public void AddParameters(string parameterName, object value)
            {
                this.RemoveParameter(parameterName);
                _sqlCommand.Parameters.AddWithValue(parameterName, value);
            }
            /// <summary>
            /// Adds a value to the end of the ParameterCollection with type of parameter
            /// </summary>
            /// <param name="parameterName"></param>
            /// <param name="value"></param>
            /// <param name="parameterDirection"></param>
            /// <param name="size"></param>
            public void AddParameters(string parameterName, object value, ParameterDirection parameterDirection, int size = 256)
            {
                this.RemoveParameter(parameterName);
                SqlParameter parameter = new SqlParameter
                {
                    Direction = parameterDirection,
                    ParameterName = parameterName,
                    Value = value,
                    Size = size
                };
                _sqlCommand.Parameters.Add(parameter);
    
            }
            /// <summary>
            /// Adds a parameter with DbType for Output/Input parameter without value
            /// </summary>
            /// <param name="parameterName"></param>
            /// <param name="dbtype"></param>
            public void AddOutputParameters(string parameterName, DbType dbtype)
            {
                this.RemoveParameter(parameterName);
                SqlParameter parameter = new SqlParameter
                {
                    ParameterName = parameterName,
                    Size = 200,
                    DbType = dbtype,
                    Direction = ParameterDirection.Output
                };
                _sqlCommand.Parameters.Add(parameter);
    
            }
            /// <summary>
            /// Remove a Parameter from ParameterCollection by parameterName.
            /// </summary>
            /// <param name="parameterName"></param>
            public void RemoveParameter(string parameterName)
            {
                if (!parameterName.Contains("@"))
                {
                    parameterName = "@" + parameterName;
                }
    
                if (_sqlCommand.Parameters.Contains(parameterName))
                {
                    _sqlCommand.Parameters.RemoveAt(parameterName);
                }
            }
    
            /// <summary>
            /// Clear parameter list
            /// </summary>
            public void ClearParameters()
            {
                _sqlCommand.Parameters.Clear();
            }
            #endregion
    
            /// <summary>
            /// Advances the DataReader to next record.
            /// </summary>
            /// <returns></returns>
            public bool Read()
            {
                return _reader.Read();
            }
    
            /// <summary>
            /// Sends the CommandText to the Connection and builds a DataReader.
            /// </summary>
            public void ExecuteReader()
            {
                if (_sqlCommand.CommandText != "")
                {
                    try
                    {
                        _sqlConnection.Open();
    
                        if (this.HasTransaction)
                        {
                            SqlTransaction sqlTransaction = _sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                            _sqlCommand.Transaction = sqlTransaction;
                            _sqlCommand.CommandTimeout = _sqlCommandTimeout;
                        }
                        _reader = _sqlCommand.ExecuteReader();
                    }
                    catch (SqlException exception)
                    {
                        if (this.HasTransaction)
                        {
                            if (_reader != null)
                            {
                                _reader.Close();
                            }
                            _sqlCommand.Transaction.Rollback();
                            _sqlConnection.Close();
                        }
                        throw exception;
                    }
                    catch (Exception ex)
                    {
                        if (this.HasTransaction)
                        {
                            if (_reader != null)
                            {
                                _reader.Close();
                            }
                            _sqlCommand.Transaction.Rollback();
                            _sqlConnection.Close();
                        }
                        throw new Exception("Data Read Error.", ex);
                    }
                }
            }
    
            public void CloseReader()
            {
                if (_reader != null)
                {
                    _reader.Close();
                }
    
                if (this.HasTransaction)
                {
                    _sqlCommand.Transaction.Commit();
                }
    
                if (_sqlConnection != null)
                {
                    _sqlConnection.Close();
                }
            }
    
            /// <summary>
            /// Executes a Transact-SQL statement against the connection and returns the number of rows affected.
            /// </summary>
            /// <returns></returns>
            public bool ExecuteNonQuery()
            {
                bool isRowsAffected = false;
    
                if (_sqlCommand.CommandText != "")
                {
                    try
                    {
                        _sqlConnection.Open();
                        _sqlCommand.CommandTimeout = _sqlCommandTimeout;
    
                        if (this.HasTransaction)
                        {
                            SqlTransaction sqlTransaction = _sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                            _sqlCommand.Transaction = sqlTransaction;
                        }
    
                        if (_sqlCommand.CommandType == CommandType.StoredProcedure)
                        {
                            _sqlCommand.ExecuteNonQuery();
                            isRowsAffected = true;
                        }
                        else if (_sqlCommand.ExecuteNonQuery() > 0)
                        {
                            isRowsAffected = true;
                        }
    
                        if (this.HasTransaction)
                        {
                            _sqlCommand.Transaction.Commit();
                        }
                    }
                    catch (SqlException exception)
                    {
                        if (this.HasTransaction)
                        {
                            _sqlCommand.Transaction.Rollback();
                        }
                        throw exception;
                    }
                    catch (Exception ex)
                    {
                        if (this.HasTransaction)
                        {
                            _sqlCommand.Transaction.Rollback();
                        }
                        throw ex;
                    }
                    finally
                    {
                        _sqlConnection.Close();
                    }
                }
    
                return isRowsAffected;
            }
    
            /// <summary>
            /// Executes a Transact-SQL statement against the connection and returns a DataSet.
            /// </summary>
            /// <returns></returns>
            public DataSet GetDataSet()
            {
                DataSet dSet = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
    
                if (_sqlCommand.CommandText != "")
                {
                    try
                    {
                        _sqlConnection.Open();
                        _sqlCommand.CommandTimeout = _sqlCommandTimeout;
                        dataAdapter.SelectCommand = _sqlCommand;
                        dataAdapter.Fill(dSet);
                    }
    
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
    
                    finally
                    {
                        _sqlConnection.Close();
                    }
                }
    
                return dSet;
            }
    
            ~DBConnection()
            {
                if (_reader != null)
                {
                    _reader.Close();
                }
    
                //if (_sqlConnection != null && _sqlConnection.State != ConnectionState.Closed)
                //{
                //    _sqlConnection.Close();
                //}
            }
        }

    Read data From Database With DBConnection Class


    public List<Signal> GetSignalStatus() { List<Signal> signals = new List<Signal>(); DBConnection dBConncetion = new DBConnection(); dBConncetion.CommandText = "SELECT PlatformId,Type,Number,Status FROM SignalStatus"; dBConncetion.ExecuteReader(); while (dBConncetion.Read()) { Signal aSignal = new Signal(); aSignal.PlatformId = Convert.ToInt32(dBConncetion.DataReader["PlatformId"]); aSignal.Type = dBConncetion.DataReader["Type"].ToString(); aSignal.Number = Convert.ToInt32(dBConncetion.DataReader["Number"]); aSignal.Status = Convert.ToBoolean(dBConncetion.DataReader["Status"]); signals.Add(aSignal); } dBConncetion.CloseReader(); return signals; }

    or used Entity Framework.

     protected iSMEDevelopmentDBEntity DBContext()
            {
                iSMEDevelopmentDBEntity iSMEcontext = new iSMEDevelopmentDBEntity();
              //  iSMEDBEntities iSMEcontext = new iSMEDBEntities();
                iSMEcontext.Configuration.ProxyCreationEnabled = false;
                iSMEcontext.Configuration.LazyLoadingEnabled = false;
                iSMEcontext.Database.CommandTimeout = 900;
                return iSMEcontext;
            }
    
    /// read data From Database
    public List<CloudDairySiteAssessment> GetAll()
            {
                using (var db = DBContext())
                {
                    return db.CloudDairySiteAssessments.ToList();
                }
            }

    API Controller return JSON string

    HttpResponseMessage _response = new HttpResponseMessage();
            APIResponse _apiResponse = new APIResponse();
    
    
    
    public async Task<HttpResponseMessage> GetAssessment()
    
               ///Try this, Will 100% work
                if (_dariyManager.GetAll() !=null)
                {
                    _apiResponse.Status = true;
                    _apiResponse.Message = "Data Insert Successfull";
                    _apiResponse.Data = _dariyManager.GetAll(); ////call data read method
                }
                else
                {
                    _apiResponse.Status = false;
                    _apiResponse.Message = "Data Insert Failed.";
                }
    
    
                _response = Request.CreateResponse(HttpStatusCode.OK, _apiResponse);
                return _response;
            }

    Thursday, February 20, 2020 5:29 AM
  • User-557095619 posted

    Your technical explanation is not helping. Did you understand what I'm looking for?

    Thursday, February 20, 2020 5:47 AM
  • User1064314092 posted

    yes, I am Understand. You are looking, return JSON data. your code in the ApiController class. that why I give you it.  if you call api in Postman 

    HttpResponseMessage _response = new HttpResponseMessage();
            APIResponse _apiResponse = new APIResponse();
    
    
    
    public async Task<HttpResponseMessage> GetAssessment()
    
               ///Try this, Will 100% work
                if (_dariyManager.GetAll() !=null)
                {
                    _apiResponse.Status = true;
                    _apiResponse.Message = "Data Insert Successfull";
                    _apiResponse.Data = _dariyManager.GetAll(); ////call data read method
                }
                else
                {
                    _apiResponse.Status = false;
                    _apiResponse.Message = "Data Insert Failed.";
                }
    
    
                _response = Request.CreateResponse(HttpStatusCode.OK, _apiResponse);
                return _response;
            }

    or only the method return type is JSON.

    string json=JsonConvert.SerializeObject(result);

    Thursday, February 20, 2020 7:58 AM
  • User-557095619 posted

    Hi,

    Using ADO.NET Entity Framework, I've something like this

     public class CountryController : ApiController
        {
            // GET api/<controller>
            public IEnumerable<country> Get()
            {
                using (testDBEntities entities = new testDBEntities())
                {
                    return entities.countries.ToList();
                }
    
            }
    
    }

    The result shown as

    001.png

    I need Json format like this,

    [
                     { DataText: "Algeria", DataKey: "flag-dz" }, { DataText: "Argentina", DataKey: "flag-ar" },
                    { DataText: "Armenia", DataKey: "flag-am" }, { DataText: "Brazil", DataKey: "flag-br" },
                    { DataText: "Bangladesh", DataKey: "flag-bd" }
    ]

    Thursday, February 20, 2020 10:36 AM
  • User475983607 posted

    You misunderstand the technology.  ASP.NET Web API 2 uses content negotiation.  The client, your browser, gets to tell the server what format to return.  Your browser is telling the server to return XML.  See the standard docs for more information.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/formats-and-model-binding/content-negotiation

    Use PostMan for testing to control the interaction.

    Keep in mind, this concept was covered in you other thread with the same subject.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 20, 2020 2:46 PM
  • User-557095619 posted

    mgebhard

    You misunderstand the technology.  ASP.NET Web API 2 uses content negotiation.  The client, your browser, gets to tell the server what format to return.  Your browser is telling the server to return XML.  See the standard docs for more information.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/formats-and-model-binding/content-negotiation

    Use PostMan for testing to control the interaction.

    Keep in mind, this concept was covered in you other thread with the same subject.  

    I understood

    1. Client need to tell Web API Server, what format to return
    2. In my case, my browser telling Web API Server return as xml format
    3. If I want return as Json format, I need to write jQuery in Client - Please return in Json format

    Thanks Sir mgebhard

    Friday, February 21, 2020 12:50 AM
  • User1120430333 posted

    mgebhard

    You misunderstand the technology.  ASP.NET Web API 2 uses content negotiation.  The client, your browser, gets to tell the server what format to return.  Your browser is telling the server to return XML.  See the standard docs for more information.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/formats-and-model-binding/content-negotiation

    Use PostMan for testing to control the interaction.

    Keep in mind, this concept was covered in you other thread with the same subject.  

    I understood

    1. Client need to tell Web API Server, what format to return
    2. In my case, my browser telling Web API Server return as xml format
    3. If I want return as Json format, I need to write jQuery in Client - Please return in Json format

    Thanks Sir mgebhard

    No you don't have to use jQuery, becuase the WebAPI's default data exchange is Json,  and it's sending Json to any client automatically.  You can see this if you use Postman(free) and give it a URL to a WebAPI controller method. The browser is showing the data in XML format its default even though the data that was actually sent to the browser was in a Json format.

    Friday, February 21, 2020 7:31 AM
  • User475983607 posted

    No you don't have to use jQuery, becuase the WebAPI's default data exchange is Json,  and it's sending Json to any client automatically.  You can see this if you use Postman(free) and give it a URL to a WebAPI controller method. The browser is showing the data in XML format its default even though the data that was actually sent to the browser was in a Json format.

    Please note, the statements above are incorrect for Web API 2.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 21, 2020 12:17 PM
  • User1120430333 posted

    DA924

    No you don't have to use jQuery, becuase the WebAPI's default data exchange is Json,  and it's sending Json to any client automatically.  You can see this if you use Postman(free) and give it a URL to a WebAPI controller method. The browser is showing the data in XML format its default even though the data that was actually sent to the browser was in a Json format.

    Please note, the statements above are incorrect for Web API 2.  

    I did two projects in WebAPI 2. One was for a C# solution and the other on was for a VB solution. I didn't have to Json serialize anything coming from the WebAPI. However, I always send back a DTO and not an EF entity.  Can you please explain why an  container object wouldn't be coming back as Json for any WebAPI no matter what version it is or who the client is?

    Friday, February 21, 2020 4:29 PM
  • User475983607 posted

    DA924

    I did two projects in WebAPI 2. One was for a C# solution and the other on was for a VB solution. I didn't have to Json serialize anything coming from the WebAPI. However, I always send back a DTO and not an EF entity.  Can you please explain why an  container object wouldn't be coming back as Json for any WebAPI no matter what version it is or who the client is?

    Web API 2 uses content negotiation and the default is XML.  This configuration can be changed and newer frameworks like ASP.NET Core return JSON by default.  There is simply no way to force the browser to request JSON when using the address bar and the default config.

    Please read the linked documentation. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 21, 2020 4:34 PM
  • User1120430333 posted

    DA924

    I did two projects in WebAPI 2. One was for a C# solution and the other on was for a VB solution. I didn't have to Json serialize anything coming from the WebAPI. However, I always send back a DTO and not an EF entity.  Can you please explain why an  container object wouldn't be coming back as Json for any WebAPI no matter what version it is or who the client is?

    Web API 2 uses content negotiation and the default is XML.  This configuration can be changed and newer frameworks like ASP.NET Core return JSON by default.  There is simply no way to force the browser to request JSON when using the address bar and the default config.

    Please read the linked documentation. 

    You are talking about the browser. And in fact, WebAPI 2 is returning Json to any other client but a browser for WebAPI 2. The poster was talking about jQuery, and I assumed that it was about some kind of JavaScript client and having to do some kind of Json serialization of data returned from a WebAPI 2 call that doesn't need to happen. that's how I interpreted it. 

    Friday, February 21, 2020 5:24 PM