public static bool ExecDirect(ODBCHStatement statementHandle, string statementText, int textLength, out string info)
{
info = string.Empty;
if (statementText == null)
{
throw new ArgumentNullException("Command string is empty");
}
if (statementText.Trim() == "")
{
throw new ArgumentException("Command string is empty");
}
var result = ODBCNativeMethods.SQLExecDirectW(statementHandle, statementText, textLength);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Unnable to execute sql command " + statementText);
}
if (result == ODBCResult.SuccessWithInfo)
{
var list_info = GetErrorInfo(statementHandle);
if (list_info.Count > 0)
{
info = list_info[0].EMessage;
}
}
return(true);
}
public static extern ODBCResult SQLBindCol(
ODBCHStatement StatementHandle,
short ColumnNumber,
ODBCDataType TargetType,
StringBuilder TargetValue,
int BufferLength,
out int StrLen_or_ind);
public static extern ODBCResult SQLPrimaryKeys(
ODBCHStatement StatementHandle,
string CatalogName,
short NameLength1,
string SchemaName,
short NameLength2,
string TableName,
short NameLength3);
public static bool DescribeCol(ODBCHStatement nStatementHandle, short ColumnNumber, ref String ColumnName, short BufferLength, out short NameLengthPtr, out short DataTypePtr, out int ColumnSizePtr, out short DecimalDigitsPtr, out short NullablePtr)
{
var result = ODBCNativeMethods.SQLDescribeCol(nStatementHandle, ColumnNumber, ref ColumnName, BufferLength, out NameLengthPtr, out DataTypePtr, out ColumnSizePtr, out DecimalDigitsPtr, out NullablePtr);
if (result != ODBCResult.Success)
{
throw GetException(nStatementHandle, "Unnable to set connection property ");
}
return(true);
}
public static extern ODBCResult SQLTables(
ODBCHStatement StatementHandle,
string CatalogName,
short NameLength1,
string SchemaName,
short NameLength2,
string TableName,
short NameLength3,
string TableType,
short NameLength4);
internal static bool GetIndexInfo(ODBCHStatement statementHandle, string schemaName, string tableName, ushort indexType)
{
var result = ODBCNativeMethods.SQLStatistics(statementHandle, String.Empty, (short)0, schemaName, (short)schemaName.Length, tableName, (short)tableName.Length, indexType, 0);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error getting index definition");
}
return(true);
}
internal static bool GetPrimKey(ODBCHStatement statementHandle, string schemaName, string tableName)
{
var result = ODBCNativeMethods.SQLPrimaryKeys(statementHandle, String.Empty, (short)0, schemaName, (short)schemaName.Length, tableName, (short)tableName.Length);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error getting primary key definition");
}
return(true);
}
internal static bool GetTables(ODBCHStatement statementHandle, string catalogName, string schemaName, string tableName)
{
var result = ODBCNativeMethods.SQLTables(statementHandle, catalogName, (short)(catalogName.Length), schemaName, (short)(schemaName.Length), tableName, (short)(tableName.Length), String.Empty, (short)0);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error getting table list");
}
return(true);
}
internal static bool SQLBindColumn(ODBCHStatement statementHandle, short ColumnNumber, ODBCDataType TargetType, StringBuilder TargetValue, int BufferLength, out int StrLen_or_ind)
{
var result = ODBCNativeMethods.SQLBindCol(statementHandle, ColumnNumber, TargetType, TargetValue, BufferLength, out StrLen_or_ind);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error binding columns");
}
return(true);
}
internal static bool SQLBindColumn(ODBCHStatement statementHandle, ODBCData column)
{
var result = ODBCNativeMethods.SQLBindCol(statementHandle, column.ColumnNumber, column.ColumnType, column.ColumnData, column.ColumnDataLength, out column.ColumnDataRealLength);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo))
{
throw GetException(statementHandle, "Error binding columns");
}
return(true);
}
public static extern ODBCResult SQLStatistics(
ODBCHStatement StatementHandle,
string CatalogName,
short NameLength1,
string SchemaName,
short NameLength2,
string TableName,
short NameLength3,
ushort Unique,
ushort Reserved);
internal static bool GetForeignKeys(ODBCHStatement nStatementHandle, string PKCatalogName, string PKSchemaName, string PKTableName, string FKCatalogName, string FKSchemaName, string FKTableName)
{
var result = ODBCNativeMethods.SQLForeignKeys(nStatementHandle, PKCatalogName, (short)PKCatalogName.Length, PKSchemaName, (short)PKSchemaName.Length, PKTableName, (short)PKTableName.Length, FKCatalogName, (short)FKCatalogName.Length, FKSchemaName, (short)FKSchemaName.Length, FKTableName, (short)FKTableName.Length);
if (result != ODBCResult.Success)
{
throw GetException(nStatementHandle, "Unnable to set connection property ");
}
return(true);
}
internal static bool Fetch(ODBCHStatement statementHandle)
{
var result = ODBCNativeMethods.SQLFetch(statementHandle);
if ((result != ODBCResult.Success) & (result != ODBCResult.SuccessWithInfo) & (result != ODBCResult.NoData))
{
throw GetException(statementHandle, "Error fetching data");
}
return((result == ODBCResult.Success) || (result == ODBCResult.SuccessWithInfo));
}
//установка атрибутов соединения
//строковое значение атрибута
public static extern ODBCResult SQLDescribeCol(
ODBCHStatement nStatementHandle,
short ColumnNumber,
[MarshalAs(UnmanagedType.LPStr)] ref String ColumnName,
short BufferLength,
out short NameLengthPtr,
out short DataTypePtr,
out int ColumnSizePtr,
out short DecimalDigitsPtr,
out short NullablePtr
);
//получение информации о внешних ключах
public List <ForeignKeyDescription> GetForeingKeys(string pkCatalogName, string pkSchemaName, string pkTableName, string fkCatalogName, string fkSchemaName, string fkTableName)
{
var foreignKeyData = new List <ForeignKeyDescription>();
if (_initialized)
{
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
List <ODBCData> data = null;
ForeignKeysStatementDefinition foreignStatementDefinition = new ForeignKeysStatementDefinition();
try
{
data = BindBuffer(foreignStatementDefinition);
if (ODBCNative.ODBCMethods.GetForeignKeys(statement, pkCatalogName, pkSchemaName, pkTableName, fkCatalogName, fkSchemaName, fkTableName))
{
if (BindColumns(data, statement))
{
bool fetched = ODBCNative.ODBCMethods.Fetch(statement);
while (fetched)
{
List <string> _fkColumns = new List <string>();
List <string> _pkColumns = new List <string>();
string _fkName = ODBCMethods.ReadString(data[11].ColumnData);
string _pkName = ODBCMethods.ReadString(data[12].ColumnData);
short _updateRule = ODBCMethods.ReadShort(data[9].ColumnData);
short _deleteRule = ODBCMethods.ReadShort(data[10].ColumnData);
while ((fetched) & (_fkName == ODBCMethods.ReadString(data[11].ColumnData)))
{
string _pkcolumnName = ODBCMethods.ReadString(data[3].ColumnData);
string _fkcolumnName = ODBCMethods.ReadString(data[7].ColumnData);
_fkColumns.Add(_fkcolumnName);
_pkColumns.Add(_pkcolumnName);
fetched = ODBCNative.ODBCMethods.Fetch(statement);
}
ForeignKeyDescription _fkd = new ForeignKeyDescription(_fkName, _pkName, _pkColumns, _fkColumns, _updateRule, _deleteRule);
foreignKeyData.Add(_fkd);
}
}
}
}
finally
{
foreach (var record in data)
{
record.Dispose();
}
}
}
}
return(foreignKeyData);
}
public bool ExecDirect(string sqlStatement)
{
if (_initialized)
{
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
string out_info;
bool result = ODBCNative.ODBCMethods.ExecDirect(statement, sqlStatement, sqlStatement.Length, out out_info);
return(result);
}
}
return(false);
}
//получение перечня полей и их типов для таблицы
public List <ColumnDescription> GetColumns(string schemaName, string tableName)
{
var _primData = new List <ColumnDescription>();
if (_initialized)
{
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
List <ODBCData> data = null;
ColumnsStatementDefinition columnDefinition = new ColumnsStatementDefinition();
try
{
data = BindBuffer(columnDefinition);
if (ODBCNative.ODBCMethods.GetTableColumns(statement, schemaName, tableName))
{
if (BindColumns(data, statement))
{
while (ODBCNative.ODBCMethods.Fetch(statement))
{
FillInternalData(ref data);
//ODBC values only
if (data[1].InternalData.intVal > 0)
{
var prim = new ColumnDescription(data[0].InternalData.strVal, data[1].InternalData.intVal, data[4].InternalData.strVal, data[2].InternalData.intVal, data[3].InternalData.intVal, data[5].InternalData.intVal);
_primData.Add(prim);
}
}
}
}
}
finally
{
foreach (var record in data)
{
record.Dispose();
}
// statement.Close();
}
}
return(_primData);
}
else
{
throw new ODBCErrorNotInitialized("You must connect to database!");
}
}
private static bool BindColumns(List <ODBCData> columns, ODBCHStatement statement)//привязка выделенной памяти к определенным колонкам в результирующем наборе
{
bool res = true;
foreach (var col in columns)
{
if (!ODBCMethods.SQLBindColumn(statement, col))
{
res = false;
break;
}
}
return(res);
}
//информация о полях в составе внешних ключей
public static extern ODBCResult SQLForeignKeys(
ODBCHStatement StatementHandle,
string PKCatalogName,
short NameLength1,
string PKSchemaName,
short NameLength2,
string PKTableName,
short NameLength3,
string FKCatalogName,
short NameLength4,
string FKSchemaName,
short NameLength5,
string FKTableName,
short NameLength6
);
//получение полей первичного ключа
public IndexData GetPrimaryKey(string schemaName, string tableName)
{
var _primData = new IndexData();
if (_initialized)
{
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
PrimaryKeyStatementDefinition primaryKeyDefinition = new PrimaryKeyStatementDefinition();
List <ODBCData> data = null;
try
{
data = BindBuffer(primaryKeyDefinition);
if (ODBCMethods.GetPrimKey(statement, schemaName, tableName))
{
if (BindColumns(data, statement))
{
while (ODBCMethods.Fetch(statement))
{
FillInternalData(ref data);
_primData.SetData(0, "");
ColumnData _cd = new ColumnData(data[0].InternalData.strVal, data[1].InternalData.intVal, true);
_primData.AddColumnData(_cd);
//PrimaryKeyData prim = new PrimaryKeyData(data[0].InternalData.strVal, data[1].InternalData.intVal);
// _primData.Add(prim);
}
}
}
}
finally
{
foreach (var record in data)
{
record.Dispose();
}
// statement.Close();
}
}
return(_primData);
}
else
{
throw new ODBCErrorNotInitialized("You must connect to database!");
}
}
//получение списка таблиц
public List <TableData> GetTableList(string catalogName = "", string schemaName = "", string tableNamePattern = "")
{
var _tablesData = new List <TableData>();
if (_initialized)
{
List <ODBCData> data = null;
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
var tablesDefinition = new TablesStatementDefinition();
data = BindBuffer(tablesDefinition);
try
{
if (ODBCNative.ODBCMethods.GetTables(statement, catalogName, schemaName, tableNamePattern))
{
if (BindColumns(data, statement))
{
while (ODBCNative.ODBCMethods.Fetch(statement))
{
FillInternalData(ref data);
TableData table = new TableData(data[0].InternalData.strVal, data[1].InternalData.strVal, data[2].InternalData.strVal);
_tablesData.Add(table);
}
}
}
}
finally
{
foreach (var record in data)
{
record.Dispose();
}
// statement.Close();
}
}
}
else
{
throw new ODBCErrorNotInitialized("You must connect to database!");
}
return(_tablesData);
}
public static extern ODBCResult SQLFetch(
ODBCHStatement StatementHandle);
//выполнение sql-команды
public static extern ODBCResult SQLExecDirectW(
ODBCHStatement statementHandle,
string statementText,
int textLength
);
//получение информации об индексах, полях, входящих в них
public List <IndexData> GetIndexData(string schemaName, string tableName)
{
var _indexesData = new List <IndexData>();
const ushort _indexType = 1; //all indexes;
if (_initialized)
{
using (ODBCHStatement statement = new ODBCHStatement(_connectionHandle))
{
List <ODBCData> data = null;
IndexStatementDefinition indexStatementDefinition = new IndexStatementDefinition();
try
{
data = BindBuffer(indexStatementDefinition);
if (ODBCNative.ODBCMethods.GetIndexInfo(statement, schemaName, tableName, _indexType))
{
if (BindColumns(data, statement))
{
bool fetched = ODBCNative.ODBCMethods.Fetch(statement);
while (fetched)
{
FillInternalData(ref data);
string indexName = data[1].InternalData.strVal;
if (indexName.Length > 1)
{
IndexData _indexData = new IndexData();
_indexData.SetData(data[0].InternalData.intVal, data[1].InternalData.strVal);
while ((fetched) & (indexName.Equals(data[1].InternalData.strVal)))
{
int _unique = ODBCMethods.ReadShort(data[0].ColumnData);
if ((_unique == ODBCNative.ODBCConstants.SQL_IS_INDEX_UNIQUE) | (_unique == ODBCNative.ODBCConstants.SQL_IS_INDEX_DUPLICATE))
{
bool asc = (data[4].InternalData.strVal == "A");
ColumnData cd = new ColumnData(data[3].InternalData.strVal, data[2].InternalData.intVal, asc);
_indexData.AddColumnData(cd);
}
fetched = ODBCNative.ODBCMethods.Fetch(statement);
if (fetched)
{
FillInternalData(ref data);
}
}
_indexesData.Add(_indexData);
}
else
{
fetched = ODBCNative.ODBCMethods.Fetch(statement);
}
}
}
}
}
finally
{
foreach (var record in data)
{
record.Dispose();
}
}
}
return(_indexesData);
}
else
{
throw new ODBCErrorNotInitialized("You must connect to database!");
}
}