SQLServerConnector

Example (Visual Basic):

      '------MyKey is a valid SQLSchemaAPIKey object

      Dim x As SqlServerConnector = MyKey.NewSchemaConnector()

      Dim sql As String = "<some sql statement>"

      Dim dt As DataTable = x.OpenSQLDataTable(sql, "<sometablename>")

       MessageBox.Show(dt.Rows.Count() & " rows")

Primary Database Properties (ReadOnly)

Inherited from the parent SQLSchemaAPIKey object

ServerName [String][ReadOnly]. Corresponds to your Schema Profile.

DatabaseName [String][ReadOnly]. Corresponds to your Schema Profile.

SQLUserID [String][ReadOnly]. Corresponds to your Schema Profile.

SQLPassword [String][ReadOnly]. Corresponds to your Schema Profile.

ConnectionTimeout [Integer][ReadOnly]. Corresponds to your Schema Profile.

UseTrustedConnection [Boolean][ReadOnly]. Corresponds to your Schema Profile.

UseAzureConnection [Boolean][ReadOnly]. Corresponds to your Schema Profile.

StoreSegmentDataInManifest [Boolean][ReadOnly]. Corresponds to your Schema Profile.

Other Properties

DBConnectionState [System.Data.ConnectionState][ReadOnly]. The current state of the internal SQLConnection object.

ConnectionString [String][ReadOnly]. SQL Connection String assembled from the properties listed above.

IsConnected [Integer][ReadOnly]. Whether an active connection to the database is open.

RecordsAffectedByLastSQL [Integer][ReadOnly]. The SQL return of the number of rows affected by the last command ExecuteSQL() or RunTransaction(). When RunTransaction() is called this property will contain the SUM of records affected by all of your commands.

UseDynamicCommandTimeOut [Boolean][Get : Set]. Inherited from the parent SQLSchemaAPIKey object (Default is True). If true whenever you execute a SQL command or transaction ( See ExecuteSQL() and RunTransaction() ) and do NOT pass in a specific CommandTimeOut of your own the CommandTimeOut is set to the ConnectionTimeOut in your schema profile X 5 (so a 60 second ConnectionTimeOut property would mean a 300 second CommandTimeOut.

Boolean CloseConnection() - Closes the database connection (if open).

Returns: A Boolean (Success or Error).

NOTE: The only cause for this method to ever return False is in the event of an unexpected System Error. For that reason it follows the Common Exceptions Interface.

String DBString(Object) - Executes a SQL command (insert or update)

Parameter 1: Object Var - tests for Null and returns the default .ToString() return value of Parameter 1.

Returns: String.

Description: A utility function useful for retrieving DataColumn and DataViewColumn values without having to check for Null.

Example (C#)

    // tbl is a DataTable

    // conn is a SqlServerConnector object

    string myValue = conn.DBString(tbl.DefaultView[1]["FieldName"]);

Boolean ExecuteSQL(String) - Executes a SQL command (insert or update)

Parameter 1: String - SQL select statement.

Returns: A Boolean (Success or Error). See

Boolean OpenConnection() - Opens a database connection

Returns: A Boolean (Success or Error). Follows the Common Exceptions Interface

DataSet OpenSQLQuery(2 Overloads) - Always returns a DataSet. If an exception occurs the DataSet will be empty (no tables).

Overload 1:

Parameter 1: String - SQL select statement.

Parameter 2: String - (optional) TableName the name of the Table you are selecting from. Will use "Table1" as the default.

Parameter 3: Boolean - (optional) FillSchema should the returned DataSet contain extended table and column properties. Default = FALSE.

Overload 2:

Parameter 1: HSQLQueryItem - Containing a SQL select statement and a Table Name.

Parameter 2: Boolean - (optional) FillSchema should the returned DataSet contain extended table and column properties. Default = FALSE.

Returns: A DataSet containing 1 DataTable named TableName with the results or an empty dataset on error. Follows the Common Exceptions Interface

NOTE: Passing TRUE in the FillSchema parameter can substantially decrease performance, especially with large queries.

DataSet OpenSQLQueries(<List of HSQLQueryItem>, Boolean) - Always returns a DataSet. If an exception occurs the DataSet will be empty (no tables).

Parameter 1: <List of HSQLQueryItem> - A HSQLQueryItem containing a SQL statement and Tablename. **TableNames MUST be unique!

Parameter 2: Boolean - (optional) FillSchema should the returned DataSet contain extended table and column properties. Default = FALSE.

Returns: A DataSet containing multiple DataTables named QueriyItem.TableName with the results. Any errors in your sql statements will cause the entire transaction to fail and rollback and an empty dataset will be returned.

NOTE: Passing TRUE in the FillSchema parameter can substantially decrease performance, especially with large queries.

EVENT: This method will fire the TransactionRunning Event (See Events below)

DataTable OpenSQLDataTable(2 Overloads) - Always returns a DataTable. If an exception occurs the DataTable will be empty (no rows OR columns).

Overload 1:

Parameter 1: String - SQL select statement.

Parameter 2: String - (optional) TableName the name of the Table you are selecting from. Will use "Table1" as the default.

Parameter 3: Boolean - (optional) FillSchema should the returned DataTable contain extended table and column properties. Default = FALSE.

Overload 2:

Parameter 1: HSQLQueryItem - Containing a SQL select statement and a Table Name.

Parameter 2: Boolean - (optional) FillSchema should the returned DataTable contain extended table and column properties. Default = FALSE.

Returns: A DataTable named TableName with the results.

NOTE: Passing TRUE in the FillSchema parameter can substantially decrease performance, especially with large queries.

Boolean RunTransaction(List<of String>, Integer)

Parameter 1: List<of String> - SQL Commands.

Parameter 2: Integer - (optional) CommandTimeout = -1 You can specify your own command time out (in seconds) to use for EACH statement. If less than 0 is passed it will use EITHER the default command timeout in your SQLSchemaAPIKey object (60 seconds) OR  a calculation (see the UseDynamicCommandTimeOut property for more information.

Returns: Boolean False if Error encountered see the Common Exceptions Interface

NOTE: See Also: RecordsAffectedByLastSQL property.

Boolean VerifyConnection() - Opens a database connection (if not already open).

Returns: A Boolean (Success or Error). Follows the Common Exceptions Interface

NOTE: You can use this method interchangeably with the OpenConnection() method.