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

Question

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; }  
       }  

Answer 1

Hi @Reborn ,
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://learn.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.

Answer 2

can you use return Ok(new myModel=draft)

Answer 3

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