/// <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 DB2DataAdapter (DB2Command selectCommand)
{
DeleteCommand = null;
InsertCommand = null;
SelectCommand = selectCommand;
UpdateCommand = null;
}
public List<MTableDesc> GetTableList(IDbConnection conn, string creater)
{
string strSql = @"select NAME, CREATOR,CTIME,REMARKS from sysibm.systables where type='T' ";
if (!string.IsNullOrEmpty(creater))
{
strSql += string.Format("and creator='{0}'", creater);
}
DB2Command cmd = new DB2Command(strSql, (DB2Connection)conn);
List<MTableDesc> ret = new List<MTableDesc>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MTableDesc model = new MTableDesc();
model.TableName = reader["NAME"] == DBNull.Value ? string.Empty : reader["NAME"].ToString();
model.Creator = reader["CREATOR"] == DBNull.Value ? string.Empty : reader["CREATOR"].ToString();
model.CreateDateTime = reader["CTIME"] == DBNull.Value ? DateTime.Parse("1990-01-01") : Convert.ToDateTime(reader["CTIME"].ToString());
model.Remarks = reader["REMARKS"] == DBNull.Value ? string.Empty : reader["REMARKS"].ToString();
ret.Add(model);
}
}
return ret;
}
public List<MColumn> GetColumnList(IDbConnection conn, string tableName)
{
string sql = string.Format(@"SELECT NAME,TBNAME,TBCREATOR,REMARKS,COLTYPE,LENGTH, NULLS,DEFAULT,KEYSEQ
FROM sysibm.syscolumns where tbname ='{0}'", tableName);
DB2Command cmd = new DB2Command(sql, (DB2Connection)conn);
List<MColumn> ret = new List<MColumn>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MColumn model = new MColumn();
model.ColumnName = DBUtil.GetDBValueStr(reader, "NAME");
model.Creator = DBUtil.GetDBValueStr(reader, "TBCREATOR");
model.Remarks = DBUtil.GetDBValueStr(reader, "REMARKS");
model.ColType = DBUtil.GetDBValueStr(reader, "COLTYPE");
model.Length = DBUtil.GetDBValueInt(reader, "LENGTH");
model.IsNullable = DBUtil.GetDBValueBool(reader, "NULLS");
model.DefaultValue = DBUtil.GetDBValueStr(reader, "DEFAULT");
model.KeySeq = DBUtil.GetDBValueInt(reader, "KEYSEQ");
ret.Add(model);
}
}
return ret;
}
/// <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();
}
}
}
}
/// <summary>
/// <para>Initialize a new instance of the <see cref="DB2CommandWrapper"/> class with the text of a query and the command type.</para>
/// </summary>
/// <param name="commandText"><para>The stored procedure name or SQL sting the command represents.</para></param>
/// <param name="commandType"><para>One of the <see crer="CommandType"/> values.</para></param>
/// <param name="parameterToken"><para>The parameter delimeter for database commands.</para></param>
internal DB2CommandWrapper(string commandText, CommandType commandType, char parameterToken)
{
this.parameterToken = parameterToken;
this.command = CreateCommand(commandText, commandType);
this.guidParameters = new Hashtable();
this.boolParameters = new Hashtable();
}
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;
}
//View Data
public DB2DataReader ExecDataView(String sql)
{
if (this.ConnectDatabase() == true)
{
Cmd = new DB2Command(sql, Conn);
Resultset = Cmd.ExecuteReader();
}
return Resultset;
}
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));
}
}
// 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;
}
/// <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 Boolean ExecData(String sql)
{
try
{
if (this.ConnectDatabase() == true)
{
Cmd = new DB2Command(sql, Conn);
Cmd.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
return false;
}
}
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;
}
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;
}
/// <summary>
/// This is a sample specific finder method for Entity1.
/// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
/// </summary>
/// <param name="id"></param>
/// <returns>Entity1</returns>
public static GreetingEvent ReadItem(string id)
{
var greetingEvent = new GreetingEvent();
var db2Connection = GetSqlConnection();
db2Connection.Open();
var sqlCommand = new DB2Command
{
CommandText = string.Format("SELECT * FROM GREETING WHERE deptno = '{0}'", id),
Connection = db2Connection
};
var sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (sqlDataReader.Read())
{
greetingEvent.DeptNo = sqlDataReader[0].ToString();
greetingEvent.Greeting = sqlDataReader[1].ToString();
}
db2Connection.Dispose();
return greetingEvent;
}
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;
}
public DB2Command GetDeleteCommand()
{
DataTable dt = GetSchema();
if (deleteCommand == null)
{
string wheres = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = row.Table.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (i != 0)
{
wheres += " and ";
}
//wheres += String.Format("(({0} is null) or ({0} = v_{0}))", column.ColumnName);
wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
}
if (tableName == String.Empty)
{
tableName = (string)dt.Rows[0]["BaseTableName"];
}
DB2Command cmdaux = new DB2Command("delete from " + tableName + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Original;
cmdaux.Parameters.Add(aux);
}
deleteCommand = cmdaux;
}
return(deleteCommand);
}
public void Close()
{
DB2Transaction transaction = null;
if (refTransaction != null)
{
transaction = (DB2Transaction)refTransaction.Target;
}
if ((transaction != null) && refTransaction.IsAlive)
{
transaction.Dispose();
}
if (refCommands != null)
{
for (int i = 0; i < refCommands.Count; i++)
{
DB2Command command = null;
if (refCommands[i] != null)
{
command = (DB2Command)((WeakReference)refCommands[i]).Target;
}
if ((command != null) && ((WeakReference)refCommands[i]).IsAlive)
{
try
{
command.ConnectionClosed();
}
catch {}
}
//?? refCommands[i] = null;
}
}
if (openConnection != null)
{
openConnection.Close();
openConnection = null;
}
}
/// <summary>
/// This is a sample finder method for Entity1.
/// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
/// </summary>
/// <returns>IEnumerable of Entities</returns>
public static IEnumerable<GreetingEvent> ReadList()
{
var db2Connection = GetSqlConnection();
var trainingEvents = new List<GreetingEvent>();
db2Connection.Open();
var db2Command = new DB2Command
{
Connection = db2Connection,
CommandText = "SELECT * FROM GREETING",
};
var sqlDataReader = db2Command.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var trainingEvent = new GreetingEvent
{
DeptNo = sqlDataReader[0].ToString(),
Greeting = sqlDataReader[1].ToString()
};
trainingEvents.Add(trainingEvent);
}
db2Connection.Dispose();
return trainingEvents;
}
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>
/// 示例:
/// 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;
}
/// <summary>
/// 执行指定事务的命令,指定参数,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Paramter参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(DB2Transaction transaction, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 预处理
DB2Command cmd = new DB2Command();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建 DataAdapter & DataSet
using (DB2DataAdapter da = new DB2DataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 探索运行时的存储过程,返回DB2Parameter参数数组.
/// 初始化参数值为 DBNull.Value.
/// </summary>
/// <param name="connection">一个有效的数据库连接</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">是否包含返回值参数</param>
/// <returns>返回DB2Parameter参数数组</returns>
private static DB2Parameter[] DiscoverSpParameterSet(DB2Connection connection, string spName, bool includeReturnValueParameter)
{
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;
connection.Open();
// 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
DB2CommandBuilder.DeriveParameters(cmd);
connection.Close();
// 如果不包含返回值参数,将参数集中的每一个参数删除.
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
// 创建参数数组
DB2Parameter[] discoveredParameters = new DB2Parameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化参数值为 DBNull.Value.
foreach (DB2Parameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
/// <summary>
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
/// </summary>
/// <param name="command">要处理的db2Command</param>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">一个有效的事务或者是null值</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名或都T-SQL命令文本</param>
/// <param name="commandParameters">和命令相关联的db2Parameter参数数组,如果没有参数为'null'</param>
/// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
private static void PrepareCommand(DB2Command command, DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// 给命令分配一个数据库连接.
command.Connection = connection;
// 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText;
// 分配事务
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// 设置命令类型.
command.CommandType = commandType;
// 分配命令参数
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary>
/// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/DB2Parameters.
/// </summary>
/// <remarks>
/// 示例:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的连接事务</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或T-SQL语句</param>
/// <param name="dataSet">要填充结果集的DataSet实例</param>
/// <param name="tableNames">表映射的数据表数组
/// 用户定义的表名 (可有是实际的表名.)
/// </param>
/// <param name="commandParameters">分配给命令的DB2Paramter参数数组</param>
private static void FillDataset(DB2Connection connection, DB2Transaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params DB2Parameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建DB2Command命令,并进行预处理
DB2Command command = new DB2Command();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令
using (DB2DataAdapter dataAdapter = new DB2DataAdapter(command))
{
// 追加表映射
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// 填充数据集使用默认表名称
dataAdapter.Fill(dataSet);
// 清除参数,以便再次使用.
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是DB2Helper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Parameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由DB2Helper提供</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
private static DB2DataReader ExecuteReader(DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, DB2ConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建命令
DB2Command cmd = new DB2Command();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
DB2DataReader dataReader;
if (connectionOwnership == DB2ConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the DB2Reader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
bool canClear = true;
foreach (DB2Parameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
public void RefreshSchema()
{
insertCommand = null;
updateCommand = null;
deleteCommand = null;
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Paramter参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 预处理
DB2Command cmd = new DB2Command();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建DB2DataAdapter和DataSet.
using (DB2DataAdapter da = new DB2DataAdapter(cmd))
{
DataSet ds = new DataSet();
// 填充DataSet.
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
/// <summary>
/// 执行数据集更新到数据库,指定inserted, updated, or deleted命令.
/// </summary>
/// <remarks>
/// 示例:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">[追加记录]一个有效的T-SQL语句或存储过程</param>
/// <param name="deleteCommand">[删除记录]一个有效的T-SQL语句或存储过程</param>
/// <param name="updateCommand">[更新记录]一个有效的T-SQL语句或存储过程</param>
/// <param name="dataSet">要更新到数据库的DataSet</param>
/// <param name="tableName">要更新到数据库的DataTable</param>
public static void UpdateDataset(DB2Command insertCommand, DB2Command deleteCommand, DB2Command updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// 创建DB2DataAdapter,当操作完成后释放.
using (DB2DataAdapter dataAdapter = new DB2DataAdapter())
{
// 设置数据适配器命令
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// 更新数据集改变到数据库
dataAdapter.Update(dataSet, tableName);
// 提交所有改变到数据集.
dataSet.AcceptChanges();
}
}
/// <summary>
/// 执行指定数据库连接对象的DB2Command命令,并产生一个XmlReader对象做为结果集返回,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>
/// <param name="commandParameters">分配给命令的DB2Paramter参数数组</param>
/// <returns>返回XmlReader结果集对象.</returns>
public static XmlReader ExecuteXmlReader(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建DB2Command命令,并进行预处理
DB2Command cmd = new DB2Command();
try
{
PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令
XmlReader retval = cmd.ExecuteXmlReader();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
return retval;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// The DB2 Implementation of this assigns internal variable directly and returns NULL,
/// due to the DB2 classes not inheriting from the Sytem.Data.Generic namespace classes.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
protected override DbCommand CreateCommandObject(string sql)
{
this._dbCmd = new DB2Command(sql, this.DbConnection);
return null;
}
/// <summary>
/// 执行带事务的DB2Command(指定参数).
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new db2Parameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">DB2Paramter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(DB2Transaction transaction, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 预处理
DB2Command cmd = new DB2Command();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行
int retval = cmd.ExecuteNonQuery();
// 清除参数集,以便再次使用.
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 将db2Parameter参数数组(参数值)分配给db2Command命令.
/// 这个方法将给任何一个参数分配DBNull.Value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param name="command">命令名</param>
/// <param name="commandParameters">DB2Parameters数组</param>
private static void AttachParameters(DB2Command command, DB2Parameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (DB2Parameter p in commandParameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
public static void DeriveParameters(DB2Command command)
{
}
protected override DbCommand CreateDbCommand()
{
DbCommand dbCommand = new DB2Command(string.Empty, this);
return(dbCommand);
}