public static void DeleteIndex(SqlConnection conn, SqlCommand cmd, PdmTable pTable)
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, pTable.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Delete_Index", paras);
}
public static void SaveTableSet(PdmTable pTable)
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, pTable.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
paras.Add(DBUtils.MakeInParam("TableSet", SqlDbType.NVarChar, 1000, pTable.TableSet));
DBUtils.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.P_Save_TableSet", paras);
}
public static void LogKey(SqlConnection conn, SqlCommand cmd, PdmTable pTable, bool islog)
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, pTable.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
paras.Add(DBUtils.MakeInParam("IsLog", SqlDbType.Bit, islog));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Log_Key", paras);
}
public static void SaveColumnTable(PdmTable pTable, List <ColumnTable> items)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = DBUtils.GetConnection();
cmd = DBUtils.GetCommand();
cmd.Transaction = conn.BeginTransaction();
ArrayList paras = new ArrayList();
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, pTable.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Delete_ColumnTable", paras);
int i = 1;
foreach (ColumnTable item in items)
{
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, item.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, item.TableCode));
paras.Add(DBUtils.MakeInParam("ColumnSerial", SqlDbType.Int, i));
paras.Add(DBUtils.MakeInParam("ColumnCode", SqlDbType.NVarChar, 40, item.ColumnCode));
paras.Add(DBUtils.MakeInParam("RelaTable", SqlDbType.NVarChar, 40, item.RelaTable));
paras.Add(DBUtils.MakeInParam("RelaColumn", SqlDbType.NVarChar, 40, item.RelaColumn));
paras.Add(DBUtils.MakeInParam("DisplayColumn", SqlDbType.NVarChar, 40, item.DisplayColumn));
paras.Add(DBUtils.MakeInParam("Prefix", SqlDbType.NVarChar, 2, item.Prefix));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Save_ColumnTable", paras);
i++;
}
cmd.Transaction.Commit();
}
catch
{
cmd.Transaction.Rollback();
throw;
}
finally
{
DBUtils.SetDispose(conn, cmd);
}
}
public static DataTable GetColumnByTable(SqlConnection conn, SqlCommand cmd, PdmTable pTable)
{
DataTable tblMain;
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, pTable.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
tblMain = DBUtils.ExecuteDataTable(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_ColumnByTable", paras);
tblMain.TableName = "SelectMain";
}
catch { throw; }
return(tblMain);
}
public override List <TableEntity> FillTables(List <string> tableNames)
{
IList <PdmTable> pdmTables = GetPdmTables();
List <TableInfo> tables = new List <TableInfo>();
if (pdmTables != null)
{
foreach (string tableName in tableNames)
{
PdmTable pdmTable = pdmTables.Where(o => o.Code.ToLower() == tableName.ToLower()).FirstOrDefault();
TableInfo table = GetTableInfo(pdmTable);
if (table != null)
{
tables.Add(table);
}
}
}
return(ReaderTables(tables));
}
protected TableInfo GetTableInfo(PdmTable pdmTable)
{
if (pdmTable != null)
{
TableInfo table = new TableInfo();
table.TableName = pdmTable.Code;
table.Attr = pdmTable.Name.TrimEnd('表');
table.Remark = pdmTable.Comment;
if (pdmTable.Columns != null)
{
foreach (PdmColumn pdmColumn in pdmTable.Columns)
{
ColumnInfo column = new ColumnInfo();
column.Field = pdmColumn.Code;
column.Display = pdmColumn.Name;
column.Remark = pdmColumn.Comment;
column.DataType = Regex.Replace(pdmColumn.DataType, @"\(\d*,?\d*\)", "").ToLower();
column.IsNullable = !pdmColumn.Mandatory;
column.DefaultValue = string.Empty;
column.Length = string.IsNullOrEmpty(pdmColumn.Length) ? 0 : Convert.ToInt32(pdmColumn.Length);
if (pdmColumn.IdEntity)
{
column.IsIdentity = true;
column.IsDataKey = true;
}
//设置主键
if (pdmTable.Keys != null)
{
var key = pdmTable.Keys.Where(o => o.Code.ToLower() == pdmColumn.Code.ToLower()).FirstOrDefault();
if (key != null)
{
column.IsDataKey = true;
}
}
table.Columns.Add(column);
}
}
return(table);
}
return(null);
}
public static DataTable GetColumnByTable(PdmTable pTable)
{
return(GetColumnByTable(null, null, pTable));
}
public static void GetTableByCode(PdmTable pTable, int DBID, string TableCode)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = DBUtils.GetConnection();
cmd = DBUtils.GetCommand();
ArrayList paras = new ArrayList();
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, TableCode));
SqlDataReader reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_TableByCode", paras);
pTable.OnInit();
if (reader.Read())
{
pTable.OnPopulate(reader);
}
reader.Close();
if (!String.IsNullOrEmpty(pTable.TableCode))
{
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_SetByCode", paras);
if (reader.Read())
{
pTable.TableSet = reader["TableSet"] as string;
}
else
{
pTable.TableSet = String.Empty;
}
reader.Close();
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_ColumnByTable", paras);
while (reader.Read())
{
PdmColumn item = new PdmColumn(reader);
pTable.AddColumn(item);
}
reader.Close();
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_KeyByTable", paras);
while (reader.Read())
{
PdmKey item = new PdmKey(reader);
pTable.AddKey(item);
}
reader.Close();
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_IndexByTable", paras);
while (reader.Read())
{
PdmIndex item = new PdmIndex(reader);
pTable.AddIndex(item);
}
reader.Close();
foreach (PdmKey pKey in pTable.Keys)
{
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
paras.Add(DBUtils.MakeInParam("KeyCode", SqlDbType.NVarChar, 40, pKey.KeyCode));
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_ColumnByKey", paras);
while (reader.Read())
{
foreach (PdmColumn pColumn in pTable.Columns)
{
if (pColumn.ColumnCode == reader["ColumnCode"].ToString())
{
pKey.AddColumn(pColumn);
break;
}
}
}
reader.Close();
}
foreach (PdmIndex pIndex in pTable.Indexs)
{
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
paras.Add(DBUtils.MakeInParam("IndexCode", SqlDbType.NVarChar, 40, pIndex.IndexCode));
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_ColumnByIndex", paras);
while (reader.Read())
{
foreach (PdmColumn pColumn in pTable.Columns)
{
if (pColumn.ColumnCode == reader["ColumnCode"].ToString())
{
pIndex.AddColumn(pColumn);
break;
}
}
}
reader.Close();
}
}
}
catch { throw; }
finally
{
DBUtils.SetDispose(conn, cmd);
}
}
private void savefile_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(filename.Text))
{
MessageBox.Show("数据库定义文件不能为空！");
return;
}
if (String.IsNullOrEmpty(dbname.Text))
{
MessageBox.Show("数据库名称不能为空！");
return;
}
if (String.IsNullOrEmpty(dbcode.Text))
{
MessageBox.Show("数据库代码不能为空！");
return;
}
if (ddlProject.SelectedValue.ToString() == DropAddFlag.Select.ToString())
{
Global.ShowSysInfo("请选择所属项目！");
return;
}
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
PdmHelper pdm = new PdmHelper(filename.Text);
pdm.InitData();
conn = DBUtils.GetConnection();
cmd = DBUtils.GetCommand();
cmd.Transaction = conn.BeginTransaction();
#region Save DB
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("DBCode", SqlDbType.NVarChar, 40, dbcode.Text));
SqlDataReader reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_DBByCode", paras);
PdmDatabase db = new PdmDatabase();
if (reader.Read())
{
db.OnPopulate(reader);
}
reader.Close();
if (String.IsNullOrEmpty(db.DBCode))
{
db.DBCode = dbcode.Text;
db.DBName = dbname.Text;
}
db.ProjectID = ddlProject.SelectedValue.ToString();
if (db.DBID > 0)
{
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, db.DBID));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Delete_Info", paras);
}
//if (db.DBID <= 0)
//{
// 新数据库文件
paras.Clear();
paras.Add(DBUtils.MakeOutParam("DBID", SqlDbType.Int));
paras.Add(DBUtils.MakeInParam("DBName", SqlDbType.NVarChar, 40, db.DBName));
paras.Add(DBUtils.MakeInParam("DBCode", SqlDbType.NVarChar, 40, db.DBCode));
paras.Add(DBUtils.MakeInParam("IsLog", SqlDbType.Bit, cbLog.Checked));
paras.Add(DBUtils.MakeInParam("DBType", SqlDbType.Int, DataBaseType.MySql));
paras.Add(DBUtils.MakeInParam("ProjectID", SqlDbType.VarChar, db.ProjectID));
paras.Add(DBUtils.MakeInParam("ACTION", SqlDbType.Int, DataProviderAction.Create));
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Save_DB", paras);
db.DBID = ((SqlParameter)paras[0]).Value != DBNull.Value ? Convert.ToInt32(((SqlParameter)paras[0]).Value) : 0;
db.DBSerial = 0;
//}
//else
//{
// paras.Clear();
// paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, db.DBID));
// paras.Add(DBUtils.MakeInParam("DBName", SqlDbType.NVarChar, 40, db.DBName));
// paras.Add(DBUtils.MakeInParam("DBCode", SqlDbType.NVarChar, 40, db.DBCode));
// paras.Add(DBUtils.MakeInParam("ProjectID", SqlDbType.VarChar, db.ProjectID));
// paras.Add(DBUtils.MakeInParam("IsLog", SqlDbType.Bit, cbLog.Checked));
// paras.Add(DBUtils.MakeInParam("ACTION", SqlDbType.Int, DataProviderAction.Update));
// DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Save_DB", paras);
// if (cbLog.Checked)
// db.DBSerial += 1;
//}
# endregion Save DB
# region Save Table & Columns
PdmTable oTable = new PdmTable();
PdmColumn oColumn = new PdmColumn();
foreach (PdmTable pTable in pdm.Tables)
{
pTable.DBID = db.DBID;
oTable.OnInit();
// 判断该表是否已经存在
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, db.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode.ToLower()));
reader = DBUtils.ExecuteReader(conn, cmd, CommandType.StoredProcedure, "dbo.P_Get_TableByCode", paras);
if (reader.Read())
{
oTable.OnPopulate(reader);
}
reader.Close();
if ((pTable.TableCode.ToLower().IndexOf("_pmt_") >= 0) || (pTable.TableCode.ToLower().IndexOf("pm_") >= 0))
{
pTable.IsPmt = true;
}
// 保存表信息
paras.Clear();
paras.Add(DBUtils.MakeInParam("DBID", SqlDbType.Int, db.DBID));
paras.Add(DBUtils.MakeInParam("TableCode", SqlDbType.NVarChar, 40, pTable.TableCode));
paras.Add(DBUtils.MakeInParam("TableName", SqlDbType.NVarChar, 40, pTable.TableName));
paras.Add(DBUtils.MakeInParam("Comment", SqlDbType.NVarChar, 400, pTable.Comment));
paras.Add(DBUtils.MakeInParam("IsPmt", SqlDbType.Bit, pTable.IsPmt));
paras.Add(DBUtils.MakeInParam("IsLog", SqlDbType.Bit, cbLog.Checked));
if (String.IsNullOrEmpty(oTable.TableCode))
{
paras.Add(DBUtils.MakeInParam("ACTION", SqlDbType.Int, DataProviderAction.Create));
}
else
{
paras.Add(DBUtils.MakeInParam("ACTION", SqlDbType.Int, DataProviderAction.Update));
}
DBUtils.ExecuteNonQuery(conn, cmd, CommandType.StoredProcedure, "dbo.P_Save_Table", paras);
SqlBaseProvider.DeleteColumn(conn, cmd, pTable, cbLog.Checked);
foreach (PdmColumn pColumn in pTable.Columns)
{
pColumn.DBID = pTable.DBID;
pColumn.TableCode = pTable.TableCode;
SqlBaseProvider.SaveColumn(conn, cmd, pColumn);
}
if (String.IsNullOrEmpty(oTable.TableCode) || cbLog.Checked)
{
SqlBaseProvider.LogColumn(conn, cmd, pTable, true);
}
SqlBaseProvider.DeleteKey(conn, cmd, pTable, cbLog.Checked);
foreach (PdmKey pKey in pTable.Keys)
{
pKey.DBID = pTable.DBID;
pKey.TableCode = pTable.TableCode;
SqlBaseProvider.SaveKey(conn, cmd, pKey);
foreach (PdmColumn pColumn in pKey.Columns)
{
SqlBaseProvider.SaveKeyColumn(conn, cmd, pKey, pColumn);
}
}
if (String.IsNullOrEmpty(oTable.TableCode) || cbLog.Checked)
{
SqlBaseProvider.LogKey(conn, cmd, pTable, true);
}
SqlBaseProvider.DeleteIndex(conn, cmd, pTable);
foreach (PdmIndex pIndex in pTable.Indexs)
{
pIndex.DBID = pTable.DBID;
pIndex.TableCode = pTable.TableCode;
SqlBaseProvider.SaveIndex(conn, cmd, pIndex);
foreach (PdmColumn pColumn in pIndex.Columns)
{
SqlBaseProvider.SaveIndexColumn(conn, cmd, pIndex, pColumn);
}
}
if (String.IsNullOrEmpty(oTable.TableCode) || cbLog.Checked)
{
SqlBaseProvider.LogIndex(conn, cmd, pTable, true);
}
}
# endregion Save Table & Columns & Key Index
private void initForm()
{
CtrlHelper.SetDropDownList(ddlDB, SqlBaseProvider.GetDBForCombox(Convert.ToInt32(DataBaseType.MySql)), DropAddType.New, DropAddFlag.Select, String.Empty, "DBName,DBID");
ddlDB.SelectedValueChanged += new EventHandler(ddlDB_SelectedIndexChanged);
pTable = new PdmTable();
}
