Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / Visual Basic

JDAC - Data Access and More

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
30 Jul 2019CPOL10 min read 7.8K   3   2
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

C#
DBReturnValue()

The default constructor for the class.

C#
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

C#
List<SqlParameter> - (Get/Set)

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

C#
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.

C#
String ErrorMessage – (Get/Set)

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

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

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

Methods

C#
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:

C#
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.

    C#
    //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.

    C#
    //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.

    C#
    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
    C#
    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.

    C#
    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
    C#
    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.

    C#
    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
C#
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.

    C#
    //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.

    C#
    // 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.

    C#
    // 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)


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionWhere? Pin
LightTempler30-Jul-19 6:37
LightTempler30-Jul-19 6:37 
AnswerRe: Where? Pin
MadMyche30-Jul-19 10:56
professionalMadMyche30-Jul-19 10:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.