Beispiel #1
0
        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);
            }
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        /// <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);
        }