C # operations on the Access Database

 

The following uses the ACCESS database as an example to describe how to access the database using the C # dataset class. The operation involves the following main C # classes: Dataset: A set corresponding to the database table, which is actually a cache datatable in the memory of the database table: corresponding to the database table, which is a set of database table rows datarow: oledbconnection: Create a database connection oledbdataadapter: The dataset is generated by the database and synchronized between the dataset and the database oledbcommandbuilder: the commands dataset, able, and datarow required to update the database are used for data operations in the cache. The preceding operations are only updated to the database and the modification results are permanently saved. Oledbconnection is required to connect to the database using the oledb method. Oledbdataadapter and oledbcommandbuilder are used to generate dataset and complete database updates. In contrast to oledbdataadapter and oledbcommandbuilder, sqldataadapter and sqlcommandbuilder can also update databases with SQL commands. Assume that an Access database is created on the drive D. The path is D: \ 0dbacs \ account. MDB. The database contains a database table named Kaizhi. The table structure is as follows: Table Name; kaizhi field name type description 1 Expenses idkzid long integer automatic number 2 expenses kzren text 50 characters 3 expenses project name kzname text 50 characters 4 Date riqi date/time 99-99-99; 0 mask 5 expenses description shuoming text 225 characters 6 total amount zonge single precision decimal point arbitrary; total spending of this expense 7 quantity shuliang long integer 8 unit price danjia single precision decimal point after any table is created :( 1) add new data to the table (2) query a field in the table. To implement these two functions, consider the following issues: 1. Make preparations to declare the necessary public variables to establish a connection with the database, create DataSet object 2 add record on DataSet object add record synchronize DataSet object and data in database, this is very important, many people forget to synchronize data, the result is that the added or modified data cannot be saved to the database. 3. query a field of a record in a database table. We can use dataworks to implement the above functions. The following are the main activities of program design. Declare the required C # system class using system; using system. collections. generic; using system. text; using system. io; using system. data; using system. data. oledb; using system. data. sqlclient; declare the public variable private string dblocation; private oledbconnection dbconn; // connect the database to private oledbdataadapter da; establish a connection with the database. The oledb method is used here: dbconn = new oledbconnection (@ "provider = Microsoft. jet. oledb.4.0; Data Source = D: \ 0dbacs \ account. mdb "); dbcon N. open (); Create a DataSet object da = new oledbdataadapter (@ "select * From Kaizhi", dbconn ); // reference the database to connect to dbconn and create the oledbdataadapter object dadataset DS = new dataset () based on the SQL statement "select * From Kaizhi"; // create the DataSet object da. fill (DS); // use the oledbdataadapter object da to fill in, update the newly created DataSet object to add records, and update the database oledbcommandbuilder cb = new oledbcommandbuilder (DA ); // create oledbcommandbuilder object CB to update the insert, delete, and update commands of the oledbdataadapter object da. updatecomman D = CB. getupdatecommand (); // The instruction designer for updating the oledbdataadapter object DA can write his/her own update instruction, or use the system default instruction as described above. However, the preceding statement cannot be missing. Otherwise, the program will throw an exception in running. invalidoperationexception, and prompt: update requires a valid insertcommand when passed datarow collection with new rows. datarow drx = Ds. tables [0]. newrow (); // create a new record row drx ["kzren"] = "kzren"; drx ["kzname"] = "kzname "; drx ["riqi"] = 2008-10-11; drx ["shuoming"] = "shuoming"; drx ["zonge"] = 12; drx ["shuliang"] = 3; drx ["danjia"] = 4; DS. tables [0]. rows. add (drx); // append the record da to the table. UPD Ate (DS); // to update a database, query a field that references a record and reference it directly using the following method. String kx = Ds. tables [0]. rows [0] ["kzren"]. tostring () connection access first let's look at an example code snippet: program code: ------------------------------------------------------------------------ using system. data; using system. data. oledb ;...... string strconnection = "provider = Microsoft. jet. oledb.4.0; "; strconnection + = @" Data Source = c: \ begaspnet \ northwind. mdb "; oledbconnection objconnection = new oledbconnection (strconnection );...... objconnectio N. open (); objconnection. close ();...... ------------------------------------------------------------------------------ explanation: connecting to the ACCESS database requires importing additional namespaces, so it is essential to have the first two using commands! The strconnection variable stores the connection string required to connect to the database. It specifies the data provider to be used and the data source to be used. "provider = Microsoft. jet. oledb.4.0; "is the index data provider. Here we use the Microsoft Jet Engine, which is the Data Engine in access. Asp.net is connected to the access database. "Data Source = c: \ begaspnet \ northwind. MDB indicates the location of the data source. Its standard format is "Data Source = mydrive: mypath \ myfile. mdb ". PS: 1. the "@" symbol after "+ =" prevents "\" in the subsequent string from being parsed as escape characters. 2. if the database file to be connected is in the same directory as the current file, you can use the following method to connect: strconnection + = "Data Source ="; strconnection + = mappath ("Northwind. mdb"); this saves you a lot of writing! 3. note that parameters in the connection string must be separated by semicolons. "oledbconnection objconnection = new oledbconnection (strconnection);" this sentence uses the defined connection string to create a link object. In the future, we will deal with this object for database operations. "objconnection. open (); "this is used to open the connection. now, the connection to the ACCESS database is complete. other operations (insert, delete ...) see related books to connect to SQL Server example code snippet: program code: ---------------------------------------------------------------------------- using system. data; using system. data. sqlclient ;... string strconnection = "User ID = sa; P Assword =; "; strconnection + =" Initial catalog = northwind; server = yoursqlserver; "; strconnection + =" Connect timeout = 30 "; sqlconnection objconnection = new sqlconnection (strconnection );... objconnection. open (); objconnection. close ();... ------------------------------------------------------------------------------ explanation: there is no major difference between the mechanism for connecting to the SQL Server database and the mechanism for connecting to access, but it only changes the parameters of. first, the namespace used to connect to SQL Server is not" System. data. oledb ", but" system. data. sqlclient ". the second is his connection string. We will introduce it one by one (note: the parameters are separated by semicolons): "User ID = sa": the authenticated user name for database connection is Sa. he also has an alias "uid", so we can also write this sentence as "uid = sa ". "Password =": the password for database connection verification is blank. its alias is "PWD", so we can write it as "Pwd = ". note that your SQL server must have a user name and password set to log on. Otherwise, you cannot log on using this method. if your SQL Server is set to Windows logon, you do not need to use the "user ID" and "password" methods to log on here, you need to use "trusted_connection = sspi" to log on. "Initial catalog = northwind": the data source used is "northwind ". Database. his alias is "Database". This sentence can be written as "database = northwind ". "Server = yoursqlserver": Use a server named "yoursqlserver. its alias is "Data Source", "Address", "ADDR ". if the local database is used and the Instance name is defined, you can enter "Server = (local) \ Instance name". If it is a remote server) "Replace with the name or IP address of the remote server. "Connect timeout = 30": the connection timeout is 30 seconds. here, the constructor used to create a connection object is sqlconnection. the rest is no different from access! **************************************** **************************************** * *********************************** Access using C # the motivation for writing this program in the ACCESS database is that when I want to access the msaccess database with C sharp, I cannot obtain any information or reference materials. all the materials available on the Internet are focused on SQL, so we will write this application in two steps. First, we will show how to connect to the msaccess database and see how complicated it is. finally, we completed the program in this way. let's start our discussion. the database connection process has changed significantly compared with the previous ADO connection process. the following chart is appropriate (I hope so) oledbconnection --> oledbcommand --> oledbdatareader. now those familiar with ADO People can clearly see the similarities between the two, but in order to make those who are not well adapted to ADO understand, the following are some explanations. oledbconnection --> represents a single connection to the database. Based on the features of the underlying database, it can manipulate the database. it is important to remember that even though the oledbconnection object has a function scope, it will not be automatically closed. therefore, you will have to call the close () method of this object. oledbcommand --> This is a common command object that we use in ADO. you can use this object to call the SQL stored procedure or SQL query statement. oledbdatareader --> this class is of great importance because it provides actual access to the underlying dataset of the database. when you call the executereader method of oledbcommand, it will be created ,. net beta2 SDK says you should not directly create objects of this class. now you can. net beta 2 documentation to see more The following describes how to access the source code of the database in a program. using system; using system. data. oledb; Class oledbtest {public static void main () {/create a database connection oledbconnection aconnection = new oledbconnection ("provider = Microsoft. jet. oledb.4.0; Data Source = c: \ db1.mdb "); // create a command object and save the SQL query statement oledbcommand acommand = new oledbcommand (" select * From emp_test ", aconnection ); try {aconnection. open ();/create a datareader object to connect to the form oledbdat Areader = acommand.exe cutereader (); console. writeline ("this is the returned data from emp_test table");/traverse the database cyclically while (areader. read () {console. writeline (areader. getint32 (0 ). tostring ();}/Close the reader object areader. close ();/Close the connection, which is important to aconnection. close () ;}/ some common exception handling catch (oledbexception e) {console. writeline ("error: {0}", E. errors [0]. message) ;}} successfully runs this program step 1. use msaccess to create a database named db1.mdb 2. Create a form named emp_test 3. make it contain the following data domain emp_code int emp_name text emp_ext text 4. save the above Code to sample. CS File 5. make sure that the database is in c: \ and that mdac2.6 or the updated version has been installed. 6. compile and run the program. Now let's take a look at some details about what we can see in the oledbconnection object constructor. Here you can see something like "provider =. below are some driver types compatible with ado.net. sqlolddb --> Microsoft ole db provider for SQL Server, msdaora --> Microsoft OLE DB provider for Oracle, Microsoft. jet. oledb.4.0 --> ole db provider for Microsoft Jet you You can select either of them, but they will need to pass different parameters, such as jet. oledb. the MDB file name needs to be passed, while sqloledb needs to pass the user name and password. all these drivers are located in system. data. in the oledb namespace, you must include it, and they are not compatible with oledb provider for ODBC. That is to say, you cannot use these drivers in VB6.0 to access the database, so don't look for information on why you should put these databases on c: \. When you use Microsoft SQL Server 7.0 or the latest version, the following are some guidance provided by Microsoft: recommended. NET data provider uses Microsoft SQL Server 7.0 or an updated mid-tier Application in the following cases, using Microsoft Data Engine (MSDE) or a single-tier Application of icrosoft SQL Server 7.0 or later. We recommend that you set ole db provider for SQL Server (sqloledb) and OLE DB. NET data provider. for Microsoft SQL Server 6.5 and earlier versions, you must use both ole db provider for SQL Server and Ole db.net data provider. we recommend that you use ole db for Microsoft SQL Server 6.5 and earlier versions or Oracle's intermediate tier applications. NET data provider. SQL Server is recommended for Microsoft SQL Server 7.0 or later versions. NET data provider. microsoft Access database is recommended for single-tier applications. it is not recommended that an intermediate tier program use ole db at the same time. NET data provider and microso The FT Access database does not support ole db provider for ODBC (msdasql). The following uses the ACCESS database as an example to describe how to access the database using the dataset class of C. The operation involves the following main C # classes: Dataset: A set corresponding to the database table, which is actually a cache datatable in the memory of the database table: corresponding to the database table, which is a set of database table rows datarow: oledbconnection: Create a database connection oledbdataadapter: The dataset is generated by the database and synchronized between the dataset and the database oledbcommandbuilder: the commands dataset, able, and datarow required to update the database are used for data operations in the cache. The preceding operations are only updated to the database and the modification results are permanently saved. Oledbconnection is required to connect to the database using the oledb method. Oledbdataadapter and oledbcommandbuilder are used to generate dataset and complete database updates. In contrast to oledbdataadapter and oledbcommandbuilder, sqldataadapter and sqlcommandbuilder can also update databases with SQL commands. Assume that an Access database is created on the drive D. The path is D: \ 0dbacs \ account. MDB. The database contains a database table named Kaizhi. The table structure is as follows: Table Name; kaizhi field name type description 1 Expenses idkzid long integer automatic number 2 expenses kzren text 50 characters 3 expenses project name kzname text 50 characters 4 Date riqi date/time 99-99-99; 0 mask 5 expenses description shuoming text 225 characters 6 total amount zonge single precision decimal point arbitrary; total spending of this expense 7 quantity shuliang long integer 8 unit price danjia single precision decimal point after any table is created :( 1) add new data to the table (2) query a field in the table. To implement these two functions, consider the following issues: 1. Make preparations to declare the necessary public variables to establish a connection with the database, create DataSet object 2 add record on DataSet object add record synchronize DataSet object and data in database, this is very important, many people forget to synchronize data, the result is that the added or modified data cannot be saved to the database. 3. query a field of a record in a database table. We can use dataworks to implement the above functions. The following are the main activities of program design. Declare the required C # system class using system; using system. collections. generic; using system. text; using system. io; using system. data; using system. data. oledb; using system. data. sqlclient; declare the public variable private string dblocation; private oledbconnection dbconn; // connect the database to private oledbdataadapter da; establish a connection with the database. The oledb method is used here: dbconn = new oledbconnection (@ "provider = Microsoft. jet. oledb.4.0; Data Source = D: \ 0dbacs \ account. mdb "); dbcon N. open (); Create a DataSet object da = new oledbdataadapter (@ "select * From Kaizhi", dbconn ); // reference the database to connect to dbconn and create the oledbdataadapter object dadataset DS = new dataset () based on the SQL statement "select * From Kaizhi"; // create the DataSet object da. fill (DS); // use the oledbdataadapter object da to fill in, update the newly created DataSet object to add records, and update the database oledbcommandbuilder cb = new oledbcommandbuilder (DA ); // create oledbcommandbuilder object CB to update the insert, delete, and update commands of the oledbdataadapter object da. updatecomman D = CB. getupdatecommand (); // The instruction designer for updating the oledbdataadapter object DA can write his/her own update instruction, or use the system default instruction as described above. However, the preceding statement cannot be missing. Otherwise, the program will throw an exception in running. invalidoperationexception, and prompt: update requires a valid insertcommand when passed datarow collection with new rows. datarow drx = Ds. tables [0]. newrow (); // create a new record row drx ["kzren"] = "kzren"; drx ["kzname"] = "kzname "; drx ["riqi"] = 2008-10-11; drx ["shuoming"] = "shuoming"; drx ["zonge"] = 12; drx ["shuliang"] = 3; drx ["danjia"] = 4; DS. tables [0]. rows. add (drx); // append the record da to the table. UPD Ate (DS); // to update a database, query a field that references a record and reference it directly using the following method. String kx = Ds. tables [0]. rows [0] ["kzren"]. tostring () connection access first let's look at an example code snippet: program code: ------------------------------------------------------------------------ using system. data; using system. data. oledb ;...... string strconnection = "provider = Microsoft. jet. oledb.4.0; "; strconnection + = @" Data Source = c: \ begaspnet \ northwind. mdb "; oledbconnection objconnection = new oledbconnection (strconnection );...... objconnectio N. open (); objconnection. close ();...... ------------------------------------------------------------------------------ explanation: connecting to the ACCESS database requires importing additional namespaces, so it is essential to have the first two using commands! The strconnection variable stores the connection string required to connect to the database. It specifies the data provider to be used and the data source to be used. "provider = Microsoft. jet. oledb.4.0; "is the index data provider. Here we use the Microsoft Jet Engine, which is the Data Engine in access. Asp.net is connected to the access database. "Data Source = c: \ begaspnet \ northwind. MDB indicates the location of the data source. Its standard format is "Data Source = mydrive: mypath \ myfile. mdb ". PS: 1. the "@" symbol after "+ =" prevents "\" in the subsequent string from being parsed as escape characters. 2. if the database file to be connected is in the same directory as the current file, you can use the following method to connect: strconnection + = "Data Source ="; strconnection + = mappath ("Northwind. mdb"); this saves you a lot of writing! 3. note that parameters in the connection string must be separated by semicolons. "oledbconnection objconnection = new oledbconnection (strconnection);" this sentence uses the defined connection string to create a link object. In the future, we will deal with this object for database operations. "objconnection. open (); "this is used to open the connection. now, the connection to the ACCESS database is complete. other operations (insert, delete ...) see related books to connect to SQL Server example code snippet: program code: ---------------------------------------------------------------------------- using system. data; using system. data. sqlclient ;... string strconnection = "User ID = sa; P Assword =; "; strconnection + =" Initial catalog = northwind; server = yoursqlserver; "; strconnection + =" Connect timeout = 30 "; sqlconnection objconnection = new sqlconnection (strconnection );... objconnection. open (); objconnection. close ();... ------------------------------------------------------------------------------ explanation: there is no major difference between the mechanism for connecting to the SQL Server database and the mechanism for connecting to access, but it only changes the parameters of. first, the namespace used to connect to SQL Server is not" System. data. oledb ", but" system. data. sqlclient ". the second is his connection string. We will introduce it one by one (note: the parameters are separated by semicolons): "User ID = sa": the authenticated user name for database connection is Sa. he also has an alias "uid", so we can also write this sentence as "uid = sa ". "Password =": the password for database connection verification is blank. its alias is "PWD", so we can write it as "Pwd = ". note that your SQL server must have a user name and password set to log on. Otherwise, you cannot log on using this method. if your SQL Server is set to Windows logon, you do not need to use the "user ID" and "password" methods to log on here, you need to use "trusted_connection = sspi" to log on. "Initial catalog = northwind": the data source used is "northwind ". Database. his alias is "Database". This sentence can be written as "database = northwind ". "Server = yoursqlserver": Use a server named "yoursqlserver. its alias is "Data Source", "Address", "ADDR ". if the local database is used and the Instance name is defined, you can enter "Server = (local) \ Instance name". If it is a remote server) "Replace with the name or IP address of the remote server. "Connect timeout = 30": the connection timeout is 30 seconds. here, the constructor used to create a connection object is sqlconnection. the rest is no different from access! **************************************** **************************************** * *********************************** Access using C # the motivation for writing this program in the ACCESS database is that when I want to access the msaccess database with C sharp, I cannot obtain any information or reference materials. all the materials available on the Internet are focused on SQL, so we will write this application in two steps. First, we will show how to connect to the msaccess database and see how complicated it is. finally, we completed the program in this way. let's start our discussion. the database connection process has changed significantly compared with the previous ADO connection process. the following chart is appropriate (I hope so) oledbconnection --> oledbcommand --> oledbdatareader. now those who are familiar with ADO can clearly see the similarities between the two, but in order to make those who are not well adapted to ADO understand, the following are some explanations. oledbconnection --> represents a single connection to the database. Based on the features of the underlying database, it can manipulate the database. it is important to remember that even though the oledbconnection object has a function scope, it will not be automatically closed. therefore, you will have to call the close () method of this object. oledbcommand --> This is a common command object that we use in ADO. you can use this object to call the SQL stored procedure or SQL query statement. oledbdatareader --> this class is of great importance because it provides actual access to the underlying dataset of the database. when you call the executereader method of oledbcommand, it will be created ,. net beta2 SDK says you should not directly create objects of this class. now you can. net beta 2 documentation to see more about these main objects, the following shows how to access the source code of the database in the program. using system; using system. data. oledb; Class oledbtest {public static void main () {/create a database connection oledbconnection aconnection = new oledbconnection ("provider = Microsoft. jet. oledb.4.0; Data Source = c: \ db1.mdb "); // create a command object and save the SQL query statement oledbcommand acommand = new oledbcommand (" select * From emp_test ", aconnection ); try {aconnection. open ();/create a datareader object to connect to the form oledbdatareader areader = acommand.exe cutereader (); console. writeline ("this is the returned data from emp_test table");/traverse the database cyclically while (areader. read () {console. writeline (areader. getint32 (0 ). tostring ();}/Close the reader object areader. close ();/Close the connection, which is important to aconnection. close () ;}/ some common exception handling catch (oledbexception e) {console. writeline ("error: {0}", E. errors [0]. message) ;}} successfully runs this program step 1. use msaccess to create a database named db1.mdb 2. create a form named emp_test 3. make it contain the following data domain emp_code int emp_name text emp_ext text 4. save the above Code to sample. CS File 5. make sure that the database is in c: \ and that mdac2.6 or the updated version has been installed. 6. compile and run the program. Now let's take a look at some details about what we can see in the oledbconnection object constructor. Here you can see something like "provider =. below are some driver types compatible with ado.net. sqlolddb --> Microsoft ole db provider for SQL Server, msdaora --> Microsoft OLE DB provider for Oracle, Microsoft. jet. oledb.4.0 --> ole db provider for Microsoft Jet you can select either of them, but they will need to pass different parameters, such as jet. oledb. the MDB file name needs to be passed, while sqloledb needs to pass the user name and password. all these drivers are located in system. data. in the oledb namespace, you must include it, and they are not compatible with oledb provider for ODBC. That is to say, you cannot use these drivers in VB6.0 to access the database, so don't look for information on why you should put these databases on c: \. When you use Microsoft SQL Server 7.0 or the latest version, the following are some guidance provided by Microsoft: recommended. NET data provider uses Microsoft SQL Server 7.0 or an updated mid-tier Application in the following cases, using Microsoft Data Engine (MSDE) or a single-tier Application of icrosoft SQL Server 7.0 or later. we recommend that you set ole db provider for SQL Server (sqloledb) and OLE DB. NET data provider. for Microsoft SQL Server 6.5 and earlier versions, you must use both ole db provider for SQL Server and Ole db.net data provider. we recommend that you use ole db for Microsoft SQL Server 6.5 and earlier versions or Oracle's intermediate tier applications. NET data provider. SQL Server is recommended for Microsoft SQL Server 7.0 or later versions. NET data provider. microsoft Access database is recommended for single-tier applications. it is not recommended that an intermediate tier program use ole db at the same time. NET data provider and Microsoft Access database. ole db provider for ODBC (msdasql) is no longer supported)