/// <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();
}
}
}
}
//ใช้ในการ เพิ่ม, ลบ แก้ไข
public Boolean ExecData(String sql)
{
try
{
if (this.ConnectDatabase() == true)
{
Cmd = new DB2Command(sql, Conn);
Cmd.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
return false;
}
}
/// <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>
/// 执行指定数据库连接对象的命令
/// </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>
/// Execute SQL command and return the number of rows affected
/// </summary>
/// <param name="command">Command (Text command or Stored Procedure)</param>
/// <param name="type">Type of command (text, stored procedure or table-direct)</param>
/// <returns>Number of rows affected</returns>
public int ExecuteNonQuery(string command, CommandType type)
{
int rowsAffected = 0;
try
{
using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString))
{
using (DB2Command cmd = new DB2Command(command))
{
cmd.Connection = connection;
foreach(DB2Parameter parameter in Parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.CommandType = type;
cmd.Connection.Open();
rowsAffected = cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod());
ErrorList.Add(aError);
}
return rowsAffected;
}
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>"));
}
}