private void 查看表结构ToolStripMenuItem_Click(object sender, EventArgs e)
{
string tableName = clbTable.SelectedItem as string;
string cmd = string.Format("SHOW COLUMNS FROM {0};", tableName);
DataTable dt = sql.ExecuteDataTable(cmd);
dt.TableName = tableName;
dgvForm dgv = new dgvForm(dt);
dgv.MdiParent = this;
dgv.Show();
}
public ColumnDesc[] GetColumnDesc(string tableName)
{
List <ColumnDesc> td = new List <ColumnDesc>();
DataTable tableDesc = sqlServer.ExecuteDataTable($@"
SELECT
syscolumns.name AS Field,
syscolumns.id,
syscolumns.isnullable AS 'Null',
systypes.name AS Type,
syscolumns.[length] AS Lenth,
ISNULL(
sys.extended_properties.
VALUE
,
''
) AS Comment,
CASE keyColumn.Column_name
WHEN syscolumns.name THEN
'1'
ELSE
'0'
END AS 'Key'
FROM
sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xusertype = systypes.xusertype
LEFT JOIN sys.identity_columns ON sys.identity_columns.object_id = syscolumns.id
AND sys.identity_columns.column_id = syscolumns.colid
LEFT JOIN sys.extended_properties ON sys.extended_properties.major_id = syscolumns.id
AND sys.extended_properties.minor_id = syscolumns.colid,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE keyColumn
WHERE
sysobjects.name = '{tableName}'
AND keyColumn.TABLE_NAME = sysobjects.name");
foreach (DataRow item in tableDesc.Rows)
{
ColumnDesc columnDesc = new ColumnDesc()
{
Comment = item["Comment"].ToString(),
Field = item["Field"].ToString(),
Key = item["Key"].ToString().ToLower() == "1",
Null = item["Null"].ToString().ToLower() == "1"
};
columnDesc.SetType(item["Type"].ToString());
td.Add(columnDesc);
}
return(td.ToArray());
}
public static List <DetectionDeduct> GetDetectionDeducts()
{
String sql = "SELECT detection_id, detection_max_scale,detection_cur_scale,detection_deduct_value FROM mdtbl_detection_deduct";
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
return((MdDataTableHelper <DetectionDeduct> .ConvertToModel(sql_helper.ExecuteDataTable(sql))).ToList <DetectionDeduct>());
}
public static List <User> GetUserCollection()
{
var depart_list = DepartmentManager.GetDepartmentCollection();
var role_list = RoleManager.GetRoleCollection();
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
string sql = "select * from mdtbl_user";
var dt = sql_helper.ExecuteDataTable(sql);
List <User> user_list = new List <User>();
foreach (DataRow row in dt.Rows)
{
User item = new User();
//item.UserId = row[0].ToString();
//item.Department = depart_list.Find((p) => p.ID.Equals(row[1].ToString()));
//item.Role = role_list.Find((p) => p.ID.Equals(row[2].ToString()));
//item.Name = row[3].ToString();
//item.LoginName = row[7].ToString();
//item.State = (UserState)Convert.ToInt32(row[9]);
user_list.Add(item);
}
return(user_list);
}
public static List <BridgeType> GetBridgeTypes()
{
string sql = "select * from mdtbl_bridge_type";
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
return((MdDataTableHelper <BridgeType> .ConvertToModel(
sql_helper.ExecuteDataTable(sql))).ToList <BridgeType>());
}
public static List <Project> GetProjectsByCon()
{
String sqlCon = "SELECT prj_id,client_id,prj_name,begintime,endtime,prj_describe,prj_contract," +
"prj_man,prj_status,check_man,check_date,prj_principal,prj_sign_date,prj_address FROM mdtbl_project";
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
return((MdDataTableHelper <Project> .ConvertToModel(sql_helper.ExecuteDataTable(sqlCon))).ToList <Project>());
}
/// <summary>
/// 获取所有子桥梁信息，只显示未注销桥梁信息，已注销的当删除
/// </summary>
/// <returns></returns>
public static List <ChildBridge> GetChildBridges()
{
// String sqlCon = "SELECT bridge_children_id,bri_id,type_id,bridge_children_name,bridge_children_discribe,bridge_pile FROM mdtbl_bridge_children";
String sql = "SELECT bridge_children_id, a.bri_id,type_id,bridge_children_name,bridge_children_discribe,bridge_pile FROM mdtbl_bridge_children a LEFT JOIN mdtbl_bridge_xzsb b on a.bri_id = b.bri_id where b.is_cancel = 1";
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
return((MdDataTableHelper <ChildBridge> .ConvertToModel(
sql_helper.ExecuteDataTable(sql))).ToList <ChildBridge>());
}
public ColumnDesc[] GetColumnDesc(string tableName)
{
List <ColumnDesc> td = new List <ColumnDesc>();
DataTable tableDesc = mySql.ExecuteDataTable($"show full fields from `{tableName}`");
foreach (DataRow item in tableDesc.Rows)
{
ColumnDesc columnDesc = new ColumnDesc()
{
Comment = item["Comment"].ToString(),
Field = item["Field"].ToString(),
Key = item["Key"].ToString().ToLower() == "pri",
Null = item["Null"].ToString().ToLower() == "yes"
};
columnDesc.SetType(item["Type"].ToString());
td.Add(columnDesc);
}
return(td.ToArray());
}
public static List <Role> GetRoleCollection()
{
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
string sql = "select * from mdtbl_role";
DataTable dt = sql_helper.ExecuteDataTable(sql);
List <Role> role_list = new List <Role>();
foreach (DataRow row in dt.Rows)
{
Role item = new Role()
{
RoleId = row[0].ToString(),
RoleName = row[1].ToString(),
RoleDescription = row[2].ToString()
};
role_list.Add(item);
}
return(role_list);
}
public static List <Department> GetDepartmentCollection()
{
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
string sql = "select * from mdtbl_department";
DataTable dt = sql_helper.ExecuteDataTable(sql);
List <Department> depart_list = new List <Department>();
//foreach (DataRow row in dt.Rows)
//{
// Department item = new Department()
// {
// ID = row[0].ToString(),
// CompanyID = row[1].ToString(),
// Name = row[2].ToString()
// };
// depart_list.Add(item);
//}
return(depart_list);
}
/// <summary>
/// 根据子桥梁ID或者桥梁名称进行查找
/// </summary>
/// <param name="bridgeChildrenId"></param>
/// <param name="bridgeChildrenName"></param>
/// <returns></returns>
public static List <ChildBridge> GetChildBridges(string name, string pileId)
{
// String sqlCon = "SELECT bridge_children_id,bri_id,type_id,bridge_children_name,bridge_children_discribe,bridge_pile FROM mdtbl_bridge_children where 1=1";
String sql = "SELECT bridge_children_id, a.bri_id,type_id,bridge_children_name, " +
"bridge_children_discribe,bridge_pile FROM mdtbl_bridge_children a LEFT JOIN " +
"mdtbl_bridge_xzsb b on a.bri_id = b.bri_id where b.is_cancel = 1 ";
string sql_filter = "";
//if (!string.IsNullOrWhiteSpace(id))
// sql_filter += string.Format("and bridge_children_id = '{0}' ", id);
if (!string.IsNullOrWhiteSpace(name))
{
sql_filter += string.Format("and bridge_children_name like '%{0}%' ", name);
}
if (!string.IsNullOrWhiteSpace(pileId))
{
sql_filter += string.Format("and bridge_pile like '%{0}%' ", pileId);
}
sql += sql_filter;
ISqlHelper sql_helper = SqlHelperFactory.GetDefaultSqlHelper();
return((MdDataTableHelper <ChildBridge> .ConvertToModel(
sql_helper.ExecuteDataTable(sql))).ToList <ChildBridge>());
}
private void cmboxDatabase_Click(object sender, EventArgs e)
{
string conn = string.Empty;
string sqlgetdb = string.Empty;
ISqlHelper sqlHelper = null;
if (string.IsNullOrEmpty(this.txtUserId.Text) || string.IsNullOrEmpty(this.txtPassword.Text) ||
string.IsNullOrEmpty(this.cmboxServer.Text))
{
this.lblTip.ForeColor = Color.Red;
this.lblTip.Text = "请输入连接字符串";
return;
}
sqlHelper = GetConnectionStr(0, ref conn, ref sqlgetdb);
if (ConnectString != conn)
{
ConnectString = string.Empty;
this.lblTip.ForeColor = Color.Indigo;
this.lblTip.Text = Data.Connecting;
this.cmboxDatabase.Items.Clear();
var th = new System.Threading.Thread(delegate()
{
if (sqlHelper.IsConnect(conn))
{
ConnectString = conn;
DataTable temp = sqlHelper.ExecuteDataTable(conn, CommandType.Text, sqlgetdb);
this.BeginInvoke(new MethodInvoker(delegate
{
this.lblTip.ForeColor = Color.Green;
this.lblTip.Text = Data.ConnectSuccess;
if (temp != null)
{
for (int i = 0; i < temp.Rows.Count; i++)
{
ComboboxItem item = new ComboboxItem();
item.Text = temp.Rows[i][0].ToString();
item.Value = temp.Rows[i][0].ToString();
this.cmboxDatabase.Items.Add(item);
}
if (this.cmboxDatabase.Items.Count > 0)
{
this.cmboxDatabase.SelectedItem = 0;
}
}
}));
}
else
{
this.BeginInvoke(new MethodInvoker(delegate
{
this.lblTip.ForeColor = Color.Red;
this.lblTip.Text = Data.ConnectionFail;
}));
}
});
th.IsBackground = true;
th.Start();
}
}