C ා connect to SQL Server database

Executing SQL statements: Command object

1.Command object overview

The    command object is a data command object. Its main function is to send SQL statements of query, update, delete and modify operations to the database. Command objects are mainly in the following ways.
                       .
                         . For example, both the Access database and My SQL database are databases exposed by OLEDB.
                        . If some databases do not provide the corresponding linker, you can use OdbcCommand after configuring the ODBC connection.
                        .

2. Set data source type

The   Command object has three important properties: Connection, CommandText, and CommandType. The Connection property is used to set the SqlConnection used by the SqlCommand. The CommandText property is used to set the SQL statement or stored procedure to execute on the data source. The CommandType property is used to set the type of the specified CommandText. The value of CommandType property is CommandType enumeration value. CommandType enumeration has 3 enumeration members, which are described as follows.
      StoredProcedure: the name of the stored procedure.
      TableDirect: the name of the table.
     Text: SQL Text command.
   if you want to set the type of the data source, you can do so by setting the CommandType property.

3. Execute SQL statement

The    Command object needs to get the SQL statement to be executed, and submit the SQL statement to the database by calling various methods provided by the class. The following details several methods of executing SQL statements in SqlCommand object.

   1.ExecuteNonQuery method
                          .

        private void button1_Click(object sender, EventArgs e)
        {
            string connString = "server=.;database=denglu;uid=test;pwd=test;connect timeout=5";
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();

            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = sqlconn;
            sqlcmd.CommandText = "update Users set password=888 where username='Ha ha ha'";
            sqlcmd.CommandType = CommandType.Text;

            //int i = Convert.ToInt32(sqlcmd.ExecuteNonQuery()); //ExecuteNonQuery returns the number of rows affected
            //MessageBox.Show(i.ToString());
        }

   2.ExecuteReader method
   execute the SQL statement and generate an instance of the SqlDataReader object that contains the data.
   Code:

        private void button1_Click(object sender, EventArgs e)
        {
            string connString = "server=.;database=denglu;uid=test;pwd=test;connect timeout=5";
            SqlConnection sqlconn = new SqlConnection(connString);
            sqlconn.Open();

            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = sqlconn;
            sqlcmd.CommandText = "select password from users";
            sqlcmd.CommandType = CommandType.Text;

            //Instantiate a SqlDataReader object using the ExecuteReader method
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                listView1.Items.Add(sdr[0].ToString());
            }

            sqlconn.Dispose();
            button1.Enabled = false;
        }

Interface:

   3.ExecuteScalar method

   execute the SQL statement and return the first column of the first row in the result set
   Code:

        SqlConnection sqlConnection;
        private void Form2_Load(object sender, EventArgs e)
        {
            string connString = "server=.;database=TBWRIMS;uid=test;pwd=test;connect timeout=5";
            sqlConnection = new SqlConnection(connString);
            sqlConnection.Open();
        }

        private void btn1_Click(object sender, EventArgs e)
        {
            try
            {
                if (sqlConnection.State == ConnectionState.Open || txt1.Text != "")
                {
                    SqlCommand sqlCommand = new SqlCommand(); //***
                    sqlCommand.Connection = sqlConnection; //Set Connection property
                    sqlCommand.CommandText = "select count(*) from" + txt1.Text.Trim(); // Set CommandText property and SQL statement
                    sqlCommand.CommandType = CommandType.Text; //Set the CommandType property to Text to execute only SQL statements

                    int i = Convert.ToInt32(sqlCommand.ExecuteScalar()); //Using the ExecuteScalar method to get the number of data in the specified data table                   
                    lab2.Text = "There are:"+ i.ToString() + "Bar data";

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Interface:

33 original articles published, 21 praised, 10000 visitors+
Private letter follow

Tags: SQL Database Stored Procedure Linker

Posted on Fri, 31 Jan 2020 07:49:13 -0800 by nestorvaldez