private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(ddlDatabase.Text))
{
return;
}
project.Namespace = ddlDatabase.Text;
project.Title = ddlDatabase.Text;
string conStr1 = "Server={0};Port={1};Uid={2};Pwd={3};Database={4}";
string conStr = string.Format(conStr1, tbxServer.Text, tbxPort.Text, tbxUserName.Text, tbxPassword.Text, ddlDatabase.Text);
MessageBox.Show(conStr);
MySqlConnection con = new MySqlConnection(conStr);
try
{
con.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
List <string> tableNames = new List <string>();
MySqlCommand cmd = new MySqlCommand("show tables", con);
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add(reader.GetString(0));
}
reader.Close();
#region 查询列结构
foreach (string strTableName in tableNames)
{
ZippyCoder.Entity.Table table = new ZippyCoder.Entity.Table();
table.Name = strTableName;
table.Title = strTableName;
project.Tables.Add(table);
string sql = "SHOW COLUMNS FROM " + strTableName;
MySqlCommand cmdColProperty = new MySqlCommand(sql, con);
IDataReader readerCol = cmdColProperty.ExecuteReader();
List <ZippyCoder.Entity.Col> fkCols = new List <ZippyCoder.Entity.Col>();
while (readerCol.Read())
{
object _colName = readerCol.GetValue(0);
object _mysqlType = readerCol.GetValue(1);
object _canNull = readerCol.GetValue(2);
object _isKey = readerCol.GetValue(3);
object _defVal = readerCol.GetValue(4);
object _extra = readerCol.GetValue(5);
string colName = _colName == null ? "" : _colName.ToString();
string mysqlType = _mysqlType == null ? "" : _mysqlType.ToString();
string canNull = _canNull == null ? "" : _canNull.ToString();
string isKey = _isKey == null ? "" : _isKey.ToString();
string defVal = _defVal == null ? "" : _defVal.ToString();
string extra = _extra == null ? "" : _extra.ToString();
string dataType = string.Empty, dataLen = string.Empty;
var match = System.Text.RegularExpressions.Regex.Match(mysqlType, @"([\w]+).*?([\d\,]+).*");
var matchCount = match.Groups.Count;
if (matchCount > 1)
{
dataType = match.Groups[1].Value;
}
if (matchCount > 2)
{
dataLen = match.Groups[2].Value;
}
if (string.IsNullOrEmpty(dataType))
{
dataType = mysqlType;
}
ZippyCoder.Entity.Col col = table.Exists(colName);
if (col == null)
{
col = new ZippyCoder.Entity.Col();
table.Cols.Add(col);
col.Parent = table;
}
col.Name = colName;
col.Title = colName;
col.DataType = ZippyCoder.TypeConverter.ToSqlDbType(dataType);
col.Default = (defVal == "NULL" ? "" : defVal);
col.Length = dataLen;
col.IsPK = (isKey != null && isKey.ToLower() == "pri");
col.IsNull = (canNull != null && canNull.ToLower() == "yes");
col.AutoIncrease = (extra != null && extra.ToLower() == "auto_increment");
}
readerCol.Close();
}
#endregion
con.Close();
_Owner.Project = project;
_Owner.UpdateUI();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(ddlDatabase.Text))
{
return;
}
project.Namespace = ddlDatabase.Text;
project.Title = ddlDatabase.Text;
string conStr1 = "Persist Security Info=False;User ID={1};Password={2};Server={0};Initial Catalog={3}";
string conStr2 = "Persist Security Info=False;Integrated Security=true;Server={0};Initial Catalog={1}";
string conStr = "";
if (string.IsNullOrEmpty(tbxUserName.Text.Trim()))
{
conStr = string.Format(conStr2, tbxServer.Text, ddlDatabase.Text);
}
else
{
conStr = string.Format(conStr1, tbxServer.Text, tbxUserName.Text, tbxPassword.Text, ddlDatabase.Text);
}
SqlConnection con = new SqlConnection(conStr);
try
{
con.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
List <string> tableNames = new List <string>();
SqlCommand cmd = new SqlCommand("select name from sysobjects where xtype='U' order by name", con);
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add(reader.GetString(0));
}
reader.Close();
foreach (string strTableName in tableNames)
{
ZippyCoder.Entity.Table table = new ZippyCoder.Entity.Table();
table.Name = strTableName;
table.Title = strTableName;
project.Tables.Add(table);
string sql = @"SELECT
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,
INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,
INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION,
INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE,
INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE,
COLUMNPROPERTY(OBJECT_ID('" + strTableName + @"'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.COLUMNS LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME AND INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '" + strTableName + "'";
//System.Diagnostics.Debug.WriteLine(sql);
//return;
SqlCommand cmdColProperty = new SqlCommand(sql, con);
IDataReader readerCol = cmdColProperty.ExecuteReader();
List <ZippyCoder.Entity.Col> fkCols = new List <ZippyCoder.Entity.Col>();
while (readerCol.Read())
{
string colName = readerCol.GetValue(0).ToString();
ZippyCoder.Entity.Col col = table.Exists(colName);
if (col == null)
{
col = new ZippyCoder.Entity.Col();
table.Cols.Add(col);
col.Parent = table;
}
col.Name = colName;
col.Title = colName;
col.DataType = ZippyCoder.TypeConverter.ToSqlDbType(readerCol.GetValue(1).ToString());
col.Default = readerCol.GetValue(2).ToString();
if (col.DataType == SqlDbType.VarChar || col.DataType == SqlDbType.NVarChar || col.DataType == SqlDbType.Char || col.DataType == SqlDbType.NChar)
{
col.Length = readerCol.GetValue(3).ToString();
}
else if (col.DataType == SqlDbType.Decimal)
{
col.Length = "(" + readerCol.GetValue(4).ToString() + "," + readerCol.GetValue(5).ToString() + ")";
}
if ((readerCol.GetValue(6).ToString().ToUpper() == "NO"))
{
col.IsNull = false;
}
if (readerCol.GetValue(7).ToString() == "1")
{
col.AutoIncrease = true;
}
if (readerCol.GetValue(8).ToString() == "PRIMARY KEY")
{
col.IsPK = true;
}
if (readerCol.GetValue(8).ToString() == "UNIQUE")
{
col.Unique = true;
}
if (readerCol.GetValue(8).ToString() == "FOREIGN KEY") //将有外键约束的列记录下来，待查。
{
fkCols.Add(col);
}
}
readerCol.Close();
}
con.Close();
_Owner.Project = project;
_Owner.UpdateUI();
}
