oracle获取表和列的备注
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.Serialization.Json;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Common;
using DevExpress.Utils.Text.Internal;
using DevExpress.Web.ASPxTabControl;
using Framework;
using NPOI.SS.Formula.Functions;
using WebApp.Common;
using Contract.Domain;
using System.Collections;
using Match = System.Text.RegularExpressions.Match;
using System.Runtime.Serialization;
namespace HraWeb
{
[DataContract(Namespace = "Contract.Domain")]
public class UserTabColumn
{
[DataMember]
public virtual string ColumnName { get; set; }
[DataMember]
public virtual string DataType { get; set; }
[DataMember]
public virtual string Comments { get; set; }
}
public partial class BasInstDefaultValueConfigManage : EntityDetail<BasInstDefaultValue>
{
//初始页面控件
protected override void InitPage(string id)
{
// BindEntityDrop(txt_TableName_, "all_tables",false);
base.InitPage(id);
}
protected void BindEntityDrop()
{
//txt_InstrumentTypeId_.DataTextField = "InstrumentTypeName";
//txt_InstrumentTypeId_.DataValueField = "Id";
//txt_InstrumentTypeId_.DataSource =
// Holworth.Utility.Utility.ListToT<BasInstrumentType>(Dao.FindList(new QueryInfo("BasInstrumentType")))
// .ToList();
//txt_InstrumentTypeId_.DataBind();
}
protected override BasInstDefaultValue LoadData(string id)
{
var obj = base.LoadData(id);
return obj;
}
protected override void Page_Load(object sender, EventArgs e)
{
if (Request["_method"] == "Table2Drop")
{
string tableName = Request["entityType"] ?? "";
if (!string.IsNullOrEmpty(tableName))
Table2Drop(tableName);
}
else if (Request["_method"] == "getTable")
{
GetTable();
}
else if (Request["_method"] == "getModel")
{
getModel();
}
// base.svc = Psvc;
base.Page_Load(sender, e);
}
public void getModel()
{
string tname = Request["_tname"] ?? "";
if (!string.IsNullOrEmpty(tname))
{
QueryInfo info=new QueryInfo("BasInstDefaultValue");
}
}
public void GetTable()
{
string tname = Request["tname"];
QueryInfo colTypeInfo = new QueryInfo();
colTypeInfo.CustomSQL = string.Format(@"select a.column_name column_name,a.DATA_TYPE DATA_TYPE,b.comments comments from User_Tab_Columns a inner join user_col_comments b on a.table_name=b.table_name
and lower(a.column_name) not in(
'sequence',
'is_deleted',
'py',
'create_uid',
'create_uname',
'create_oid',
'create_pid',
'create_date',
'last_mod_uid',
'last_mod_uname',
'last_mod_oid',
'last_mod_pid',
'last_mod_datetime')
and a.column_name=b.column_name where a.TABLE_NAME=upper(trim('{0}'))", tname.ToUpper());
List<UserTabColumn> columnDic = Dao.ExcuteDataSet(colTypeInfo).Tables[0].ToList<UserTabColumn>();
var s = Newtonsoft.Json.JsonConvert.SerializeObject(columnDic);
HttpContext.Current.Response.Write(s);
HttpContext.Current.Response.End();
}
public void Table2Drop(string tableName)
{
QueryInfo tabInfo = new QueryInfo();
tabInfo.CustomSQL = @"select a.TABLE_NAME TABLE_NAME,b.COMMENTS COMMENTS
from user_tables a,user_tab_comments b
WHERE a.TABLE_NAME=b.TABLE_NAME
";
//
var keyword = Request["keyword"] ?? string.Empty;
if (!string.IsNullOrEmpty(keyword))
{
tabInfo.CustomSQL += string.Format(" and b.{0} like '%{1}%'", "COMMENTS", keyword);
}
//tabInfo.PageSize = int.Parse(HttpContext.Current.Request["rows"]);
//int pageIndex = int.Parse(HttpContext.Current.Request["page"]);
//tabInfo.StartRecord = (pageIndex - 1) * tabInfo.PageSize;
//tabInfo.TotalCount = 1;
//
DataTable dt = Dao.ExcuteDataSet(tabInfo).Tables[0];
var s = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
HttpContext.Current.Response.Write(s);
HttpContext.Current.Response.End();
}
protected Hashtable SetInfo()
{
Hashtable aa = new Hashtable();
aa.Clear();
foreach (string key in Request.Params.Keys)
{
if (regEx.IsMatch(key))
{
Match m = regEx.Match(key);
string g = m.Groups["TAG"].Value;
if (string.IsNullOrEmpty(Request.Params[key]))
{
continue;
}
if (string.IsNullOrEmpty(g))
{
if (!aa.Contains(m.Groups["PROP"].Value))
{
aa.Add(m.Groups["PROP"].Value, Request.Params[key]);
}
}
else
{
if (!aa.Contains(m.Groups["PROP"].Value + "_" + g))
{
aa.Add(m.Groups["PROP"].Value + "_" + g, Request.Params[key]);
}
}
}
}
return aa;
}
private static string regexCtlValObj = @"\w{3,5}?_(?<PROP>\w*)_(?<TAG>(\w\d{1,2})*)$";
public static System.Text.RegularExpressions.Regex regEx = new Regex(regexCtlValObj, RegexOptions.IgnoreCase);
private Contract.IService.IRskValuationModelConfigService psvc;
/// <summary>
/// 初始化
/// </summary>
Contract.IService.IRskValuationModelConfigService Psvc
{
get
{
if (psvc == null)
{
psvc = ctx.GetObject("RskValuationModelConfigService") as Contract.IService.IRskValuationModelConfigService;
}
return psvc;
}
}
}
}
