Simple database operation of ado.net (1)

Simple database operation of ado.net (1)

Abstract: The next few blogs will talk about how to use ado.net to implement simple database operations, including additions, deletions, and other content. 1. we will introduce the basic database operation, and then explain it with an example. This example will read a data table to winform, and then there are some buttons and text boxes on winform, through which the content in the database can be increased Operation of deletion and modification. I personally compare dishes, so my records are more detailed and my words are verbose, so I may write a few articles before I can finish the introduction.

I am a rookie. I have just been in contact with .net for two weeks. What I write is to copy what the teacher teaches in the video I watched. The main purpose is to consolidate my learning. A beginner, I am also very happy. Talk less, then I will start.

  Yesterday I wrote an essay about the simplest three-layer essay, but I only used one SqlHelper for the operation of the database inside. As for how this SqlHelper is implemented, many students who have just contacted .net for a day or two I am still in the circle, so today I will take this example to talk about the simple database operation of ado.net in detail. what? what? You want to ask complicated. Sorry, if you want to ask complicated please go away, why? Because neither will I!

Now I officially started, haha!

Part 1: Connect to the database

 1. Let me talk about the basic steps to connect to the database:

(1) Create a connection string:

There are two ways to create a connection string, which are applicable to different ways to log in to the database. I am talking about the sqlserver database I use. Other databases should be similar. I haven't tried it, haha!

The first type: Windows login

This way should be written like this:

 string constr = "Data Source = acer-pc; Initial Catalog = database name; Intergrated Security = true"; 

what? You don’t know what is the login method of windows, well, when you log in with this picture, you are the windows login method:

The second type: sql account login (usually the sa account is used to log in)

Just write this way

string constr = "Data Source = acer-pc; Initial Catalog = database name; UserID = sa (account user name) Passqord = 168168 (password)";

Among them, the address of your database is written after Data Source, and the database you are connected to, if this machine, write localhost\127.0.0.1\server name (the content in the second box in the above figure)\or directly Type a dot (.), these types of writing are ok; just write the name of the database after Initial Catalog, and you can understand it later.

(2) Create a connection object

To create a connection object, you need to use this stuff == "SqlConnection ( the space of this class is not introduced, it needs to be introduced by shift + alt + f10 ), how to write it? look down

1 using (SqlConnection con = new SqlConnection(constr))
2 {
3     
4}    

con is the connection object.

(3) Open the connection

(4) Database operation

(5) Close the connection and release resources

Look at the code directly:

  using (SqlConnection con = new SqlConnection(constr))
 {
    con.Open();//Open the connection
    Console.WriteLine("Open the connection successfully");
    con.Close();//Close the connection
 }     
             

You can create a new console program and write this code in the main function to try it out. Here is my code and result:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ado801am_ConnectSql
{
    class Program
    {
        static void Main(string[] args)
        {
            string conStr = "Data Source = acer-pc;Initial Catalog = FirstDB;Integrated Security = true";

            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                Console.WriteLine("Connected successfully");
                con.Close();
            }
            Console.ReadKey();
            Console.WriteLine("Close connection");
        }
    }
}

The second part, database operation

  The database operation part is connected to the fourth step above, here is a few steps, see below:

1. Write sql statement

  What does this mean? In fact, first write sql = "XXX". For example, if you want to query all the contents of a table, then you have to write:

string sql = "select * from TbXXX table";

That's it.

2. Create an object that executes the sql statement

  This is the same thing as creating a connection object, you need to use this == "SqlCommand ( the space for this class is not introduced, you need to introduce shift + alt + f10 ), how to write it? Still look at the following:

 using (SqlCommand cmd = new SqlCommand(sql,con))//Two parameters
{

}

Here are two parameters, don't make a mistake.

3. Execute sql statement

  How to execute it? Call the method in SqlCommand!

As for how many methods can be called here, I have to talk about them in detail. While I can still remember, I quickly write them down. Haha, don't forget one day, after all, I didn't take notes when I watched the video.

There are three methods available, namely:

(1) ExecuteNonQuery(): How to use this method, can it be used to execute all sql statements? Obviously, not possible. This method, after it is executed, will return you an int type value (that is, an integer), what does this integer code table mean? This integer means: your sql statement changes the content of this table Number of rows; for example, if you insert three records into the XXX table, then this buddy will return an integer 3 for you. If you change two pieces of data in the table, this buddy will throw a 2 for you, and you delete it. After 10 pieces of data, he will give you a 10, but ah, if you perform the operation of checking the data, what will he give you? For example: if you want to look up a person named ChrishTheYouth, what will he give you ? The answer is: -1, why? Because the data in this table has not changed, half of the line has not changed, expect him to give you something! Therefore, we can only use this method when executing SQL statements that are added, deleted, or modified. The check operation can only use the following two methods.

(2) ExecuteScalar(); is written in this book == "Returns an object type, returns the value of the first row and first column of the result set, what does it mean? My understanding is this. For example, if you check a table if there is someone in it, if you find it, he will return the value of the first field in the column where the person is (usually id or the like), so ah, this A sentence is mostly used for only one content you check. For example, when you log in, you check whether a person is in the table, and if the person is found, then he will return the id in this column to you.

For example, look at this SQL statement:

select count(*) from TbAreas

The content of this sentence found in the database is like this, look at the picture:

We use ExecuteScalar() to get the first row and first column of the query result, which is 54;

Look at one more:

select * from TbAreas where TbAreas.areaName ='Tianjin City'

The query result is:

And what is returned with ExecuteScalar() is this areaId. So you should understand it. Looking at the third one:

(3) ExcuteReader(): The last one talked about the method used when querying a single record, so if I want to query all the data in a table and display it, what should I do? Haha, then you have to use the ExecutiveReader() method here. This method returns a typed DataReader object. When you check it, no matter what you find, it will return you a DataReader. However, don't think that there is data in this DataReader. There is nothing here? ? what? Nothing, then why are you returning this to me? Don't worry, listen to me, in fact, the content you query is stored in the memory of the database, but how do you get the content in this? Just get it through the DataReader, this DataReader, it’s not a bag, it’s not a bag, it’s not a bag, it’s a person, it’s a helper, it’s a link, you can only query you with the help of this thing The data is taken from the database memory. That said, you should understand. What? ? I don’t understand yet, then I’m not in a good position. I recommend reading this blog: http://www.cnblogs.com/net515/archive/2012/06/14/2549267.html

The above is the three methods of database operations. Next, I want to talk about how to use this more complicated ExecutiveReader(), mainly to deepen my understanding and impression.

Usage of ExcuteReader():

Still write an example, combined with a simple example, how to use this thing, how to check the data, how to take out the found data. Look at the code first:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ado801am_ConnectSql
{
    class Program
    {
        static void Main(string[] args)
        {
            string conStr = "Data Source = acer-pc;Initial Catalog = FirstDB;Integrated Security = true";//Database connection string
            using (SqlConnection con = new SqlConnection(conStr))//Connect to the database instance
            {
                string sql = "select * from TbClass";//sql statement string
                using (SqlCommand cmd = new SqlCommand(sql,con))//Execute sql statement example
                {
                    con.Open();//Open the database connection
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                       //The result of the query is an object of the SqlDataReader type, and this object is the reader
                       //Determine whether there is data in the query result
                        if (reader.HasRows)//The return value of reader.HasRows is a bool type variable, if there is data, it returns true, and if there is no data, it returns false
                        {
                            while (reader.Read())//reader.Read() puts the cursor read by the cursor on the first line of the table, and then moves down one line each time through the loop, until all lines are traversed, return false
                            {
                                for (int i = 0; i <reader.FieldCount; i++)
                                {
                                    Console.Write(reader[i] + "");
                                }
                                Console.WriteLine();//New line
                            }
                            Console.ReadKey();
                           
                        }
                        else
                        {
                            Console.WriteLine("No data found");
                        }                   

                    }
                        
                }

            }
        }

    }
}

Then check whether the result is consistent with the content in the table:

Obviously, the query results are consistent.

  Here are a few specific points in this program:

(1) reader.HasRows: The function of this method is to first check whether the data you need is queried in the database, return true if it has, and false if it is not;

(2) reader.Read(): This method is equivalent to a pointer. It initially points to the first row, and then moves backward in turn, moving once in a loop. When you point to that row, you can read the data of that row. When all the lines are pointed out, it returns false, and then exits the loop.

(3) reader.FieldCount: The function of this thing is == "Get the number of columns in the current row;

In the above example, instead of using the for loop to fetch each data in a row, you can directly fetch the data in each position. In doing so, you need to guide the data type of each column, as shown below:

  while (reader.Read())
  {
                               
        Console.Write(reader.GetInt32(0) + "");
        Console.Write(reader.GetString(1) + "");
        Console.Write(reader.GetString(2) + "");

        Console.WriteLine();//New line
    }
  Console.ReadKey();

The results of the operation are shown in the figure:

There is a problem with the writing of this reader.GetXXX() that needs attention, that is, if the data in the data table is null, then an exception will be reported. Therefore, when using this, you need to make a judgment on whether it is empty or not. The judgment method as follows:

 Console.Write( reader.IsDBNull(2)?"NULL": reader.GetString(2));

First judge whether it is null, if it is null, then output NULL, if it is not null, then output the value that exists, I modified the table just now, and some of the values ​​are empty, and then look at the results of the query:

Tables in the database:

A. Added the result of null check

B. Do not add the result of verification:

Okay, that's all about the operation of the ado.net database today, and I will write the rest of the evening.

The writing is relatively shallow, not much content, too food, there are any mistakes, students who hope to see can leave a message to let us know, thank you!

QQ: 3074596466

Reference: https://cloud.tencent.com/developer/article/1503482 Simple database operation of ado.net (1)-Cloud + Community-Tencent Cloud