question

Reborn-0047 avatar image
0 Votes"
Reborn-0047 asked LanHuang-MSFT answered

How do i insert web api data in to sql server database

I want to know how can i insert web api data into sql server database, I have written an web api and i want to insert the api data into sql server database and i do it without entity framework, Can i do it with data reader? I feel like I can't do it with data reader, are there any experts know how to do it, big thanks.

My Code:

[HttpGet]
        public IHttpActionResult Get(string Name)
        {
            List<TestClass> draft = new List<TestClass>();
            string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            string sqlquery = "SELECT UserID, Name, Mobile, Age, Date where charindex(@name, Name)= 1 From tbluser";
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
            SqlDataReader sdr = sqlcomm.ExecuteReader();
            while (sdr.Read())
            {
                draft.Add(new TestClass()
                {
                    UserId = sdr.GetString(0),
                    Name = sdr.GetString(1),
                    Mobile = sdr.GetString(2),
                    Age = sdr.GetInt32(3),
                    Date = sdr.GetDateTime(4)
                });
            }
            return Ok(draft);
        }


Class:

[DataContract]
    public class TestClass
    {
        [DataMember(Order=1)]
        public string UserId { get; set; }
        [DataMember(Order = 2)]
        public string Name { get; set; }
        [DataMember(Order = 3)]
        public string Mobile { get; set; }
        [DataMember(Order = 4)]
        public int Age { get; set; }
        [DataMember(Order = 5)]
        public DateTime Date { get; set; }
    }
dotnet-csharpdotnet-aspnet-webapi
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

mS-dotcom avatar image
0 Votes"
mS-dotcom answered

can you use return Ok(new myModel=draft)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered Reborn-0047 commented

The DataReader reads the response. The sql string to SqlCommand and the command parameters are used write to the database. You typically would execute an insert or update sql command

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so i need to my sql query to update or insert?

0 Votes 0 ·
LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @Reborn-0047,
You can try using the SqlDataAdapter.InsertCommand property:Gets or sets a Transact-SQL statement or stored procedure to insert new records into the data source.
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter.insertcommand?view=dotnet-plat-ext-6.0

  // Create the InsertCommand.
     command = new SqlCommand(
         "INSERT INTO Customers (CustomerID, CompanyName) " +
         "VALUES (@CustomerID, @CompanyName)", connection);
    
     // Add the parameters for the InsertCommand.
     command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
     command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
    
     adapter.InsertCommand = command;

Best regards,
Lan Huang


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.