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

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

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

Let's select all the 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 running the above code, the resulting data table has the following data:

Insert new record

You an insert a record using 2 ways, using SQL or using a built in function, let's do 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? parameterized SQL query to avoid SQL injection issues.

You can also use the following method to insert also: 

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

Read a record

Read 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 using a 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 use the direct methods to update, as shown 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 using 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);

Delete a record using API method:

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

Upsert a record

The Upsert method will insert the data if the record does not exist; otherwise, it will update 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 called within a transaction, committed if successful, and rolled back if unsuccessful.  The following code shows 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();