/// <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();
}
}
}
}
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;
}
public IDbConnection OpenConnection(string connectionString)
{
var connection = new IBM.Data.DB2.DB2Connection(connectionString);
connection.Open();
return(connection);
}
protected override IDbConnection CreateConnection()
{
DB2Connection conn = new DB2Connection(ConnectionString);
conn.Open();
return conn;
}
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));
}
}
//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;
}
}
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> 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>
/// 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;
}
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>
/// <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>
/// 探索运行时的存储过程,返回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>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
/// </remarks>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataSet">要填充结果集的DataSet实例</param>
/// <param name="tableNames">表映射的数据表数组
/// 用户定义的表名 (可有是实际的表名.)
/// </param>
/// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
public static void FillDataset(string connectionString, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建并打开数据库连接对象,操作完成释放对象.
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// 调用指定数据库连接字符串重载方法.
FillDataset(connection, spName, dataSet, tableNames, parameterValues);
}
}
/// <summary>
/// 数据库连接测试
/// </summary>
/// <param name="constr">数据库连接字符串</param>
/// <returns>True: OK , false : Error</returns>
public static bool testCon(string conStr)
{
bool flag = false;
DB2Connection db2con = new DB2Connection(conStr);
try
{
if (db2con != null)
{
db2con.Open();
flag = true;
}
}
catch (Exception ce)
{
throw new Exception(ce.Message);
}
finally
{
db2con.Close();
}
return flag;
}
/// <summary>
/// 执行指定数据库连接字符串的命令,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(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">DB2Paramters参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建并打开数据库连接对象,操作完成释放对象.
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// 调用指定数据库连接字符串重载方法.
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 执行指定数据库连接字符串的命令,映射数据表并填充数据集.指定命令参数.
/// </summary>
/// <remarks>
/// 示例:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new DB2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">分配给命令的DB2Paramter参数数组</param>
/// <param name="dataSet">要填充结果集的DataSet实例</param>
/// <param name="tableNames">表映射的数据表数组
/// 用户定义的表名 (可有是实际的表名.)
/// </param>
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params DB2Parameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建并打开数据库连接对象,操作完成释放对象.
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// 调用指定数据库连接字符串重载方法.
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
public void queryBoundaryDate()
{
DB2Connection conndb2 = new DB2Connection(globalConnStringDb2);
try
{
conndb2.Open();
DB2Command cmd = conndb2.CreateCommand();
DB2Transaction trans = conndb2.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText = "" + rtCheckBoundary.Text;
tbBoundaryDate.Text = cmd.ExecuteScalar().ToString();
string strBoundaryLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran Archive Boundary Query. Current boundary date is " + tbBoundaryDate.Text + "@" ;
strBoundaryLog = strBoundaryLog.Replace("@", "" + System.Environment.NewLine);
rtLogging.AppendText(strBoundaryLog);
conndb2.Close();
}
catch (Exception e)
{
MessageBox.Show("An error was encountered during runtime. Please try again.\n\n" + e.GetBaseException().Message, "Check Archive Boundary Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public BexisPublication[] getPublicationsMetadataXml(string DataBase)
{
var bexisPublications = new List<BexisPublication>();
var docs = new List<XmlDocument>();
var doc = new XmlDocument();
string mySelectQuery = "SELECT ID,DATA FROM \"EXPLORER\".\"PUBLICATIONLIST\"";
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));
var bp = new BexisPublication();
bp.Id = (int)(myReader.GetValue(0));
bp.MetaDataXml = doc;
var fileCommand = new DB2Command("SELECT FILENAME, MIMETYPE ,FILE FROM \"EXPLORER\".\"PUBLICATIONFILES\" where PUBID=" + bp.Id, connect);
var fileReader = fileCommand.ExecuteReader();
string dataPath = AppConfiguration.DataPath;
string storepath = Path.Combine(dataPath, "Temp", "Administrator");
// if folder not exist
if (!Directory.Exists(storepath))
Directory.CreateDirectory(storepath);
var index = 1;
if (fileReader.Read())
{
PublicationContentDescriptor pubContent = new PublicationContentDescriptor()
{
OrderNo = index++,
Name = fileReader.GetString(0),
MimeType = fileReader.GetString(1)
};
var filePath = Path.Combine(storepath, Guid.NewGuid().ToString() + Path.GetExtension(pubContent.Name));
File.WriteAllBytes(filePath, ((Byte[])fileReader.GetValue(2)));
pubContent.URI = filePath;
bp.PublicationContentDescriptors.Add(pubContent);
}
fileReader.Close();
bexisPublications.Add(bp);
}
myReader.Close();
connect.Close();
return bexisPublications.ToArray();
}
//資料庫初始化
public void InitDB()
{
sConnstring ="Database=TCS;UserID=db2inst1;Password=db2inst1;Server=10.21.50.31:50000";
conn = new DB2Connection(sConnstring);
conn.Open();
}
public void queryPrimaryWS()
{
DB2Connection conndb2 = new DB2Connection(globalConnStringDb2);
try
{
conndb2.Open();
DB2Command cmd = conndb2.CreateCommand();
DB2Transaction trans = conndb2.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText = "" + rtPrimaryWS.Text;
tbPrimWS.Text = cmd.ExecuteScalar().ToString();
string strLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran TA00WB.WORK_SUMMARY Query. " + tbPrimWS.Text + " rows returned.@";
strLog = strLog.Replace("@", "" + System.Environment.NewLine);
rtLogging.AppendText(strLog);
//logger
string logFilePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
using (TextWriter outputFile = new StreamWriter(logFilePath + @"\WBArchiving.txt", true))
{
outputFile.Write(strLog);
}
conndb2.Close();
}
catch (Exception e)
{
tbPrimWS.Text = " ";
MessageBox.Show("An error was encountered during runtime. Please try again.\n\n" + e.GetBaseException().Message, "TA00WB.WORK_SUMMARY Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
string strArchWSLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran TA00WB.WORK_SUMMARY Query. Error occured. No" + tbPrimWS.Text + "rows returned.@";
strArchWSLog = strArchWSLog.Replace("@", "" + System.Environment.NewLine);
rtLogging.AppendText(strArchWSLog);
}//catch
}
/// <summary>
/// Get whether the connection can be established or not
/// </summary>
public bool CanConnect()
{
bool canConnect = false;
try
{
using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString))
{
connection.Open();
canConnect = Convert.ToBoolean(connection.State == ConnectionState.Open);
connection.Close();
}
}
catch (Exception ex)
{
Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod());
ErrorList.Add(aError);
canConnect = false;
}
return canConnect;
}
private List<DB2TimeStamp> queryDistInsertDates(string dataSetID, string DataBase)
{
List<DB2TimeStamp> distInsertDates = new List<DB2TimeStamp>();
string mySelectQuery = "select distinct insertdate";
mySelectQuery += " from explorer.observation where datasetid = " + dataSetID + " order by insertdate asc;";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
distInsertDates.Add(myReader.GetDB2TimeStamp(0));
}
myReader.Close();
connect.Close();
return distInsertDates;
}
/// <summary><see cref="Edrych.DataAccess.DataAccessBase.GetDbConnection"/></summary>
protected override System.Data.IDbConnection GetDbConnection()
{
DB2Connection conn = new DB2Connection(this.ConnectionString);
conn.Open();
return conn;
}
private List<Observation> queryObservation(string dataSetID, string DataBase, string insertDateStrg)
{
List<Observation> observations = new List<Observation>();
string mySelectQuery = "select obsid, data, deleted, newest";
mySelectQuery += " from explorer.observation";
mySelectQuery += " where datasetid = " + dataSetID;
mySelectQuery += " and insertdate = '" + insertDateStrg + "';";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Observation observation = new Observation();
observation.obsid = myReader.GetInt64(0);
observation.data = new XmlDocument();
observation.data.LoadXml(myReader.GetString(1));
observation.deleted = myReader.GetChar(2);
observation.newest = myReader.GetChar(3);
observations.Add(observation);
}
myReader.Close();
connect.Close();
return observations;
}
/// <summary>
/// 执行指定连接字符串,类型的DB2Command.如果没有提供参数,不返回结果.
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new db2Parameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的数据库连接字符串</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">db2Parameter参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
/// <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;
}
}
protected void DB2Connection()
{
string Text1 = TextBox1.Text;
string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
Regex input = new Regex(inputPattern);
Match inputmatch = input.Match(TextBox1.Text);
if (inputmatch.Success)
{
string s = @"Server=localhost:50000;Database=" + DropDownList2.SelectedItem.Text + ";UID=db2admin;PWD=***;CurrentSchema=db2admin";
DB2Connection myConnection = new DB2Connection(s);
DB2Command cmd = new DB2Command();
DB2DataReader r = null;
DB2DataAdapter adapter = new DB2DataAdapter(Text1, myConnection);
//string usertype = User.Identity.Name.Substring(4);
string usertype = Session["Role"].ToString().ToLower();
string pattern = "";
switch (usertype)
{
case "level1":
pattern = @"((^|\;)\s*(?i)(\binsert\b|\bupdate\b|\bdelete\b|\bmerge\b|\bcreate\b|\balter\b|\bdrop\b)(?i))|(?i)\binto\b(?i)";
break;
case "level2":
pattern = @"(^|;|\*\/)\s*(?i)(\bcreate\b|\balter\b|\bdrop\b)(?i)";
break;
default:
pattern = @"((^|;|\*\/)\s*(?i)(\bdrop\b\s*database)(?i))";
break;
}
Regex rgx = new Regex(pattern);
Match match = rgx.Match(TextBox1.Text);
if (match.Success)
{
msg.Text = "Permission was denied on database " + DropDownList2.SelectedItem.Text;
}
else
{
try
{
cmd.CommandText = TextBox1.Text;
cmd.Connection = myConnection;
myConnection.Open();
string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
if (firstWord == "select")
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridView1.DataSource = dt;
r = cmd.ExecuteReader();
int rowCount = 0;
if (r.HasRows) //results>0
{
while (r.Read())
{
rowCount++;
}
msg.Text = "Result: " + rowCount + " row(s) found";
}
else
{
msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
for (int i = 0; i < r.FieldCount; i++)
{
msg.Text += r.GetName(i) + " |";
}
msg.Text += "</span><br />";
}
r.Close();
}
else
{
int numberOfRecords = cmd.ExecuteNonQuery();
msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
}
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
finally
{
myConnection.Close();
GridView1.DataBind();
FormatView();
}
}
}
else
{
//msg.Text = "Only one statement allowed";
Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
}
}