从WEBAPI返回json值 [英] Return json value from WEBAPI

问题描述

伙计们我有sql查询在Asp.Net C中返回Json字符串＃我无法返回它请帮我解决它。我不想使用datareader。



我尝试过：

 public Employee Get（）
 {
 SqlDataReader reader = null; 
 SqlConnection myConnection = new SqlConnection（）; 
 myConnection.ConnectionString = @Data Source = Demo\SQLEXPRESS; Initial Catalog = DB; User ID = xyz; Password = xyz123; Connection Timeout = 1800;; 
 
 SqlCommand sqlCmd = new SqlCommand（）; 
 sqlCmd.CommandType = CommandType.Text; 
 string jsonOutputParam =@ jsonOutput; 
 //sqlCmd.CommandText =Create_JSON_ProjectList; 
 //sqlCmd.Parameters.Add(jsonOutputParam,SqlDbType.NVarChar,- 1）.Direction = ParameterDirection.Output; 
 sqlCmd.Connection = myConnection; 
 myConnection.Open（）; 
 string st =从QryProjectWithDepartmentDetails中选择projectName ORDER BY projectName DESC FOR JSON PATH，WITHOUT_ARRAY_WRAPPER; 
 // FOR JSON PATH，WITHOUT_ARRAY_WRAPPER）; 
 sqlCmd = new SqlCommand（st，myConnection）; 
 reader = sqlCmd.ExecuteReader（）; 
 Employee emp = null; 
 while（reader.Read（））
 {
 emp = new Employee（）; 
 emp.DepartmentNmae = reader [projectName]。ToString（）; 
 
} 
返回emp; 
 myConnection.Close（）; 
} 

解决方案

您的查询将返回一行或多行，其中包含一个具有自动生成名称的字符串列：

格式查询结果为带有FOR JSON（SQL Server）的JSON | Microsoft Docs [ ^ ]

使用FOR JSON SQL Server和客户端应用程序（SQL Server）中的输出Microsoft Docs [ ^ ]

 使用（ var  myConnection =  new  SqlConnection（  ...）
 使用（ var  sqlCmd =  new  SqlCommand（ 从QryProjectWithDepartmentDetails中选择projectName ORDER BY projectName DESC FOR JSON PATH ，WITHOUT_ARRAY_WRAPPER，myConnection））
 {
 myConnection.Open（）; 
 
  var  sb = 新 Syst em.Text.StringBuilder（）; 
 使用（ var  reader = sqlCmd.ExecuteReader（））
 {
  while （reader.Read（））
 {
 sb.Append（reader.GetString（ 0 ））; 
} 
} 
 
 返回 新员工{ DepartmentName = sb.ToString（）}; 
} 

注意：您应该避免对连接字符串进行硬编码。请从配置文件中读取它们：

如何to：从Web.config文件中读取连接字符串Microsoft Docs [ ^ ]

Guys i have sql query which return Json string in Asp.Net C# i am unable return it please help me out of it. and i dont want to use datareader.

What I have tried:

public Employee Get()
        {
            SqlDataReader reader = null;
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = @"Data Source=Demo\SQLEXPRESS;Initial Catalog=DB;User ID=xyz;Password=xyz123;Connection Timeout=1800;";

            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.Text;
            string jsonOutputParam = "@jsonOutput";
            //sqlCmd.CommandText = "Create_JSON_ProjectList";
            //sqlCmd.Parameters.Add(jsonOutputParam, SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
            sqlCmd.Connection = myConnection;
            myConnection.Open();
            string st = "select projectName from QryProjectWithDepartmentDetails ORDER BY projectName DESC FOR JSON PATH, WITHOUT_ARRAY_WRAPPER";
            //FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)";
            sqlCmd = new SqlCommand(st, myConnection);
            reader = sqlCmd.ExecuteReader();
            Employee emp = null;
            while (reader.Read())
            {
                emp = new Employee();
                emp.DepartmentNmae = reader["projectName"].ToString();
               
            }
            return emp;
            myConnection.Close();  
        }

解决方案

Your query will return one or more rows with a single string column that has an auto-generated name:
Format Query Results as JSON with FOR JSON (SQL Server) | Microsoft Docs[^]
Use FOR JSON output in SQL Server and in client apps (SQL Server) | Microsoft Docs[^]

using (var myConnection = new SqlConnection("...")
using (var sqlCmd = new SqlCommand("select projectName from QryProjectWithDepartmentDetails ORDER BY projectName DESC FOR JSON PATH, WITHOUT_ARRAY_WRAPPER", myConnection))
{
    myConnection.Open();
    
    var sb = new System.Text.StringBuilder();
    using (var reader = sqlCmd.ExecuteReader())
    {
        while (reader.Read())
        {
            sb.Append(reader.GetString(0));
        }
    }
    
    return new Employee { DepartmentName = sb.ToString() };
}

NB: You should avoid hard-coding your connection strings. Read them from the config file instead:
How to: Read Connection Strings from the Web.config File | Microsoft Docs[^]

这篇关于从WEBAPI返回json值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！