//コンストラクタ
public MySQLDAOBase(MySQLDAOContext context)
{
if (context == null) throw new ArgumentNullException("context");
this.Context = context;
string sql = "SET NAMES SJIS";
this.ExecuteNonQuery(sql);
}
public static void AddTable(string tableName)
{
if (!MySQLTableHelper.MySQLTableData.Keys.Contains(tableName))
{
DataTable newTable = null;
using (MySQLDAOContext con = new MySQLDAOContext(MySQLConString.getStaticConstring()))
{
con.OpenConnection();
myTableDAO dao = new myTableDAO(con, tableName);
newTable = dao.selectAll();
con.CloseConnection();
}
MySQLTableHelper.MySQLTableData.Add(tableName, new MySQLTableData( newTable));
}
}
public myTableColDAO(MySQLDAOContext con, string tableName_, string columnName_)
: base(con)
{
this.tableName = tableName_;
this.columnName = columnName_;
}
public InsertDAO(MySQLDAOContext con)
: base(con)
{
}
public MySQLSchemaDAO(MySQLDAOContext con)
: base(con)
{
}
public static IList<string> getTop5TableCol(string tableName, string colName)
{
List<string> ret = new List<string>();
DataTable tbl = null;
using (MySQLDAOContext con = new MySQLDAOContext(MySQLConString.getStaticConstring()))
{
con.OpenConnection();
myTableColDAO dao = new myTableColDAO(con, tableName, colName);
tbl = dao.selectTop5();
con.CloseConnection();
}
if (tbl != null)
{
foreach (DataRow row in tbl.Rows)
{
ret.Add(row[colName].ToString());
}
}
return ret;
}
public myTableDAO(MySQLDAOContext con, string tableName_)
: base(con)
{
this.tableName = tableName_;
}
private void btnTestInsert_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cmbKensu.Text))
{
MessageBox.Show("挿入件数を指定して下さい。");
return;
}
if (MessageBox.Show("ダミーデータを挿入しますか？", "確認", MessageBoxButtons.OKCancel) != System.Windows.Forms.DialogResult.OK)
{
MessageBox.Show("キャンセルしました。");
return;
}
lblTotalNum.Text = cmbKensu.Text;
lblInsertedNum.Text = "0";
long insertednum = 0;
long kensu = 0;
long.TryParse(cmbKensu.Text, out kensu);
MySQLDAO.MySQLConString objConString = this.getObjConstring();
List<string> colNames = new List<string>();
Dictionary<string, string> colTypes = new Dictionary<string, string>();
Dictionary<string, string> colVales = new Dictionary<string, string>();
Dictionary<string, string> paramsByColumn = new Dictionary<string, string>();
Dictionary<string, string> nullratiosByColumn = new Dictionary<string, string>();
foreach (DataGridViewRow row in this.grid.Rows)
{
CustomDataMySQLTable c = row.DataBoundItem as CustomDataMySQLTable;
if (c != null)
{
string columnName = c.ColName;
string param = c.Val;
string nullratio = c.NullRatio;
paramsByColumn.Add(columnName, param);
nullratiosByColumn.Add(columnName, nullratio);
}
}
ColumnData cold = new ColumnData(paramsByColumn, nullratiosByColumn);
try
{
using (MySQLDAOContext con = new MySQLDAOContext(MySQLConString.getStaticConstring()))
{
con.OpenConnection();
//con.BeginTransaction(IsolationLevel.RepeatableRead);
InsertDAO dao = new InsertDAO(con);
for (int l = 0; l < kensu; l++)
{
Dictionary<string, string> genedCols = cold.GenerateNext();
colNames.Clear();
colTypes.Clear();
colVales.Clear();
foreach (DataGridViewRow row in this.grid.Rows)
{
CustomDataMySQLTable c = row.DataBoundItem as CustomDataMySQLTable;
if (c != null)
{
string columnName = c.ColName;
string colVal = genedCols[columnName];
string colType = c.ColType;
colNames.Add(columnName);
colTypes.Add(columnName, colType);
colVales.Add(columnName, colVal);
}
}
try
{
dao.Insert(tbxTableName.Text, colNames, colTypes, colVales);
//con.CommitTransaction();
}
catch (Exception excp)
{
/*
MessageBox.Show(excp.Message);
MessageBox.Show("ロールバックします。");
con.RollbackTransaction();
con.CloseConnection();
return;
*/
MySQLTableHelper.SetNext();
continue;
}
MySQLTableHelper.SetNext();
insertednum++;
lblInsertedNum.Text = insertednum.ToString();
System.Windows.Forms.Application.DoEvents();
}
con.CloseConnection();
}
MessageBox.Show(insertednum + "の挿入に成功しました。");
}
catch (Exception excp)
{
MessageBox.Show("エラーが発生しました。MySQLが起動しているかご確認下さい。");
return;
}
}
private void btnTest1_Click(object sender, EventArgs e)
{
MySQLDAO.MySQLConString objConString = this.getObjConstring();
try
{
using (MySQLDAO.MySQLDAOContext con = new MySQLDAO.MySQLDAOContext(objConString.ConString))
{
con.OpenConnection();
con.CloseConnection();
MessageBox.Show("success");
}
}
catch (MySql.Data.MySqlClient.MySqlException excep)
{
MessageBox.Show("error");
}
}
private void btnSqlTest_Click(object sender, EventArgs e)
{
MySQLDAO.MySQLConString objConString = this.getObjConstring();
using (MySQLDAO.MySQLDAOContext con = new MySQLDAO.MySQLDAOContext(objConString.ConString))
{
con.OpenConnection();
t_table1Dao dao = new t_table1Dao(con);
//insert
dao.insert("test1");
int id = 1;
//select
DataTable tbl = dao.selectAll();
if (tbl.Rows.Count > 0)
{
MessageBox.Show(tbl.Rows[0]["name"].ToString() + "がInsertされました。");
id = (int)tbl.Rows[0]["id"];
}
else
{
MessageBox.Show("insert失敗");
}
//update
dao.update(id, "test2");
//select
DataTable tbl2 = dao.select(id);
if (tbl2.Rows.Count > 0)
{
string name = tbl2.Rows[0]["name"].ToString();
if ("test2".Equals(name))
{
MessageBox.Show(name + "にupdateされました。");
}
else
{
MessageBox.Show("updateに失敗しました。");
}
}
//delete
dao.delete(id);
//select
DataTable tbl3 = dao.select(id);
if (tbl3.Rows.Count > 0)
{
MessageBox.Show("deleteに失敗しました。");
}
else
{
MessageBox.Show("deleteに成功しました。");
}
con.CloseConnection();
MessageBox.Show("success");
}
}
private void btnReadColumn_Click(object sender, EventArgs e)
{
try
{
MySQLDAO.MySQLConString objConString = this.getObjConstring();
DataTable schema = null;
using (MySQLDAO.MySQLDAOContext con = new MySQLDAO.MySQLDAOContext(objConString.ConString))
{
con.OpenConnection();
MySQLSchemaDAO dao = new MySQLSchemaDAO(con);
schema = dao.selectTableColumns(tbxDbName.Text, tbxTableName.Text);
con.CloseConnection();
}
if (schema != null)
{
this.dataSource.Clear();
this.cmbColumns.Items.Clear();
foreach (DataRow row in schema.Rows)
{
this.dataSource.Add(new CustomDataMySQLTable(row["COLUMN_NAME"].ToString(), row["DATA_TYPE"].ToString(), "",""));
this.cmbColumns.Items.Add(row["COLUMN_NAME"].ToString());
this.cmbNullColumns.Items.Add(row["COLUMN_NAME"].ToString());
}
}
this.grid.DataSource = this.dataSource;
if (this.cmbColumns.Items.Count > 0)
{
this.cmbColumns.SelectedIndex = 0;
}
if (this.cmbNullColumns.Items.Count > 0)
{
this.cmbNullColumns.SelectedIndex = 0;
}
}
catch (Exception excp)
{
MessageBox.Show("エラーが発生しました。MySQLが起動しているかご確認下さい。");
return;
}
}