private void btnDel_Click(object sender, EventArgs e)
{
DictionaryEntry Item = (DictionaryEntry)ListBox1.SelectedItem;
string procedureName = Item.Value.ToString();
string message = procedureName + " を削除します。本当？";
DialogResult result = MessageBox.Show(this, message, "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
string str = "drop procedure CRMSC." + procedureName;
using (SymfowareConnection conn = DbUtil.GetConnection())
{
try
{
SymfowareCommand command = new SymfowareCommand(str, conn);
command.ExecuteReader();
MessageBox.Show("できた。");
btnReload.PerformClick();
}
catch (Fujitsu.Symfoware.Client.SymfowareException ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
/// <summary>
/// プロシージャ定義取得
/// </summary>
/// <param name="conn"></param>
/// <param name="storedCode"></param>
/// <returns></returns>
private SymfowareDataReader GetProcedureDefinition(SymfowareConnection conn, string storedCode)
{
string str = @"SELECT DESC_VALUE
FROM
RDBII_SYSTEM.RDBII_DESCRIPTION
WHERE OBJECT_CODE = :OBJECT_CODE ";
SymfowareCommand command = new SymfowareCommand(str, conn);
command.Parameters.Add("OBJECT_CODE", storedCode);
return(command.ExecuteReader());
}
private SymfowareDataReader GetProcedureParameterInfo(SymfowareConnection conn, string storedCode)
{
string str = @"SELECT
COLUMN_NAME, COLUMN_CODE, COLUMN_TYPE, DATA_TYPE, CHAR_MAX_LENGTH
FROM
RDBII_SYSTEM.RDBII_PROC_COL
WHERE PROCEDURE_CODE = :PROCEDURE_CODE
ORDER BY COLUMN_CODE ";
SymfowareCommand command = new SymfowareCommand(str, conn);
command.Parameters.Add("PROCEDURE_CODE", storedCode);
return(command.ExecuteReader());
}
private SymfowareDataReader GetProcedureNameList(SymfowareConnection conn)
{
string str = @"SELECT
PROCEDURE_NAME, PROCEDURE_CODE
FROM
RDBII_SYSTEM.RDBII_PROC
WHERE SCHEMA_NAME = 'CRMSC'
ORDER BY PROCEDURE_NAME";
SymfowareCommand command = new SymfowareCommand(str, conn);
SymfowareDataReader myReader = command.ExecuteReader();
return(myReader);
}
private SymfowareDataReader GetColumnList(SymfowareConnection conn, string dbCode, string schemaCode, string tableCode)
{
string str = @"SELECT * FROM
RDBII_SYSTEM.RDBII_COLUMN
WHERE
DB_CODE = :DB_CODE
AND SCHEMA_CODE = :SCHEMA_CODE
AND TABLE_CODE = :TABLE_CODE ";
SymfowareCommand command = new SymfowareCommand(str, conn);
command.Parameters.Add("DB_CODE", dbCode);
command.Parameters.Add("SCHEMA_CODE", schemaCode);
command.Parameters.Add("TABLE_CODE", tableCode);
SymfowareDataReader sfReader = command.ExecuteReader();
return(sfReader);
}
private SymfowareDataReader GetProcedureCaller(SymfowareConnection conn, string storedName)
{
string str = @"SELECT
T1.PROCEDURE_NAME
FROM
RDBII_SYSTEM.RDBII_DESCRIPTION T
LEFT JOIN RDBII_SYSTEM.RDBII_PROC T1
ON T.DB_CODE = T1.DB_CODE
AND T.SCHEMA_CODE = T1.SCHEMA_CODE
AND T.OBJECT_CODE = T1.PROCEDURE_CODE
WHERE UPPER(T.DESC_VALUE) LIKE :PROCEDURE_NAME
";
SymfowareCommand command = new SymfowareCommand(str, conn);
command.Parameters.Add("PROCEDURE_NAME", "%" + storedName.ToUpper() + "%");
return(command.ExecuteReader());
}
private void CreateProcedure()
{
string str = richTxtStoredDef.Text;
using (SymfowareConnection conn = DbUtil.GetConnection())
{
try
{
SymfowareCommand command = new SymfowareCommand(str, conn);
command.ExecuteNonQuery();
string text1 = "result: " + command.ToString();
MessageBox.Show("できた。");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}
private SymfowareDataReader GetTableNameList(SymfowareConnection conn)
{
string str = @"SELECT
T1.DB_NAME, T1.SCHEMA_NAME, T1.TABLE_NAME, T1.DB_CODE, T1.SCHEMA_CODE, T1.TABLE_CODE, T2.TABLE_OWNER
FROM RDBII_SYSTEM.RDBII_TABLE T1
LEFT JOIN RDBII_SYSTEM.TABLES T2
ON T1.DB_CODE = T2.DB_CODE
AND T1.SCHEMA_CODE = T2.SCHEMA_CODE
AND T1.TABLE_CODE = T2.TABLE_CODE
WHERE T1.DB_NAME = :DB_NAME ";
SymfowareCommand command = new SymfowareCommand(str, conn);
var appConfig = ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly().Location);
string dbName = appConfig.AppSettings.Settings["DBName"].Value;
command.Parameters.Add("DB_NAME", dbName);
SymfowareDataReader sfReader = command.ExecuteReader();
return(sfReader);
}
public static SymfowareCommand CallProcedure(SymfowareCommand command, List <ProcedureParameter> paramInfos)
{
foreach (ProcedureParameter paramInfo in paramInfos)
{
SymfowareParameter param = new SymfowareParameter();
param.ParameterName = paramInfo.colName;
param.SymfowareDbType = paramInfo.getDataType();
param.Direction = paramInfo.getDirect();
if (param.Direction == ParameterDirection.Input)
{
param.Value = DbUtil.GetUserInput(paramInfo.colName);
}
else if (param.Direction == ParameterDirection.Output)
{
param.Size = paramInfo.max;
}
command.Parameters.Add(param);
}
command.ExecuteNonQuery();
return(command);
}
private void btnExeProc_Click(object sender, EventArgs e)
{
List <ProcedureParameter> paramInfos = new List <ProcedureParameter>();
DictionaryEntry Item = (DictionaryEntry)ListBox1.SelectedItem;
using (SymfowareConnection conn = DbUtil.GetConnection())
{
SymfowareDataReader sfReader = GetProcedureParameterInfo(conn, Item.Key.ToString());
while (sfReader.Read())
{
ProcedureParameter paramInfo = new ProcedureParameter();
paramInfo.colType = int.Parse(DbUtil.GetValue(sfReader, "COLUMN_TYPE"));
paramInfo.dataType = int.Parse(DbUtil.GetValue(sfReader, "DATA_TYPE"));
paramInfo.max = int.Parse(DbUtil.GetValue(sfReader, "CHAR_MAX_LENGTH"));
paramInfo.colName = DbUtil.GetValue(sfReader, "COLUMN_NAME");
paramInfos.Add(paramInfo);
}
sfReader.Close();
}
string strCommand = "CALL CRMSC." + Item.Value + "(";
foreach (ProcedureParameter paramInfo in paramInfos)
{
strCommand += "?,";
}
strCommand = strCommand.TrimEnd(new Char[] { ',' }) + ")";
using (SymfowareConnection conn = DbUtil.GetConnection())
{
SymfowareCommand command = new SymfowareCommand(strCommand, conn);
command = DbUtil.CallProcedure(command, paramInfos);
string text1 = "result: " + command.Parameters["oERRMSG"].Value;
command.Dispose();
}
}
