Is there any tools to generate db class

Jan 5, 2009 at 8:59 AM
I have many of tables, I have to write the DB class inherit IDbobject by hand, is there any tools to generate the classes automatically?
Jan 5, 2009 at 9:27 AM
connection.Open();
            DataTable table = connection.GetSchema("Columns");
            connection.Close();
            Dictionary<string, Dictionary<string, DataRow>> dic = DataSetFactoryChooser.GetTables("TABLE_NAME", "COLUMN_NAME", table);
            foreach (string name in dic.Keys)
            {
Jan 5, 2009 at 9:45 AM
Edited Jan 5, 2009 at 9:50 AM
SQLServer

DataTable table2 = connection.GetSchema ("Columns", new string[] {null, null, table.Name, null});
                        for (int r = 0; r < table2.Rows.Count; r++) {
Jan 5, 2009 at 9:50 AM
SQLite:
public override ColumnSchema[] GetTableColumns (TableSchema table)
                {
                        if ( IsOpen == false && Open () == false)
                                throw new InvalidOperationException ("Invalid connection");

                        ArrayList collection = new ArrayList ();

                        using (SqliteCommand command = new SqliteCommand()) {
                                command.CommandText = "PRAGMA table_info('" +  table.Name + "')";
                                command.Connection = this.connection;

                                SqliteDataReader r = command.ExecuteReader ();

                                while (r.Read ()) {
                                        ColumnSchema column = new ColumnSchema ();
                                        column.Provider = this;

                                        column.ColumnID = r.GetInt32 (0);
                                        column.Name = r.GetString (1);
                                        column.DataTypeName = r.GetString (2);
                                        column.NotNull = r.IsDBNull (3);
                                        column.Default = r.GetString (4);

                                        collection.Add (column);
                                }

                                r.Close ();
                        }

                        return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
                }



MYSQL

public override ColumnSchema[] GetTableColumns (TableSchema table)
                {
                        if (IsOpen == false && Open () == false)
                                throw new InvalidOperationException ("Invalid connection");

                        ArrayList collection = new ArrayList ();

                        using (MySqlCommand command = new MySqlCommand ()) {
                                command.Connection = Connection;

                                // XXX: Use String.Format cause mysql parameters suck assmar.
                                command.CommandText =
                                        String.Format ("DESCRIBE {0}", table.Name);
                                MySqlDataReader r = command.ExecuteReader ();

                                while (r.Read ()) {
                                        ColumnSchema column = new ColumnSchema ();
                                        column.Provider = this;

                                        column.Name = r.GetString (0);
                                        column.DataTypeName = r.GetString (1);
                                        column.NotNull = r.IsDBNull (2);
                                        column.Default = r.GetString (4);
                                        column.Options["extra"] = r.GetString (5);

                                        collection.Add (column);
                                }

                                r.Close ();
                        }

                        return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
                }
Jan 5, 2009 at 10:09 AM

var x = c.GetSchema("Columns", new string[] {null,null, "t_khjbxx" ,null});

            for (int r = 0; r < x.Rows.Count; r++)
            {
                DataRow row2 = x.Rows[r];
                string columnName = row2["COLUMN_NAME"].ToString();
                string dataType = row2["DATA_TYPE"].ToString();
                string maxlen=row2["CHARACTER_MAXIMUM_LENGTH"].ToString();
                string ISNULL = row2["IS_NULLABLE"].ToString();
                foreach (DataColumn c1 in x.Columns)
                {
                    System.Diagnostics.Debug.WriteLine(row2[c1].ToString());
                }

            }

Jan 5, 2009 at 12:23 PM
Edited Jan 5, 2009 at 12:27 PM

 var tbname="t_kwqw";
            var x1 = c.GetSchema("IndexColumns",new string[] {null,null, tbname ,null});

            for (int r = 0; r < x1.Rows.Count; r++)
            {
                DataRow row3 = x1.Rows[r];

                string constraint_name = row3["constraint_name"].ToString();
                if (constraint_name == "PK_" + tbname)
                {
                    var column_name = row3["column_name"].ToString();
                }
                foreach (DataColumn c1 in x1.Columns)
                {
                    System.Diagnostics.Debug.WriteLine(row3[c1].ToString());
                }

               
                System.Diagnostics.Debug.WriteLine("---------------------------------------");
            }

Jan 5, 2009 at 1:21 PM
this is custom to my program:

I wish you can add these function to Dbenrty

public static bool getkey(DataTable x1, string tbname, string column_name)
       {
           for (int r = 0; r < x1.Rows.Count; r++)
           {
               DataRow row3 = x1.Rows[r];

               string constraint_name = row3["constraint_name"].ToString();
               if (constraint_name == "PK_" + tbname)
               {
                   if (row3["column_name"].ToString() == column_name)
                   {
                       return true;
                   }
               }
               /*
               foreach (DataColumn c1 in x1.Columns)
               {System.Diagnostics.Debug.WriteLine(row3[c1].ToString());}
               System.Diagnostics.Debug.WriteLine("---------------------------------------");
                */
           }

           return false;
       }

        static void Main(string[] args)
        {

            DbContext dc = new DbContext("Se");
            System.Data.Common.DbConnection c = (System.Data.Common.DbConnection)dc.Driver.GetDbConnection();
            c.Open();
            var tnames=dc.GetTableNames();

            string s = "";
            foreach (string tbname in tnames)
            {
                var xColumns = c.GetSchema("Columns", new string[] { null, null, tbname, null });
                var xIndexColumns = c.GetSchema("IndexColumns", new string[] { null, null, tbname, null });
                s += "public class " + tbname + " : IDbObject\n{\n";
                for (int r = 0; r < xColumns.Rows.Count; r++)
                {
                    DataRow row2 = xColumns.Rows[r];
                    string columnName = row2["COLUMN_NAME"].ToString();
                    string dataType = row2["DATA_TYPE"].ToString();
                    string maxlen = row2["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    string ISNULL = row2["IS_NULLABLE"].ToString();
                    string NUMERIC_PRECISION = row2["NUMERIC_PRECISION"].ToString();
                    string NUMERIC_SCALE = row2["NUMERIC_SCALE"].ToString();
                    bool iskey = getkey(xIndexColumns, tbname, columnName);
                    /*foreach (DataColumn c1 in x.Columns){ //System.Diagnostics.Debug.WriteLine(row2[c1].ToString());}*/
                    if (dataType == "varchar" || dataType == "char" || dataType == "nchar" || dataType=="nvarchar")
                    {
                        dataType = "string";
                        if (int.Parse(maxlen) > 0)
                        {
                            s += "[Length(" + maxlen.ToString() + ")]\n";
                        }
                    }

                    if (dataType == "money" )
                    {
                        dataType = "decimal";
                    }

                    if (dataType == "datetime" || dataType == "smalldatetime")
                    {
                        dataType = "DateTime";
                    }

                    if (dataType == "numeric")
                    {
                        dataType="decimal";
                    }

                    if (dataType == "bit")
                    {
                        dataType = "bool";
                    }

                    if (dataType == "int" || dataType == "smallint" || dataType == "bigint")
                    {
                        dataType = "int";
                    }

                    if (iskey)
                    {
                        s += "[DbKey(IsDbGenerate = false)]\n";
                    }

                    if (ISNULL == "YES")
                    {
                        s += "[AllowNull]\n";
                    }

                    s += "public " + dataType + " " + columnName + ";\n\n";
                }

                s += "}\n\n";

            }


以上是我个人的应用,写得不好,如果您有兴趣,希望您能写一个类似的给所有的数据库类型。这会让dbentry更好。
如果您还有兴趣,您可以现有class与现在数据库结构对比,对比出多出来什么列,少了什么列,再写一个自动ALTER TABLE的程序什么的。:)

Coordinator
Jan 6, 2009 at 3:44 AM
我会考虑加入从数据库生成类的功能到SqlQuerier.exe中。不过,对生成的类进行手工修改不可避免。比如,Oracle 不支持 int, long, short, single, double 等类型,只支持 decimal 。再比如,DbEntry 的关系类型不支持数据库外键。

我更倾向于用类生成数据库的方式,而且,一般我会在开发的时候使用Sqlite,部署的时候使用SqlServer或MySql。

不过,对比数据库结构的事不会做,就我的项目经验来说,这个功能用处非常有限,绝大多数情况下,还是需要 drop database and rebuild 。

thx.