/// <summary>
/// 根据表名获取字段
/// </summary>
private void GetFieId(string tableName)
{
DataTable table = new DataTable();
List <Fieid> fieids = new List <Fieid>();
List <string> keyName = new List <string>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlDataAdapter adpter = new SqlDataAdapter("Select * from [" + tableName + "] where 1!=1", conn);
//添加key
adpter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adpter.Fill(table);
conn.Close();
}
foreach (DataColumn dc in table.PrimaryKey)
{
keyName.Add(dc.ColumnName);
}
foreach (DataColumn dc in table.Columns)
{
Fieid fieid = new Fieid();
fieid.Fieid_Length = dc.MaxLength;
fieid.Fieid_Name = dc.ColumnName;
fieid.Fieid_Type = dc.DataType.Name;
fieid.AllowDBNull = dc.AllowDBNull;
if (keyName.Where(p => p == dc.ColumnName).FirstOrDefault() != null)
{
fieid.PrimaryKey = true;
}
else
{
fieid.PrimaryKey = false;
}
fieids.Add(fieid);
}
FieidDic.Add(tableName, fieids);
}
/// <summary>
/// 根据表名获取字段
/// </summary>
private void GetFieId(string tableName)
{
if (tableName == "ICChargeRecord")
{
tableName = tableName;
}
DataTable table = new DataTable();
List <Fieid> fieids = new List <Fieid>();
List <string> keyName = new List <string>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlDataAdapter adpter = new SqlDataAdapter("Select * from " + tableName + " where 1!=1", conn);
//添加key
adpter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adpter.Fill(table);
conn.Close();
}
foreach (DataColumn dc in table.PrimaryKey)
{
keyName.Add(dc.ColumnName);
}
string str = $@"SELECT
col.name AS field,
isnull(prop.[value],'') AS remark
FROM
sys.columns col
left join sys.extended_properties prop
on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
WHERE
col.object_id =
(SELECT object_id FROM sys.tables WHERE name = '{tableName}')";
var list = SqlHelper.Instance.ExecuteGetDt <Field_Remark>(str, new Dictionary <string, string>());
foreach (DataColumn dc in table.Columns)
{
Fieid fieid = new Fieid();
fieid.Fieid_Length = dc.MaxLength;
fieid.Fieid_Name = dc.ColumnName;
if (list.Where(p => p.field == dc.ColumnName).Count() > 0)
{
fieid.remark = list.Where(p => p.field == dc.ColumnName).First().remark;
}
fieid.Fieid_Type = dc.DataType.Name;
fieid.AllowDBNull = dc.AllowDBNull;
if (keyName.Where(p => p == dc.ColumnName).FirstOrDefault() != null)
{
fieid.PrimaryKey = true;
}
else
{
fieid.PrimaryKey = false;
}
fieids.Add(fieid);
}
FieidDic.Add(tableName, fieids);
}
/// <summary>
/// 返回请求数据 请求格式: GET:
///
///
/// 获取 某张表的 的信息
/// http://localhost:55039/jcservice/getdbinfo?tablename
///
///
///
/// 获取 第page张表 至 第pagesize张表 的所有信息
/// http://localhost:55039/jcservice/getdbinfo?tablename=&page=1&pagesize=2
///
///
/// 获取 所有表 的信息
/// http://localhost:55039/jcservice/getdbinfo
///
/// </summary>
/// <returns></returns>
//GET api/User/?username=xx
public WebApi GetInfoByTableName(string tablename, int page, int pagesize)
{
//获取数据库中表的数据
HwdbapiBLL hwbll = new HwdbapiBLL();
Hwdbapi hw = new Hwdbapi();
List <Hwdbapi> list = new List <Hwdbapi>();
DataTable dt = new DataTable();
List <Models.GetInfo> infoResList = new List <Models.GetInfo>();
// 处理过滤条件
if (tablename == null && page == 0 && pagesize == 0)
{
// 获取表中所有表的数据
dt = hwbll.SelectAll();
}
else if (tablename == null)
{
//获取表中部分表的数据
dt = hwbll.SelectPart(page, pagesize);
}
else
{
//获取表中单个表的数据
dt = hwbll.SelectSingle(tablename);
}
var temp = DataConvert <hwdbapiEntity> .ToList(dt);
#region 对表做处理
try
{
for (int i = 0; i < temp.Count;)
{
Models.GetInfo tt = new Models.GetInfo();
if (tt.TableName != temp[i].EnTableName)
{
try
{
List <Fieid> fieidlist = new List <Fieid>();
tt.TableName = temp[i].EnTableName;
for (; tt.TableName == temp[i].EnTableName; i++)
{
Fieid fieid = new Fieid();
fieid.Fieidname = temp[i].Field;
fieid.Fieidtype = temp[i].Type;
fieid.Fieidmean = "null";
fieidlist.Add(fieid);
}
tt.Field = fieidlist;
infoResList.Add(tt);
}
catch (System.ArgumentOutOfRangeException e)
{
//下标越界,表示当前表结束
break;
}
}
}
}catch (System.NullReferenceException e)
{
//数据为空
infoResList = null;
//return
}
#endregion
WebApi _webApi = new WebApi
{
Code = 1,
Msg = "Request Success!",
Data = infoResList
};
return(_webApi);
}