SQL Anywhere. NET

SQL Anywhere. NET Support

1. ADO is the latest data access API in Microsoft's ODBC, OLE DB, and ADO family. It is a Microsoft. NET Framework data access component that can be used to access a relational database system.

2. The SQL Anywhere. NET Data provider implements the IAnywhere.Data.SQLAnywhere namespace, allowing you to use any language that supports. NET, such as C # and Visual Basic. NET) to write programs and access data from the SQL Anywhere database.

SQL Anywhere. NET Data Provider features

SQL Anywhere supports Microsoft. NET Framework versions 2.0, 3.0, 3.5, and 4.0. Three different namespaces are available:

1 , IAnywhere.Data.SQLAnywhere The ADO object model is a universal data access model.

2. System.Data.Oledb This namespace supports OLE DB data sources.

3. System.Data.Odbc This namespace supports ODBC data sources.

using SQL Anywhere. NET The program has advantages:

In the. NET environment, SQL anywhere. NET programs provide local access to SQL anywhere. It communicates directly with the SQL Anywhere server without the need to use Bridge technology. Therefore, SQL Anywhere. NET data providers are faster than OLE DB and ODBC data providers. We recommend that you use this program to access the SQL Anywhere database.

in Visual Studio using. NET in Projects program

Use SQL Anywhere. NET programs and Visual Studio to develop. NET applications by adding SQL Anywhere in the source code. NET program, and a line that references that SQL Anywhere. NET Program Class statement.

Steps:

1. Start Visual Studio and open the project.

2. In the Solution Explorer window, right-click References, and then click Add Reference.

The reference will indicate which providers to include and locate the code for the SQL Anywhere. NET data Provider.

Click the [. NET] tab, and scroll through the list to locate one of the following:

IAnywhere.Data.SQLAnywhere (. NET 2)

IAnywhere.Data.SQLAnywhere (. NET 3.5)

IAnywhere.Data.SQLAnywhere (. NET 4)

Note: The default location is %sqlany12%\ce\assembly\v2.

3. Click the provider you want, and then click [OK]. The provider is added to the References folder of the project's Solution Explorer window.

4. Specify a directive to the source code to help use the SQL Anywhere. NET Data Provider namespace and the types defined therein.

Add the following command line to the project:

Using C #, add the following line to the list of instructions at the beginning of the project using :

Using IAnywhere.Data.SQLAnywhere;

SQL Anywhere. NET Program System Requirements

Install the following items on your computer or handheld device:

The. NET Framework and/or. NET Compact Framework version 2.0 or later.

Visual Studio 2005 or later, or. NET language compilers, such as C # (required only at development time).

SQL Anywhere. NET Required files for the program

The SQL Anywhere. NET Data Provider code resides in the DLLs for each platform.

Windows The required files

For Windows (except for Windows Mobile), you need one of the following DLLs:

%sqlany12%\v2\assembly\v2\ianywhere.data.sqlanywhere.dll

%sqlany12%\v2\assembly\v3.5\ianywhere.data.sqlanywhere.v3.5.dll

%sqlany12%\v2\assembly\v4\ianywhere.data.sqlanywhere.v4.0.dll

The choice of DLL depends on the version of. NET that is oriented.

The following DLLs are also required for the Windows version of the provider.

The Policy.12.0.ianywhere.data.sqlanywhere.dll policy file can be used to replace the provider version that created the application. Sybase updates the policy file whenever an update about the provider is published. There is also a policy file (Policy.12.0.ianywhere.data.sqlanywhere.v3.5.dll) for the provider for version 3.5, and a policy file for the provider for version 4.0 ( Policy.12.0.ianywhere.data.sqlanywhere.v4.0.dll).

Dblgen12.dll The language DLL contains the English (EN) message issued by the provider. Many other languages are available, including Chinese (en), French (FR), German (DE), and Japanese (JP).

Dbcon12.dll This DLL contains support code for the [Connect to SQL Anywhere] window.

Connecting to a database

1. Create a Saconnection object named [] using the following code conn :

Saconnection conn = new Saconnection (connection-string);

Or

Using (Saconnection conn = new Saconnection (connection-string))

{};

Note: The using statement ensures that objects that use the IDisposable object interface can be easily freed.

2. Open a connection to the database.

Conn. Open ();

3. Close and Database

Conn. Close ();

Accessing and manipulating data

SQL Anywhere. NE provides two ways to access data:

Sacommand Object

The Sacommand object allows users to retrieve or modify SQL statements of data directly from the database and invoke stored procedures.

Sacommand object, Sadatareader is used to return a read-only result set from a query or stored procedure. Sadatareader only returns one row at a time, but this does not degrade performance because the SQL Anywhere client's library uses prefetch buffering to prefetch multiple rows at a time.

You can use the Sacommand object to change the group composition transaction instead of in autocommit mode. When you use the Satransaction object, the row is locked so that other users cannot modify it.

Sadataadapter Object

The Sadataadapter object retrieves the entire result set into a dataset. A dataset is a disconnected store that is used to hold data retrieved from a database. You can then edit the data in the dataset, and when the edits are complete, the Sadataadapter object updates the database with the changes made to the dataset. When using Sadataadapter, it is not possible to prevent other users from modifying rows in the DataSet. You need to include logic in your application to resolve any conflicts that may occur.

using Sacommand Object Access Data

Use the Sacommand object to execute SQL statements or call stored procedures against the SQL Anywhere database.

You can use either of the following methods to retrieve data from a database:

ExecuteReader emits a SQL query that returns a result set. This method uses a forward-only, read-only cursor. You can quickly iterate through the rows in the result set in one direction.

ExecuteScalar emits an SQL query that returns a single value. Can be the first column in the first row of the result set, or an SQL statement that returns a collection value, such as COUNT or AVG. This method uses a forward-only, read-only cursor.

using Sacommand Object Manipulation Data

Use the Sacommand object to perform an insert, update, or delete, using the executenonquery function. The ExecuteNonQuery function emits a query (SQL statement or stored procedure) that does not return a result set.

Note: ' 1 ' or ' 1 ' = ' 1 ' the query vulnerability issue.

Spelling string

Cmd.commandtext = "SELECT id from \" user\ "WHERE name =" + name;

Workaround named parameters

Cmd.commandtext = "SELECT id from \" user\ "WHERE name =: Name";

Cmd. Parameters.Add ("name", name);

Sadataadapter object access and manipulation data

Use Sadataadapter to view the entire result set and populate the query results into this DataTable using the Fill method.

using a DataTable The example

Saconnection conn = new Saconnection ("Data source=dmis");

Conn. Open ();

DataTable dt = new DataTable ("Results");

Sadataadapter da = new Sadataadapter ("SELECT * from Employees", conn);

Da. Fill (DT);

Conn. Close ();

using a DataSet Example

Saconnection conn = new Saconnection ("Data source=dmis");

Conn. Open ();

DataSet ds = new DataSet ();

Sadataadapter da = new Sadataadapter ("SELECT * from Employees", conn);

Da. Fill (ds, "Results");

Conn. Close ();

Datagridview1.datasource = ds. tables["Results"];

Database link string

In the Web. config file, add

<connectionStrings>

<add name= "connstring" connectionstring= "Dsn=webdata;uid=user;pwd=password"/>

</connectionStrings>

Reference connstring in a. cs file

configurationmanager.connectionstrings["ConnString"]. ConnectionString;

Null in the database

A null value in a database field is not equivalent to a null value in C #.

A null value in the database field that corresponds to the DBNull.Value value in C #.

You can use the following functions to convert:

Judging DBNull values

public Object IsDbNull (object Dbvalue)

{

if (Convert.isdbnull (Dbvalue))

{

Object value = NULL;

return value;

}

Else

{

return dbvalue;

}

}