Simple database operation with ADO (i)

Summary: The next few blogs will talk about how to use ADO to implement simple database operations, including additions and deletions. First, the basic database operation is introduced, and then explained by an example, this example will read a data table to WinForm, and then on the WinForm, there are some buttons and text boxes, through the implementation of the database contents of the deletion and modification of the operation. I personally compare dishes, so the record of detail, talk also wordy, so may write a few to introduce the end.

　　I am a rookie, just contact. NET two weeks, writing something is also copy their own video of the teacher taught things, the purpose is mainly to consolidate their own study, of course, if it is just to help the same as I started rookie, then I am very happy. Gossip Less, then I began.

Yesterday wrote an essay on the simplest three-story, but, for the operation of the database inside only with a sqlhelper a pen, as to how this sqlhelper is achieved, May be a lot of new contact with the. NET one or two days of the students or the circle, so today to borrow this example, a detailed talk about the simple database operation of ADO. Huh? What the? You want to ask the complex. I'm sorry, I want to ask the complex please go away, why? Because I wouldn't!

　　Now I'm officially starting, haha!

Part I: Connecting to a database

　1. Start by talking about the basic steps of connecting to a database ha:

(1) Create connection string:

There are two ways to create a connection string, respectively, for the different ways of logging in to the database, I am talking about the SQL Server database I use HA, the other database should be similar, I have not tried, haha!

The first type: Windows logon

This way should be written like this:

string " Data Source = acer-pc; Initial Catalog = database name; intergrated Security = True"

What the? You do not know what is Windows login, OK, when you log in with this image, you are the Windows logon method:

The second type: SQL Account login (usually with SA account login)

That's how it's written.

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

Where the data Source is written in the address of your database, you even the database, the local word, here to write localhost\127.0.0.1\ server name (in the second box of content) \ or directly hit a dot (.), which can be written in several ways; Initial Catalog after the name of the database can be, and then you can read it.

(2) Creating a Connection object

Create connection object need to use this thing = = "SqlConnection ( This class of space did not introduce, need SHIFT + ALT + F10 introduced ), how to write? Look underneath.

1 using New SqlConnection (constr)) 2 {3     4 }    

Con is the connection object.

(3) Open connection

(4) Database operations

(5) Close the connection and release the resources

Look directly at the code:

  using New SqlConnection (CONSTR)) {    con. Open (); // Open Connection    Console.WriteLine (" Open Connection succeeded ");    Con. Close (); // Close Connection  }                  

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

usingSystem;usingSystem.Collections.Generic;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceado801am_connectsql{classProgram {Static voidMain (string[] args) {            stringConstr ="Data Source = acer-pc;initial Catalog = firstdb;integrated Security = True"; using(SqlConnection con =NewSqlConnection (CONSTR)) {con.                Open (); Console.WriteLine ("Connection Successful"); Con.            Close ();            } console.readkey (); Console.WriteLine ("Close Connection"); }    }}

Part Two, database operations

The database Operation section is followed by the fourth step above, where a few steps are subdivided, see below:

1. Writing SQL statements

What this means, is actually to write a sql = "XXX", for example, you want to query all the contents of a table, then you will write:

string " SELECT * FROM tbxxx table ";

That's it.

2. Create an object that executes an SQL statement

This and create the connection object one thing, need to use a This = = "SqlCommand ( This class of space did not introduce, need SHIFT + ALT + F10 Introduction ), how to write?" Or look at the following:

using New SqlCommand (Sql,con))// two parameters {}

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

3. Execute SQL statements

How do you do that? Call the method in SqlCommand!

As far as there are several methods can be called, it is necessary to tell the details, while I still remember to live, quickly recorded down, haha, do not suddenly forget the day, after all, watching the video without making notes.

There are three methods available, namely:

(1) ExecuteNonQuery (): How does this approach work, and does all the SQL statements work? Obviously, No. This method, he will return you with an int type value (that is, an integer), what does this integer code table mean? This integer represents the number of rows that your SQL statement has changed for the contents of the table; for example, But Ah, what would he give you if you were doing the data-checking? For example: you have to look for a person named Chrishtheyouth, then what does he give you, The answer is:-1, why? Because, the data in this table does not change Ah, half a row has not changed, expect him to give you what! So, we can use this method when we execute an Add, delete, or change SQL statement, and the following two methods are available for the operation of the check.

(2) executescalar (); it's written in this book. = = "Returns an object type that returns the value of the first column in the first row of the result set . What do you mean? I understand that, for example, you check a table there is no one, if found, he returned to this person in the column of the value of the first field (usually ID and so on), so Ah, this statement is more for you to check the content of only one, such as login, you check someone in the table, The result is to find out the person in this column, then he will return this column of this ID to you.

For an example, look at this SQL statement:

Select  from Tbareas

This statement in the database to find the content is this, look at the picture:

We use ExecuteScalar () to obtain the first row of the first line of the query results, that is, this;

Look at one more:

Select  from where ' Tianjin City '

The query results are:

and the ExecuteScalar () returned to the Areaid. That should be understood. Look at the third one:

(3)Excutereader (): The last one is about the method used when querying a single record, so what if I want to query all the data in a table and show him? Haha, it is necessary to use the Excutereader () method here. This method returns a typed DataReader object, you check the time, no matter what found, you return a DataReader, but, do not think this DataReader inside there is data, there is nothing?? AH? Nothing, what are you doing back here for me? Don't worry, listen to me, in fact, the contents of your query are stored in the database memory, but the contents of this inside how do you take it? Through the DataReader to take, this DataReader Ah, it is not a bag, in the inside loaded things, it is not a bag, he is a person, is a helper, is a link, you can only through the help of this thing, you can get your query data from the database memory. So, you should understand. What?? Still do not understand, then I have no idea, recommend to see this blog: http://www.cnblogs.com/net515/archive/2012/06/14/2549267.html

The above is the database operation of the three methods. Next, I would like to talk about how this more complex Excutereader () is used, mainly to deepen their understanding and impression of it.

usage of Excutereader ():

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

usingSystem;usingSystem.Collections.Generic;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceado801am_connectsql{classProgram {Static voidMain (string[] args) {            stringConstr ="Data Source = acer-pc;initial Catalog = firstdb;integrated Security = True";//Database connection String            using(SqlConnection con =NewSqlConnection (CONSTR))//connecting to a DB instance            {                stringsql ="SELECT * from Tbclass";//SQL statement String                using(SqlCommand cmd =NewSqlCommand (Sql,con))//executing an instance of a SQL statement{con. Open ();//Open a database connection                    using(SqlDataReader reader =cmd. ExecuteReader ()) {//The result returned by the query is an object of type SqlDataReader, which is the reader//determine if there is data in the query results                        if(Reader. HasRows)//Reader. The return value of the hasrows is a bool type variable, with the data returning true, and the countless numbers returning false                        {                             while(Reader. Read ())//Reader. Read () places the cursor read cursor on the first row of the table, and then moves one line down each time, until all rows are traversed, returning false                            {                                 for(inti =0; I < reader. FieldCount; i++) {Console.Write (Reader[i]+"       "); } Console.WriteLine ();//line Break} console.readkey (); }                        Else{Console.WriteLine ("No data found"); }                                       }                                        }            }        }    }}

Then look at the results and the contents of the table are consistent:

Obviously, the query results are consistent.

Here are a few points about this program:

(1) Reader. HasRows: The function of this method is to first look at the database has no query to the data you need, there is a return true, no return false;

(2) Reader. Read (): This method is quite the same as a pointer, it first points to the first line, and then moves backwards, the loop moves once, referring to that line, you can read the row of data, instruct all lines are finished, it returns a false, and then exits the loop.

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

In the above example, instead of using a for loop to fetch individual data from a row, the data for each location is taken out directly, so that the data type of each column needs to be guided as follows:

   while (reader. Read ())  {                                       Console.Write (reader. GetInt32 (0"   ");        Console.Write (reader. GetString (1"   ");        Console.Write (reader. GetString (2"    ");        Console.WriteLine (); // line Break     }  console.readkey ();

Run results

This reader. GetXXX () There is a problem to note, that is, if the data table data is NULL, then the exception will be reported, so when using this need to make a judgment on whether null, the method is as follows:

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

To determine whether NULL, if NULL, the output is null, if it is not NULL, then the output exists, I modified the table just now, there are several values are empty, and then look at the results of the query:

Tables in the database:

A, the result of adding a null check

B. The result of not adding a checksum:

Well, the operation of the ADO database is here today, and the rest of the evening.

Write things relatively shallow, no content, too much food, there are any mistakes in the place, hope to see the students can leave a message to tell, thank you!

qq:3074596466

Simple database operation with ADO (i)