internal List<Right> GetBexisRights(string dataBase, Dictionary<int, int> dataSetsMapping)
{
List<Right> bexisRights = new List<Right>();
string datasetQuery = "";
foreach (var dataSetMapping in dataSetsMapping)
{
datasetQuery += "DATASETID = "+ dataSetMapping.Key;
if (dataSetsMapping.Last().Key != dataSetMapping.Key)
datasetQuery += " or ";
}
if (dataSetsMapping.Any())
{
datasetQuery = "where " + datasetQuery + "";
}
// DB query
string mySelectQuery = "SELECT ROLENAME, DATASETID, FOREDIT, APPLICATIONNAME FROM \"PROVIDER\".\"RIGHTS\" "+ datasetQuery;
DB2Connection connect = new DB2Connection(dataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisRights.Add(new Right()
{
RoleName = myReader.GetString(0),
DataSetId = (int)(myReader.GetValue(1)),
CanEdit = myReader.GetString(2)=="N"?false:true
});
}
myReader.Close();
connect.Close();
return bexisRights;
}
/// <summary>
/// 创建DB2Command命令,指定数据库连接对象,存储过程名和参数.
/// </summary>
/// <remarks>
/// 示例:
/// DB2Command command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="sourceColumns">源表的列名称数组</param>
/// <returns>返回DB2Command命令</returns>
public static DB2Command CreateCommand(DB2Connection connection, string spName, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 创建命令
DB2Command cmd = new DB2Command(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
// 如果有参数值
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
DB2Parameter[] commandParameters = DB2HelperParameterCache.GetSpParameterSet(connection, spName);
// 将源表的列到映射到DataSet命令中.
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the DB2Command object
AttachParameters(cmd, commandParameters);
}
return cmd;
}
public static IDbConnection GetDbConn(string dbname, MDataBaseType type)
{
IDbConnection conn = null;
string connstr = GetBDConnString(dbname);
switch (type)
{
case MDataBaseType.MYSQL:
conn = new MySqlConnection(connstr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
break;
case MDataBaseType.SQLSERVER:
conn = new SqlConnection(connstr);
break;
case MDataBaseType.DB2:
conn = new DB2Connection(connstr);
break;
case MDataBaseType.UNKNOW:
throw new Exception("未知数据库类型，创建数据库链接失败");
}
return conn;
}
public IDbConnection OpenConnection(string connectionString)
{
var connection = new IBM.Data.DB2.DB2Connection(connectionString);
connection.Open();
return(connection);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">ArrayList</param>
public static void ExecuteSqlTran(ArrayList sqlList)
{
bool mustCloseConnection = false;
string ConString = System.Configuration.ConfigurationSettings.AppSettings["conInsertDB2"].ToString();
using (DB2Connection conn = new DB2Connection(ConString))
{
conn.Open();
using (DB2Transaction trans = conn.BeginTransaction())
{
DB2Command cmd = new DB2Command();
try
{
for (int i = 0; i < sqlList.Count; i++)
{
string cmdText = sqlList[i].ToString();
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
int val = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
cmd.Dispose();
}
}
}
}
protected override IDbConnection CreateConnection()
{
DB2Connection conn = new DB2Connection(ConnectionString);
conn.Open();
return conn;
}
public DB2Command(string commandStr, DB2Connection con) : this()
{
db2Conn = con;
commandText = commandStr;
if(con != null)
{
con.AddCommand(this);
}
}
object ICloneable.Clone()
{
DB2Connection clone = new DB2Connection();
clone.connectionSettings = connectionSettings;
clone.connectionTimeout = connectionTimeout;
return(clone);
}
public void Rollback()
{
CheckStateOpen();
DB2CLIWrapper.SQLEndTran(DB2Constants.SQL_HANDLE_DBC, db2Conn.DBHandle, DB2Constants.SQL_ROLLBACK);
this.db2Conn.openConnection.transactionOpen = false;
this.state = TransactionState.Rolledback;
this.db2Conn.WeakRefTransaction = null;
this.db2Conn = null;
}
public void Commit()
{
CheckStateOpen();
DB2CLIWrapper.SQLEndTran(DB2Constants.SQL_HANDLE_DBC, db2Conn.DBHandle, DB2Constants.SQL_COMMIT);
this.state = TransactionState.Committed;
this.db2Conn.openConnection.transactionOpen = false;
this.db2Conn.WeakRefTransaction = null;
this.db2Conn = null;
}
public DB2Command(string commandStr, DB2Connection con) : this()
{
db2Conn = con;
commandText = commandStr;
if (con != null)
{
con.AddCommand(this);
}
}
public DB2Command (string commandStr, DB2Connection con, DB2Transaction trans)
{
commandText = commandStr;
db2Conn = con;
db2Trans = trans;
if(con != null)
{
con.AddCommand(this);
}
}
public DB2Command(string commandStr, DB2Connection con, DB2Transaction trans)
{
commandText = commandStr;
db2Conn = con;
db2Trans = trans;
if (con != null)
{
con.AddCommand(this);
}
}
public DB2OpenConnection GetOpenConnection(DB2Connection db2Conn)
{
DB2OpenConnection connection = null;
lock (openFreeConnections.SyncRoot)
{
if ((connectionSettings.ConnectionPoolSizeMax > 0) &&
(connectionsOpen >= connectionSettings.ConnectionPoolSizeMax))
{
throw new ArgumentException("Maximum connections reached for connectionstring");
}
while (connectionsOpen > connectionsInUse)
{
connection = (DB2OpenConnection)openFreeConnections[openFreeConnections.Count - 1];
openFreeConnections.RemoveAt(openFreeConnections.Count - 1);
// check if connection is dead
int isDead;
short sqlRet = DB2CLIWrapper.SQLGetConnectAttr(connection.DBHandle, DB2Constants.SQL_ATTR_CONNECTION_DEAD, out isDead, 0, IntPtr.Zero);
if (((sqlRet == DB2Constants.SQL_SUCCESS_WITH_INFO) || (sqlRet == DB2Constants.SQL_SUCCESS)) &&
(isDead == DB2Constants.SQL_CD_FALSE))
{
connectionsInUse++;
break;
}
else
{
connectionsOpen--;
connection.Dispose();
connection = null;
}
}
if (connectionsOpen == connectionsInUse)
{
if (timer != null)
{
timer.Dispose();
timer = null;
}
}
}
if (connection == null)
{
openFreeConnections.Clear();
connectionsUsableOffset = 0;
connection = new DB2OpenConnection(connectionSettings, db2Conn);
connectionsOpen++;
connectionsInUse++;
}
return(connection);
}
public DB2OpenConnection GetRealOpenConnection(DB2Connection connection)
{
if (pool != null)
{
return(pool.GetOpenConnection(connection));
}
else
{
return(new DB2OpenConnection(this, connection));
}
}
public DB2OpenConnection GetRealOpenConnection(DB2Connection connection)
{
if(pool != null)
{
return pool.GetOpenConnection(connection);
}
else
{
return new DB2OpenConnection(this, connection);
}
}
public DB2OpenConnection GetOpenConnection(DB2Connection db2Conn)
{
DB2OpenConnection connection = null;
lock(openFreeConnections.SyncRoot)
{
if((connectionSettings.ConnectionPoolSizeMax > 0) &&
(connectionsOpen >= connectionSettings.ConnectionPoolSizeMax))
{
throw new ArgumentException("Maximum connections reached for connectionstring");
}
while(connectionsOpen > connectionsInUse)
{
connection = (DB2OpenConnection)openFreeConnections[openFreeConnections.Count - 1];
openFreeConnections.RemoveAt(openFreeConnections.Count - 1);
// check if connection is dead
int isDead;
short sqlRet = DB2CLIWrapper.SQLGetConnectAttr(connection.DBHandle, DB2Constants.SQL_ATTR_CONNECTION_DEAD, out isDead, 0, IntPtr.Zero);
if(((sqlRet == DB2Constants.SQL_SUCCESS_WITH_INFO) || (sqlRet == DB2Constants.SQL_SUCCESS)) &&
(isDead == DB2Constants.SQL_CD_FALSE))
{
connectionsInUse++;
break;
}
else
{
connectionsOpen--;
connection.Dispose();
connection = null;
}
}
if(connectionsOpen == connectionsInUse)
{
if(timer != null)
{
timer.Dispose();
timer = null;
}
}
}
if(connection == null)
{
openFreeConnections.Clear();
connectionsUsableOffset = 0;
connection = new DB2OpenConnection(connectionSettings, db2Conn);
connectionsOpen++;
connectionsInUse++;
}
return connection;
}
public SitioCentral(String id, String cc,List<SuperGridControl> sgc)
: base(id, cc)
{
conexion = new DB2Connection(cadena_conexion);
SPG = new SitioPG("CONTROL_INTERNO", dame_cc_de("CONTROL_INTERNO"));
SO = new SitioOracle("FARMACIA", dame_cc_de("FARMACIA"));
SM1 = new SitioMySQL("CONSULTORIO1", dame_cc_de("CONSULTORIO1"));
SM2 = new SitioMySQL("CONSULTORIO2", dame_cc_de("CONSULTORIO2"));
SM3 = new SitioMySQL("CONSULTORIO3", dame_cc_de("CONSULTORIO3"));
grids = sgc;
}
public void IBMBuldCopy()
{
string sql_bruv_1 = "Server=10.24.1.202:446;Database=BRUVDB4V;UID=ATWO;PWD=24rete31;Max Pool Size=100;Min Pool Size=10;";
string sql_frymek = "Server=10.27.5.197:50000;Database=BRUV;UID=db2admin;PWD=db2pass1234!@#$;Max Pool Size=100;Min Pool Size=10;";
try
{
using (DB2Connection myConn = new DB2Connection(sql_bruv_1))
{
using (DB2Connection conn = new DB2Connection(sql_frymek))
{
myConn.Open();
conn.Open();
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
string myInsertQuery = String.Format("SELECT * FROM RREV.{0}", Table);
using (DB2Command myDB2Command = new DB2Command(myInsertQuery, myConn))
{
using (DB2DataReader reader = myDB2Command.ExecuteReader())
{
using (DB2BulkCopy salesCopy = new DB2BulkCopy(conn))
{
salesCopy.DestinationTableName = String.Format("ATWO.{0}", Table);
salesCopy.WriteToServer(reader);
var errors = salesCopy.Errors;
if (errors.Count > 0)
{
log.ErrorFormat("table:{0}, errors:{1}", Table, errors.Count);
foreach (var er in errors)
{
log.ErrorFormat("table:{0}, msg:{1}", Table, er.ToString());
}
}
salesCopy.Close();
myConn.Close();
conn.Close();
}
}
}
}
}
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
}
catch (Exception ex)
{
log.Error(ex.ToString());
Console.WriteLine(String.Format("error: {0} with exception: {1}", Table, ex.Message));
}
}
private void InternalOpen(string connnectionString, DB2Connection connection)
{
try
{
DB2Constants.RetCode sqlRet = (DB2Constants.RetCode)DB2CLIWrapper.SQLAllocHandle(DB2Constants.SQL_HANDLE_DBC, DB2Environment.Instance.PenvHandle, out dbHandle);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, DB2Environment.Instance.PenvHandle, "Unable to allocate database handle in DB2Connection.", connection);
StringBuilder outConnectStr = new StringBuilder(DB2Constants.SQL_MAX_OPTION_STRING_LENGTH);
short numOutCharsReturned;
sqlRet = (DB2Constants.RetCode)DB2CLIWrapper.SQLDriverConnect(dbHandle, IntPtr.Zero,
connnectionString, DB2Constants.SQL_NTS,
outConnectStr, DB2Constants.SQL_MAX_OPTION_STRING_LENGTH, out numOutCharsReturned,
DB2Constants.SQL_DRIVER_NOPROMPT);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, dbHandle, "Unable to connect to the database.", connection);
databaseProductName = SQLGetInfo(dbHandle, DB2Constants.SQL_DBMS_NAME);
databaseVersion = SQLGetInfo(dbHandle, DB2Constants.SQL_DBMS_VER);
/* Set the attribute SQL_ATTR_XML_DECLARATION to skip the XML declaration from XML Data */
sqlRet = (DB2Constants.RetCode)DB2CLIWrapper.SQLSetConnectAttr(dbHandle, DB2Constants.SQL_ATTR_XML_DECLARATION, new IntPtr(DB2Constants.SQL_XML_DECLARATION_NONE), DB2Constants.SQL_NTS);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, dbHandle, "Unable to set SQL_ATTR_XML_DECLARATION", connection);
connection.NativeOpenPerformed = true;
if ((settings.Pool == null) || (settings.Pool.databaseProductName == null))
{
settings.Pool.databaseProductName = databaseProductName;
settings.Pool.databaseVersion = databaseVersion;
}
else if (settings.Pool != null)
{
if (settings.Pool != null)
{
databaseProductName = settings.Pool.databaseProductName;
databaseVersion = settings.Pool.databaseVersion;
}
}
}
catch
{
if (dbHandle != IntPtr.Zero)
{
DB2CLIWrapper.SQLFreeHandle(DB2Constants.SQL_HANDLE_DBC, dbHandle);
dbHandle = IntPtr.Zero;
}
throw;
}
}
//Boolean result = false;
public Boolean ConnectDatabase()
{
try
{
//ConnPath = "";
Conn = new DB2Connection("Server=localhost; UserID=db2admin; Password=admin...; Database=HRESS;");
Conn.Open();
return true;
}
catch (Exception e)
{
return false;
}
}
/// <summary>
///
/// </summary>
/// <param name="con"></Connection object to Db2>
/// <param name="com"></Command object>
internal DB2DataReader(DB2Connection con, DB2Command com, CommandBehavior behavior)
{
db2Conn = con;
db2Comm = com;
this.behavior = behavior;
hwndStmt = com.statementHandle; //We have access to the results through the statement handle
_resultSet = null;
GetFieldCountAndAffectedRows();
internalBuffer = Marshal.AllocHGlobal(internalBufferSize);
isClosed = false;
}
public List<MColumn> GetColumnList(string tableName)
{
string connstr = ConnectionFactory.TRSDbConnString;
List<MColumn> ret = new List<MColumn>();
using (DB2Connection conn = new DB2Connection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);
ret = dao.GetColumnList(conn, tableName);
}
return ret;
}
// query bexis1 user from provider.users and generate a random password
public List<UserProperties> GetFromBExIS(string DataBase)
{
List<UserProperties> transferUsers = new List<UserProperties>();
// DB query
string mySelectQuery = "select username, email, firstname, lastname, " +
"organization, projectname, projectleader, " +
"url, phone, mobile, fax, original, street, zipcode, city";
mySelectQuery += " from provider.users;";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
// random password
Random gen = new Random();
while (myReader.Read())
{
UserProperties transferUser = new UserProperties();
// bexis1 DB user data
transferUser.username = myReader.GetValue(0).ToString();
transferUser.email = myReader.GetValue(1).ToString();
transferUser.firstname = myReader.GetValue(2).ToString();
transferUser.lastname = myReader.GetValue(3).ToString();
transferUser.organization = myReader.GetValue(4).ToString();
transferUser.projectname = myReader.GetValue(5).ToString();
transferUser.projectleader = myReader.GetValue(6).ToString();
transferUser.url = myReader.GetValue(7).ToString();
transferUser.phone = myReader.GetValue(8).ToString();
transferUser.mobile = myReader.GetValue(9).ToString();
transferUser.fax = myReader.GetValue(10).ToString();
transferUser.original = myReader.GetValue(11).ToString();
transferUser.street = myReader.GetValue(12).ToString();
transferUser.zipcode = myReader.GetValue(13).ToString();
transferUser.city = myReader.GetValue(14).ToString();
// bexis2 required security data
transferUser.password = randomPassword(ref gen); // random password
transferUser.securityQuestionId = 1;
transferUser.securityAnswer = "1";
transferUser.authenticatorId = 1;
// add to list; username required
if (transferUser.username != "")
transferUsers.Add(transferUser);
}
myReader.Close();
connect.Close();
return transferUsers;
}
public List<MTableDesc> GetTableList(string creator)
{
string connstr = ConnectionFactory.TRSDbConnString;
List<MTableDesc> ret = null;
using (DB2Connection conn = new DB2Connection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);
ret = dao.GetTableList(conn, creator);
}
return ret;
}
internal List<string> GetBexisRoles(string DataBase)
{
List<string> bexisRoles = new List<string>();
// DB query
string mySelectQuery = "SELECT ROLENAME, APPLICATIONNAME FROM \"PROVIDER\".\"ROLES\"";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisRoles.Add(myReader.GetValue(0).ToString());
}
myReader.Close();
connect.Close();
return bexisRoles;
}
internal List<string> GetBexisUsersInRole(string dataBase, string roleName)
{
List<string> bexisUsersInRole = new List<string>();
// DB query
string mySelectQuery = "SELECT USERNAME FROM \"PROVIDER\".\"USERSINROLES\" where ROLENAME='" + roleName + "'";
DB2Connection connect = new DB2Connection(dataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisUsersInRole.Add(myReader.GetValue(0).ToString());
}
myReader.Close();
connect.Close();
return bexisUsersInRole;
}
/// <summary>
/// Please refer to the documentation of <see cref="GentleProvider"/> and the
/// <see cref="IGentleProvider"/> interface it implements for details.
/// </summary>
public override IDbConnection GetConnection()
{
try
{
IDbConnection dbc = new DB2Connection( ConnectionString );
dbc.Open();
Check.VerifyEquals( dbc.State, ConnectionState.Open, Error.NoNewConnection );
return dbc;
}
catch( GentleException )
{
throw; // expose the errors raised by ourselves (i.e. the data framework) in the try block
}
catch( Exception e )
{
Check.Fail( e, Error.DatabaseUnavailable, Name, ConnectionString );
throw new GentleException( Error.Unspecified, "Unreachable code!" );
}
}
public XmlDocument getMetadataXml(string dataSetID, string DataBase)
{
XmlDocument doc = new XmlDocument();
string mySelectQuery = "select datasetid, metadata";
mySelectQuery += " from explorer.datasets where datasetid = " + dataSetID + ";";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
doc.LoadXml(myReader.GetString(1));
}
myReader.Close();
connect.Close();
return doc;
}
internal DB2Transaction(DB2Connection con, IsolationLevel isoL)
{
long db2IsoL;
connection = con;
short sqlRet;
isolationLevel = isoL;
switch (isoL)
{
default:
case System.Data.IsolationLevel.Chaos: //No DB2equivalent, default to SQL_TXN_READ_COMMITTED
case System.Data.IsolationLevel.ReadCommitted: //SQL_TXN_READ_COMMITTED
db2IsoL = DB2Constants.SQL_TXN_READ_COMMITTED;
break;
case System.Data.IsolationLevel.ReadUncommitted: //SQL_TXN_READ_UNCOMMITTED
db2IsoL = DB2Constants.SQL_TXN_READ_UNCOMMITTED;
break;
case System.Data.IsolationLevel.RepeatableRead: //SQL_TXN_REPEATABLE_READ
db2IsoL = DB2Constants.SQL_TXN_REPEATABLE_READ;
break;
case System.Data.IsolationLevel.Serializable: //SQL_TXN_SERIALIZABLE_READ
db2IsoL = DB2Constants.SQL_TXN_SERIALIZABLE_READ;
break;
}
//AutoCommit
if (connection.AutoCommit)
{
sqlRet = DB2CLIWrapper.SQLSetConnectAttr(connection.DBHandle, DB2Constants.SQL_ATTR_AUTOCOMMIT, new IntPtr(DB2Constants.SQL_AUTOCOMMIT_OFF), 0);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, connection.DBHandle, "Error setting AUTOCOMMIT OFF in transaction CTOR.", connection);
connection.AutoCommit = false;
}
sqlRet = DB2CLIWrapper.SQLSetConnectAttr(connection.DBHandle, DB2Constants.SQL_ATTR_TXN_ISOLATION, new IntPtr(db2IsoL), 0);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, connection.DBHandle, "Error setting isolation level.", connection);
state = TransactionState.Open;
}
public static void DB2CheckReturn(short sqlRet, short handleType, IntPtr handle, string message, DB2Connection connection)
{
switch (sqlRet)
{
case DB2Constants.SQL_SUCCESS_WITH_INFO:
if(connection != null)
{
connection.OnInfoMessage(handleType, handle);
}
goto case DB2Constants.SQL_SUCCESS;
case DB2Constants.SQL_SUCCESS:
case DB2Constants.SQL_NO_DATA:
return;
case DB2Constants.SQL_INVALID_HANDLE:
throw new ArgumentException("Invalid handle");
default:
case DB2Constants.SQL_ERROR:
throw new DB2Exception(handleType, handle, message);
}
}
protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
ConnectionClosed();
if (db2Conn != null)
{
db2Conn.RemoveCommand(this);
db2Conn = null;
}
}
if (statementParametersMemory != IntPtr.Zero)
{
Marshal.FreeHGlobal(statementParametersMemory);
statementParametersMemory = IntPtr.Zero;
}
}
base.Dispose(disposing);
disposed = true;
}
internal DB2Transaction(DB2Connection con, IsolationLevel isoL)
{
long db2IsoL;
db2Conn = con;
short sqlRet;
isolationLevel = isoL;
switch (isoL)
{
default:
case System.Data.IsolationLevel.Chaos: //No DB2equivalent, default to SQL_TXN_READ_COMMITTED
case System.Data.IsolationLevel.ReadCommitted: //SQL_TXN_READ_COMMITTED
db2IsoL = DB2Constants.SQL_TXN_READ_COMMITTED;
break;
case System.Data.IsolationLevel.ReadUncommitted: //SQL_TXN_READ_UNCOMMITTED
db2IsoL = DB2Constants.SQL_TXN_READ_UNCOMMITTED;
break;
case System.Data.IsolationLevel.RepeatableRead: //SQL_TXN_REPEATABLE_READ
db2IsoL = DB2Constants.SQL_TXN_REPEATABLE_READ;
break;
case System.Data.IsolationLevel.Serializable: //SQL_TXN_SERIALIZABLE_READ
db2IsoL = DB2Constants.SQL_TXN_SERIALIZABLE_READ;
break;
}
if(db2Conn.openConnection.autoCommit)
{
sqlRet = DB2CLIWrapper.SQLSetConnectAttr(db2Conn.DBHandle, DB2Constants.SQL_ATTR_AUTOCOMMIT, new IntPtr(DB2Constants.SQL_AUTOCOMMIT_OFF), 0);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, db2Conn.DBHandle, "Error setting AUTOCOMMIT OFF in transaction CTOR.", db2Conn);
db2Conn.openConnection.autoCommit = false;
}
sqlRet = DB2CLIWrapper.SQLSetConnectAttr(db2Conn.DBHandle, DB2Constants.SQL_ATTR_TXN_ISOLATION, new IntPtr(db2IsoL), 0);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, db2Conn.DBHandle, "Error setting isolation level.", db2Conn);
state = TransactionState.Open;
}
public void queryAuthorAndVariables(ref User user, ref string variableNames, string dataSetID, string DataBase)
{
string mySelectQuery = "select X.* from explorer.datasets, XMLTABLE ('$METADATA/*:metaProfile' Columns " +
"Author varchar(256) Path '*:general/*:metadataCreator'," +
"VarNames varchar(1028) Path 'string-join(*:data/*:dataStructure/*:variables/*:variable/*:name,\",\")'" +
") as X where datasetid = " + dataSetID + ";";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
string author = "";
while (myReader.Read())
{
author = myReader.GetString(0);
variableNames = myReader.GetString(1);
}
myReader.Close();
connect.Close();
SubjectManager subjectManager = new SubjectManager();
user = subjectManager.UsersRepo.Get(u => author.Equals(u.FullName)).FirstOrDefault();
}
/// <summary>
/// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
/// 示例:
/// DB2DataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="spName">T存储过程名</param>
/// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
public static DB2DataReader ExecuteReader(DB2Connection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
DB2Parameter[] commandParameters = DB2HelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// DB2DataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandParameters">DB2Paramter参数数组</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
public static DB2DataReader ExecuteReader(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
return ExecuteReader(connection, (DB2Transaction)null, commandType, commandText, commandParameters, DB2ConnectionOwnership.External);
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 示例:
/// DB2DataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
public static DB2DataReader ExecuteReader(DB2Connection connection, CommandType commandType, string commandText)
{
return ExecuteReader(connection, commandType, commandText, (DB2Parameter[])null);
}
/// <summary>
/// 执行指定数据库连接字符串的数据阅读器,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// DB2DataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Paramter参数数组(new DB2Parameter("@prodid", 24))</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
public static DB2DataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
DB2Connection connection = null;
try
{
connection = new DB2Connection(connectionString);
connection.Open();
return ExecuteReader(connection, null, commandType, commandText, commandParameters, DB2ConnectionOwnership.Internal);
}
catch
{
// If we fail to return the DB2DatReader, we need to close the connection ourselves
if (connection != null) connection.Close();
throw;
}
}
/// <summary>
/// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回受影响的行数.
/// </summary>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">使用DataRow作为参数值</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQueryTypedParams(DB2Connection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 如果row有值,存储过程必须初始化.
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
DB2Parameter[] commandParameters = DB2HelperParameterCache.GetSpParameterSet(connection, spName);
// 分配参数值
AssignParameterValues(commandParameters, dataRow);
return DB2Helper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return DB2Helper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
public DB2OpenConnection(string connnectionString, DB2Connection connection)
{
InternalOpen(connnectionString, connection);
}
/// <summary>
/// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值
/// 示例:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="spName">存储过程名</param>
/// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(DB2Connection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 如果有参数值
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 从缓存中加载存储过程参数
DB2Parameter[] commandParameters = DB2HelperParameterCache.GetSpParameterSet(connection, spName);
// 给存储过程分配参数值
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new db2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">T存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">DB2Paramter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 创建DB2Command命令,并进行预处理
DB2Command cmd = new DB2Command();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
public DB2OpenConnection(DB2ConnectionSettings connectionSetting, DB2Connection connection)
{
this.settings = connectionSetting;
InternalOpen(ConvertADONET2CLIConnString(connectionSetting), connection);
}
public DB2OpenConnection(DB2ConnectionSettings settings, DB2Connection connection)
{
this.settings = settings;
try
{
short sqlRet = DB2CLIWrapper.SQLAllocHandle(DB2Constants.SQL_HANDLE_DBC, DB2Environment.Instance.penvHandle, out dbHandle);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, DB2Environment.Instance.penvHandle, "Unable to allocate database handle in DB2Connection.", connection);
if (settings.Server.Length > 0)
{
StringBuilder outConnectStr = new StringBuilder(60); // TODO: ????
short numOutCharsReturned;
sqlRet = DB2CLIWrapper.SQLDriverConnect(dbHandle, IntPtr.Zero,
settings.ConnectionString, (short)settings.ConnectionString.Length,
outConnectStr, (short)outConnectStr.Length, out numOutCharsReturned,
DB2Constants.SQL_DRIVER_NOPROMPT /*SQL_DRIVER_COMPLETE*/);
}
else
{
sqlRet = DB2CLIWrapper.SQLConnect(dbHandle,
settings.DatabaseAlias, (short)settings.DatabaseAlias.Length,
settings.UserName, (short)settings.UserName.Length,
settings.PassWord, (short)settings.PassWord.Length);
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_DBC, dbHandle, "Unable to connect to the database.", connection);
}
if ((settings.Pool == null) || (settings.Pool.databaseProductName == null))
{
StringBuilder sb = new StringBuilder(256);
short stringLength;
sqlRet = DB2CLIWrapper.SQLGetInfo(dbHandle, /*SQL_DBMS_NAME*/ 17, sb, (short)(sb.Capacity / 2), out stringLength);
new DB2ErrorCollection(DB2Constants.SQL_HANDLE_DBC, dbHandle).ToString();
if (sqlRet == 0)
{
databaseProductName = sb.ToString(0, Math.Min(sb.Capacity, stringLength / 2));
}
sqlRet = DB2CLIWrapper.SQLGetInfo(dbHandle, /*SQL_DBMS_VER*/ 18, sb, (short)(sb.Capacity / 2), out stringLength);
if (sqlRet == 0)
{
databaseVersion = sb.ToString(0, Math.Min(sb.Capacity, stringLength / 2));
try
{
string[] splitVersion = databaseVersion.Split('.');
majorVersion = int.Parse(splitVersion[0]);
minorVersion = int.Parse(splitVersion[1]);
}
catch {}
}
if (settings.Pool != null)
{
settings.Pool.databaseProductName = databaseProductName;
settings.Pool.databaseVersion = databaseVersion;
settings.Pool.majorVersion = majorVersion;
settings.Pool.minorVersion = minorVersion;
}
}
else if (settings.Pool != null)
{
if (settings.Pool != null)
{
databaseProductName = settings.Pool.databaseProductName;
databaseVersion = settings.Pool.databaseVersion;
majorVersion = settings.Pool.majorVersion;
minorVersion = settings.Pool.minorVersion;
}
}
}
catch
{
if (dbHandle != IntPtr.Zero)
{
DB2CLIWrapper.SQLFreeHandle(DB2Constants.SQL_HANDLE_DBC, dbHandle);
dbHandle = IntPtr.Zero;
}
throw;
}
}
public DB2DataAdapter(string selectCommandText, DB2Connection selectConnection)
: this(new DB2Command(selectCommandText, selectConnection))
{
}
public static void DB2CheckReturn(short sqlRet, short handleType, IntPtr handle, string message, DB2Connection connection)
{
switch ((DB2Constants.RetCode)sqlRet)
{
case DB2Constants.RetCode.SQL_SUCCESS_WITH_INFO:
if (connection != null)
{
connection.OnInfoMessage(handleType, handle);
}
goto case DB2Constants.RetCode.SQL_SUCCESS;
case DB2Constants.RetCode.SQL_SUCCESS:
case DB2Constants.RetCode.SQL_NO_DATA:
return;
case DB2Constants.RetCode.SQL_INVALID_HANDLE:
throw new ArgumentException("Invalid handle");
default:
case DB2Constants.RetCode.SQL_ERROR:
throw new DB2Exception(handleType, handle, message);
}
}
