intern_adodotnet_sqlcommand.md - brainchildservices/curriculum GitHub Wiki

https://dotnettutorials.net/lesson/ado-net-sqlcommand-class/

• SqlCommand():

  • This constructor is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class..

• SqlCommand(string cmdText):

  • It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query. Here, the cmdText is the text of the query that we want to execute.

• SqlCommand(string cmdText, SqlConnection connection):

  • It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query and a System.Data.SqlClient.SqlConnection. Here, the cmdText is the text of the query that we want to execute and the parameter connection is the connection to an instance of SQL Server.

  • Example: Here getting whole movies from database saved into sqlSelect and connection is the object of SqlConnection with connectionstring as its parameter

                       string sqlSelect = "SELECT * FROM Movies";
                       SqlCommand command = new SqlCommand(sqlSelect, connection);
    

• Methods of SqlCommand Class in C#

  • Cancel(): This method tries to cancel the execution of a System.Data.SqlClient.SqlCommand.

  • Clone(): This method creates a new System.Data.SqlClient.SqlCommand object is a copy of the current instance.

  • CreateParameter(): This method creates a new instance of a System.Data.SqlClient.SqlParameter object.

  • ExecuteReader(): This method Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection and builds a System.Data.SqlClient.SqlDataReader.

  • ExecuteNonQuery(): This method executes a Transact-SQL statement against the connection and returns the number of rows affected.

• ExecuteReader method of SqlCommand Object in C#:

  • As we already discussed this method is used to send the CommandText to the Connection and builds a SqlDataReader. When your SQL statement returns more than a single value (for example rows of data), then you need to use the ExecuteReader method.

  • The following example uses the ExecuteReader method of the SqlCommand object(here command) to executes the SQL statement which returns multiple rows of data.

                           SqlDataReader reader = command.ExecuteReader();
    
                           while (reader.Read())
                           {
                               MovieList.Add(new Movie(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.                  GetString(5)));
                           }
    

• Understanding the ADO.NET SqlCommand Object in C#:

  • In our example, we are creating an instance of the SqlCommand by using the constructor which takes two parameters as shown in the below image. The first parameter is the command text that we want to execute, and the second parameter is the connection object which provides the database details on which the command is going to execute.

  • 

  • You can also create the SqlCommand object using the parameterless constructor, and later you can specify the command text and connection using the CommandText and the Connection properties of the SqlCommand object as shown in the below example.

                       string ConString = "data source=.; database=StudentDB; integrated security=SSPI";
                       using (SqlConnection connection = new SqlConnection(ConString))
                       {
                           // Creating SqlCommand object 
                           SqlCommand cmd = new SqlCommand();
                           cmd.CommandText = "select * from student";
                           cmd.Connection = connection;
       
                           // Opening Connection  
                           connection.Open();
       
                           // Executing the SQL query  
                           SqlDataReader sdr = cmd.ExecuteReader();
                           while (sdr.Read())
                           {
                               Console.WriteLine(sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                           }
                       }
    

  • Best way to create SqlCommand object is given below. Here 'using' directive is not used.

                       string sqlConnectString = "Data Source=(localdb)\\BookMyShowDB;Initial Catalog=BookMyShow;Integrated Security=True;";
                       SqlConnection connection = new SqlConnection(sqlConnectString);
                       connection.Open();
    
                       string sqlSelect = "SELECT * FROM Movies WHERE MovieID=@MovieID";
                       SqlCommand command = new SqlCommand(sqlSelect, connection);
    

• ExecuteNonQuery Method of ADO.NET SqlCommand Object in C#:

  • When you want to perform Insert, Update or Delete operations and want to return the number of rows affected by your query then you need to use the ExecuteNonQuery method of the SqlCommand object in C#.