public SqlServerConfigInfo Get(int id)
{
SqlServerConfigInfo dbConfigInfo = null;
string sqlText = @" SELECT `Id`,
`ServerAddress`,
`DBName`,
`UserName`,
`Password`,
`UpdatedDate`,
`CreatedDate`,
`EquipmentNum`,
`PageSize`,
`MaxCapacity`,
`StoredType`
FROM `custom_db_config`
WHERE `Id` = @Id;";
MySqlParameter[] parameters =
{
new MySqlParameter("@Id", MySqlDbType.Int32)
{
Value = id
}
};
MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, parameters);
if (sqlDataReader.Read())
{
dbConfigInfo = new SqlServerConfigInfo();
ReadRecordData(sqlDataReader, dbConfigInfo);
}
sqlDataReader.Close();
return(dbConfigInfo);
}
public void CheckValid(SqlServerConfigInfo dbConfigInfo)
{
if (string.IsNullOrEmpty(dbConfigInfo.EquipmentNum))
{
throw new CustomException("请输入设备编号", ExceptionType.Warn);
}
if (string.IsNullOrEmpty(dbConfigInfo.ServerAddress))
{
throw new CustomException("请输入数据库服务器地址", ExceptionType.Warn);
}
if (string.IsNullOrEmpty(dbConfigInfo.DBName))
{
throw new CustomException("请输入数据库名称", ExceptionType.Warn);
}
if (string.IsNullOrEmpty(dbConfigInfo.UserName))
{
throw new CustomException("请输入数据库登录名", ExceptionType.Warn);
}
if (string.IsNullOrEmpty(dbConfigInfo.Password))
{
throw new CustomException("请输入数据库登录密码", ExceptionType.Warn);
}
}
private void DgvGrid_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.RowIndex < 0)
{
return;
}
int id = Convert.ToInt32(DgvGrid["ColAction", e.RowIndex].Value.ToString()); // 获取所要修改关联对象的主键。
//用户单击DataGridView“操作”列中的“修改”按钮。
if (DBConfigDataGridViewActionButtonCell.IsModifyButtonClick(sender, e))
{
SqlServerConfigInfo dbConfigInfo = SqlServerConfigInfoBLL.CreateInstance().Get(id);
FormMain.LoadNewControl(SqlServerConfigEdit.BindJobDetail(dbConfigInfo)); // 载入该模块的修改信息界面至主窗体显示。
}
//用户单击DataGridView“操作”列中的“删除”按钮。
if (DBConfigDataGridViewActionButtonCell.IsDeleteButtonClick(sender, e))
{
DialogResult dialogResult = FormSysMessage.ShowMessage("确定要删除该项吗？");
if (dialogResult == DialogResult.OK)
{
SqlServerConfigInfoBLL.CreateInstance().Delete(id);
BindDataGrid();
}
}
}
public PageData GetPageList(int pageSize, int curPage, string dbName = "")
{
string sqlWhere = "";
List <MySqlParameter> listParms = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(dbName))
{
sqlWhere = " AND `DBName` LIKE @DBName";
listParms.Add(new MySqlParameter("@DBName", MySqlDbType.VarChar, 45)
{
Value = "%" + dbName + "%"
});
}
int recordsTotal = MySqlDbHelper.ExecuteScalar <int>("SELECT COUNT(*) FROM custom_db_config WHERE `ServerState`=" + (byte)ServerStateEnum.Enabled + sqlWhere, listParms.ToArray());
string sqlText = @" SELECT `Id`,
`ServerAddress`,
`DBName`,
`UserName`,
`Password`,
`UpdatedDate`,
`CreatedDate`,
`EquipmentNum`,
`PageSize`,
`MaxCapacity`,
`StoredType`,
`DBType`,
`ServerState`,
`AuthenticatedType`
FROM `custom_db_config`
WHERE `ServerState`=" + (byte)ServerStateEnum.Enabled
+ sqlWhere
+ " ORDER BY Id DESC LIMIT " + (curPage - 1) * pageSize + "," + pageSize;
List <SqlServerConfigInfo> list = new List <SqlServerConfigInfo>();
MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, listParms.ToArray());
PageData pageData = new PageData();
pageData.PageSize = pageSize;
pageData.CurPage = curPage;
pageData.RecordCount = Math.Max(1, recordsTotal);
if (pageData.RecordCount > 0)
{
pageData.PageCount = Convert.ToInt32(Math.Ceiling((double)pageData.RecordCount / (double)pageSize));
}
while (sqlDataReader.Read())
{
SqlServerConfigInfo dbConfigInfo = new SqlServerConfigInfo();
ReadRecordData(sqlDataReader, dbConfigInfo);
list.Add(dbConfigInfo);
}
sqlDataReader.Close();
pageData.PageList = list;
return(pageData);
}
public static SqlServerConfigEdit BindJobDetail(SqlServerConfigInfo dbConfigInfo)
{
if (instance == null)
{
instance = new SqlServerConfigEdit();
}
instance.dbConfigInfo = dbConfigInfo; // 创建新的关联对象，可以在“数据实体层”中指定对象的默认值。
instance.BindObjectToForm(); // 每次返回该控件的实例前，都将关联对象的默认值，绑定至界面控件进行显示。
return(instance);
}
void ReadRecordData(IDataReader dataReader, SqlServerConfigInfo dbConfigInfo)
{
if (dataReader["Id"] != DBNull.Value)
{
dbConfigInfo.Id = Convert.ToInt32(dataReader["Id"]);
}
if (dataReader["ServerAddress"] != DBNull.Value)
{
dbConfigInfo.ServerAddress = Convert.ToString(dataReader["ServerAddress"]);
}
if (dataReader["DBName"] != DBNull.Value)
{
dbConfigInfo.DBName = Convert.ToString(dataReader["DBName"]);
}
if (dataReader["UserName"] != DBNull.Value)
{
dbConfigInfo.UserName = Convert.ToString(dataReader["UserName"]);
}
if (dataReader["Password"] != DBNull.Value)
{
dbConfigInfo.Password = Convert.ToString(dataReader["Password"]);
}
if (dataReader["UpdatedDate"] != DBNull.Value)
{
dbConfigInfo.UpdatedDate = Convert.ToDateTime(dataReader["UpdatedDate"]);
}
if (dataReader["CreatedDate"] != DBNull.Value)
{
dbConfigInfo.CreatedDate = Convert.ToDateTime(dataReader["CreatedDate"]);
}
if (dataReader["EquipmentNum"] != DBNull.Value)
{
dbConfigInfo.EquipmentNum = Convert.ToString(dataReader["EquipmentNum"]);
}
if (dataReader["StoredType"] != DBNull.Value)
{
dbConfigInfo.StoredType = Convert.ToByte(dataReader["StoredType"]);
}
if (dataReader["PageSize"] != DBNull.Value)
{
dbConfigInfo.PageSize = Convert.ToInt32(dataReader["PageSize"]);
}
if (dataReader["MaxCapacity"] != DBNull.Value)
{
dbConfigInfo.MaxCapacity = Convert.ToInt32(dataReader["MaxCapacity"]);
}
}
public int Update(SqlServerConfigInfo dbConfigInfo)
{
CheckValid(dbConfigInfo);
return(_dal.Update(dbConfigInfo));
}
public int Insert(SqlServerConfigInfo dbConfigInfo)
{
CheckValid(dbConfigInfo);
return(_dal.Insert(dbConfigInfo));
}
public int Insert(SqlServerConfigInfo dbConfigInfo)
{
dbConfigInfo.UpdatedDate = DateTime.Now;
dbConfigInfo.CreatedDate = DateTime.Now;
string sqlText = @"INSERT INTO `custom_db_config`
(`Id`,
`ServerAddress`,
`DBName`,
`UserName`,
`Password`,
`UpdatedDate`,
`CreatedDate`,
`EquipmentNum`,
`PageSize`,
`MaxCapacity`,
`StoredType`,
`DBType`,
`ServerState`,
`AuthenticatedType`)
VALUES
(@Id,
@ServerAddress,
@DBName,
@UserName,
@Password,
@UpdatedDate,
@CreatedDate,
@EquipmentNum,
@PageSize,
@MaxCapacity,
@StoredType,
@DBType,
@ServerState,
@AuthenticatedType); SELECT LAST_INSERT_ID();";
MySqlParameter[] parameters =
{
new MySqlParameter("@Id", MySqlDbType.Int32)
{
Value = dbConfigInfo.Id
},
new MySqlParameter("@DBName", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.DBName
},
new MySqlParameter("@ServerAddress", MySqlDbType.VarChar, 30)
{
Value = dbConfigInfo.ServerAddress
},
new MySqlParameter("@UserName", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.UserName
},
new MySqlParameter("@Password", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.Password
},
new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime)
{
Value = dbConfigInfo.UpdatedDate
},
new MySqlParameter("@CreatedDate", MySqlDbType.DateTime)
{
Value = dbConfigInfo.CreatedDate
},
new MySqlParameter("@EquipmentNum", MySqlDbType.VarChar, 25)
{
Value = dbConfigInfo.EquipmentNum
},
new MySqlParameter("@PageSize", MySqlDbType.Int32)
{
Value = dbConfigInfo.PageSize
},
new MySqlParameter("@MaxCapacity", MySqlDbType.Int32)
{
Value = dbConfigInfo.MaxCapacity
},
new MySqlParameter("@StoredType", MySqlDbType.Byte)
{
Value = dbConfigInfo.StoredType
},
new MySqlParameter("@DBType", MySqlDbType.Byte)
{
Value = dbConfigInfo.DBType
},
new MySqlParameter("@ServerState", MySqlDbType.Byte)
{
Value = dbConfigInfo.ServerState
},
new MySqlParameter("@AuthenticatedType", MySqlDbType.Byte)
{
Value = dbConfigInfo.AuthenticatedType
}
};
return(MySqlDbHelper.ExecuteScalar <int>(sqlText, parameters));
}
public int Update(SqlServerConfigInfo dbConfigInfo)
{
dbConfigInfo.UpdatedDate = DateTime.Now;
string sqlText = @"UPDATE `custom_db_config`
SET
`ServerAddress` = @ServerAddress,
`DBName` = @DBName,
`UserName` = @UserName,
`Password` = @Password,
`UpdatedDate` = @UpdatedDate,
`CreatedDate` = @CreatedDate,
`EquipmentNum`=@EquipmentNum,
`PageSize`=@PageSize,
`MaxCapacity`=@MaxCapacity,
`StoredType`=@StoredType,
`DBType`=@DBType,
`ServerState`=@ServerState,
`AuthenticatedType`=@AuthenticatedType
WHERE `Id` = @Id;";
MySqlParameter[] parameters =
{
new MySqlParameter("@Id", MySqlDbType.Int32)
{
Value = dbConfigInfo.Id
},
new MySqlParameter("@DBName", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.DBName
},
new MySqlParameter("@ServerAddress", MySqlDbType.VarChar, 30)
{
Value = dbConfigInfo.ServerAddress
},
new MySqlParameter("@UserName", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.UserName
},
new MySqlParameter("@Password", MySqlDbType.VarChar, 45)
{
Value = dbConfigInfo.Password
},
new MySqlParameter("@UpdatedDate", MySqlDbType.DateTime)
{
Value = dbConfigInfo.UpdatedDate
},
new MySqlParameter("@CreatedDate", MySqlDbType.DateTime)
{
Value = dbConfigInfo.CreatedDate
},
new MySqlParameter("@EquipmentNum", MySqlDbType.VarChar, 25)
{
Value = dbConfigInfo.EquipmentNum
},
new MySqlParameter("@PageSize", MySqlDbType.Int32)
{
Value = dbConfigInfo.PageSize
},
new MySqlParameter("@MaxCapacity", MySqlDbType.Int32)
{
Value = dbConfigInfo.MaxCapacity
},
new MySqlParameter("@StoredType", MySqlDbType.Byte)
{
Value = dbConfigInfo.StoredType
},
new MySqlParameter("@DBType", MySqlDbType.Byte)
{
Value = dbConfigInfo.DBType
},
new MySqlParameter("@ServerState", MySqlDbType.Byte)
{
Value = dbConfigInfo.ServerState
},
new MySqlParameter("@AuthenticatedType", MySqlDbType.Byte)
{
Value = dbConfigInfo.AuthenticatedType
}
};
return(MySqlDbHelper.ExecuteNonQuery(sqlText, parameters));
}