private DataTable getDataTable(string sql)
{
switch (DB)
{
case DBType.Access:
Access access = new Access(connstr, Tout);
return(access.getDataTable(sql));
case DBType.MYSQL:
DataDriver.MySql mysql = new DataDriver.MySql(connstr, Tout);
return(mysql.getDataTable(sql));
case DBType.MSSQL:
DataDriver.MSSql mssql = new DataDriver.MSSql(connstr, Tout);
return(mssql.getDataTable(sql));
case DBType.SQLite:
DataDriver.SQLite sqlite = new DataDriver.SQLite(connstr, Tout);
return(sqlite.getDataTable(sql));
case DBType.PostgreSQL:
DataDriver.PostgreSQL pgsql = new DataDriver.PostgreSQL(connstr, Tout);
return(pgsql.getDataTable(sql));
default:
return(null);
}
}
private IDataBase MakeConnection()
{
IDataBase DBOper = null;
switch (DB)
{
case DBType.Access:
DBOper = new Access(connstr, Tout);
break;
case DBType.MYSQL:
DBOper = new DataDriver.MySql(connstr, Tout);
break;
case DBType.MSSQL:
DBOper = new DataDriver.MSSql(connstr, Tout);
break;
case DBType.Oracle:
DBOper = new DataDriver.Oracle(connstr, Tout);
break;
case DBType.SQLite:
DBOper = new DataDriver.SQLite(connstr, Tout);
break;
case DBType.PostgreSQL:
DBOper = new DataDriver.PostgreSQL(connstr, Tout);
break;
default:
DBOper = null;
break;
}
return(DBOper);
}
/// <summary>
/// 创建表
/// </summary>
/// <returns>执行结果</returns>
public bool CreatTable()
{
T org = new T();
TableAttribute[] TableAttributes = (TableAttribute[])org.GetType().GetCustomAttributes(typeof(TableAttribute), false);
string TableName = (TableAttributes == null ? "" : (TableAttributes.Length > 0 ? TableAttributes.ToList().First().Name.Trim() : ""));
if (TableName.Trim() == "")
{
return(false);
}
string sql = "";
string Column = "";
string PKColumn = "";
PropertyInfo[] properties = null;
IDataBase DBOper = null;
switch (DB)
{
case DBType.Access:
sql = "Create Table " + TableName + " (";
Column = "";
properties = org.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo p in properties)
{
if (p != null)
{
Type PT = p.PropertyType;
ColumnAttribute[] ColumnAttributes = (ColumnAttribute[])p.GetCustomAttributes(typeof(ColumnAttribute), false);
string ColumnName = (ColumnAttributes == null ? "" : (ColumnAttributes.Length > 0 ? ColumnAttributes.ToList().First().Name.Trim() : ""));
bool IsPrimaryKey = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.IsPrimaryKey) != null);
Type t = p.PropertyType;
if (ColumnName.Trim() != "")
{
Column += ",[" + ColumnName + "]";
if (IsPrimaryKey)
{
Column += " Counter primary key ";
}
else if (t == typeof(string))
{
Column += " string(255) ";
}
else if (t == typeof(char))
{
Column += " Char ";
}
else if (t == typeof(DateTime))
{
Column += " Time ";
}
else if (t == typeof(bool) || t == typeof(short) || t == typeof(int) || t == typeof(long) ||
t == typeof(byte) || t == typeof(ushort) || t == typeof(uint) || t == typeof(ulong))
{
Column += " integer ";
}
else if (t == typeof(double) || t == typeof(float) || t == typeof(decimal))
{
Column += " double ";
}
else if (PT.IsEnum)
{
Column += " int(11) ";
}
}
}
}
if (Column.StartsWith(","))
{
Column = Column.Substring(1, Column.Length - 1);
}
sql += Column + " )";
DBOper = new Access(connstr, Tout);
DBOper.ExecuteNonQuery(sql);
return(DBOper.TableIsExist(TableName));
case DBType.MYSQL:
sql = "Create Table " + TableName + " (";
Column = "";
PKColumn = "";
properties = org.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo p in properties)
{
if (p != null)
{
Type PT = p.PropertyType;
ColumnAttribute[] ColumnAttributes = (ColumnAttribute[])p.GetCustomAttributes(typeof(ColumnAttribute), false);
string ColumnName = (ColumnAttributes == null ? "" : (ColumnAttributes.Length > 0 ? ColumnAttributes.ToList().First().Name.Trim() : ""));
bool IsPrimaryKey = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.IsPrimaryKey) != null);
bool CanBeNull = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.CanBeNull) != null);
Type t = p.PropertyType;
if (ColumnName.Trim() != "")
{
Column += ",`" + ColumnName + "`";
if (IsPrimaryKey)
{
Column += " int(11) NOT NULL auto_increment ";
PKColumn = ",PRIMARY KEY (`" + ColumnName + "`)";
}
else if (t == typeof(string))
{
Column += " varchar(255) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(char))
{
Column += " varchar(50) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(bool))
{
Column += " bit " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(DateTime))
{
Column += " datetime " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(short) || t == typeof(int) || t == typeof(long) ||
t == typeof(byte) || t == typeof(ushort) || t == typeof(uint) || t == typeof(ulong))
{
Column += " int(11) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(double) || t == typeof(float) || t == typeof(decimal))
{
Column += " float(12,4) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (PT.IsEnum)
{
Column += " int(11) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
}
}
}
if (Column.StartsWith(","))
{
Column = Column.Substring(1, Column.Length - 1);
}
sql += Column + PKColumn + " )";
DBOper = new DataDriver.MySql(connstr, Tout);
DBOper.ExecuteNonQuery(sql);
return(DBOper.TableIsExist(TableName));
case DBType.MSSQL:
sql = "Create Table " + TableName + " (";
Column = "";
PKColumn = "";
properties = org.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo p in properties)
{
if (p != null)
{
Type PT = p.PropertyType;
ColumnAttribute[] ColumnAttributes = (ColumnAttribute[])p.GetCustomAttributes(typeof(ColumnAttribute), false);
string ColumnName = (ColumnAttributes == null ? "" : (ColumnAttributes.Length > 0 ? ColumnAttributes.ToList().First().Name.Trim() : ""));
bool IsPrimaryKey = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.IsPrimaryKey) != null);
bool CanBeNull = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.CanBeNull) != null);
Type t = p.PropertyType;
if (ColumnName.Trim() != "")
{
Column += ",[" + ColumnName + "]";
if (IsPrimaryKey)
{
Column += " int not null identity(1,1) ";
PKColumn = ",CONSTRAINT " + TableName + "_PK PRIMARY KEY (" + ColumnName + ")";
}
else if (t == typeof(string))
{
Column += " varchar(255) " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (t == typeof(char))
{
Column += " varchar(50) " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (t == typeof(DateTime))
{
Column += " datetime " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (t == typeof(bool))
{
Column += " BIT " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (t == typeof(short) || t == typeof(int) || t == typeof(long) ||
t == typeof(byte) || t == typeof(ushort) || t == typeof(uint) || t == typeof(ulong))
{
Column += " int " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (t == typeof(double) || t == typeof(float) || t == typeof(decimal))
{
Column += " number(12,4) " + (CanBeNull ? " NULL " : " NOT NULL ");
}
else if (PT.IsEnum)
{
Column += " int " + (CanBeNull ? " NULL " : " NOT NULL ");
}
}
}
}
if (Column.StartsWith(","))
{
Column = Column.Substring(1, Column.Length - 1);
}
sql += Column + PKColumn + " )";
DBOper = new DataDriver.MSSql(connstr, Tout);
DBOper.ExecuteNonQuery(sql);
return(DBOper.TableIsExist(TableName));
case DBType.Oracle:
sql = "Create Table " + TableName + " (";
Column = "";
PKColumn = "";
string PKTRIGGER = "";
properties = org.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo p in properties)
{
if (p != null)
{
Type PT = p.PropertyType;
ColumnAttribute[] ColumnAttributes = (ColumnAttribute[])p.GetCustomAttributes(typeof(ColumnAttribute), false);
string ColumnName = (ColumnAttributes == null ? "" : (ColumnAttributes.Length > 0 ? ColumnAttributes.ToList().First().Name.Trim() : ""));
bool IsPrimaryKey = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.IsPrimaryKey) != null);
bool CanBeNull = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.CanBeNull) != null);
Type t = p.PropertyType;
if (ColumnName.Trim() != "")
{
Column += ",`" + ColumnName + "`";
if (IsPrimaryKey)
{
Column += " INT NOT NULL PRIMARY KEY ";
PKColumn = "create sequence " + TableName + "_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10";
PKTRIGGER = "CREATE OR REPLACE TRIGGER " + TableName + "_Increase BEFORE insert ON " + TableName + " "
+ " FOR EACH ROW "
+ "begin "
+ "select " + TableName + "_seq.nextval into:New." + Column + " from dual "
+ "end";
}
else if (t == typeof(string))
{
Column += " VARCHAR(255) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(char))
{
Column += " VARCHAR(50) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(bool))
{
Column += " char(1) check (" + ColumnName + " in(0,1))";
}
else if (t == typeof(byte[]))
{
Column += " blob ";
}
else if (t == typeof(DateTime))
{
Column += " date " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(short) || t == typeof(int) || t == typeof(long) ||
t == typeof(byte) || t == typeof(ushort) || t == typeof(uint) || t == typeof(ulong))
{
Column += " INT " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(double) || t == typeof(float) || t == typeof(decimal))
{
Column += " NUMBER(12,4) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (PT.IsEnum)
{
Column += " INT " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
}
}
}
if (Column.StartsWith(","))
{
Column = Column.Substring(1, Column.Length - 1);
}
sql += Column + " )";
DBOper = new DataDriver.Oracle(connstr, Tout);
if (!string.IsNullOrEmpty(PKColumn) && !string.IsNullOrEmpty(PKTRIGGER))
{
DBOper.ExecuteNonQuery(PKColumn);
DBOper.ExecuteNonQuery(sql);
DBOper.ExecuteNonQuery(PKTRIGGER);
}
else
{
DBOper.ExecuteNonQuery(sql);
}
return(DBOper.TableIsExist(TableName));
case DBType.SQLite:
sql = "Create Table " + TableName + " (";
Column = "";
PKColumn = "";
properties = org.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo p in properties)
{
if (p != null)
{
Type PT = p.PropertyType;
ColumnAttribute[] ColumnAttributes = (ColumnAttribute[])p.GetCustomAttributes(typeof(ColumnAttribute), false);
string ColumnName = (ColumnAttributes == null ? "" : (ColumnAttributes.Length > 0 ? ColumnAttributes.ToList().First().Name.Trim() : ""));
bool IsPrimaryKey = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.IsPrimaryKey) != null);
bool CanBeNull = (ColumnAttributes == null ? false : ColumnAttributes.ToList().Find(c => c.CanBeNull) != null);
Type t = p.PropertyType;
if (ColumnName.Trim() != "")
{
Column += "," + ColumnName + " ";
if (IsPrimaryKey)
{
Column += " integer PRIMARY KEY autoincrement ";
}
else if (t == typeof(string))
{
Column += " varchar(255) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(char))
{
Column += " varchar(50) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(DateTime))
{
Column += " datetime " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(short) || t == typeof(int) || t == typeof(long) ||
t == typeof(byte) || t == typeof(ushort) || t == typeof(uint) || t == typeof(ulong))
{
Column += " int(11) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (t == typeof(double) || t == typeof(float) || t == typeof(decimal))
{
Column += " float(12,4) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
else if (PT.IsEnum)
{
Column += " int(11) " + (CanBeNull ? " default NULL " : " NOT NULL ");
}
}
}
}
if (Column.StartsWith(","))
{
Column = Column.Substring(1, Column.Length - 1);
}
sql += Column + " )";
DBOper = new DataDriver.SQLite(connstr, Tout);
DBOper.ExecuteNonQuery(sql);
return(DBOper.TableIsExist(TableName));
}
return(false);
}