JDAC - Data Access and More

MSBassSinger

0.00/5 (No votes)

30 Jul 2019CPOL10 min read

8.9K

JDAC is an excellent data access component instead of Entity Framework with its higher support and maintenance costs.

This article appears in the Third Party Products and Tools section. Articles in this section are for the members only and must not be used to promote or advertise products in any way, shape or form. Please report any spam or advertising.

Introduction

JDAC is a .NET Standard 2.0 library component that can be used with any .NET project, whether Core or Standard, on any supported OS to access SQL Server. The library provides versatile and easy access to execute SQL queries or execute SQL with no returned dataset. There are also some built-in queries for schema information, and methods to convert System.Data.DataTable objects to various uses, including domain object classes (C#).

Background

Accessing a SQL Server database is not difficult, but the code is repetitive. I created JDAC after seeing how brittle and convoluted Entity Framework (EF) is, particularly in the support and maintenance phases of a software development project when database schema changes occur and ripple through the code using EF. I find it is, overall, less time consuming to create my own schema files, stored procedures, etc. and make the data access generic. Along the way, I got tired of writing simple domain object classes to hold data from a query or to send for an update. So I added method calls in JDAC that can convert any given datatable to the C# code for a matching domain object, and the separate C# code for a method to populate that same domain object. That functionality is NOT required to make good use of JDAC, but it can be helpful.

Using the Code

Understanding the DBReturnValue Object

When executing a query of any kind, there is an opportunity to do more than just execute a query. There may be return parameters or a return code. If something goes wrong and an exception is thrown, it is often the case that there are multiple layers of exceptions that could be examined to troubleshoot the issue.
DBReturnValue has several properties that address these issues and any System.Data.Dataset that is returned. The class is marked “[Serializable]”.

Constructors

DBReturnValue()

The default constructor for the class.

DBReturnValue(List<SqlParameter> sqlParams, Int32 retCode, String errorMessage)

Constructor for providing the SQL Parameters, the return code, and an error message, if any. This constructor is normally used internally.

Properties

List<SqlParameter> - (Get/Set)

The parameters passed in, and after execution, the parameters are updated for any out parameters.

Int32 RetCode – (Get/Set)

The return code from a query execution. It can be the number of records affected, or a value representing status, depending on the type of SQL executed. If it is “execute no query” SQL, then RetCode is the number of records affected.

String ErrorMessage – (Get/Set)

An error message coming back from the SQL execution. If no exceptions, then it is an empty string.

System.Data.DataSet Data – (Get/Set)

For calls that return data, this is the dataset returned.

Methods

Dispose()

Called, usually in a Finally block, to dispose of any resources the object instance has. An internal flag ensures the internal Dispose process is called only once. There is also a finalizer method for the Garbage Collector to call.

Understanding the JDataAccessSQL Object

The JDataAccessSQL object is the workhorse for accessing data from SQL Server.

Constructors

JDataAccessSQL()

Default constructor:

JDataAccessSQL(String server,
               String defaultDB,
               Boolean useAuthentication,
               String username,
               String password,
               Int32 connectionTimeout,
               Int32 commandTimeout,
               Int32 connectRetryCount = 3,
               Int32 connectRetryInterval = 10,
               String applicationName = "",
               String workstationID = "",
               Int32 portNumber = 1433,
               Boolean connectionPooling = true)

This constructor takes all the necessary and optional data points needed for a connection to SQL Server. The last six parameters provide default values, in case those are acceptable to you. When useAuthentication = true, use blank strings for the username and password. If you use other values, they will be ignored.

For particulars on the parameters, see their matching properties below.

Properties

String Server - (Get/Set): Name of the server, including the instance name, if used
String DefaultDB - (Get/Set): The database to use as default in the connection string
Boolean UseAuthentication - (Get/Set): Whether to use Windows authentication or not. True to use it, false to use a username and password
String UserName - (Get/Set): Username to use if NOT using Windows Authentication
String Password - (Get/Set): Password to use if NOT using Windows authentication
Int32 ConnectionTimeout - (Get/Set): Number of seconds for the connection object to wait on a timeout exception
Int32 CommandTimeout - (Get/Set): Number of seconds for a command object to wait on an inactive command execution before timing out
Int32 PortNumber - (Get/Set)
Port number to use for connecting to SQL Server. If 0 or less, port 1433 is used. Default value is 1433.
Int32 ConnectRetryCount - (Get/Set): How many times to try reconnecting the connection before throwing an exception. Default is 3. See also ConnectRetryInterval
Int32 ConnectRetryInterval - (Get/Set): How long, in seconds, to wait between reconnect attempts. Default is 10. See also ConnectRetryCount
String ApplicationName - (Get/Set): Optional name for the application using this. That value is used by SQL Server. Default value is an empty string
String WorkstationID - (Get/Set): An ID string identifying the workstation the connection is made from. This is optional. The default value is an empty string
Boolean UseConnectionPooling – (Get/Set): True to use connection pooling (default), and false if not
String ConnectionString – (Get): Read-only connection string

Methods with Examples

async Task<Boolean> CheckConnectionAsync()

A true/false check to see if the connection can be made.

//Code example:
JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                        "CookieStore", true, "", "",
                                        10, 20, 3, 10, "MyProgram",
                                        "MyComputer", 20161, true);
Boolean retVal = dac.CheckConnectionAsync().Result;
dac.Dispose();
dac = null;

async Task<DBReturnValue> ExecuteQueryAsync(String cmd, // SQL Command
Boolean isSP, // True if a stored procedure, false if not
List<SqlParameter> sqlParams) // List of parameter objects, or
// null if no parameters used.

Asynchronous method to execute a query and return data. DBReturnValue instance with results and parameters that have post-execution values.

//Code Example:
JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
String cmd = "SELECT * FROM dbo.Cookies WHERE ID > @id";
List<SqlParameter> queryParams = new List<SqlParameter>();
queryParams.Add(new SqlParameter
               {
               DbType = DbType.Int64,
               ParameterName = "@id",
               Value = 0
               });
DBReturnValue dbRetVal = dac.ExecuteQueryAsync(cmd, false, queryParams).Result;
DataSet retVal = dbRetVal.Data;
dac.Dispose();
dac = null;

async Task<DBReturnValue> ExecuteStatementAsync(String cmd,  // SQL Command Boolean isSP,                 // True if a stored procedure, false if not List<SqlParameter> sqlParams) // List of parameter objects

Asynchronous method to execute SQL that does not return a dataset. DBReturnValue instance with results and parameters that have post-execution values.

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
String cmd = "UPDATE dbo.Cookies SET Name='Raspberry' WHERE ID = @id";
List<SqlParameter> queryParams = new List<SqlParameter>();
queryParams.Add(new SqlParameter
{
  DbType = DbType.Int64,
  ParameterName = "@id",
  Value = 1
});
DBReturnValue dbRetVal = dac.ExecuteStatementAsync(cmd, false, queryParams).Result;
dac.Dispose();
dac = null;

async Task<SQLServerData> GetServerPropertiesAsync()

Asynchronous method to query data on the SQL Server properties. SQLServerData is serializable and has a data contract. The properties for the SQLServerData class are:

String SvrName
String ServerMachineName
String EditionName
String ProductVersion
String ProductLevel
String LicenseType
Boolean IsIntegratedSecurityOnly
Boolean IsClustered
String Language
String Platform
String Description
Int32 ProcessorsCount

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
SQLServerData retVal = dac.GetServerPropertiesAsync().Result;
dac.Dispose();
dac = null;

async Task<List<String>> GetDatabaseNamesAsync()

Asynchronous method to get a list of database names. More complete data can be obtained by using the GetSchema(SchemaTypesEnum.Databases) method. A list of string names for the databases is returned by this method.

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
List<String> retVal = dac.GetDatabaseNamesAsync().Result;
dac.Dispose();
dac = null;

async Task<DataTable> GetDatabaseRelationshipsAsync(String targetDatabase = "")

Asynchronous method to get a list or relationships in the database specified. Default database is the one specified in the connection string. Returns a System.Data.DataTable with relationship data. The columns are all String with the following names:

Foriegn_Key_Name
Parent_Table
Primary_Key_Column
Child_Table
Foriegn_Key_Column

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
DataTable retVal = dac.GetDatabaseRelationshipsAsync("Test").Result;
dac.Dispose();
dac = null;

async Task<List<String>> GetTableNamesAsync(String targetDatabase = "")

Asynchronous method to get names of tables for the database specified, or by default uses the database named in the connection string. The method returns a list of table names for the database specified, or by default, the database named in the connection string.

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
List<String> retVal = dac.GetTableNamesAsync().Result;
dac.Dispose();
dac = null;

async Task<DataTable> GetSchemaAsync(SchemaTypesEnum schemaType, String targetDatabase = "")
Asynchronous method to get the schema elements data based on the schema type requested. The method returns a DataTable with the schema definitions. DataTable columns vary with each schema type. You can use the SchemaFactory object to get the same schema information as serializable objects with properties specific to the schema type.

SchemaTypesEnum

Unspecified - Default value
MetaDataCollections - Lists the names of the known schemas
DataSourceInformation = SQL Server information
DataTypes = List of SQL Server data types and information, plus their .NET equivalents
ReservedWords - List of reserved words in T-SQL for this server.
Users - List of database users
Databases - List of databases on the server
Tables - List of tables for the database specified in the connection string
Columns - List of all columns in the tables for the database specified in the connection string
AllColumns - Functionally, the same as Columns above, but may provide other columns not normally available
ColumnSetColumns - ColumnSets (SQL 2016 and newer)
StructuredTypeMembers - List of structured types
Views - List of views for the database specified in the connection string
ViewColumns - List of columns in views for the database specified in the connection string
ProcedureParameters - List of parameters for stored procedures for the database specified in the connection string
Procedures - List of stored procedures for the database specified in the connection string
ForiegnKeys - Data on foreign key relationships for the database specified in the connection string
IndexColumns - List of indices and their columns for the database specified in the connection string
Indexes - List of indexes for the database specified in the connection string
UserDefinedTypes - List of user-defined types for the database specified in the connection string

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
DataTable retVal = dac.GetSchemaAsync(SchemaTypesEnum.AllColumns).Result;
dac.Dispose();
dac = null;

Understanding the SchemaFactory Object

The SchemaFactory object is used to create and populate strongly typed objects that contain schema data. There are also some other methods that can be quite useful.

Methods and Code Examples

async Task<String> ConvertTableToCSClassAsync (DataTable dt, String namespaceName, String className)
Takes a DataTable and creates a data object C# class as a string. The class is marked for serialization and use as a data object. You specify the namespace you want the class to have, and the name of the .cs class. The method returns a String that can be written to a file as a C# class. One example of the use for this function is when using this library to execute SQL, you can take the DataTable that is returned from a SQL query, use it in this method, and now you have a C# class, fully coded and ready to compile. This method was used to create the code for the schema classes used in this library.

C#
```
// Code Example:
// retVal is a DataTable object you have obtained elsewhere
String targetClassFileName = Environment.CurrentDirectory + 
                             @"\" + schemaType.ToString() + "DO.cs";
SchemaFactory fac = new SchemaFactory();
String csClass = fac.ConvertTableToCSClassAsync(retVal,
                                                "MyNamespace.Something",
                                                schemaType.ToString() + "DO").Result;
System.IO.File.WriteAllText(targetClassFileName, csClass);
fac = null;
```
async Task<String> GetFactoryMethodSchemaCodeAsync(DataTable dt, SchemaTypesEnum schemaType, String altDOName = "Test")
Creates the code for an async method to populate a DO class, using a JDataAccessSQL data access component passed in as a parameter. The method is specific to a DO created by ConvertTableToCSClass(), using the schemaType and DataTable passed in. The method returns a string with the full C\# method code. One example of the usage of this method is to create the code needed to create the corresponding class for the schema. This method was used for code generation for some of this library.

C#
```
// Code Example:
// retVal is a DataTable object you have obtained elsewhere
String targetClassFileName = Environment.CurrentDirectory +
                             @"\SchemaFactoryMethod.cs";
SchemaFactory fac = new SchemaFactory();
SchemaTypesEnum schemaType = <one of the SchemaTypesEnum enum values other than Undefined>
String factoryMethodString = 
          fac.GetFactoryMethodSchemaCodeAsync(retVal, schemaType).Result;
System.IO.File.AppendAllText(targetClassFileName, factoryMethodString);
fac = null;
```

async Task<String> GetFactoryMethodCodeAsync(DataTable dt, String dOName)

This method takes a DataTable object, and creates the C\# code for a method to populate an instance of a class (the class name is represented by the String parameter dOName). The properties MUST have the same name and case-sensitive spelling as the column names in the DataTable. If you used the method "ConvertTableToCSClassAsync" in this SchemaFactory object, that part will be taken care of. Note that the class represented by the name dOName is one data row, not a collection.

//Code Example:
JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
DBReturnValue retVal = dac.ExecuteQueryAsync("SELECT * FROM Cookies", false, null).Result;
DataTable retTable = retVal.Data.Tables[0];
SchemaFactory sf = new SchemaFactory();
// This gets the C# code for the class representing the table data,
// in this example called CookieDO..
String tempCSFile = sf.ConvertTableToCSClassAsync
                    (retTable, "YourNamespace.Cookies", "CookieDO").Result;
// This gets the C# code to populate one instance of the class (the code created above)
// and return 1 to n instances in a List<T> object. In this example, it would be
// a List<CookieDO> object.
String tempMethod = sf.GetFactoryMethodCodeAsync(retTable, "CookieDO").Result;
sf = null;
dac.Dispose();
dac = null;

So now you can have this library write the C# class file and the method to populate it, all from an instance of the DataTable from your query.

async Task<String> GetDataTableContentsAsDelimitedFileAsync(DataTable dataTable, String fieldDelimiter)
Takes a DataTable and creates a string to use for a delimited file from the data, using the specified field delimiter. The caller must decide what to use as a delimiter that would not be used in the data. The value [((Char)28).ToString()] is recommended.

C#
```
// Code Example:
// retVal is a DataTable object you have obtained elsewhere
SchemaFactory fac = new SchemaFactory();
String schema = fac.GetDataTableContentsAsDelimitedFileAsync(retVal,
                                                            ((Char)28).ToString()).Result;
fac = null;
```

async Task<String> GetDataTableSchemaAsDelimitedFileAsync(DataTable dataTable, String fieldDelimiter)

Takes a DataTable and creates a delimited file from the schema, using the specified field delimiter. The caller must decide what to use as a delimiter that would not be used in the data. The value [((Char)28).ToString()] is recommended. The method returns a String containing the delimited file.

// Code Example:
// retVal is a DataTable object you have obtained elsewhere
SchemaFactory fac = new SchemaFactory();
String schema = fac.GetDataTableSchemaAsDelimitedFileAsync(retVal,
                                                          ((Char)28).ToString()).Result;
fac = null;

The following methods return schema objects with information on specific schema collections that correspond to the SchemaTypesEnum enumeration. This method creates and populates an object instance with the data about the schema type query matching the returned object.

*** The code created requires the NuGet package System.Data.DataExtensions. ***

This is a typical code example. You may adapt it to any of the methods.

// Code Example:
// retVal is a DataTable object you have obtained elsewhere
JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                         "CookieStore", true, "", "",
                                         10, 20, 3, 10, "MyProgram",
                                         "MyComputer", 20161, true);
SchemaFactory fac = new SchemaFactory();
List<AllColumnsDO> returnValue = fac.GetAllColumnsDO(dac).Result;
fac = null;

Similar Methods

async Task<List<AllColumnsDO>> GetAllColumnsDO(JDataAccessSQL dac)
async Task<List<ColumnsDO>> GetColumnsDO(JDataAccessSQL dac)
async Task<List<ColumnSetColumnsDO>> GetColumnSetColumnsDO(JDataAccessSQL dac)
async Task<List<DatabasesDO>> GetDatabasesDO(JDataAccessSQL dac)
async Task<List<DataSourceInformationDO>> GetDataSourceInformationDO(JDataAccessSQL dac)
async Task<List<DataTypesDO>> GetDataTypesDO(JDataAccessSQL dac)
async Task<List<ForiegnKeysDO>> GetForiegnKeysDO(JDataAccessSQL dac)
async Task<List<IndexColumnsDO>> GetIndexColumnsDO(JDataAccessSQL dac)
async Task<List<IndexesDO>> GetIndexesDO(JDataAccessSQL dac)
async Task<List<MetaDataCollectionsDO>> GetMetaDataCollectionsDO(JDataAccessSQL dac)
async Task<List<ProcedureParametersDO>> GetProcedureParametersDO(JDataAccessSQL dac)
async Task<List<ProceduresDO>> GetProceduresDO(JDataAccessSQL dac)
async Task<List<ReservedWordsDO>> GetReservedWordsDO(JDataAccessSQL dac)
async Task<List<StructuredTypeMembersDO>> GetStructuredTypeMembersDO(JDataAccessSQL dac)
async Task<List<TablesDO>> GetTablesDO(JDataAccessSQL dac)
async Task<List<UserDefinedTypesDO>> GetUserDefinedTypesDO(JDataAccessSQL dac)
async Task<List<UsersDO>> GetUsersDO(JDataAccessSQL dac)
async Task<List<ViewColumnsDO>> GetViewColumnsDO(JDataAccessSQL dac)
async Task<List<ViewsDO>> GetViewsDO(JDataAccessSQL dac)

History

When	Who	What
07/26/2019	JDJ	Genesis

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)