Detailed explanation of ADO.NET's implementation of add, delete, modify and query operations on SQL Server database-C#.Net Tutorial-php.cn

Detailed explanation of ADO.NET's implementation of add, delete, modify and query operations on SQL Server database

巴扎黑

Release： 2017-08-16 14:58:12

Original

2385 people have browsed it

This article mainly introduces the example of ADO.NET implementation of addition, deletion, modification and query of SQL Server database. It is of great practical value and friends in need can refer to it.

After understanding the introduction to ADO.NET in the previous article, we can perform basic operations such as adding, deleting, modifying, and querying the database! The following is the specific implementation of each operation.

First define the database connection object and connection string in the header of the custom class:

 string connectionString = "Data Source=SC-201607131829;Initial Catalog=Animal;Integrated Security=True";
  SqlConnection conn;

Copy after login

1. Database query operation returns a DataTable

 public DataTable doSelect()
    {

      string sql = "select * from detial";

      using (conn = new SqlConnection(connectionString))
      {

        conn.Open();

        SqlDataAdapter da = new SqlDataAdapter(sql, conn);

        DataSet ds = new DataSet();

        da.Fill(ds);  //填充DataSet

        return ds.Tables[0];

      }
    }

Copy after login

2. Database insertion operation, return Boolean value

public bool doInsert(string name, string skin, string weight)
    {

      string sql = "insert into detial(name,skin,weight)values(@name,@skin,@weight)";

      SqlParameter[] newAnimal = {
         new SqlParameter("name",name),
         new SqlParameter("skin",skin),
         new SqlParameter("weight",skin)
      };

      using (conn = new SqlConnection(connectionString))
      {
        SqlCommand com = new SqlCommand(sql, conn);
        try
        {
          if (newAnimal != null)
          {
            foreach (SqlParameter parameter in newAnimal)
            {
              com.Parameters.Add(parameter);

            }
          }
          conn.Open();

          int influence = com.ExecuteNonQuery();

          if (influence > 0)
          {

            return true;
          }
          else
          {

            return false;
          }
        }
        catch (Exception exception)
        {
          return false;
        }
      }
    }

Copy after login

3. Database deletion operation, Return a Boolean value

public bool doDelete(string name)
    {

      string sql = "delete from detial where name = @name";

      SqlParameter[] deleteParameter = { new SqlParameter("name", name) };

      using (conn = new SqlConnection(connectionString))
      {

        SqlCommand com = new SqlCommand(sql, conn);
        
        try
        {

          if (deleteParameter != null)
          {
            foreach (SqlParameter parameter in deleteParameter)
            {
              com.Parameters.Add(parameter);
            }
            
          }

          conn.Open();

          int influence = com.ExecuteNonQuery();

          if (influence > 0)
          {

            return true;
          }
          else
          {

            return false;
          }
        }
        catch (Exception exception)
        {
          return false;
        }
      }
    }

Copy after login

4. Database update operation, return a Boolean value

public bool doUpdate(string name , string skin) {

      string sql = "update detial set skin = @skin where name = @name";
      SqlParameter[] updateParameter = {
                    new SqlParameter("name",name),
                    new SqlParameter("skin",skin)
      };

      using (conn = new SqlConnection(connectionString)) {

        SqlCommand com = new SqlCommand(sql,conn);

          try {

            if (updateParameter != null) { 
              
              foreach(SqlParameter parameter in updateParameter){

                com.Parameters.Add(parameter);

              } 
            }

            conn.Open();

            int influence = com.ExecuteNonQuery();

            if (influence > 0)
            {

              return true;
            }
            else
            {

              return false;
            }
          
          }catch(Exception exception){

            return false;
          }
      }

    }

Copy after login

In order to prevent sql injection, the SqlParameter class is used.

The above is the detailed content of Detailed explanation of ADO.NET's implementation of add, delete, modify and query operations on SQL Server database. For more information, please follow other related articles on the PHP Chinese website!