Brief Introduction to ado.net (I)

Ado.net encapsulates some objects to connect C # To the database, which is actually a bridge.

The following describes how ado.net connects to the database through SQL Server.

First, let's take a look at creating a database in vs (vs has a built-in Database Server)

Open the server resource manager in the view and you will see it on the left of.

 

Right-click a database connection and create a database

 

". "Represents the local server, the new data name persondb, create a person persons table, the three field ID shaping automatically grow as the primary key, a personname, nvarchar (15), the name of the storage person, the age of the person who stores personal data. After the table is created, let's take a look at how to connect it to the program to add, delete, modify, and query the database.

Create a windowform program with the following interface:

 

Let's first implement the add operation, first reference the namespace: using system. Data. sqlclient;

First, connect to the database and create a connection object through sqlconnection.

Sqlconnection con = new sqlconnection (); // create a connection object

To connect to a database, you must first know which server you want to connect to and the database name.

Con. connectionstring = "Server =.; database = persondb; uid = sa; Pwd = sa"; // indicates the server name, database name, logon username, and password respectively.

Next, create an SQL command object:

Sqlcommand cmd = con. createcommand (); // SQL command object, which indicates that you want to execute an SQL statement on the SQL database

Cmd. commandtext = "insert into persons (personname, personage) values (@ name, @ age)"; // SQL statement @ name, @ age indicates two parameters

Cmd. Parameters. Add ("@ name", sqldbtype. nvarchar). value = textbox1.text; // assign a value to the parameter and specify the type

Cmd. Parameters. Add ("@ age", sqldbtype. INT). value = textbox2.text;

Con. open (); // open the connection

Cmd. executenonquery (); // execute an SQL statement that is not queried.

MessageBox. Show ("inserted successfully! ");

To open a database connection, remember to close the connection:

Catch (exception ex) // capture exceptions because SQL statements are sensitive to halfwidth.

{

MessageBox. Show (ex. Message );

}

Finally

{

Con. Close (); // you can close the connection no matter whether the connection is successful or fails.

}

Now that the insert operation is complete, let's see if we can insert data into the table.

 

Click Add. Let's go to the persons table to see if data is successfully inserted.

We can see that "Michael" is successfully added, and we can add more data.

Next, let's take a look at how the query operation is actually not much different from the insert operation, but there is an additional sqldatareader reader object: Let's take a look at the source code below:

Try

{

Con. connectionstring = "Server =.; database = persondb; uid = sa; Pwd = sa"; // indicates the server name, database name, logon username, and password respectively.

Sqlcommand cmd = con. createcommand ();

Cmd. commandtext = "select ID, personname, personage from persons"; // query all

Con. open ();

Sqldatareader DR = cmd. executereader (); // provides a method to read rows only from the SQL Server database.

Listbox1.items. Clear (); // clear ListBox data

Combobox1.items. Clear (); // clear combox data

While (dr. Read () // read data in one row. The returned value is of the bool type.

{

Listbox1.items. Add ("name:" + dr. getvalue (1). tostring () + "Age:" + dr. getvalue (2). tostring ());

Combobox1.items. Add (dr. getvalue (0); // put the ID in combox for deletion and modification.

}

Dr. Close (); // close the stream reading operation.

}

Catch (exception ex)

{

MessageBox. Show (ex. Message );

}

Finally

{

Con. Close ();

}

Let's take a look at the query results:

 

Next, we will implement the delete operation, which is almost the same as the insert operation. First, we will query all the data from the database and obtain their IDs (all in the combox)

The specific implementation is as follows:

Try

{

Con. connectionstring = "Server =.; database = persondb; uid = sa; Pwd = sa ";

Sqlcommand cmd = con. createcommand ();

Con. open ();

Cmd. commandtext = "delete persons where id = @ ID"; // The delete statement. The ID is used as the condition to delete the statement.

Cmd. Parameters. Add ("@ ID", sqldbtype. INT). value = combobox1.text;

Cmd. executenonquery ();

}

Catch (exception ex)

{

MessageBox. Show (ex. Message );

}

Finally

{

Con. Close ();

}

First query,

Select the deletion ID,

Click Delete and query again.

 

We can see that Zhu Zhongba has been deleted.

The last modification is troublesome. First, you need to obtain the data to be modified, first obtain all the data, query the data by ID, and then confirm the modification.

View the modification query code:

Try

{

Con. connectionstring = "Server =.; database = persondb; uid = sa; Pwd = sa ";

Sqlcommand cmd = con. createcommand ();

Cmd. commandtext = "select personname, personage from persons where id = @ ID ";

Cmd. Parameters. Add ("@ ID", sqldbtype. INT). value = combobox1.text;

Con. open ();

Sqldatareader DR = cmd. executereader ();

Dr. Read (); // The while loop is not needed because only one piece of data is read.

Textbox1.text = dr. getvalue (0). tostring ();

Textbox2.text = dr. getvalue (1). tostring ();

Dr. Close ();

}

Catch (exception ex)

{

MessageBox. Show (ex. Message );

}

Finally

{

Con. Close ();

}

Let's take a look at the modified Code:

Try

{

Con. connectionstring = "Server =.; database = persondb; uid = sa; Pwd = sa ";

Sqlcommand cmd = con. createcommand ();

Con. open ();

Cmd. commandtext = "Update persons set personname = @ name, personage = @ age where id = @ ID ";

Cmd. Parameters. Add ("@ ID", sqldbtype. INT). value = combobox1.text;

Cmd. Parameters. Add ("@ name", sqldbtype. nvarchar). value = textbox1.text;

Cmd. Parameters. Add ("@ age", sqldbtype. nvarchar). value = textbox2.text;

Cmd. executenonquery ();

}

Catch (exception ex)

{

MessageBox. Show (ex. Message );

}

Finally

{

Con. Close ();

}

All right, let's first query all the information and get the modification information through the ID.

 

Change the name to seven horses, confirm the modification, and check whether the modification is successful.

 

Now we can see that the modification is successful.

At this point, the basic things are all finished, and there are also sqldataadapter and able classes, which will be discussed next time.

If you are interested, you can contact me...