A brief introduction to the operational processes and connections for SQL Server databases

Learn ADO, inevitably to deal with the database, for beginners, if not tidy up the entire process, then there may be a lot of problems, the following simple introduction of the database operation process.

1. We finally operate on a data table, before we manipulate the data sheet we need to identify the database we want to operate, because only the database is connected to the table stored in the database operation.

So there is the following code to connect to the database:

// Database connection String

String connstring = "Data source=.;i Nitial catalog=myschool;integrated security=true ";

server=.\sqlexpress; Database=myschool; User Id=sa; pwd=*****

server=.\sqlexpress; Database=myschool; Uid=sa; password=*****

        // Database Connection Connection Object , Connecting Channels

SqlConnection connection = new SqlConnection (connstring);

After the database connection is established, it is necessary to open the connection to really implement the database connection, otherwise the connection statement is just a statement and cannot play any role. So you need the following code:

connection.  Open (); Open Connection

2. What do we need to do when we successfully connect to the database?

we know that the operational database can be used SQL statement, SQL The statement is used to do the database additions, deletions, modifications, query operations. So we need to define a SQL statement that tells the computer what it should do. So the following statements are possible:

String sql = string.   Format ("SELECT count (*) from Admin WHERE loginid= ' {0} ' and loginpwd= ' {1} '", LogInId, loginpwd); Excutescalar excutenonquery ()

if (num!=0)

{

}

3. When I successfully connect to the database, also write a good SQL statement on the line? Of course not, because if we don't execute the SQL statement, it's just a statement, just like we wrote SQL in SQL SERVER . statement Instead of executing it does not play any role. So we need a command to "execute" theSQL statement, which is like SqlCommand to the image , It is a system-to-image, and we have to use it to instantiate a new pair of images first, so we have the following code:

SqlCommand command = new SqlCommand (sql, connection);//command is to instantiate the name of the image, SQL is a description of what needs to be done SQL statement, Connection is to show how to connect to the database

Command.commandtext=sql;

Command.connection=connection;

4 . It's not enough to have a command , because he's just a pair of images, and the appropriate way to invoke it is to "look at it . " method ". Here's how:

A: if it is to make a query, that is to find the value I want to get the specific data (not the number of eligible is not an aggregate function , nor is it a single value), use SqlCommand 's ExecuteReader () method returns a SqlDataReader object that is the object of the Read () method can return a value of one row at a time, the value of the row is determined by the SQL Statement of Select to decide, Select The more fields you query later, the more values this method will get, which means the more data is returned for this row!

B : If the data is added, deleted, modified, then the call will be SqlCommand of the ExecuteNonQuery () method, which can perform an operation while returning both the affected and the number of rows. What exactly is the operation? This is related to your SQL statement. when the SQL statement is added, it executes. If the deletion is written, it executes the delete. Returns 1 if the deletion and modification are not added

C : If you just return the number of conditions that are met or return the value of the first column in the first row, you will typically use SqlCommand of the ExecuteScalar () method, such as SQL The statement contains aggregate functions and so on. Its return type is object and requires a forced type conversion because it is possible to convert an object to a different type, so convert is generally used to convert. ( All the data in the database is a value type, so it needs to be boxed after it is read, and the unboxing can only be split into the original data type ) .

5. So how do we know that this operation has been successfully executed? This time we need a method to return the corresponding value,executescalar() returns a single value, such as an aggregate function or a single column of a value, the type of this value is object, you need to do a forced type conversion , ExecuteNonQuery () returns the number of rows affected , which can be an integer value, so we often see code like this:

count = (int) command.  ExecuteScalar (); Execute Query statement

but ExecuteReader () returns multiple values, so we need to use loops to receive them, usually by assigning the values we read to the corresponding string variables, or putting them in the relevant controls to display them , so we might see code like this:

while (Datareader.read ())

{// assigns a value to a variable

Gradename = (string) datareader[0];

}

6. for executescalar() and executenonquery() Next we can make a judgment on the value returned by the method, so we often see this code:

if (result== 1) @ @rowcount

{

MessageBox.Show (" added successfully! ");

}

Else

{

MessageBox.Show (" add failed! ");

}

And we often get in touch with the applications that are exchanged with the database, so we can use a simple diagram to illustrate how the application deals with the database:

        The above diagram clearly shows the five objects each applying their posts and their relationship, if there is any problem you can contact me!