Question

Write a robust ADO.NET connectIon-oriented code snippet which inserts a record (with parameters coming from the user, assume these are already stored in some variables) into the following table: StudentID, Name, YearOfBirth). Make sure you avoid SQL injection type attacks by using a parameterized SQL query. StudentID is an automatically generated column, provided by the database.

Answer 1

try{

//create  object  of Connection 
SqlConnection conn = new SqlConnection();

// Set Connection String property of Connection object
conn.ConnectionString = "Data Source=<Enter your Own>;Initial Catalog=<Database Name> ;IntegratedSecurity=True";

//Open Connection
conn.Open();

//Create object of Command Class
SqlCommand cmd = new SqlCommand();

//set Connection Property  of  Command object
cmd.Connection = conn;
//Command type for command object
//1.StoredProcedure
//2.TableDirect
//3.Text   (By Default)

cmd.CommandType = CommandType.Text;

//Set Command text Property of command object

cmd.CommandText = "Insert into <TableName> (StudentID,Name,YearOfBirth) values ('@id','@name',@yob)";

//Assign values as `parameter`. It avoids `SQL Injection`
//Make sure Values have proper Validation before inserting

cmd.Parameters.AddWithValue("id", <Variable>);
cmd.Parameters.AddWithValue("name", <Variable>);
cmd.Parameters.AddWithValue("yob", <Variable>);
//For Update,Delete,Insert Query
cmd.ExecuteNonQuery(); 

//THIS IS MOST IMPORTANT FROM PROGRAMMING POINT OF VIEW
conn.Close();

}catch(Exception ex){

conn.Close();

}