使用wcf、c#从sqlserver数据库中获取json格式的数据

本文关键字：json 获取 格式 数据 数据库 wcf sqlserver 使用 | 更新日期: 2023-09-27 18:25:39

我可以从SQL Server表中获取json数据，但json的格式似乎不正确。

json:后有额外的"

"[{'"deviceid'":'"jafjajf17841278947'"},
 {'"deviceid'":'"ahfaj2528'"},
 {'"deviceid'":'"hefhsf9872987572'"},
 {'"deviceid'":'"22'"},
 {'"deviceid'":'"23'"}]"

json末尾和开头的倒置逗号现在的问题是，当我们调用这个json时，它是在android和浏览器中调用的，我们会得到这些倒引号，但在fiddler中不会。我需要更改代码吗。

我尝试过的代码：

GetDeviceId.svc.cs:

public string GetDeviceIds()
{
    try
    {
        MySqlCommand command = default(MySqlCommand);
        MySqlDataAdapter adaptor = new MySqlDataAdapter();
        DataTable dt = new DataTable();
        string sql = "select  deviceid from userreg";
        digitalindia.Open();
        command = new MySqlCommand(sql, digitalindia);
        adaptor.SelectCommand = command;
        adaptor.Fill(dt);
        adaptor.Dispose();
        command.Dispose();
        digitalindia.Close();
        if (dt.Rows.Count > 0)
        {
             string json = GetJson(dt);
             //var j = JsonConvert.SerializeObject(json);
             //j = j.Substring(2);
             //json
             //string json = GetJson(dt);
             //json.Remove(1, json.Length - 1);
             //var json1 = EvaluateException(json);
             //ArrayList json = new ArrayList();
             //ArrayList json = new ArrayList();
             //json.Add(DataTableToJsonWithStringBuilder(dt));
             return json;
             //return string.Format("You entered: {0}", json); ;
         }
         else
         {
             string json ="null";
             return json;
             //return "No Party Found";
         }
     }
     catch (Exception ex)
     {
         //ArrayList json = new ArrayList();
         //return ex;
         return ex.Message.ToString();
     }
}
private string GetJson(DataTable dt)
{
    System.Web.Script.Serialization.JavaScriptSerializer Jserializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rowsList = new List<Dictionary<string, object>>();
    Dictionary<string, object> row = null;
    foreach (DataRow dr in dt.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in dt.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }
        rowsList.Add(row);
    }
    return Jserializer.Serialize(rowsList);
}

IGetDeviceId.svc:

[ServiceContract]
public interface IGetDeviceId
{
    [OperationContract()]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare, UriTemplate = "GetDeviceId")]
    string GetDeviceIds();       
}

使用wcf、c#从sqlserver数据库中获取json格式的数据

您可以使用以下代码：它动态地进行查询并返回json

  public List<List<string>> GetDataAsList(string query)
    {
        try
        {
            intializeConnection();

                    DataTable dataTable;
                    DataSet dataset = new DataSet();
                    List<List<string>> data;
                    query = query.Trim().ToUpper();
                    DataRow row = null;
                    string[] commasCount;
                    string[] commasCountWithoutFrom;
                    if (query.Contains('*'))
                    {
                        return null;
                    }
                    commasCount = query.Trim().Split(new string[] { "FROM" }, StringSplitOptions.None);
                    string commasString = commasCount[0].Trim();
                    commasCountWithoutFrom = commasString.Trim().Split(',');
                    ArrayList columnsName2 = new ArrayList();
                    for (int c = 0; c < commasCountWithoutFrom.Length; c++)
                    {
                        string raw = commasCountWithoutFrom[c].Trim();
                        string[] parts = raw.Trim().Split(' ');
                        string sub_parts = parts[parts.Length - 1];
                        columnsName2.Add(sub_parts.ToString());
                    }

            List<List<string>> resultArr=null;
            string cmdStr = String.Format(query);
            command = new SqlCommand(cmdStr, connection);
            set = new DataSet();
            adapter = new SqlDataAdapter(command);
            adapter.Fill(set);
            if (set.Tables[0].Rows.Count > 0)
            {
                resultArr = new List<List<string>>();

                for (int i = 0; i < set.Tables[0].Rows.Count; i++)
                {
                    resultArr.Add(new List<string>());
                    for (int col = 0; col < columnsName2.Count; col++)
                    {
                        resultArr[i].Add(set.Tables[0].Rows[i][columnsName2[col].ToString()].ToString().Trim());
                    }

                }
            }
            else
            {
                    resultArr = new List<List<string>>();
                    resultArr.Add(new List<string>());
                    for (int col = 0; col < columnsName2.Count; col++)
                    {
                        resultArr[0].Add("No Data");
                    }

            }
            connection.Close();
            return resultArr;
        }
        catch (SystemException ex)
        {
            connection.Close();
            return null;
        }
    }

并在ISERVICE.cs 中使用此

 [WebInvoke(Method = "GET", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "GetDataAsList/{query}")]
    List<List<string>> GetDataAsList(string query);
相关文章：