Operations on SQL database in Unity

In unity, we sometimes need to connect to a database to read and store data. And in. NET platform, ADO. NET provides us with classes that expose data access services. The client application can use ADO to connect to the data source and query, add, delete, and update the contained data.

For ADO, you need to know the Connection,command,datareader,dataadapter,dataset objects, they are the important objects to manipulate the database. Here's a brief introduction to the roles and functions of these objects (in SQL, for example).

1, Connection: It is to establish the connection between the application and the database channel, to connect the database function. The form of access depends on the type of database. In SQL, for example, the connection type is SqlConnection. This connection requires the introduction of the corresponding database namespace, where we need to introduce System.Data.SqlClient. The System.Data.dll file is also required to introduce this namespace, which can be found in the Unity installation source directory and copied into Unity's asset.
The wording is as follows:

//声明一个字符串用于存储连接数据库字符串        string s = "server=localhost;database=hasion;uid=sa;pwd=hasion";        SqlConnection con = new SqlConnection(s);        con.Open();

The connection to the database is turned on.

2. Command: When an application establishes a connection to a data source, the command object is required to execute commands and return results from the data source. It is a data command object, the main function is to send queries to the database, update, delete, modify the operation of the SQL statement. We need to talk about it here. Several ways to execute SQL: The ExecuteNonQuery method, which returns the number of rows affected to be used for statistics, (if a stored procedure is required, change the properties of the CommandType).

The wording is as follows:

//声明一个字符串用于存储连接数据库字符串        string s = "server=localhost;database=hasion;uid=sa;pwd=hasion";SqlConnection con = new SqlConnection(s);        con.Open();//创建SqlCommand对象，并指定其使用con连接数据库 SqlCommand cmd = new SqlCommand(); cmd.Connection = con;//设置CommandText，设置其执行SQL语句 cmd.CommandText="update Table_1 set 资产=1000 where 性别=‘女‘";int i = Convert.ToInt32 (cmd.ExecuteNonQuery ());print ("查询到"+i+"个女性");

The ExecuteScalar method returns the first column of the first row of the result set, the number of common terms statistics, and uses the following:

//声明一个字符串用于存储连接数据库字符串        string s = "server=localhost;database=hasion;uid=sa;pwd=hasion";SqlConnection con = new SqlConnection(s);        con.Open();//创建SqlCommand对象，并指定其使用con连接数据库 SqlCommand cmd = new SqlCommand(); cmd.Connection = con;//设置CommandText，设置其执行SQL语句cmd.CommandText="select * from Table_1 where 性别=‘女‘";int i = Convert.ToInt32 (cmd.ExecuteScalar ());print ("查询到"+i+"个女性");

The ExecuteReader method returns a SqlDataReader object that can be read with the following usage:

//声明一个字符串用于存储连接数据库字符串        string s = "server=localhost;database=hasion;uid=sa;pwd=hasion";SqlConnection con = new SqlConnection(s);        con.Open();//创建SqlCommand对象，并指定其使用con连接数据库 SqlCommand cmd = new SqlCommand(); cmd.Connection = con;//设置CommandText，设置其执行SQL语句cmd.CommandText="select * from Table_1";SqlDataReader st = cmd.ExecuteReader ();while (st.Read()) {print(st[0].ToString());}cmd.Dispose ();

The function above is to output all the contents of the first column of the table.

3, DataReader: This does not explain, is to use the object returned in ExecuteReader, the specific form above has been written.

4, DataAdapter: A data adapter, is a bridge between a dataset and a data source. It works in two ways: by executing SQL statements from the command object, retrieving data from the data source, populating the retrieved data into the DataSet object, and writing changes to the DataSet object to the data source (for convenience, The following is an example of a Windows application for VS that shows its specific use, as its DataGridView can show tabular data well. The first usage is the following (that is, populating the DataSet DataSet):

 //declares a string to store the connection database string string s =  "server=localhost;database=hasion;uid=sa;pwd=hasion"; SqlConnection con = new SqlConnection (s);  Create a SqlCommand object and specify that it uses con to connect to the database SqlCommand cmd = new SqlCommand (  "select * from Table_1", con); //Create SqlDataAdapter object SqlDataAdapter SDA = new SqlDataAdapter ( ); //specify Commandsda.selectcommand = cmd; //Create DataSet object DataSet ds = new DataSet (); SDA. Fill (ds);D Atagridview.datasource = ds. Tables [0];             

Here you populate the DataSet DataSet with the Fill method of the DataAdapter object, and the Fill method uses the SELECT statement to retrieve the data from the data source. It is important to note that the connection object associated with the Select command must be valid, but it does not need to be opened.

Another usage is to update the data source, which is to use the DataAdapter Update method to update the modified data in the dataset to the database in a timely manner. Use the following:

SqlConnection con =Null        SqlDataAdapter SDA; DataSet ds;PrivatevoidButton1_Click(Object sender, EventArgs e) {con =New SqlConnection ("Server=localhost;database=hasion;uid=sa;pwd=hasion");SqlCommand com = new SqlCommand ("SELECT * from Table_1", con); SDA =New SqlDataAdapter ("SELECT * from Table_1", con);Sda. SelectCommand = com; DS =New DataSet (); Sda. Fill (DS,"CS"); Datagridview.datasource = ds. tables[0]; }PrivatevoidDatagridview_cellcontentclick(Object sender, DataGridViewCellEventArgs e) {Show each row of data TextBox1.Text = datagridview.selectedcells[0]. Value.tostring (); TextBox2.Text = datagridview.selectedcells[1]. Value.tostring (); TextBox3.Text = datagridview.selectedcells[2]. Value.tostring (); Textbox4.text = datagridview.selectedcells[3]. Value.tostring (); }private void button2_click (object sender, EventArgs e) {//Create a datatable datatable dt = ds. Tables[ "CS"]; //load the table mechanism into the table_1 SDA. FillSchema (DT, schematype.mapped); //create a DataRow and set the value in the DataRow dr = dt. Rows.find (TextBox1.Text.Trim ()); Dr[ "sex" = TextBox2.Text.Trim (); Dr[ "age"] = TextBox3.Text.Trim (); Dr[ "money"] = TextBox4.Text.Trim (); //instantiation of a sqlcommadnbuilder sqlcommandbuilder CMB = new SqlCommandBuilder (SDA); //Update database SDA. Update (DT); }

This makes it possible to modify the data in the database.

5, DataSet: In fact, the above has been used in this object, he is the core of the entire system, its data from the database or XML, in order to obtain data from the database, the need to use data adapter data to query data.

The fundamentals and methods of connecting a database to C # are the above. These are generally used in the connection database. These are also my own through reading some of the things, are the basic part of the more complex database connection needs to be specific treatment.

Here's an example: a simple table created in SQL

In unity, how do we present the data that we read, and use the above to do it all. The main methods are shown below for reference only:

Using Unityengine;Using System.Collections;Using System;Using System.Data;Using System.Data.SqlClient;Using System.Data.Common;PublicClassSQLConnection:Monobehaviour {SqlConnection con=Null SqlDataAdapter sda=NullAccept Data variablesPrivateString str;voidStart(){Create Connection con =New SqlConnection ("Server=localhost;database=hasion;uid=sa;pwd=hasion");Executive Sqlsda=New SqlDataAdapter ( "select * from Table_1", con); //instantiate the dataset and write the query to the data System.Data.DataSet ds = new System.Data.DataSet (); SDA. Fill (ds,  "table"); for (int i=0; i<ds. Tables[0]. Rows.Count; i++) {for (int j=0;j<ds. Tables[0]. columns.count;j++) {Str+=ds. Tables[0]. ROWS[I][J]. ToString (). Trim () +0]. Columns.count-1) {print (str); Str=    

After the script executes, the following results are printed:

Since the data can be presented, we can do other further operations, such as the production of tables, logic control in the program, and so many features, which have been encountered in specific problems in the concrete treatment.

Of course, there are many SQL statements, additions and deletions can be done, and then with Unity GUI or Ngui and so on UI production path. It's good to make the effect you want.

I summed up the basic is so much, there are insufficient places to welcome everyone to criticize!!! Thanks, ~~~~~~~~.

Operations on SQL database in Unity