/// <summary>取得数据库名称</summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
GenericSqlCommand command = new GenericSqlCommand(connectionString, "SQLite");
string commandText = string.Format("SHOW FULL FIELDS FROM {1} FROM {0}", databaseName, tableName);
var table = command.ExecuteQueryForDataTable(commandText);
foreach (DataRow row in table.Rows)
{
DataColumnSchema item = new DataColumnSchema();
// 名称
item.Name = row["Field"].ToString();
// 数据类型
item.Type = SetDataType(row["Type"].ToString());
// 原生数据类型
item.NativeType = row["Type"].ToString();
// 是否允许为空
item.Nullable = (row["Null"].ToString() == "NO") ? false : true;
// 默认值
item.DefaultValue = row["Default"].ToString();
// 注释
item.Description = row["Comment"].ToString();
list.Add(item);
}
return(list);
}
/// <summary>
/// 取得数据库名称
/// </summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetPrimaryKeyColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
GenericSqlCommand command = new GenericSqlCommand(connectionString, "OracleClient");
string commandText = string.Format(@"
select cu.*
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = 'P'
and au.table_name = '{0}' ", tableName);
var table = command.ExecuteQueryForDataTable(commandText);
foreach (DataRow row in table.Rows)
{
DataColumnSchema item = new DataColumnSchema();
item.Name = row["column_name"].ToString();
list.Add(item);
}
return(list);
}
/// <summary>
/// 取得数据库名称
/// </summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetPrimaryKeyColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"
SELECT
cols.COLUMN_NAME AS Name
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE cols
WHERE
cols.TABLE_CATALOG = @DatabaseName
AND cols.TABLE_SCHEMA = @OwnerName
AND cols.TABLE_NAME = @TableName
ORDER BY
cols.ORDINAL_POSITION;
";
SqlParameter param;
param = new SqlParameter("@DatabaseName", SqlDbType.NVarChar);
param.Value = databaseName;
cmd.Parameters.Add(param);
param = new SqlParameter("@OwnerName", SqlDbType.NVarChar);
param.Value = ownerName;
cmd.Parameters.Add(param);
param = new SqlParameter("@TableName", SqlDbType.NVarChar);
param.Value = tableName;
cmd.Parameters.Add(param);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DataColumnSchema item = new DataColumnSchema();
item.Name = (dr["Name"] == DBNull.Value) ? string.Empty : (string)dr["Name"];
list.Add(item);
}
}
conn.Close();
}
return(list);
}
/// <summary>
/// 取得数据库名称
/// </summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetNoPrimaryKeyColumns(DataTableSchema table)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
for (int i = 0; i < table.Columns.Count; i++)
{
if (!table.Columns[i].PrimaryKey)
{
list.Add(table.Columns[i]);
}
}
return(list);
}
/// <summary>
/// 取得数据库名称
/// </summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
GenericSqlCommand command = new GenericSqlCommand(connectionString, "OracleClient");
string commandText = string.Format(@"
select column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable,
data_default
from user_tab_columns
where table_name = '{0}'
order by column_id", tableName);
var table = command.ExecuteQueryForDataTable(commandText);
foreach (DataRow row in table.Rows)
{
DataColumnSchema item = new DataColumnSchema();
item.Name = row["column_name"].ToString().ToLower();
item.Type = SetDataType(row["data_type"].ToString());
item.Nullable = (row["nullable"].ToString() == "N") ? false : true;
switch (item.Type)
{
case DbType.String:
item.Length = (row["data_length"] == DBNull.Value) ? 0 : Convert.ToInt32(row["data_length"].ToString());
break;
case DbType.Decimal:
// item.Precision = (dr["Precision"] == DBNull.Value) ? (byte)0 : (byte)dr["Precision"];
// item.Scale = (dr["Scale"] == DBNull.Value) ? 0 : (int)dr["Scale"];
break;
default:
break;
}
list.Add(item);
}
return(list);
}
/// <summary>查询数据库中表的外键字段信息</summary>
/// <param name="databaseName">数据库</param>
/// <param name="ownerName">所有者</param>
/// <param name="tableName">表名</param>
/// <returns>外键字段信息集合</returns>
public DataColumnSchemaCollection GetForeignKeyColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
GenericSqlCommand command = new GenericSqlCommand(connectionString, "SQLite");
string commandText = string.Format("SHOW FULL FIELDS FROM {1} FROM {0} WHERE `Key`='MUL'", databaseName, tableName);
var table = command.ExecuteQueryForDataTable(commandText);
foreach (DataRow row in table.Rows)
{
DataColumnSchema item = new DataColumnSchema();
item.Name = row["Field"].ToString();
list.Add(item);
}
return(list);
}
/// <summary>
/// 取得数据库名称
/// </summary>
/// <param name="tableName">数据表</param>
/// <returns>数据库名称</returns>
public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
{
DataColumnSchemaCollection list = new DataColumnSchemaCollection();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"
SELECT
cols.COLUMN_NAME AS Name,
CASE
WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
ELSE cols.DATA_TYPE
END AS Type,
cols.DATA_TYPE AS DataType,
CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int) AS Length,
cols.NUMERIC_PRECISION AS Precision,
cols.NUMERIC_SCALE AS Scale,
cols.IS_NULLABLE AS IsNull,
cols.COLUMN_DEFAULT,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsIdentity') AS IS_IDENTITY,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsRowGuidCol') AS IS_ROW_GUID_COL,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC,
CASE
WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0)
then CONVERT(nvarchar(40), ident_seed(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'))
else null end AS IDENTITY_SEED,
CASE
WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0)
then CONVERT(nvarchar(40), ident_incr(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']')) else null
end AS IDENTITY_INCREMENT,
NULL AS COMPUTED_DEFINITION,
NULL AS [collation]
FROM
INFORMATION_SCHEMA.COLUMNS cols
WHERE
cols.TABLE_CATALOG = @DatabaseName
AND cols.TABLE_SCHEMA = @OwnerName
AND cols.TABLE_NAME = @TableName
ORDER BY
cols.ORDINAL_POSITION;
";
SqlParameter param;
param = new SqlParameter("@DatabaseName", SqlDbType.NVarChar);
param.Value = databaseName;
cmd.Parameters.Add(param);
param = new SqlParameter("@OwnerName", SqlDbType.NVarChar);
param.Value = ownerName;
cmd.Parameters.Add(param);
param = new SqlParameter("@TableName", SqlDbType.NVarChar);
param.Value = tableName;
cmd.Parameters.Add(param);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DataColumnSchema item = new DataColumnSchema();
item.Name = (dr["Name"] == DBNull.Value) ? string.Empty : (string)dr["Name"];
item.Type = SetDataType((string)dr["Type"]);
item.Nullable = ((string)dr["IsNull"] == "NO") ? false : true;
switch (item.Type)
{
case DbType.String:
item.Length = (dr["Length"] == DBNull.Value) ? 0 : (int)dr["Length"];
break;
case DbType.Decimal:
item.Precision = (dr["Precision"] == DBNull.Value) ? (byte)0 : (byte)dr["Precision"];
item.Scale = (dr["Scale"] == DBNull.Value) ? 0 : (int)dr["Scale"];
break;
default:
break;
}
list.Add(item);
}
}
conn.Close();
}
return(list);
}
