如何在SQL Server 2008中创建.net web服务来插入数据

本文关键字：web net 服务 数据 插入 创建 SQL Server 2008 | 更新日期: 2023-09-27 18:06:57

我发现很难实现。net web服务在SQL Server 2008中创建的数据库中插入数据。在实现以下代码后，我卡住了:

namespace DataService
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld(String entity)
        {
            String firstName = "";
            SqlConnection myConnection = new SqlConnection(
                @"Data Source=.'SQLEXPRESS;" + 
                @"Initial Catalog=student;User ID=sa;Password=123");
            try
            {
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand();
                myCommand.Connection = myConnection;
                myCommand.CommandText = "insert into stud values " +
                    "stud_name = '" + firstName + "'";
                SqlDataReader myReader = myCommand.ExecuteReader();
                //while
                if (myReader.Read())
                {
                    firstName = myReader["stud_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            { 
                myConnection.Close();
            }
            return firstName;
        }
    }
}

这里的实体是JSONArray的形式:

[{"key0":"john","key2":"ann","key1":"joe"}]

我需要在数据库表中插入每个值，例如"john"。

如何在SQL Server 2008中创建.net web服务来插入数据

Parth_90:

首先，如果您从客户端获得JSONArray，我建议您首先在列表中对其进行反序列化

JavascripSerializer js = new JavascriptSerializer<List<sring>>();
List<string> recordsToInsert= js.Deserialize(entity);

然后，您可以通过循环并将所有内容包含在Transaction中来插入表中的每条记录。

foreach(name in recordsToInsert)
{
 //perform the table insert here... 
 //Your SQL code seems correct to me. 
}

改进:在SQLTransaction中包含insert语句

EDIT:添加一些代码来演示如何反序列化问题中提供的示例JSON字符串:

private static void DoSerializationTest()
    {
        string entity="{'"key0'":'"john'",'"key2'":'"ann'",'"key1'":'"joe'"}";
        JavaScriptSerializer js = new JavaScriptSerializer();
        Dictionary<string,string> result= js.Deserialize<Dictionary<string,string>>(entity);
        foreach (var item in result)
        {
            Console.WriteLine("Value: "+item.Value);
            Console.WriteLine("Key :"+item.Key);
        }
    }

您需要使用ExecuteNonQuery()执行您的命令;ExecuteReader用于在执行select操作时从数据库中获取数据。

  SqlCommand myCommand = new SqlCommand();
                    myCommand.Connection = myConnection;
                    myCommand.CommandText = "insert into stud values " +
                        "stud_name = '" + firstName + "'";
     if(myCommand.ExecuteNonQuery() > 0)
     {
         //The command executed with affected rows > 0, OK.
     }
     else
     {
         //No rows affected by the execution. Error management.
     }
相关文章：