Click here to Skip to main content
14,271,874 members

Free Tools

This forum is for discussing and recommending Free tools for software development. Please post direct links to tools, and not just links to pages that review or list tools.No shareware and no commercial products allowed. Please report spammers by voting to remove their messages and reporting their accounts.

 
GeneralMessage Closed Pin
21hrs 57mins ago
memberMember 1456732121hrs 57mins ago 
GeneralMessage Closed Pin
4-Aug-19 10:39
membermark clo4-Aug-19 10:39 
GeneralMessage Closed Pin
30-Jul-19 16:29
memberMember 1454616130-Jul-19 16:29 
GeneralFree NuGet Components Pin
MSBassSinger16-Jul-19 11:16
professionalMSBassSinger16-Jul-19 11:16 
GeneralRe: Free NuGet Components Pin
Richard MacCutchan16-Jul-19 21:26
protectorRichard MacCutchan16-Jul-19 21:26 
GeneralRe: Free NuGet Components Pin
MSBassSinger17-Jul-19 1:11
professionalMSBassSinger17-Jul-19 1:11 
GeneralMessage Closed Pin
7-Aug-19 20:41
memberjohnnick7-Aug-19 20:41 
AnswerJHelpers README Contents Pin
MSBassSinger17-Jul-19 12:59
professionalMSBassSinger17-Jul-19 12:59 
GeneralRe: JHelpers README Contents Pin
Richard MacCutchan17-Jul-19 21:19
protectorRichard MacCutchan17-Jul-19 21:19 
AnswerJLoggers README Content Pin
MSBassSinger17-Jul-19 13:34
professionalMSBassSinger17-Jul-19 13:34 
AnswerJDAC README Contents Pin
MSBassSinger17-Jul-19 14:26
professionalMSBassSinger17-Jul-19 14:26 
JDAC Data Access Component
==========================

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.Datat.DataTable objects to various
uses. Details are provided below, along with code samples.

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

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, or null.

Asynchronous method to execute SQL that does not return a dataset. 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 = "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

Code Example

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.

Code Example

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

Code Example

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.

Code Example

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 info, 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 foriegn 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.

Code Example

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

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.

Code Example:

SchemaFactory fac = new SchemaFactory();
String csClass = fac.ConvertTableToCSClassAsync(retVal, "MyNamespace.Something",
                                            schemaType.ToString() + "DO").Result;
System.IO.File.WriteAllText(Environment.CurrentDirectory + @"\" + schemaType.ToString() + "DO.cs",
                            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.

Code Example

SchemaFactory fac = new SchemaFactory();
DataTable retVal = <some DataTable>
SchemaTypesEnum schemaType = <one of the SchemaTypesEnum enum values other than Undefined>
String factoryMethod = fac.GetFactoryMethodSchemaCodeAsync(retVal, schemaType).Result;
System.IO.File.AppendAllText(Environment.CurrentDirectory + @"\SchemaFactoryMethod.cs", 
                             factoryMethod);
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<> 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.

Code Example

SchemaFactory fac = new SchemaFactory();
DataTable retVal = <some DataTable>
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:

SchemaFactory fac = new SchemaFactory();
DataTable retVal = <some DataTable>
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.

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

JDataAccessSQL dac = new JDataAccessSQL(@"SQLServerABC\SomeInstance",
                                  "CookieStore", true, "", "", 10, 20, 
                                  3, 10, "MyProgram", "MyComputer", 20161, true);
SchemaFactory fac = new SchemaFactory();
DataTable retVal = <some DataTable>
List<AllColumnsDO> returnValue = fac.GetAllColumnsDO(dac).Result;
fac = null;


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)

GeneralMessage Closed Pin
11-Jul-19 21:31
memberrichardsmith702111-Jul-19 21:31 
GeneralVisual studio image library Pin
HumourStill10-Jul-19 18:38
memberHumourStill10-Jul-19 18:38 
GeneralLooking for Recent Article on Backup Utility Pin
Patrick Skelton9-Jul-19 21:37
memberPatrick Skelton9-Jul-19 21:37 
GeneralRe: Looking for Recent Article on Backup Utility Pin
Simon_Whale14-Jul-19 21:56
professionalSimon_Whale14-Jul-19 21:56 
GeneralRe: Looking for Recent Article on Backup Utility Pin
Patrick Skelton14-Jul-19 22:46
memberPatrick Skelton14-Jul-19 22:46 
GeneralRe: Looking for Recent Article on Backup Utility Pin
Simon_Whale14-Jul-19 22:48
professionalSimon_Whale14-Jul-19 22:48 
GeneralRe: Looking for Recent Article on Backup Utility Pin
Nelek17-Jul-19 10:46
protectorNelek17-Jul-19 10:46 
GeneralRe: Looking for Recent Article on Backup Utility Pin
Patrick Skelton17-Jul-19 22:00
memberPatrick Skelton17-Jul-19 22:00 
NewsAsmSpy: Your Next Must-Have Tool If You Write in C#, VB, or F# Pin
David A. Gray2-Jul-19 5:00
groupDavid A. Gray2-Jul-19 5:00 
GeneralLooking for timer tool that works with TFS 2018 Pin
May16892-Jul-19 1:02
memberMay16892-Jul-19 1:02 
QuestionRe: Looking for timer tool that works with TFS 2018 Pin
Maciej Los2-Jul-19 5:52
protectorMaciej Los2-Jul-19 5:52 
AnswerRe: Looking for timer tool that works with TFS 2018 Pin
May16892-Jul-19 10:00
memberMay16892-Jul-19 10:00 
GeneralMessage Removed Pin
26-Jun-19 20:01
membersmithenkelly26-Jun-19 20:01 
GeneralStackblitz: do web dev in your browser, free Pin
raddevus24-Jun-19 4:27
mvaraddevus24-Jun-19 4:27 

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.