The following CRUD operations can be performed using the data access API. Suppose we have a table called “data” with the fields id (Unique identifier, Primary key) and name (varchar (200)).

Here's what the data in the table looks like:

To use the Data Access API, utilise the namespace FlowWright.DataAccess as shown below: 

Let's select all users from the data table.

string connStr = "Data Source=COBRA;Initial Catalog=FlowWright10;User ID=sa;Password=xxx";
 
string sSQL = "Select * from data";
 
DataTable oDT = FWDataAccess.GetDataTable(connStr, sSQL, out string sError);

After executing the above code, the data table displays the following data:

Insert new record.

You can insert a record in two ways: SQL or a built-in function. Let's start with the SQL method first.

sSQL = "INSERT INTO data(id, name) VALUES(?,?)";
 
OrderedDictionary oParms = new();
oParms["id"] = Guid.NewGuid().ToString();
oParms["name"] = "Jackson";
 
bool status = FWDataAccess.NonQuery(connStr, sSQL, out sError, oParms);

Note: The above code uses a parameterized SQL query to avoid SQL injection issues.

You can also employ the following method to insert: 

Hashtable oParms = new();
oParms["id"] = Guid.NewGuid().ToString();
oParms["name"] = "Jackson";
 
 
bool status = FWDataAccess.InsertRecord(connStr, "data", oParms);

Read a record.

Retrieve a record using an SQL statement.

sSQL = "Select * from Data Where name LIKE 'Bob'";
DataTable oDT = FWDataAccess.GetDataTable(connStr, sSQL, out sError);

Read a single value with a specific type.

string name = FWDataAccess.GetScalarValue<string>(connStr, sSQL, out sError, oParms);

Update a record.

You can update a record using the following parameterized SQL query:

OrderedDictionary oParms = new();
oParms["name"] = "Dolly";
oParms["id"] = "e20cb694-a754-4448-8301-70cdf0d02711";
 
sSQL = "Update data set name=? where id like ?";
 
bool status = FWDataAccess.NonQuery(connStr, sSQL, out sError, oParms);

You can also update using direct methods, as demonstrated below:

Hashtable oParms = new();
oParms["name"] = "Dolly";
oParms["id"] = "e20cb694-a754-4448-8301-70cdf0d02711";
 
FWDataAccess.UpdateRecord(connStr, "data", oParms, "id");

Delete a record.

Delete a record with a SQL parameterized query.

OrderedDictionary oParms = new();
oParms["id"] = "e20cb694-a754-4448-8301-70cdf0d02711";
 
sSQL = "Delete From data Where id=?";
 
bool status = FWDataAccess.NonQuery(connStr, sSQL, out sError, oParms);

Remove a record with the API method:

FWDataAccess.DeleteRecord(connStr, "data", "name", "bob");

Upsert a record.

The Upsert method inserts data if the record doesn't exist; otherwise, it updates the existing record.

Hashtable oParms = new();
oParms["id"] = "e20cb694-a754-4448-8301-70cdf0d02711";
oParms["name"] = "Dale";
 
bool status = FWDataAccess.UpsertRecord(connStr, "data", oParms, "id");

Transactions.

Multiple SQL calls can be made within a transaction, which is committed if successful and rolled back if not. The following code demonstrates how to perform transactions.

OrderedDictionary oParms = new();
oParms["id"] = Guid.NewGuid().ToString();
oParms["name"] = "Dale";
 
 
sSQL = "INSERT INTO data(id, name) VALUES(?,?)";
 
OrderedDictionary oParms2 = new();
oParms2["id"] = Guid.NewGuid().ToString();
oParms2["name"] = "Jen";
 
FWDataAccess oDA = new FWDataAccess(connStr);
 
oDA.BeginTransaction();
 
try
{
    oDA.NonQuery(sSQL, oParms);
 
    oDA.NonQuery(sSQL, oParms2);
 
    oDA.CommitTransaction();
}
catch 
{
    oDA.RollbackTransaction();            
}
 
oDA.Disconnect();