The INSERT operator inserts new entries to the table. In this case, the values \u200b\u200bof the columns may be literal constants, or be the result of subquery. In the first case, a separate INSERT operator is used to insert each string; In the second case, so many lines will be inserted as the subquery is returned.
The syntax of the operator is as follows:
| Default Values)
INSERT INTO [(, ...)]
(Values \u200b\u200b(, ...))
As can be seen from the presented syntax, the list of columns is not mandatory (they say square brackets in the syntax description). In the event that it is missing, the list of inserted values \u200b\u200bmust be complete, that is, provide values \u200b\u200bfor all columns of the table. In this case, the order of values \u200b\u200bmust correspond to the order specified by the CREATE TABLE statement for the table in which lines are inserted. In addition, these values \u200b\u200bshould include the same data type as the columns in which they are entered. As an example, consider the insertion of the string in the Product table created by the following Create Table operator:
Create Table Product.
maker Char (1) not NULL,
model Varchar (4) Not NULL,
type Varchar (7) Not NULL
Let it be necessary to add to this table model PC 1157 manufacturer B. This can be done by the following operator:
INSERT INTO PRODUCT
Values \u200b\u200b("B", 1157, "PC");
If you set a list of columns, you can change the "natural" order of their following:
INSERT INTO PRODUCT (Type, Model, Maker)
Values \u200b\u200b("PC", 1157, "B");
It would seem that this is a completely unnecessary possibility that makes the design only more cumbersome. However, it becomes winning if the columns have default values. Consider the following structure of the table:
Create Table Product_d.
maker Char (1) NULL,
model Varchar (4) NULL,
type Varchar (7) NOT NULL DEFAULT "PC"
Note that here the values \u200b\u200bof all columns have the default values \u200b\u200b(the first two - , and the last column is Type - PC). Now we could write:
INSERT INTO PRODUCT_D (MODEL, MAKER)
Values \u200b\u200b(1157, "B");
In this case, the missing value when inserting the string will be replaced by the default value - PC. Note that if the default value is not specified for the column in the CREATE TABLE statement and the NOT NULL restriction is not specified, which prohibits the use of NULL in this table column, the default value is NULL.
The question arises: can I not specify the list of columns and, nevertheless, use the default values? The answer is positive. To do this, instead of explicitly specifying the value of using the reserved word default:
INSERT INTO PRODUCT_D.
Values \u200b\u200b("B", 1158, Default);
Since all columns have default values, it would be possible to write to the default values \u200b\u200bto insert a string:
INSERT INTO PRODUCT_D.
Values \u200b\u200b(Default, Default, Default);
However, for this case, a special design of Default Values \u200b\u200bis intended (see the operator syntax), with which the above operator can be rewritten as
INSERT INTO PRODUCT_D DEFAULT VALUES;
Note that when inserting a string in the table, all restrictions imposed on this table are checked. These may be limitations of the primary key or a unique index, check limits of type Check, limit reference integrity. In case of violation of any restriction, the insertion string will be rejected. Consider now the case of using a subquery. Let us need to insert into the product_d table all lines from the Product table related to the models of personal computers (Type \u003d 'PC'). Since the values \u200b\u200byou need are already available in some table, the formation of inserted strings manually, firstly, is ineffective, and, secondly, it may allow input errors. Use subqueries solves these problems:
The use of the "*" symbol in the subquery is in this case justified, since the order of the columns is the same for both tables. If it were not so, it would be necessary to apply a list of columns either in the INSERT statement, or in a subquery, or in both places, which would bring the order of the columns:
Here, as well as before, you can not specify all columns if you want to use the default values, for example:
In this case, the default PC for all inserted rows will be substituted into the Type Type Table Column.
Note that when using a subquery containing a predicate, only those lines for which the value of the predicate is equal to True (not unknown!). In other words, if the Type column in the Product table would allow NULL-value, and this value would be present in a number of rows, then these lines would not be inserted into the product_d table.
Overcome the restriction on the insertion of one line in the INSERT statement when using the row constructor in the Values \u200b\u200boffer allows an artificial use of a subquery to form a string with the UNION ALL offer. So if we need to insert several lines using one insert operator, you can write:
INSERT INTO PRODUCT_D.
SELECT "B" As Maker, 1158 AS Model, "PC" as Type
Union All.
SELECT "C", 2190, "LAPTOP"
Union All.
SELECT "D", 3219, "PRINTER";
Using UNION ALL preferred Union Even if the lack of rows-duplicates is guaranteed, since in this case the check will not be performed to exclude duplicates.
It should be noted that inserting several tuples using a row designer is already implemented in Systems for managing relational databases (DBMS) developed by Microsoft. Language of structured queries) is a universal computer language used to create, modify and manage data in relational databases.SQL Server 2008. With this opportunity, the last request can be rewritten in the form:
INSERT INTO PRODUCT_D VALUES
("B", 1158, "PC"),
("C", 2190, "Laptop"),
General syntax:
a) insert one new line in the table:
Insert.<таблица> [(columns)]
Values
b) Insert in a table of one or more rows taken from the source specified in the subquery:
Insert.<таблица> [(columns)]
Rules:
The number of items in the "Columns" section must match the number of items in the "Values" section; data types must match or allow an implicit transformation;
In the case when the "Column" section is missing, the "Values" section should contain as many values \u200b\u200bas the entire columns in the editable table, in addition, the values \u200b\u200bmust be listed in the same order in which table columns are declared; Therefore, it is recommended to always clearly specify the list of columns in which new values \u200b\u200bwill be added;
In the case of the use of syntax (b), the SELECT subquery can be addressed to either the same table or another database table or another database on the server;
If the default value is set for some table column, and it is required to use this value in the recording added, then in the "Values" section for this column you need to write a keyword default.
Examples :
INSERT INTO DETAIL (DETAIL_NAME)
Values \u200b\u200b("Bearing")
Insert Into Supply (Supplier_ID, Supply_Quantity, Supply_Cost,
supply_Date, Detail_ID)
Values \u200b\u200b(4, 177, 453.45, "09/25/2009", 1)
INSERT SUPPLIER (Supplier_Name)
Values \u200b\u200b(Default, "Smirnov S. S.")
Modification of existing records (UPDATE)
General syntax:
Update.<таблица>
SET.<столбец> = <значение> [, <столбец> = <значение>]
Examples :
Set weight \u003d 210
SET MATERIAL_NAME \u003d "TOLO"
Where material_id \u003d 2
Set department_id \u003d ,
delivery_Date \u003d Delivery_Date + 1 / * Increase delivery date for day
WHERE DELIVERY_ID \u003d 1 OR DEPARTMENT_ID\u003e 3
SET DETAIL_NAME \u003d Detail_name + "!!!"
Where Detail_Name Like "_A%" and weight Between 6 and 10
Delete Records from Table (Delete)
General syntax:
Delete.<таблица>
<условие>
Examples :
Where detail_id in (2, 5, 8)
Where Weight Is Null
The task:
Fill in the database by means of ManagementStudio. To do this, use the "Open Table" command to the table in the Object Browser (Fig. 4.1).
Data Sampling Query
SELECT query common syntax
SELECT<список столбцов>
From.<таблица(-ы) источник>
Select and FROM sections must be present in each request; The remaining sections may be absent or in part.
Examples of query implementation
The most simple requests are:
1. Select title and weight of details
Fig. 5.1. Request results 1
2. Select all the information from the table of materials.
Fig. 5.2. Request Results No. 2
Uniqueness Distinct.
3. Select unique supplier codes from the supply table
Fig. 5.3. Request Results No. 3
WHERE LIMITATION
4. Select the quantity and date of delivery Details with code 1
Fig. 5.4. Request Results No. 4
5. Select the names of suppliers with codes 1, 4 and 6
Fig. 5.5. Request Results No. 5
6. Select all the information about the deliveries taken to 1.10.2009
Fig. 5.6. Request results No. 6
7. Select all the information about the details that are not starting with the letter "B" (in any register) and whose weight is less than 50
Fig. 5.7. Request results number 7
8. Select the name and code of the material for parts with a weight of 5 to 10 g or in the title "H" in the third position
Fig. 5.8. Request results number 8
9. Select the names of suppliers long no more than 15 characters
Fig. 5.9. Request results number 9
10. Select months and years of supplies of parts
Fig. 5.10. Request results 10
Sort Order By.
11. Sort delivery first by the Supplier code, and then by delivery date
Fig. 5.11. Request results №11
12. Select the names of suppliers with codes 4, 6, 8, 9 ordered by alphabetically in reverse order
Fig. 5.12. Request results №12
Aggregation and grouping group by
Fig. 5.13. Request results №13
14. Determine the average weight of details from the material with code 2
Fig. 5.14. Request Results No. 2
15. From the deliveries committed to 1.10.2009, choose the largest delivery and the smallest
Fig. 5.15. Request results №15
16. For suppliers with codes in the range of 5 - 8, calculate the total number of parts supplied by them
Fig. 5.16. Results of execution of request №16
Fig. 5.17. Request results №17
Grouping restriction Having
18. Choose materials for which the total weight of these parts are not more than 20
Fig. 5.18. Request results №18
19. From the supply of 2008 to choose the details that came more than once
Fig. 5.19. Request results №19
Type conversionCast.
20. Get information about delivery dates in text form
Fig. 5.20. Request results No. 20
21. Get information from the part table in the form of strings "Detail X has a weight y"
Fig. 5.21. Request results №21
AlternativesCase
22. Divide the parts on the lungs (weighing up to 20), medium (between 20 and 50) and heavy
Fig. 5.22. Request results №22
NULL-Value Processing
23. Get information about the details and their weights, and if the part has the weight not specified, then instead of NULL Write -100
Fig. 5.23. Request results №23
EXISTS existence feature
24. Select the name and code of the material only those details that have ever been supplied
Fig. 5.24. Request Results No. 24
25. Select the names of those materials from which no detail is made.
Fig. 5.25. Request results №25
Subqueries
26. Get information about the latest (by date) delivery
Fig. 5.26. Request results №26
27. Get all the delivery of parts from the material with code 2
Fig. 5.27. Request results №27
28. For each provider, get information about the first (by date) of its delivery
Fig. 5.28. Request results №28
29. For each supplier get its name and date of the last order
Fig. 5.29. Request results №29
Join Table Communication
30. Get a table of type: the name of the part, the name of the material from which this item is made
Fig. 5.30. Request results No. 30
31. Get a table of type: supplier, part name, quantity and delivery date for details that have a weight
Fig. 5.31. Request Results No. 31
32. Select all the information about those details that have ever been supplied.
Fig. 5.32. Request results №32
Fig. 5.33. Request results №33
34. Select the names of materials and made of them parts.
Fig. 5.34. Request Results No. 34
35. Get all data on suppliers, suppliers and details.
Fig. 5.35. Request results number 35
Combining Tables Union.
36. Get the table of titles and weights of parts, and the last line of the table must contain the results in the form of the total weight of all parts
Fig. 5.36. Request Results No. 36
37. Get a table of two fields, where the first field is the name of the part, material, supplier, or delivery date, and the second field - the length of the line from the first field
Fig. 5.37. Request Results No. 37
The content of the article
1.
The most simple MySQL requests
2.
Simple SELECT (Select) Requests
3.
Simple INSERT (New Record) Requests
4.
Simple Update (Overwrite, Finish) Requests
5.
Simple Delete (Delete Recording) Requests
6.
Simple Drop (Delete Table) Requests
7.
Complex mysql requests
8.
MySQL Requests and Variables PHP
1. The most simple SQL requests
1. Will the list of all the databases.Show databases;
2. Wills a list of all tables in the base_name database.
Show Tables in Base_Name;
2. Simple Select (Select) MysQL database queries
SELECT - A request that chooses already existing data from the database. You can specify certain selection parameters. For example, the essence of the request to the Russian language sounds like this - choose such a column of such a table where the parameter of such a column is equal to the value.1. Selects all the data in the TBL_NAME table.
SELECT * from TBL_NAME;
2. Displays the number of entries in the TBL_NAME table.
SELECT COUNT (*) from TBL_NAME;
3. Selects (SELECT) from (from) Table Table TBL_NAME Limit (Limit) 3 records starting from 2.
SELECT * from TBL_NAME Limit 2.3;
4. Selects (SELECT) all (*) Records from (from) Table TBL_NAME and sorts them (Order by) by field ID in order.
SELECT * from TBL_NAME ORDER by ID;
5. Selects (SELECT) all records from (from) Table TBL_NAME and sorts them (ORDER BY) by field ID in reverse order.
SELECT * from TBL_NAME ORDER by ID DESC;
6. Chooses ( SELECT) All (*) entries from ( From.) Tables users. and sorts them ( Order by.) on the field id In ascending order, limit ( Limit.) The first 5 records.
SELECT * from Users Order by ID Limit 5;
7. Chooses all records from the table users.where the field fname. Corresponds to value Gena..
SELECT * from Users WHERE FNAME \u003d "GENA";
8. Selects all records from the table users.where the field value fname. begin with GE..
SELECT * from Users WHERE FNAME LIKE "GE%";
9. Selects all records from the table. users.where fname. ends on na.and streamlines records in increasing value id.
Select * from Users WHERE FNAME LIKE "% NA" Order by id;
10. Selects all the data from the speakers. fname., lName. From the table users..
SELECT FNAME, LNAME FROM USERS;
11.
Suppose you have a custom data in the table. So if you want to display only a list of encountered values \u200b\u200b(so that, for example, Russia is not displayed 20 times, but only one), then we use Distinct. Will out, from the mass of repeated values \u200b\u200bof Russia, Ukraine, Belarus. Thus, from the table users. Speakers country. All unique meanings will be displayed.
Select Distinct Country From Users;
12. Selects all lines from the table users. Where age. It has 18.19 and 21.
Select * from Users WHERE AGE IN (18,19,21);
13.
Selects the maximum value age. Table users.. That is, if you have the most important in the table age.(from English) Equally 55, then the result of the query will be 55.
SELECT MAX (AGE) from users;
14. Select data from the table users. By fields name. and age. WHERE age. Accepts the smallest value.
SELECT NAME, MIN (AGE) from users;
15. Select data from the table users. on the field name. WHERE id Not equal to 2.
SELECT NAME FROM Users WHERE ID! \u003d "2";
3. Simple INSERT (new entry) requests
Insert. - Request, which allows initially inserting an entry in the database. That is, it creates a new entry (line) in the database.1. Makes a new entry in the table users., in field name. inserts Sergey, and in the field age. Inserts 25. Thus, the table adds a new row with data values. If the speakers are larger, then they remain left either empty, or with default values.
INSERT INTO USERS (Name, Age) Values \u200b\u200b("Sergey", "25");
4. Simple Update MysQL database requests
Update. - A request that allows you to overwrite the values \u200b\u200bof the fields or add something in the already existing line in the database. For example, there is a ready line, but it needs to overwrite the parameter of the age, as it has changed with time.1. Table users. age. It becomes 18.
Update Users Set AGE \u003d "18" WHERE ID \u003d "3";
2.
All the same as in the first request, the query syntax is simply shown, where two fields are overwritten and more.
Table users. Where ID is 3 field value age. becomes 18, and country. Russia.
Update Users Set Age \u003d "18", Country \u003d "Russia" WHERE ID \u003d "3";
5. Simple Delete (Delete Recording) Query to the MYSQL database
Delete. - A request that removes a string from the table.1. Deletes a string from the table users. WHERE id equal to 10.
Delete from Users WHERE ID \u003d "10";
6. Simple Drop (Delete Table) Query to the MySQL database
Drop. - A request that deletes the table.1. Removes the entire table tBL_NAME..
DROP Table TBL_NAME;
7. Sophisticated MYSQL database queries
Curious requests that can be useful even to experienced usersSELECT ID, NAME, COUNTRY FROM Users, Admins Where To_Days (now ()) - to_days (registry_date)<= 14 AND activation != "0" ORDER BY registration_date DESC;
This complex query chooses speakers iD, NAME, COUNTRY In tables users, Admins. WHERE registration_date. (date) not older 14
days I. activation. NOT EQUAL 0
, Sort by registration_date. in reverse order (new at the beginning).
Update Users Set AGE \u003d "18+" WHERE AGE \u003d (Select Age from Users WHERE MALE \u003d "MAN");
The example is the example of the so-called request in the request in SQL. Update age among users at 18+, where the floor is male. Similar query options do not recommend. According to personal experience, I will say, it is better to create several separate - they will be worked out faster.
8. Query to the MYSQL and PHP database
In MySQL requests in the PHP page, you can insert variables as a compared and TP values. A pair of examples1. Chooses all records from the table users.where the field fname. corresponds to the value of the variable $ Name..
SELECT * from Users WHERE FNAME \u003d "$ NAME";
2. Table users. Where ID is 3 field value age. Changes to the value of the $ AGE variable.
Update Users Set Age \u003d "$ Age" WHERE ID \u003d "3";
Attention! If you are interested in any other example, then write a question in the comment!
In this article we will discern, perhaps, one of the most important SQL queries. it requests for adding and deleting records from the database table. Since, very often has add new entries to the tableAnd to do it in automatic mode, then this material is required to be learning.
To start SQL request to add a new entry to the table:
INSERT INTO USERS (LOGIN, PASS) VALUES ("TestUser", "123456")
When adding an entry, the team comes at the beginning. " INSERT INTO", Then the name of the table in which we insert the record. Next goes to the heads of the fields, which we want to fill. And then in parentheses after the word" values."We start listed the values \u200b\u200bof those fields that we have chosen. After executing this request, a new entry will appear in our table.
Sometimes required refresh entry in the tablefor this there is the following SQL request:
Update Users Set Login \u003d "TestUser2", pass \u003d "1234560" WHERE LOGIN \u003d "TestUser"
This request is more difficult, as it has a design " Where.", but about it just below. At first there is a team" Update.", then the name of the table, and after" SET."We describe the values \u200b\u200bof all the fields that we want to change. It would be simple, but the question arises:" What kind of recording should be updated?". For this exists" Where."In this case, we update the entry, the field" login."Which is important" Testuser.". Please note that if there are several such records, then absolutely everything will be updated! It is very important to understand, otherwise you risk losing your table.
Let's talk a little about " Where."In addition to simple checks on equality there are also inequalities, as well as logical operations: And. and Or..
Update Users Set Login \u003d "TestUser2", Pass \u003d "1234560" WHERE ID< 15 AND login="TestUser"
The SQL request Update those records id which are less 15 AND field " login." has the meaning " Testuser."I hope you dealt with the design" Where.", Because it is very important. It is" Where."Used by sampling records from tablesAnd this is the most part-to-use task when working with databases.
And, finally, simple SQL request to delete records from the table:
Delete from Users WHERE LOGIN \u003d "TestUser2"
After the team " Delete from."There is a name of the table in which you want to delete records. Then describe the" WHERE "design. If the record will comply with the conditions described, it will be deleted. Again, pay attention, depending on the number of records that satisfy the condition after" Where.", Any amount of their number can be deleted.
At times, the programmers arise a burning desire to read something from the database. Programmers become nervous and irritable, losing sleep and feverly poke your fingers into the keyboard. For the sake of universal good and world world, consider simple work with the database from C # using ADO.NET and OLE DB. Although this mechanism supports different databases like Oracle, here and now we will use MS SQL Server.
Two main tasks when working with the database
2. Execution of the SQL command, something making on the server (INSERT, UPDATE, DELETE, Calling a function or stored procedure
3. Sequential reading from the select-sample string per line. Mostly used in web applications, it's easier to download all the sample into the RAM, saving from reading only the desired lines is insignificant.
4. Rare case. Automatic update table in the database based on changes to DataTable (as a rule editable via visual interface). In real life, data is usually read through a complex request with a join bunch or representation, so that automatic synchronization is not suitable.
Basic classes used for these purposes: OLEDBCONNECTION - Connection with a base, create with a string containing disordination parameters, open, close, OLEDBCOMMAND - Create with a connection instance and SQL command if you just need to execute update or get a single value, it is enough class, OLEDBDATAADAPTER - created with OLEDBCOMMAND, specializes in one-time reading row sets in datatable, can automatically create datatable speakers based on the sample, to transfer changes from DataTable to the table in the database, OLEDBDATAREADER - serial reading rows in DataTable one at a time (it works inside OLEDBDATAADAPTER), DATATABLE / DATASET - main data container. In the OLEDBTYPE listing stored database data types.
Using System.Data; Using System.Data.oledb; // Connection string, used Windows user system (Integrated Security \u003d SSPI;) STRING CONNSTRING \u003d "PROVIDER \u003d SQLOLOLODB.1; Integrated Security \u003d SSPI; Persist Security Info \u003d False; initial catalog \u003d databasename; Data source \u003d serverName"; // Alternative Line of Connection with MS SQL Server Authentication // Connestring \u003d "Provider \u003d SQLOLOLEDB.1; Persist Security Info \u003d False; Initial Catalog \u003d DatabaseName; Connect Timeout \u003d 20; Data Source \u003d ServerName; UID \u003d UserName; PWD \u003d UserPassword ; " OLEDBCONNECTION DBCONN \u003d NEW OLEDBCONNECTION (CONNSTRING); dbconn.open (); somedatatable \u003d new datatable (); OLEDBDATAADAPTER DBADAPTER \u003d NEW OLEDBDATAADAPTER ("SELECT COLUMN1, COLUMN2 FROM TEST_TABLE ORDER BY COLUMN2", DBCONN); // The internal structure of the empty table will be created automatically based on the data of the data if the table structure is already set (for example, through typed DataSet), the data will be written in columns with matching names or add new dbadapter.fill columns (somedatatable); // Alternative to fill the table in dataset // dbadapter.fill (Somedataset, "Somedatatable"); dbconn.close ();
2. Execution of the SQL command, something making on the server (INSERT, UPDATE, DELETE, Calling a function or stored procedure.
Especially worth noting the problems with dates. The implementation of the Dat of V.NET is extremely curved - initially the dates cannot be empty, but they are empty enough and nearby. The most correct solution is to use a special date class, correcting the shoals of programmers from Microsoft. Loyant developers keep all the dates in the code for strings and convert them to DateTime only if necessary, for example, when writing to the database or DATATABLE. NULLABLE DATETIME does not save, since the empty date in the interface should look a blank string, and when recording to the database as dbnull.value - and the banal NULL in the code is not converted into these values \u200b\u200bwithout additional dancing with tambourine.
The first option implies a banal bonding of the query string. It is considered bad practice and is especially dangerous in web applications, as you vulnerable for hacker attacks. The problem with empty dates is so simple not solved. In addition, an additional problem appears when working with dates - Different Date Line Formats Depending on the Regional Settings .NET Framework, the development environment and SQL server. It can look like this can surreal - the same request works in SQL Managment Studio, but flies when performing from the code. Partially saves a special format of the dates row, nevertheless, so often make in small programs for internal use, the existence of which the outside world will never know.
OLEDBCOMMAND DBCOMMAND \u003d DBCONN.CREATECOMMAND (); dbcommand.commandText \u003d "Insert Into Test_Table (int_column, varchar_column, datetime_column) Values \u200b\u200b(" + intvariable "," + StringVariable "," + datetimevariable.tostring ("yyyymmdd") + ")"; dbcommand.executenonquery ();
The correct option implies the creation of a command with a set of strictly typed parameters. Consider that the previous paragraph was not.
OLEDBCOMMAND DBCOMMAND \u003d DBCONN.CREATECOMMAND (); dbcommand.commandText \u003d "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (?,?,?)"; dbcommand.parameters.add ("int_column", oledbtype.integer) .Value \u003d intvariable; dbcommand.parameters.add ("varchar_column", oledbtype.varchar) .Value \u003d StringVariable; if (stringdate \u003d\u003d "") ("datetime_column", oledbtype.datetime) .Value \u003d dbnull.value;) ELSE (dbcommand.parameters.add ("datetime_column", oledbtype.datetime) .Value \u003d Convert.todatetime (stringdate);) dbcommand.executenonquery ();
The stored procedure is called in the same way, the varieties for the sake of another version of recording values \u200b\u200bin the parameters (it is not related to the stored procedure):
OLEDBCOMMAND SOMEDBCOMM \u003d NEW OLEDBCOMMAND ("SOMESTOREDPROCEDURE", THIS.DBCONN); somedbcomm.commandtype \u003d commandtype.StoredProCedure; somedbcomm.parameters.add ("@ parameter1", oledbtype.varchar); somedbcomm.parameters.add ("@ Parameter2", OLEDBTYPE.VARCHAR); somedbcomm.parameters.value \u003d "(! lang: any problem always has a solution - simple, comfortable and, of course, erroneous"; someDbComm.Parameters.Value = "Henry Luis Menken"; someDbComm.ExecuteNonQuery(); !}
Advanced option describing a parameter with an indication of the field size and binding to a specific table column.
Dbcommand.parameters.add ("varchar_column", OLEDBTYPE.VARCHAR, 100, "VARCHAR_COLUMN"). Value \u003d StringVariable;
If we do not need a binding command parameter to a specific datatable field, it is best not to specify the size in general, for example, if the string is less than the specified period of VARCHAR, then kind .Net framework will add to the space string to the specified length, spoiling the data transmitted to the server.
The value of the unit field is read by the ExecuteScalar () method
OLEDBCOMMAND DBCOMMAND \u003d DBCONN.CREATECOMMAND (); dbcommand.commandText \u003d "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN \u003d?"; dbcommand.parameters.add ("int_column", oledbtype.integer) .Value \u003d intvariable; INT Result \u003d convert.TOINT32 (dbcommand.executescalar ());
It should be especially noted that ExecuteScalar returns an Object and if the request did not return anything at all, the result will be NULL and convert to a normal data type will fly with an error. If a situation is possible when we don't get anything in response, then you need to do this:
OLEDBCOMMAND DBCOMMAND \u003d DBCONN.CREATECOMMAND (); dbcommand.commandText \u003d "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN \u003d?"; dbcommand.parameters.add ("int_column", oledbtype.integer) .Value \u003d intvariable; Object resultobj \u003d dbcommand.executescalar () INT Result \u003d -1; // Default value, meaning an empty IF result (resultobj! \u003d NULL) (result \u003d convert.TOINT32 (dbcommand.executescalar ());)
3. Sequential reading from select sampling string per line
Reading one line (are somewhat read in the cycle);
OLEDBCOMMAND DBCOMMAND \u003d NEW OLEDBCOMMAND (SELECT PERSON_ID, NAME, SURNAME FROM TEST_TABLE, DBCONN); OLEDBDATAREADER DBReader \u003d dbcommand.executeReader (); dbreader.read (); String name \u003d convert.tostring (dBReader ["Name"]); STRING SURNAME \u003d convert.tostring (dbreader ["Surname"]); dbreader.close ();
4. Rare case. Automatic update table in the database based on changes to DataTable (as a rule editable via the visual interface).
You must register for dbadapter four commands for each possible case - Select, Insert, Update, Delete.
Dbadapter.insertcommand \u003d new OLEDBCOMMAND ("INSERT INTO TEST_TABLE (NAME, FAMIL, AGE) VALUES (?,?,?)", DBConnection); dbadapter.insertcommand.parameters.add ("Name", OLEDBTYPE.VARCHAR, 100, "NAME"); dbadapter.insertcommand.parameters.add ("Famil", OLEDBTYPE.VARCHAR, 100, "FAMIL"); // For data types with constant long specified in the command Long ignored dbadapter.insertcommand.parameters.add ("Age", OLEDBTYPE.integer, 100, "Age"); // Add a dbadapter.updatecommand \u003d New OLEDBCOMMAND update command ("Update Test_Table Set name \u003d?, Famil \u003d?, Age \u003d? WHER ID \u003d?", DBConnection); dbadapter.updatecommand.parameters.add ("Name", OLEDBTYPE.VARCHAR, 100, "NAME"); dbadapter.updatecommand.parameters.add ("FAMIL", OLEDBTYPE.VARCHAR, 100, "FAMIL"); dbadapter.updatecommand.parameters.add ("Age", OLEDBTYPE.integer, 100, "Age"); dbadapter.updatecommand.parameters.add ("ID", OLEDBTYPE.INTEGER, 100, "ID"); // Add a DBADAPTER.DeleteCommand \u003d New OLEDBCOMMAND removal command ("Delete from Test_table Where ID \u003d?", DBConnection); dbadapter.deletecommand.parameters.add ("ID", OLEDBTYPE.INTEGER, 100, "ID"); Try (// Transfer all changes from Datatable to a table in the dBadapter.update database (Table);) Catch (Error Error when saving data! "+ error.message); Return;) MessageBox. Show ("Changes saved!");
