public static List<DBTableInfo> GetTableInfo(string tableName)
{
List<DBTableInfo> list = new List<DBTableInfo>();
try
{
string sql = string.Format("PRAGMA table_info({0});",tableName);
SQLiteCommand command = new SQLiteCommand(sql, LocalSqllite.m_sqlLiteConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DBTableInfo data = new DBTableInfo();
data.m_cid = reader["cid"].ToString();
data.m_columnName = reader["name"].ToString();
data.m_columnType = reader["type"].ToString();
data.m_defaultValue = reader["notnull"].ToString();
data.m_notNull = reader["dflt_value"].ToString();
data.m_primaryKey = reader["pk"].ToString();
list.Add(data);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return list;
}
//private static string _sqlTables = "SELECT [name],[xtype] FROM [sysobjects] Where [xtype] in ('U','V') and [name] not in('dtproperties','sysdiagrams') ORDER BY [xtype],[crdate] desc";
/// <summary>
/// 获取所有用户表
/// </summary>
/// <returns></returns>
public virtual List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
{
ParamList lstParam = new ParamList();
//DataBaseOperate oper = info.DefaultOperate;
List <DBTableInfo> lstName = new List <DBTableInfo>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
DBTableInfo tableInfo = new DBTableInfo();
if (row.IsNull("TABLE_NAME"))
{
continue;
}
tableInfo.Name = row["TABLE_NAME"].ToString();
tableInfo.IsView = false;
lstName.Add(tableInfo);
}
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "VIEW" });
foreach (DataRow row in dt.Rows)
{
DBTableInfo tableInfo = new DBTableInfo();
if (row.IsNull("TABLE_NAME"))
{
continue;
}
tableInfo.Name = row["TABLE_NAME"].ToString();
tableInfo.IsView = true;
lstName.Add(tableInfo);
}
}
return(lstName);
}
private static string ToSelect(DBTableInfo tb)
{
StringBuilder cBuilder = new StringBuilder();
cBuilder.AppendLine("--【Select】------------------------------------------");
cBuilder.AppendLine("SELECT");
var idx = 0;
foreach (DBColInfo c in tb.Cols)
{
idx++;
if (string.IsNullOrWhiteSpace(c.ColumnName))
{
continue;
}
var scriptEnd = idx == tb.Cols.Count ? "" : ",";
cBuilder.AppendLine(string.Format(" a.[{0}]{1}", c.ColumnName, scriptEnd));
}
cBuilder.AppendLine(string.Format("FROM dbo.[{0}] as a", tb.TableName));
cBuilder.AppendLine("");
return(cBuilder.ToString());
}
/// <summary>
/// 填充是否存在的列
/// </summary>
private void RefreashExistsInfo()
{
foreach (DataGridViewRow dr in gvTables.Rows)
{
string exists = "未生成";
DBTableInfo info = dr.DataBoundItem as DBTableInfo;
if (info != null)
{
string fileName = DBEntityInfo.GetEntityRealFileName(info, _dbInfo, DesignerInfo);
try
{
if (File.Exists(fileName))
{
exists = "已生成";
dr.DefaultCellStyle.ForeColor = Color.Red;
}
}
catch { }
}
dr.Cells["ColExists"].Value = exists;
}
}
protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
{
List <string> values = new List <string>();
List <string> fields = new List <string>();
foreach (DBTableColumnInfo colInf in selectedCols.Where(x => !x.Exclude))
{
values.Add(PrefixInputParameter + colInf.ColumnName);
fields.Add(Wrap(colInf.ColumnName));
}
var sb = new StringBuilder();
sb.Append($"\tINSERT INTO {tableInfo.FullTableName}");
if (values.Any())
{
sb.AppendLine($" ({string.Join(", ", fields)})");
}
if (tableInfo.Columns.Any(x => x.IsAutogeneratedGuid))
{
IEnumerable <string> autoGuids = tableInfo.Columns.Where(x => x.IsAutogeneratedGuid)
.Select(x => $"inserted.[{x.ColumnName}]");
sb.AppendLine($"\tOUTPUT {string.Join(", ", autoGuids)}");
}
if (values.Any())
{
sb.Append($"\tVALUES ({string.Join(", ", values)})");
}
else
{
sb.AppendLine();
sb.Append("\tDEFAULT VALUES");
}
if (tableInfo.Columns.Any(x => x.IsIdentity))
{
sb.AppendLine();
sb.Append("\tSELECT @@IDENTITY");
}
return(sb.ToString());
}
public StoredProcedure GenerateSingleSP(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols,
List <DBTableColumnInfo> whereConditionCols)
{
var name = GetSpName(tableInfo.TableName, whereConditionCols);
var statementBuilder = new StringBuilder();
statementBuilder.AppendLine($"CREATE PROCEDURE [{tableInfo.Schema}].[{name}]");
//GenerateErrorNumberOutParameter(statementBuilder);
var inputs = GenerateInputParameters(selectedCols);
var wheres = GenerateWhereParameters(whereConditionCols);
if (!string.IsNullOrWhiteSpace(inputs))
{
statementBuilder.Append(inputs);
if (!string.IsNullOrWhiteSpace(wheres))
{
statementBuilder.Append(',');
}
statementBuilder.AppendLine();
}
if (!string.IsNullOrWhiteSpace(wheres))
{
statementBuilder.AppendLine(wheres);
}
statementBuilder.AppendLine("AS");
//GenerateStartTryBlock(statementBuilder);
statementBuilder.AppendLine(GenerateStatement(tableInfo, selectedCols, whereConditionCols));
//GenerateEndTryBlock(statementBuilder);
//GenerateCatchBlock(statementBuilder);
var res = new StoredProcedure
{
Name = name,
DropScript = GenerateDropScript(name),
Script = statementBuilder.ToString()
};
return(res);
}
protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
{
string selectFieldsStr;
if (selectedCols.Count == tableInfo.Columns.Count)
{
selectFieldsStr = "*";
}
else
{
List <string> fields = new List <string>();
foreach (DBTableColumnInfo colInf in selectedCols.Where(x => !x.Exclude))
{
fields.Add(Wrap(colInf.ColumnName));
}
selectFieldsStr = string.Join(", ", fields);
}
var sb = new StringBuilder();
sb.AppendLine($"\tSELECT {selectFieldsStr} FROM {tableInfo.FullTableName}{Environment.NewLine}{GenerateWhereStatement(whereConditionCols)}");
return(sb.ToString());
}
private static string ToTable(string server, string db, DBTableInfo tb)
{
StringBuilder cBuilder = new StringBuilder();
cBuilder.AppendLine("--【表结构】------------------------------------------");
cBuilder.AppendLine("CREATE TABLE [dbo].[" + tb.TableName + "](");
var idx = 0;
var tableDao = new GeneralDAO.TableDAO();
var Indexs = tableDao.GetIndexs(server, db, tb.TableName);
var primaryColumn = Indexs.FirstOrDefault(t => t.IsPrimaryKey);
foreach (DBColInfo c in tb.Cols)
{
idx++;
if (string.IsNullOrWhiteSpace(c.ColumnName))
{
continue;
}
var scriptPrimaryKey = "";
if (primaryColumn != null)
{
scriptPrimaryKey = c.ColumnName == primaryColumn.ColumnNames ? "primary key" : "";
}
var scriptEnd = idx == tb.Cols.Count ? "" : ",";
var scriptNullable = c.IsNullable ? "null" : "not null";
cBuilder.AppendLine(string.Format(" [{0}] [{1}]{2} {3} {4} {5} --{6}"
, c.ColumnName, c.ColumnType, c.ColumnLengthStr, scriptNullable, scriptPrimaryKey, scriptEnd, c.DisplayName));
}
cBuilder.AppendLine(")");
cBuilder.AppendLine("GO");
cBuilder.AppendLine("");
return(cBuilder.ToString());
}
/// <summary>
/// 获取所有用户表
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
List <DBTableInfo> lstName = new List <DBTableInfo>();
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
string sql = _sqlTables;
if (!string.IsNullOrEmpty(inTable))
{
sql += " and TABLE_NAME in(" + inTable + ")";
}
using (IDataReader reader = oper.Query(sql, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader[0] as string;
string type = reader[1] as string;
if (!string.IsNullOrEmpty(type))
{
if (type.Trim() == "VIEW")
{
tableInfo.IsView = true;
}
}
string comment = reader[2] as string;
tableInfo.Description = comment;
lstName.Add(tableInfo);
}
}
return(lstName);
}
private static string ToInsert(DBTableInfo tb)
{
StringBuilder cBuilder = new StringBuilder();
cBuilder.AppendLine("--【Insert】------------------------------------------");
cBuilder.AppendLine("INSERT INTO [dbo].[" + tb.TableName + "](");
var idx = 0;
foreach (DBColInfo c in tb.Cols)
{
idx++;
if (string.IsNullOrWhiteSpace(c.ColumnName))
{
continue;
}
var scriptEnd = idx == tb.Cols.Count ? "" : ",";
cBuilder.AppendLine(string.Format(" [{0}]{1}", c.ColumnName, scriptEnd));
}
/////////////////////////////////
cBuilder.AppendLine(")VALUES(");
idx = 0;
foreach (DBColInfo c in tb.Cols)
{
idx++;
if (string.IsNullOrWhiteSpace(c.ColumnName))
{
continue;
}
var scriptEnd = idx == tb.Cols.Count ? "" : ",";
var scriptNullable = c.IsNullable ? "null" : "not null";
cBuilder.AppendLine(string.Format(" 'xxxxxx'{4}--<{0},{1}{2},{3}>", c.ColumnName, c.ColumnType, c.ColumnLengthStr, scriptNullable, scriptEnd));
}
cBuilder.AppendLine(")");
cBuilder.AppendLine("");
return(cBuilder.ToString());
}
public string GetRemarkSql(DBTableInfo tb)
{
var sb = new StringBuilder();
if (tb.TableDisplayName.IsNullOrEmpty() == false)
{
sb.AppendFormat(@"
IF(EXISTS( SELECT a.* FROM sys.tables AS a
INNER JOIN sys.extended_properties AS b ON a.object_id=b.major_id AND b.minor_id = 0
WHERE ISNULL(b.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN'
AND ISNULL(b.name, 'MS_Description') = 'MS_Description'
AND a.name='{0}'))
BEGIN
EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}';
End
", tb.TableName);
sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
sb.AppendFormat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{1}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}';", tb.TableName, tb.TableDisplayName);
sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
}
foreach (var col in tb.Cols)
{
if (col.DisplayName.IsNullOrEmpty() == false)
{
sb.AppendFormat("EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}';", tb.TableName, col.ColumnName);
sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
sb.AppendFormat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{2}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}';", tb.TableName, col.ColumnName, col.DisplayName);
sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
}
}
return(sb.ToString());
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
string inTables = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
StringBuilder sbSQL = new StringBuilder(_sqlTables);
if (string.IsNullOrEmpty(inTables))
{
sbSQL.Append(" and [sql]<>''");
}
else
{
sbSQL.Append(" and [Name] IN(" + inTables + ")");
}
List <DBTableInfo> lstName = new List <DBTableInfo>();
///只能获取表名,其它的没用
using (IDataReader reader = oper.Query(sbSQL.ToString(), lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader["name"] as string;
tableInfo.IsView = (reader["type"] as string) == "view";
if (IsSysTable(tableInfo.Name))
{
continue;
}
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// 填充字段信息
/// </summary>
/// <param name="prm">字段信息</param>
/// <param name="reader">reader</param>
private void FillParam(DBTableInfo table, IDataReader reader)
{
string prmName = reader["COLUMN_NAME"] as string;
if (string.IsNullOrEmpty(prmName))
{
return;
}
foreach (EntityParam ep in table.Params)
{
if (ep.ParamName == prmName)
{
return;
}
}
EntityParam prm = new EntityParam();
prm.ParamName = prmName;
EntityPropertyType type = EntityPropertyType.Normal;
bool isIdentity = false;
bool isPrimaryKey = false;
string extra = reader["EXTRA"] as string;
if (!string.IsNullOrEmpty(extra) && extra.Trim().Equals("auto_increment", StringComparison.CurrentCultureIgnoreCase))
{
isIdentity = true;
}
string columnKey = reader["COLUMN_KEY"] as string;
if (!string.IsNullOrEmpty(columnKey) && columnKey.Trim().Equals("PRI", StringComparison.CurrentCultureIgnoreCase))
{
isPrimaryKey = true;
}
if (isPrimaryKey)
{
type = EntityPropertyType.PrimaryKey;
}
if (isIdentity)
{
type = type | EntityPropertyType.Identity;
}
prm.PropertyType = type;
if (!(reader["CHARACTER_OCTET_LENGTH"] is DBNull))
{
prm.Length = Convert.ToInt64(reader["CHARACTER_OCTET_LENGTH"]);
}
if (!table.IsView)
{
prm.Description = reader["COLUMN_COMMENT"] as string;
}
prm.AllowNull = Convert.ToInt32(reader["IS_NULLABLE"]) == 1;
string strDBType = reader["COLUMN_TYPE"] as string;
bool isUnsigned = strDBType.IndexOf("unsigned") > -1;
string strDataType = reader["DATA_TYPE"] as string;
prm.SqlType = GetDbType(strDataType, isUnsigned);
table.Params.Add(prm);
}
/// 填充字段信息
/// </summary>
/// <param name="prm">字段信息</param>
/// <param name="reader">reader</param>
private void FillParam(DBTableInfo table, IDataReader reader)
{
string prmName = reader["column_name"] as string;
if (string.IsNullOrEmpty(prmName))
{
return;
}
foreach (EntityParam ep in table.Params)
{
if (ep.ParamName == prmName)
{
return;
}
}
EntityParam prm = new EntityParam();
prm.ParamName = prmName;
EntityPropertyType type = EntityPropertyType.Normal;
bool isIdentity = false;
bool isPrimaryKey = false;
string columnKey = reader["constraint_type"] as string;
if (!string.IsNullOrEmpty(columnKey))
{
isPrimaryKey = true;
isIdentity = true;
}
if (isPrimaryKey)
{
type = EntityPropertyType.PrimaryKey;
}
if (isIdentity)
{
type = type | EntityPropertyType.Identity;
}
prm.PropertyType = type;
object val = reader["character_maximum_length"];
if (!(val is DBNull))
{
prm.Length = Convert.ToInt64(val);
}
val = reader["is_nullable"];
if (!(val is DBNull))
{
prm.AllowNull = val.ToString().Equals("YES", StringComparison.CurrentCultureIgnoreCase);
}
string strDataType = reader["udt_name"] as string;
prm.SqlType = GetDbType(strDataType);
table.Params.Add(prm);
}
protected abstract string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols);
/// <summary>
/// 填充字段信息
/// </summary>
/// <param name="prm">字段信息</param>
/// <param name="reader">reader</param>
private void FillParam(DBTableInfo table, IDataReader reader)
{
string prmName = reader["COLNAME"] as string;
if (string.IsNullOrEmpty(prmName))
{
return;
}
foreach (EntityParam ep in table.Params)
{
if (ep.ParamName == prmName)
{
return;
}
}
EntityParam prm = new EntityParam();
prm.ParamName = prmName;
EntityPropertyType type = EntityPropertyType.Normal;
int isPrimary = 0;
object val = reader["KEYSEQ"];
if (!(val is DBNull))
{
isPrimary = Convert.ToInt32(val);
}
val = reader["IDENTITY"];
bool isIdentity = false;
if (!(val is DBNull))
{
isIdentity = val.ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase);
}
if (isPrimary == 1)
{
type = EntityPropertyType.PrimaryKey;
}
if (isIdentity)
{
type = type | EntityPropertyType.Identity;
}
val = reader["NULLS"];
bool isNull = false;
if (!(val is DBNull))
{
isNull = val.ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase);
}
prm.AllowNull = isNull;
prm.PropertyType = type;
prm.Length = Convert.ToInt64(reader["LENGTH"]);
if (!table.IsView)
{
prm.Description = reader["REMARKS"] as string;
}
string strDBType = reader["TYPENAME"] as string;
prm.SqlType = GetDbType(strDBType);
table.Params.Add(prm);
}
private static bool SaveObject <T>(string ProcedureName, T EntityInstance, bool IsInserting) where T : class
{
bool bSuccess = false;
try
{
Type oCustomType = typeof(T);
DBTableInfo oDBInfo = SchemaManager.GetTable(oCustomType.Name);
object objPropertyValue = null;
PropertyInfo[] arrProperties = oCustomType.GetProperties();
ArrayList arrParams = new ArrayList();
foreach (PropertyInfo oInfo in arrProperties)
{
DBColumnInfo oColumnInfo = oDBInfo.Columns[oInfo.Name] as DBColumnInfo;
if (oColumnInfo != null && !(IsInserting && oColumnInfo.IsPrimaryKey))
{
objPropertyValue = oInfo.GetValue(EntityInstance, null);
if (objPropertyValue == null || DateTime.MinValue.Equals(objPropertyValue))
{
arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, DBNull.Value));
}
else
{
arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, objPropertyValue));
}
}
}
SqlParameter [] Parameters = new SqlParameter[arrParams.Count];
arrParams.CopyTo(Parameters, 0);
object Result = SqlHelper.ExecuteScalar(ConfigManager.Current.ConnectionString, CommandType.StoredProcedure, ProcedureName, Parameters);
bSuccess = true;
if (IsInserting && Result != null)
{
try
{
foreach (string Key in oDBInfo.Columns.Keys)
{
DBColumnInfo oColInfo = oDBInfo.Columns[Key] as DBColumnInfo;
if (oColInfo.IsPrimaryKey)
{
PropertyInfo oInfo = oCustomType.GetProperty(oColInfo.Name);
oInfo.SetValue(EntityInstance, Convert.ChangeType(Result, oInfo.PropertyType), null);
return(bSuccess);
}
}
}
catch (Exception) { }
}
}
catch (Exception ex)
{
ExceptionManager.Publish(ex);
}
return(bSuccess);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
List <DBTableInfo> lst = new List <DBTableInfo>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dtpk = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null); //主键
Dictionary <string, bool> dicPk = new Dictionary <string, bool>();
foreach (DataRow row in dtpk.Rows)
{
string key = row["TABLE_NAME"].ToString() + ":" + row["COLUMN_NAME"].ToString();
dicPk[key.ToLower()] = true;
}
foreach (string tableName in tableNames)
{
DBTableInfo tableinfo = new DBTableInfo();
tableinfo.Params = new List <EntityParam>();
tableinfo.RelationItems = new List <TableRelationAttribute>();
tableinfo.IsView = false;
DataTable dtStr = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "TABLE" });
if (dtStr.Rows.Count <= 0)
{
dtStr = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, tableName, "VIEW" });
tableinfo.IsView = true;
}
if (dtStr.Rows.Count <= 0)
{
continue;
}
tableinfo.Name = dtStr.Rows[0]["TABLE_NAME"] as string;
tableinfo.Description = dtStr.Rows[0]["DESCRIPTION"] as string;
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
//获取表结构
DataTable dtData = null;
string sql = "select * from [" + tableName + "]";
using (OleDbCommand comm = new OleDbCommand(sql, conn))
{
using (OleDbDataReader dr = comm.ExecuteReader(CommandBehavior.KeyInfo))
{
dtData = dr.GetSchemaTable();
}
}
DataView dv = dt.DefaultView;
dv.Sort = "ORDINAL_POSITION asc";
foreach (DataRowView row in dv)
{
FillParam(tableinfo, row.Row, dicPk, dtData);
}
lst.Add(tableinfo);
}
}
foreach (DBTableInfo table in lst)
{
dicTables[table.Name] = table;
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
FillRelation(dicTables, lstRelation);
return(lst);
}
private DBTableInfo GetTableInfo(List <List <string> > data, List <List <string> > pkData, string tableName)
{
DBTableInfo dbTableInfo = new DBTableInfo()
{
TableName = tableName,
FieldInfos = new List <DBFieldInfo>()
};
foreach (var row in data)
{
try
{
string genetalType = "null";
string type = row.ElementAt(0);
switch (type)
{
case "tinyint(1)":
genetalType = "bool";
break;
case string int_1 when int_1.StartsWith("tinyint"):
case string int_2 when int_2.StartsWith("int"):
case string int_3 when int_3.StartsWith("bigint"):
case string int_4 when int_4.StartsWith("numeric"):
genetalType = "int";
break;
case string string_1 when string_1.StartsWith("varchar"):
case string string_2 when string_2.StartsWith("longtext"):
case string string_3 when string_3.StartsWith("character"):
case string string_4 when string_4.StartsWith("character varying"):
genetalType = "string";
break;
case string dateTime_1 when dateTime_1.StartsWith("datetime"):
case string dateTime_2 when dateTime_2.StartsWith("date"):
genetalType = "datetime";
break;
default:
break;
}
bool isPk = false;
if (pkData.Any(i => i.ElementAt(0) == row.ElementAt(1)))
{
isPk = true;
}
DBFieldInfo field = new DBFieldInfo()
{
//Id = reader.GetInt32(0),
Name = row.ElementAt(1),
Type = row.ElementAt(0),
GeneralType = genetalType,
Nullable = row.ElementAt(2) == "YES",
// df value needed?
IsPrimaryKey = isPk
};
dbTableInfo.FieldInfos.Add(field);
}
catch (Exception ex)
{
// _logger.LogError(ex, ex.Message);
}
}
return(dbTableInfo);
}
protected override string GenerateStatement(DBTableInfo tableInfo, List <DBTableColumnInfo> selectedCols, List <DBTableColumnInfo> whereConditionCols)
{
return("\tDELETE FROM " + tableInfo.FullTableName + Environment.NewLine
+ GenerateWhereStatement(whereConditionCols));
}
public DBTableInfoEx(DBTableInfo tableInfo)
: base(tableInfo)
{
}
private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
{
string tableDisplayName = sqlTableInfo.Schema == "dbo"
? sqlTableInfo.TableName
: sqlTableInfo.Schema + "." + sqlTableInfo.TableName;
TreeViewNode tblNode = new TreeViewNode(tableDisplayName, parent);
tblNode.Tag = sqlTableInfo;
TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);
AddColumnNodes(insertSp, sqlTableInfo.Columns.Where(x => !x.Exclude), true);
tblNode.Children.Add(insertSp);
TreeViewNode deleteSp = new TreeViewNode(Constants.deleteTreeNodeText, tblNode);
TreeViewNode whereDelCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, deleteSp);
deleteSp.Children.Add(whereDelCondition);
tblNode.Children.Add(deleteSp);
AddColumnNodes(whereDelCondition, sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToList(), true);
var updateCols = sqlTableInfo.Columns.Where(x => !x.Exclude).ToArray();
var updateWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
if (updateCols.Any() && updateCols.All(x => !updateWhereCols.Contains(x)))
{
TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);
AddColumnNodes(updateSp, updateCols, true);
tblNode.Children.Add(updateSp);
TreeViewNode whereUpdateCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
updateSp.Children.Add(whereUpdateCondition);
AddColumnNodes(whereUpdateCondition, updateWhereCols, true);
}
AddColumnNodes(deleteSp, new List <DBTableColumnInfo>(), true);
var selectColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
TreeViewNode selectSp = new TreeViewNode(Constants.selectTreeNodeText, tblNode);
sqlTableInfo.Columns.CopyTo(selectColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
AddColumnNodes(selectSp, selectColumns, true);
tblNode.Children.Add(selectSp);
var selectOneColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectOneWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectOneColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
if (selectOneColumns.Any() &&
selectOneWhereCols.All(x => selectOneColumns.Any(y => x.ColumnName != y.ColumnName)))
{
TreeViewNode selectOneSp = new TreeViewNode(Constants.selectOneTreeNodeText, tblNode);
AddColumnNodes(selectOneSp, selectOneColumns, true);
tblNode.Children.Add(selectOneSp);
TreeViewNode whereSelectOneCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectOneSp);
selectOneSp.Children.Add(whereSelectOneCondition);
AddColumnNodes(whereSelectOneCondition, selectOneWhereCols, true);
}
var selectByColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectByWhereCols = sqlTableInfo.Columns.Where(x => x.IsForeignKey && !x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectByColumns, 0);
Array.ForEach(selectByColumns, x =>
{
x.Exclude = false;
});
if (selectByColumns.Any() &&
selectByWhereCols.Any())
{
TreeViewNode selectBySp = new TreeViewNode(Constants.selectByTreeNodeText, tblNode);
AddColumnNodes(selectBySp, selectByColumns, true);
tblNode.Children.Add(selectBySp);
TreeViewNode whereSelectByCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectBySp);
selectBySp.Children.Add(whereSelectByCondition);
AddColumnNodes(whereSelectByCondition, selectByWhereCols, true);
}
return(tblNode);
}
private DBTableInfo GetTableInfo(DbCommand command, string tableName)
{
DBTableInfo dbTableInfo = new DBTableInfo()
{
TableName = tableName,
FieldInfos = new List <DBFieldInfo>()
};
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
string genetalType = "null";
string type = reader.GetString(1);
if (type.Contains("("))
{
type = type.Substring(0, type.IndexOf('('));
}
type = type.ToLower();
switch (type)
{
case "tinyint": // TODO SmallInt 1 is bool
case "int":
case "bigint":
case "smallint":
genetalType = "int";
break;
case "varchar":
genetalType = "string";
break;
case "datetime":
genetalType = "datetime";
break;
default:
break;
}
DBFieldInfo field = new DBFieldInfo()
{
//Id = reader.GetInt32(0),
Name = reader.GetString(0),
Type = reader.GetString(1),
GeneralType = genetalType,
Nullable = reader.GetString(2) == "YES",
// df value needed?
IsPrimaryKey = reader.GetString(3) == "PRI"
};
dbTableInfo.FieldInfos.Add(field);
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
}
}
}
return(dbTableInfo);
}
