c# - Paramterized Queries -

i new visual c#, , confused on how write parameterized queries. here code without them,

using system; using system.windows.forms; using system.data.sqlclient;  namespace insert_data {     public partial class form1 : form     {         private void button1_click(object sender, eventargs e)         {             sqlconnection con = new sqlconnection("data source=ztabassum\\sqlexpress01;initial catalog=introdatabase;integrated security=true");             con.open();             sqlcommand sc = new sqlcommand("insert employee values ('"+ textbox1.text +"' , " + textbox2.text + ", '" + textbox3.text + "',  " + textbox4.text + ", " + textbox5.text + ");", con);             int o = sc.executenonquery();             messagebox.show(o + ":record has been inserted");             con.close();         }     } } 

i not sure on how write parameterized queries each of text boxes.

i added notes in code along best practices recap after.

// best practice - use meaningful method names private void buttonsaveemployee_click(object sender, eventargs e) {     // best practice - wrap database connections in using block closed & disposed in event of exception     // best practice - retrieve connection string name app.config or web.config (depending on application type) (note, requires assembly reference system.configuration)     using(sqlconnection con = new sqlconnection(system.configuration.configurationmanager.connectionstrings["myconnectionname"].connectionstring))     {         // best practice - use column names in insert statement not dependent on sql schema column order         // best practice - use parameters avoid sql injection attacks , errors if malformed text used including single quote sql equivalent of escaping or starting string (varchar/nvarchar)         // best practice - give parameters meaningful names variables in code         sqlcommand sc = new sqlcommand("insert employee (firstname, lastname, dateofbirth /*etc*/) values (@firstname, @lastname, @dateofbirth /*etc*/)", con);          // best practice - specify database data type of column using         // best practice - check valid values in code and/or use database constraint, if inserting null use system.dbnull.value         sc.parameters.add(new sqlparameter("@firstname", sqldbtype.varchar, 200){value = string.isnullorempty(textboxfirstname.text) ? (object) system.dbnull.value : (object) textboxfirstname.text});         sc.parameters.add(new sqlparameter("@lastname", sqldbtype.varchar, 200){value = string.isnullorempty(textboxlastname.text) ? (object) system.dbnull.value : (object) textboxlastname.text});          // best practice - use correct types when specifying parameters, in case string converted datetime type before being assigned sqlparameter.value         // note - not robust way parse date user never notified in event of failure, purpose here show how use parameters of various types         datetime dob;         sc.parameters.add(new sqlparameter("@dateofbirth", sqldbtype.date){value = datetime.tryparse(textboxdateofbirth.text, out dob) ? (object) dob : (object) system.dbnull.value});          // best practice - open connection late possible unless need verify database connection valid , wont fail , proceeding code execution takes long time (not case here)         con.open();         int o = sc.executenonquery();         messagebox.show(o + ":record has been inserted");          // end of using block close , dispose sqlconnection         // best practice - end using block possible release database connection     } } 

---

best practice recap working ado.net

• wrap database connections in using block closed & disposed in event of exception. see using statement (c# reference) more information on using statements
• retrieve connection strings name app.config or web.config (depending on application type)
  • this requires assembly reference system.configuration
  • see connection strings , configuration files additional information on how structure configuration file
• always use parameters incoming values
  • avoid sql injection attacks
  • avoid errors if malformed text used including single quote sql equivalent of escaping or starting string (varchar/nvarchar)
  • letting database provider reuse query plans (not supported database providers) increases efficiency
• when working parameters
  • give sql parameters meaningful names variables in code
  • specify database data type of column using, ensures wrong parameter types not used lead unexpected results
  • validate incoming parameters before pass them command, there expression called garbage in garbage out. validate incoming values possible in stack
  • use correct types when assigning parameter values, example: not assign string value of datetime, instead assign actual datetime instance value of parameter
  • do not use method addwithvalue, main reason is easy forget specify parameter type or precision/scale when needed. additional information see can stop using addwithvalue already?
• when using database connections
  • open connection late possible , close possible. general guideline when working external resource
  • never share database connections (example: having singleton host shared database connection). have code create new database connection instance when needed , have calling code dispose of , "throw away" when done. reason
    1. most database providers have sort of connection pooling cheap in managed code
    2. it eliminates future errors if code starts working multiple threads