public DBColumnAttribute(DBTYPE type, bool isPK = false, bool nullable = false, object defValue = null)
{
Type = type;
IsPK = isPK;
Nullable = nullable;
DefaultValue = defValue;
}
public IDbConnection GetOpenConnection(string connectStr, DBTYPE type)
{
return(GetConn(new DatabaseModel()
{
Dbtype = type, ConnectStr = connectStr
}));
}
internal DBColumn(TableEntity host, PropertyInfo pinfo, DBTYPE type, int size1, int size2, bool isPK = false, bool nullable = false, Func <object> defValue = null)
{
if (host == null)
{
throw new ArgumentNullException("DBColumn(host) is null!");
}
if (pinfo == null)
{
throw new ArgumentNullException("DBColumn(pinfo) is null!");
}
_host = host;
_pinfo = pinfo;
Name = _pinfo.Name;
Type = type;
Size1 = size1;
Size2 = size2;
IsPK = isPK;
Nullable = nullable;
FuncDefaultValue = defValue;
_value0 = DBColumnRAW.VALUE;
_status0 = STATUS.RAW;
CheckName();
CheckDBTYPE();
CheckNullable();
}
internal int get_type(ref DBTYPE type)
{
int ret;
ret = libdb_csharpPINVOKE.DB_get_type(swigCPtr, ref type);
DatabaseException.ThrowException(ret);
return(ret);
}
public static IDbConnection CreateDBConnection(DBTYPE dbType, string connectionStr)
{
switch (dbType)
{
case DBTYPE.SQLSERVER:
return(new SqlConnection(connectionStr));
default:
return(new SqlConnection(connectionStr));
}
}
internal int open(DB_TXN txn, string file, string database, DBTYPE type, uint flags, int mode)
{
int ret;
ret = libdb_csharpPINVOKE.DB_open(swigCPtr, DB_TXN.getCPtr(txn), file, database, (int)type, flags, mode);
if (ret != 0)
{
close(0);
}
DatabaseException.ThrowException(ret);
return(ret);
}
public static object ValueTypeConvert(object val, DBTYPE type)
{
if (val == null)
{
return(val);
}
var type1 = val.GetType();
var type2 = type;
var flag = string.Format("{0}=>{1}", type1.ToString(), type2.ToString());
object value = null;
switch (flag)
{
//case "System.String=>VARCHAR":
//case "System.String=>NVARCHAR":
//case "System.Int32=>INT":
//case "System.Int64=>BIGINT":
//case "System.DateTime=>DATETIME":
//case "System.Decimal=>DECIMAL":
//case "System.Decimal=>NUMERIC":
//case "System.Float=>NUMERIC":
//case "System.Boolean=>BOOLEAN":
// value = val;
// break;
case "System.Int32=>BIGINT":
value = Convert.ToInt64(val);
break;
case "System.Int64=>INT":
value = Convert.ToInt32(val);
break;
case "System.Boolean=>BIT":
value = Convert.ToByte(val);
break;
case "System.Byte=>BOOLEAN":
value = Convert.ToBoolean(val);
break;
case "System.String=>UNIQID":
value = string.IsNullOrWhiteSpace(val.ToString()) ? null : (Guid?)Guid.Parse(val.ToString());
break;
default:
value = val;
break;
}
return(value);
}
/// <summary>
/// 获取数据库列表.
/// </summary>
/// <returns></returns>
public List<string> GetDataBaseName(DBTYPE dbtype)
{
switch (dbtype)
{
case DBTYPE.UNKNOW:
break;
case DBTYPE.SQLSERVER:
using (SqlConnection conn = new SqlConnection(formConf.connectionString))
{
conn.Open();
List<string> nameList = new List<string>();
string cmdText = "select [name] from sys.databases where snapshot_isolation_state = 0";
SqlCommand cmd = new SqlCommand(cmdText, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
nameList.Add(dr["name"].ToString());
}
}
return nameList;
}
case DBTYPE.MYSQL_MARIADB:
using (MySqlConnection conn = new MySqlConnection(formConf.connectionString))
{
conn.Open();
List<string> nameList = new List<string>();
string cmdText = "SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`SCHEMATA";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
nameList.Add(dr["SCHEMA_NAME"].ToString());
}
}
return nameList;
}
case DBTYPE.ORACLE:
break;
default:
break;
}
return null;
}
private void ConfigDataBase()
{
string server, intergrated, uid, connectstring;
//AddLog("数据库连接中....");
switch (cmbDataType.SelectedIndex)
{
case 0:
this.currentType = DBTYPE.SQLSERVER;
server = "Server=" + txserver.Text.Trim() + ";";
intergrated = "Integrated Security=" + (cmbAuthor.SelectedIndex == 0 ? "True" : "False") + ";";
uid = "";
if (!(cmbAuthor.SelectedIndex == 0))
{
uid = "uid=" + this.txusername.Text + ";pwd=" + this.txpassword.Text + ";";
}
connectstring = server + intergrated + uid;
formConf.connectionString = formConf.DefaultConnectionString = connectstring;
break;
case 1:
this.currentType = DBTYPE.MYSQL_MARIADB;
server = "Host=" + txserver.Text.Trim() + ";";
uid = "UserName="******";Password="******";Port=" + txMYSQLPORT.Text + ";";
connectstring = server + uid;
formConf.connectionString = formConf.DefaultConnectionString = connectstring;
break;
case 2:
this.currentType = DBTYPE.ORACLE;
break;
default:
this.currentType = DBTYPE.UNKNOW;
break;
}
this.ConfigDataTable();
}
/// <summary>
/// 读取数据库类型 mysql, oracle, sqlserver, access
/// </summary>
/// <returns></returns>
public DBTYPE ReadTypeOfDataBase()
{
DBTYPE dbType = DBTYPE.None;
string s = Directory.GetCurrentDirectory() + @"\Config\System\";
XmlDocument doc = new XmlDocument();
string type = "";
doc.Load(s + "DataBase.xml");
var nodes = doc.SelectSingleNode("database").ChildNodes;
foreach (XmlNode item in nodes)
{
XmlElement xe = (XmlElement)item;
if (xe.Name == "type")
{
type = xe.InnerText;
break;
}
}
switch (type.ToLower())
{
case "mysql":
dbType = DBTYPE.MySql;
break;
case "oracle":
dbType = DBTYPE.Oracle;
break;
case "sqlserver":
dbType = DBTYPE.SqlServer;
break;
case "access":
dbType = DBTYPE.Access;
break;
}
return(dbType);
}
public UserInfoRepository(string connectStr, DBTYPE type) : base(connectStr, type)
{
}
internal int get_type(ref DBTYPE type)
{
int ret;
ret = libdb_csharpPINVOKE.DB_get_type(swigCPtr, ref type);
DatabaseException.ThrowException(ret);
return ret;
}
internal int open(DB_TXN txn, string file, string database, DBTYPE type, uint flags, int mode)
{
int ret;
ret = libdb_csharpPINVOKE.DB_open(swigCPtr, DB_TXN.getCPtr(txn), file, database, (int)type, flags, mode);
if (ret != 0)
close(0);
DatabaseException.ThrowException(ret);
return ret;
}
/// <summary>
/// 获取数据库表集合.
/// </summary>
/// <returns></returns>
public List<EntityInfo> GetTables(DBTYPE dbtype, string database)
{
switch (dbtype)
{
case DBTYPE.UNKNOW:
break;
case DBTYPE.SQLSERVER:
if (formConf.connectionString.Contains("Database=tempdb"))
return new List<EntityInfo>();
using (SqlConnection conn = new SqlConnection(formConf.connectionString))
{
conn.Open();
List<EntityInfo> tables = new List<EntityInfo>();
string cmdText = "select * from sys.tables";
SqlCommand cmd = new SqlCommand(cmdText, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
EntityInfo table = new EntityInfo();
table.EntityName = table.ClassName = dr["name"].ToString();
table.CreateDate = dr["create_date"].ToString();
if (table.ClassName != "sysdiagrams") //数据库关系图自动生成的表
tables.Add(table);
}
}
return tables;
}
case DBTYPE.MYSQL_MARIADB:
if (formConf.connectionString.Contains("Database=information_schema") || formConf.connectionString.Contains("Database=performance_schema"))
return new List<EntityInfo>();
using (MySqlConnection conn = new MySqlConnection(formConf.connectionString))
{
conn.Open();
List<EntityInfo> tables = new List<EntityInfo>();
string cmdText = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
EntityInfo table = new EntityInfo();
table.ClassName = dr["TABLE_NAME"].ToString();
table.EntityName = dr["TABLE_COMMENT"].ToString().Length > 0 ? dr["TABLE_COMMENT"].ToString() : dr["TABLE_NAME"].ToString();
if (table.ClassName != "sysdiagrams") //数据库关系图自动生成的表
tables.Add(table);
}
}
return tables;
}
case DBTYPE.ORACLE:
break;
default:
break;
}
return null;
}
/// <summary>
/// 返回外键，主键不在这里返回.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbtype"></param>
/// <param name="table_schema"></param>
/// <returns></returns>
public List<ConstraintInfo> GetConstaint(string tableName, DBTYPE dbtype, string table_schema)
{
List<ConstraintInfo> constrains = new List<ConstraintInfo>();
switch (dbtype)
{
case DBTYPE.UNKNOW:
break;
case DBTYPE.SQLSERVER:
return null;
case DBTYPE.MYSQL_MARIADB:
using (MySqlConnection conn = new MySqlConnection(formConf.connectionString))
{
conn.Open();
string cmdText = "select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='" + tableName + "' and CONSTRAINT_NAME !='PRIMARY'";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ConstraintInfo constraint = new ConstraintInfo();
constraint.Constraint_catalog = dr["CONSTRAINT_CATALOG"].ToString();
constraint.Constraint_schema = dr["CONSTRAINT_SCHEMA"].ToString();
constraint.Constraint_name = dr["CONSTRAINT_NAME"].ToString();
constraint.Table_Catalog = dr["TABLE_CATALOG"].ToString();
constraint.Table_Schema = dr["TABLE_SCHEMA"].ToString();
constraint.Table_Name = dr["TABLE_NAME"].ToString();
constraint.Column_Name = dr["COLUMN_NAME"].ToString();
constraint.ORDINAL_POSITION = dr["ORDINAL_POSITION"].ToString();
constraint.POSITION_IN_UNIQUE_CONSTRAINT = dr["POSITION_IN_UNIQUE_CONSTRAINT"].ToString();
constraint.REFERENCED_TABLE_SCHEMA = dr["REFERENCED_TABLE_SCHEMA"].ToString();
constraint.REFERENCED_TABLE_NAME = dr["REFERENCED_TABLE_NAME"].ToString();
constraint.REFERENCED_COLUMN_NAME = dr["REFERENCED_COLUMN_NAME"].ToString();
constrains.Add(constraint);
}
}
return constrains;
}
case DBTYPE.ORACLE:
break;
default:
break;
}
return constrains;
}
public DBColumnAttribute(string name, DBTYPE type, int size1, int size2, bool isPK = false, bool nullable = false, object defValue = null) : this(type, isPK, nullable, defValue)
{
Size1 = size1;
Size2 = size2;
}
public static extern int DB_get_type(HandleRef jarg1, ref DBTYPE jarg2);
public DBColumnAttribute(DBTYPE type, int size, bool isPK = false, bool nullable = false, object defValue = null) : this(type, isPK, nullable, defValue)
{
Size1 = size;
}
/// <summary>
/// 获取单表列的集合.
/// </summary>
/// <param name="table_Schema">数据库名.</param>
/// <param name="tableID">表名.</param>
/// <param name="dbtype"></param>
/// <returns></returns>
public List<AttributeInfo> GetTableFields(string tableID, DBTYPE dbtype, string table_Schema)
{
switch (dbtype)
{
case DBTYPE.UNKNOW:
break;
case DBTYPE.SQLSERVER:
using (SqlConnection conn = new SqlConnection(formConf.connectionString))
{
conn.Open();
List<AttributeInfo> columns = new List<AttributeInfo>();
string cmdText = "SELECT *,syscolumns.length as max_length,systypes.name as typeName FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = '" + tableID + "'";
SqlCommand cmd = new SqlCommand(cmdText, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
AttributeInfo column = new AttributeInfo();
column.cisNull = Convert.ToBoolean(dr["isnullable"]);
column.ColumnName = dr["name"].ToString();
//column.DefaultVal
//column.DeText
//column.IsIdentity = Convert.ToBoolean(dr["is_identity"]);
//column.IsPK
//column.Length = dr["max_length"].ToString();
column.Length = dr["length"].ToString();
column.Preci = dr["prec"].ToString();
column.Scale = dr["scale"].ToString();
column.TypeName = dr["typeName"].ToString();
columns.Add(column);
}
}
return columns;
}
case DBTYPE.MYSQL_MARIADB:
//string strcc = formConf.connectionString.Replace(table_Schema, "information_schema");
using (MySqlConnection conn = new MySqlConnection(formConf.connectionString))
{
conn.Open();
List<AttributeInfo> columns = new List<AttributeInfo>();
//string cmdText = "show columns from " + tableID;
string cmdText = "SHOW FULL FIELDS from " + tableID;
//string cmdText = "select * from COLUMNS where table_schema='" + table_Schema + "' and table_name='" + tableID + "'";
//string cmdText = "SELECT *,syscolumns.length as max_length,systypes.name as typeName FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = ' " + tableID + "'";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// use information_schema;
// SELECT TABLE_NAME '表名',ORDINAL_POSITION '顺序',COLUMN_NAME '字段',DATA_TYPE '类型'
// ,CHARACTER_OCTET_LENGTH '字节长',if(COLUMN_KEY='PRI',"√","") '主键',if(EXTRA='auto_increment',"√","") '自增长'
// ,if(IS_NULLABLE='YES',"√","") '空',CHARACTER_SET_NAME '编码',COLUMN_DEFAULT '默认值',COLUMN_COMMENT '说明'
// FROM COLUMNS WHERE TABLE_SCHEMA = 'webmanagesystem' ORDER BY TABLE_NAME,ORDINAL_POSITION;
AttributeInfo column = new AttributeInfo();
column.ColumnName = dr["Field"].ToString();
column.TypeName = dr["Type"].ToString();
Regex r = new Regex(@"\d+");
Regex r1 = new Regex(@"\w+");
column.TypeName = r1.Match(dr["Type"].ToString()).Value;
column.Length = r.Match(dr["Type"].ToString()).Value;
column.Collation = dr["Collation"].ToString();
column.cisNull = dr["Null"].ToString().ToUpper().Equals("YES");
column.IsPK = dr["KEY"].ToString().Equals("PRI");
//column.IsUnique = dr["IsUnique"].ToString();
column.Extra = dr["Extra"].ToString();
column.IsIdentity = column.Extra == "auto_increment";
column.Comment = dr["Comment"].ToString().Length > 0 ? dr["Comment"].ToString() : dr["Field"].ToString();
string fdd = dr["Type"].ToString();
try
{
string lll = fdd.Split('(')[1];
if (lll.Contains(','))
{
column.Preci = lll.TrimEnd(')').Split(',')[0];
column.Scale = lll.TrimEnd(')').Split(',')[1];
}
}
catch (Exception ex)
{
MessageBox.Show(tableID + ":" + ex.Message);
}
columns.Add(column);
}
}
return columns;
}
case DBTYPE.ORACLE:
break;
default:
break;
}
return null;
}
public DatabaseModel(string connectStr, DBTYPE dbtype)
{
ConnectStr = connectStr;
Dbtype = dbtype;
}
public IDbConnection GetOpenConnection(string connectStr, DBTYPE type)
{
return(GetConn(new DatabaseModel(connectStr, type)));
}
public DapperRepositoryBase(string connectStr, DBTYPE type)
{
_con = DbConnectionFactory.Instance.GetOpenConnection(connectStr, type);
}