Insert into C with SQLCommand

Question

What s the best way to INSERT data into a database   This is what I have but it s wrong    cmd CommandText    INSERT INTO klant klant id naam voornaam  VALUES  param1  param2  param3     cmd Parameters Add new SqlParameter   param1   klantId    cmd Parameters Add new SqlParameter   param2   klantNaam    cmd Parameters Add new SqlParameter   param3   klantVoornaam      The function add data into the listBox   http   www pictourl com viewer 37e4edcf  link is dead   but not into the database    http   www pictourl com viewer 4d5721fc  link is dead   The full function   private void Form1 Load object sender  EventArgs e                    conn2   new SqlConnection        conn2 ConnectionString   ConfigurationManager ConnectionStrings  connSpionshopString   ConnectionString     private void button2 Click object sender  EventArgs e                 string sqlCmd    SELECT naam voornaam klant id FROM klant        SqlCommand cmd   new SqlCommand sqlCmd  conn2        conn2 Open         using SqlDataReader reader   cmd ExecuteReader                  while  reader Read                          listBox2 Items Add reader GetString 0          reader GetString 1            reader GetInt16 2                                conn2 Close       private void button4 Click object sender  EventArgs e        int klantId   Convert ToInt32 textBox1 Text       string klantNaam   textBox2 Text      string klantVoornaam   textBox3 Text       conn2 Open         SqlCommand cmd   new SqlCommand        cmd Connection   conn2      cmd CommandText    INSERT INTO klant klant id  naam  voornaam    VALUES  param1  param2  param3         cmd Parameters AddWithValue   param1   klantId       cmd Parameters AddWithValue   param2   klantNaam       cmd Parameters AddWithValue   param3   klantVoornaam        cmd ExecuteNonQuery          conn2 Close

User · Answer

using  SqlConnection connection   new SqlConnection connectionString          connection Open         using  SqlCommand command   connection CreateCommand                    command CommandText    INSERT INTO klant klant id naam voornaam  VALUES  param1  param2  param3               command Parameters AddWithValue   param1   klantId              command Parameters AddWithValue   param2   klantNaam              command Parameters AddWithValue   param3   klantVoornaam               command ExecuteNonQuery

User · Answer

Use AddWithValue    but be aware of the possibility of the wrong implicit type conversion  like this   cmd Parameters AddWithValue   param1   klantId       cmd Parameters AddWithValue   param2   klantNaam       cmd Parameters AddWithValue   param3   klantVoornaam

User · Answer

Try confirm the data type  SqlDbType  for each parameter in the database and do it this way    using SqlConnection connection   new SqlConnection ConfigurationManager ConnectionStrings  connSpionshopString   ConnectionString                  connection Open                string sql     INSERT INTO klant klant id naam voornaam  VALUES  param1  param2  param3                using SqlCommand cmd   new SqlCommand sql connection                                    cmd Parameters Add   param1   SqlDbType Int  value   klantId                      cmd Parameters Add   param2   SqlDbType Varchar  50  value   klantNaam                    cmd Parameters Add   param3   SqlDbType Varchar  50  value   klantVoornaam                    cmd CommandType   CommandType Text                    cmd ExecuteNonQuery

User · Answer

You can use dapper library   conn2 Execute   INSERT INTO klant klant id naam voornaam  VALUES   p1  p2  p3                     new   p1   klantId  p2   klantNaam  p3   klantVoornaam       BTW Dapper is a Stack Overflow project     UPDATE  I believe you can t do it simpler without something like EF  Also try to use using statements when you are working with database connections  This will close connection automatically  even in case of exception  And connection will be returned to connections pool   private readonly string  spionshopConnectionString   private void Form1 Load object sender  EventArgs e                     spionshopConnectionString   ConfigurationManager            ConnectionStrings  connSpionshopString   ConnectionString     private void button4 Click object sender  EventArgs e        using var connection   new SqlConnection  spionshopConnectionString                  connection Execute   INSERT INTO klant klant id naam voornaam                                 VALUES   klantId  klantNaam  klantVoornaam                                  new                                          klantId   Convert ToInt32 textBox1 Text                                          klantNaam   textBox2 Text                                         klantVoornaam   textBox3 Text

User · Answer

public class customer       public void InsertCustomer string name int age string address                   create and open a connection object         using SqlConnection Con DbConnection GetDbConnection                             1  create a command object identifying the stored procedure             SqlCommand cmd   new SqlCommand  spInsertCustomerData  Con                   2  set the command object so it knows to execute a stored procedure             cmd CommandType   CommandType StoredProcedure               SqlParameter paramName   new SqlParameter                paramName ParameterName     nvcname               paramName Value   name              cmd Parameters Add paramName                SqlParameter paramAge   new SqlParameter                paramAge ParameterName     inage               paramAge Value   age              cmd Parameters Add paramAge                SqlParameter paramAddress   new SqlParameter                paramAddress ParameterName     nvcaddress               paramAddress Value   address              cmd Parameters Add paramAddress                cmd ExecuteNonQuery

User · Answer

you can use implicit casting AddWithValue  cmd Parameters AddWithValue   param1   klantId   cmd Parameters AddWithValue   param2   klantNaam   cmd Parameters AddWithValue   param3   klantVoornaam     sample code   using  SqlConnection conn   new SqlConnection  connectionString           using  SqlCommand cmd   new SqlCommand                    cmd Connection   conn          cmd CommandType   CommandType Text          cmd CommandText     INSERT INTO klant klant id naam voornaam                               VALUES  param1  param2  param3               cmd Parameters AddWithValue   param1   klantId             cmd Parameters AddWithValue   param2   klantNaam             cmd Parameters AddWithValue   param3   klantVoornaam              try                       conn Open                cmd ExecuteNonQuery                       catch SqlException e                        MessgeBox Show e Message ToString     Error Message

User · Answer

You should avoid hardcoding SQL statements in your application  If you don t use ADO nor EntityFramework  I would suggest you to ad a stored procedure to the database and call it from your c  application  A sample code can be found here  How to execute a stored procedure within C  program and here http   msdn microsoft com en-us library ms171921 28v vs 80 29 aspx

[c#] Insert into C# with SQLCommand

Examples related to c#

Examples related to .net

Examples related to ado.net

Examples related to sqlcommand