home *** CD-ROM | disk | FTP | other *** search
/ io Programmo 40 / IOPROG_40.ISO / SOFT / NETFrameworkSDK.exe / comsdk.cab / samples.exe / QuickStart / util / TDGUtil / TDGUtil.cs < prev   
Encoding:
Text File  |  2000-06-05  |  16.0 KB  |  366 lines
  1. /***
  2.  ***
  3.  *** Generates an XSD schema and typed dataset for a set of database tables
  4.  ***
  5.  ***/
  6. namespace TypedDataSetGenerator {
  7.     using System;
  8.     using System.Data;
  9.     using System.IO;
  10.     using System.Data.ADO;
  11.     using Microsoft.VisualStudio.Data.CodeGen;
  12.     using System.Collections;
  13.     using System.CodeDOM.Compiler;
  14.  
  15.     //* Expose GetSchemaTable so that we can fk info to build relationships
  16.     internal class ADOSchemaConnection : ADOConnection {
  17.         public ADOSchemaConnection(string con) :base(con) {}
  18.  
  19.         public DataTable ADOGetSchemaTable(Type tableType, Guid schema, object[] restrictions) {
  20.             return this.GetSchemaTable(tableType, schema, restrictions) ;
  21.         }
  22.  
  23.     }
  24.  
  25.     public class TDGUtil {
  26.         
  27.         public static void BadInput(string param) {
  28.             Console.WriteLine("Bad Parameter: " + param);        
  29.             BadInput();
  30.         }   
  31.  
  32.         public static void BadInput() {
  33.             Console.WriteLine("Usage is: TDGUtil /dsname DataSet Name /table TableName /conn ConnectionString [/cs | /vb] [/pn packageName] [/out targetDir]");        
  34.             Console.WriteLine("Can have multiple table parameters");
  35.             Console.WriteLine("\n\tFor example: TDGUtil /dsname CustomersDataSet /cs /table customers /out out /conn \"Provider=SQLOLEDB;Data Source=localhost;Database=northwind;User Id=sa;\"");        
  36.         }   
  37.  
  38.         public static void Main(string[] args) {
  39.             try {
  40.                 
  41.                 if (args == null ||
  42.                     args.Length == 0 ||
  43.                     "-h".Equals(args[0]) ||
  44.                     "/h".Equals(args[0])) {
  45.                     BadInput();
  46.                     return;
  47.                 }
  48.  
  49.                 bool vb = false, cs = false;
  50.                 string dbConnection = null;
  51.                 string packageName = null;    
  52.                 string targetDir=System.WinForms.Application.StartupPath;
  53.                 string xmlFile = null ;
  54.                 string dataSetName = null;
  55.                 ArrayList tables = new ArrayList();
  56.  
  57.                 int curArg = 0;
  58.                 while (curArg < args.Length) {
  59.  
  60.                     //Console.WriteLine("args[" + curArg + "] = " + args[curArg]);
  61.  
  62.                     if (String.Compare(args[curArg],"/out", true) == 0) {
  63.                         if (args.Length < curArg + 2 ) {
  64.                             BadInput("/out");
  65.                             return;
  66.                         }
  67.                         curArg++;
  68.                         targetDir = args[curArg];
  69.                     }
  70.                     else if (String.Compare(args[curArg],"/table", true) == 0) {
  71.                         if (args.Length < curArg + 2 ) {
  72.                             BadInput("/table");
  73.                             return;
  74.                         }
  75.                         curArg++ ;
  76.                         tables.Add(args[curArg]);
  77.                     }
  78.                     else if (String.Compare(args[curArg],"/dsname", true) == 0) {
  79.                         if (args.Length < curArg + 2 ) {
  80.                             BadInput("/dsname");
  81.                             return;
  82.                         }
  83.                         curArg++ ;
  84.                         dataSetName = args[curArg];
  85.                     }
  86.                     else if (String.Compare(args[curArg],"/conn", true) == 0) {
  87.                         if (args.Length < curArg + 2 ) {
  88.                             BadInput("/conn");
  89.                             return;
  90.                         }
  91.                         curArg++;
  92.                         dbConnection = args[curArg];
  93.                     }
  94.                     else if (String.Compare(args[curArg],"/cs", true) == 0) {
  95.                         cs = true;
  96.                     }
  97.                     else if (String.Compare(args[curArg],"/vb", true) == 0) {
  98.                         vb = true;
  99.                     }
  100.                     else if (String.Compare(args[curArg], "/pn", true) == 0) {
  101.                         if (args.Length < curArg + 2 ) {
  102.                             BadInput("/pn");
  103.                             return;
  104.                         }
  105.                         curArg++;
  106.                         packageName = args[curArg];
  107.                     }
  108.  
  109.                     curArg++;
  110.                 }
  111.  
  112.                 //Check args
  113.                 if (null == dbConnection) {
  114.                     BadInput("/conn");
  115.                     return;
  116.                 }
  117.  
  118.                 if (tables.Count == 0) {
  119.                     BadInput("/table");
  120.                     return;
  121.                 }
  122.  
  123.                  //set up defaults if required
  124.                 if (null == dataSetName) {
  125.                     dataSetName = tables[0] + "DataSet";
  126.                 }
  127.  
  128.                  //set up defaults if required
  129.                 if (null == packageName) {
  130.                     packageName = dataSetName;
  131.                 }
  132.  
  133.  
  134.                 xmlFile = dataSetName +".xsd" ;
  135.                 
  136.  
  137.                 string langString = null;
  138.  
  139.                  // if neither specified, do both.
  140.                 if (!vb && !cs) {
  141.                     langString = "VB & C#"; 
  142.                 } else if (vb) {
  143.                     langString = "VB"; 
  144.                 } else if (cs) {
  145.                     langString = "C#"; 
  146.                 }
  147.  
  148.  
  149.                 Console.WriteLine("Generating XSD Schema and DataSet classes for: ");
  150.                 Console.WriteLine("\tDataSet Name:" + dataSetName);
  151.                 Console.WriteLine("\tXSD File:" + xmlFile);
  152.                 Console.Write("\tTables: ");
  153.                 for (int i=0 ; i < tables.Count ; i++) {
  154.                     Console.Write(" " + tables[i].ToString());
  155.                 }
  156.                 Console.WriteLine("\n\tDatabase: " + dbConnection);
  157.                 Console.WriteLine("\tTarget Directory: " + targetDir);
  158.                 if (packageName != null) 
  159.                     Console.WriteLine("\tPackage: " + packageName);
  160.                 Console.WriteLine("\tLanguages: " + langString);
  161.  
  162.                 DataSet ds = new DataSet();
  163.                 ADOSchemaConnection con = new ADOSchemaConnection(dbConnection);
  164.                 ds.DataSetName = dataSetName;
  165.  
  166.                 Console.WriteLine("\n\tLoading DataSet Schema...");
  167.  
  168.                 for (int i=0 ; i < tables.Count ; i++) {
  169.  
  170.                     //Use Schema stuff instead - talk to Gio about exposing this
  171.                     //A lot more work tho'
  172.                     ADODataSetCommand cmd = new ADODataSetCommand("Select TOP 1 * from [" + tables[i] + "]", con);
  173.                     cmd.FillDataSetSchema(ds, SchemaType.Source, ((string)(tables[i])));
  174.                 }
  175.  
  176.                 if (tables.Count > 1) {
  177.                     Console.WriteLine("\n\tCreating Relationships...");
  178.                     CreateRelationships(con, ds, tables);
  179.                 }
  180.  
  181.                 Console.WriteLine("\n\tCreating XSD File...");
  182.  
  183.                 StreamWriter fs = new StreamWriter(targetDir + "\\" + xmlFile);
  184.                 try {
  185.                     fs.Write(ds.XmlSchema);    
  186.                 } finally {
  187.                     fs.Close();
  188.                 }
  189.  
  190.                 Console.WriteLine("\n\tCreating Types...");
  191.  
  192.                 // if neither specified, do both.
  193.                 if (!vb && !cs) {
  194.                     GenerateVB(ds, targetDir, packageName);
  195.                     GenerateCs(ds, targetDir, packageName);
  196.                 } else if (vb) {
  197.                     GenerateVB(ds, targetDir, packageName);
  198.                 } else if (cs) {
  199.                     GenerateCs(ds, targetDir, packageName);
  200.                 }
  201.  
  202.                 Console.WriteLine("Done");
  203.  
  204.             }
  205.             catch (Exception e) {
  206.                 Console.WriteLine("Failed to generate: " + e.Message);
  207.                 Console.WriteLine(e.GetType().Name);
  208.                 Console.WriteLine(e.StackTrace);
  209.             }
  210.         }
  211.  
  212.     private static void GenerateCs(DataSet dataSet, string targetDir, string packageName) {
  213.         string fileName = targetDir + "\\" + packageName + ".cs";
  214.         StreamWriter fs = new StreamWriter(fileName);
  215.         try {
  216.             Generator.GenerateDataSetCSharp(fs, packageName, dataSet);
  217.         } finally {
  218.             fs.Close();
  219.         }
  220.     }        
  221.  
  222.     private static void GenerateVB(DataSet dataSet, string targetDir, string packageName) {
  223.         string fileName = targetDir + "\\" + packageName + ".vb";
  224.         StreamWriter fs = new StreamWriter(fileName);
  225.         try {
  226.             Generator.GenerateDataSetVB(fs, packageName, dataSet);
  227.         } finally {
  228.             fs.Close();
  229.         }
  230.     }        
  231.  
  232.  
  233.         private static void CreateRelationships(ADOSchemaConnection con, DataSet targetDS, ArrayList tables) {
  234.             con.Open();
  235.             try {
  236.                 //Get Schema tables for fkeys 
  237.                 DataTable schemaTable = con.ADOGetSchemaTable(  typeof(DataTable)
  238.                                                               , DBSchemaGUIDs.FOREIGN_KEYS
  239.                                                               , new object[] {});
  240.  
  241.                 /* Gives back:
  242.                 <PK_TABLE_CATALOG>
  243.                 <PK_TABLE_SCHEMA>
  244.                 <PK_TABLE_NAME>
  245.                 <PK_COLUMN_NAME>
  246.                 <FK_TABLE_CATALOG>
  247.                 <FK_TABLE_SCHEMA>
  248.                 <FK_TABLE_NAME>
  249.                 <FK_COLUMN_NAME>
  250.                 <ORDINAL>
  251.                 <UPDATE_RULE>
  252.                 <DELETE_RULE>
  253.                 <PK_NAME>
  254.                 <FK_NAME>
  255.                 <DEFERRABILITY>
  256.                 */
  257.  
  258.                 //Get all info for each fkey and then build relationships
  259.                 Hashtable fkeys = new Hashtable() ;
  260.  
  261.                 for (int i=0 ; i < tables.Count ; i++) {
  262.                     string tablename = (string)(tables[i]);
  263.  
  264.                     //Find all the fk relationships for this table
  265.                     DataRow[] rows = schemaTable.Select("PK_TABLE_NAME='" + tablename +"'");
  266.  
  267.                     for (int j=0 ; j < rows.Length ; j++) {
  268.  
  269.                         DataRow row2 = rows[j];
  270.                         string fkname = (string)(row2["FK_NAME"]);
  271.                         string childTableName = (string)(row2["FK_TABLE_NAME"]);
  272.                         ArrayList fkcols ;
  273.  
  274.                         if (targetDS.Tables.Contains(childTableName)) {
  275.                             if (!fkeys.ContainsKey(fkname)) {
  276.                                 fkcols = new ArrayList();
  277.                                 fkeys[fkname] = fkcols;
  278.                             } else {
  279.                                 fkcols = (ArrayList)(fkeys[fkname]);
  280.                             }
  281.             
  282.                             fkcols.Add(row2);
  283.                         }
  284.                     }
  285.                 }
  286.  
  287.                 IDictionaryEnumerator en = (IDictionaryEnumerator)(fkeys.GetEnumerator());
  288.                 while (en.MoveNext()) {
  289.                     ArrayList fkcols = (ArrayList)(en.Value);
  290.                     DataRow row = (DataRow)(fkcols[0]);
  291.  
  292.                     string fkname = (string)(row["FK_NAME"]);
  293.                     DataTable pTable = targetDS.Tables[(string)(row["PK_TABLE_NAME"])];
  294.                     DataTable cTable = targetDS.Tables[(string)(row["FK_TABLE_NAME"])];
  295.                     
  296.                     string relName = pTable.TableName + cTable.TableName;
  297.  
  298.                     if (targetDS.Relations.Contains(relName)) {
  299.                         //If duplicate then simply append fk name to end
  300.                         relName = relName + fkname;
  301.                     }
  302.  
  303.                     Console.WriteLine("\t\tGenerating relationship for " +  pTable.TableName);
  304.                     Console.WriteLine("\t\t\t Creating " + relName + " for: "  +  fkname);
  305.  
  306.                     DataColumn[] pCols = new DataColumn[fkcols.Count];
  307.                     DataColumn[] cCols = new DataColumn[fkcols.Count];
  308.                     for (int i = 0 ; i < fkcols.Count ; i++) {
  309.                         DataRow row1 = (DataRow)(fkcols[i]);
  310.                         pCols[i] = pTable.Columns[(string)(row1["PK_COLUMN_NAME"])];
  311.                         cCols[i] = cTable.Columns[(string)(row1["FK_COLUMN_NAME"])];
  312.                     }
  313.  
  314.                     targetDS.Relations.Add(relName, pCols, cCols, true);
  315.                 }
  316.  
  317.             } finally {
  318.                 con.Close();
  319.             }
  320.         }
  321.  
  322.  
  323.     }
  324.  
  325.  
  326.     sealed internal class DBSchemaGUIDs {
  327.         static readonly public Guid TABLES_INFO             = new Guid("c8b522e0-5cf3-11ce-ade5-00aa0044773d");
  328.         static readonly public Guid TRUSTEE                 = new Guid("c8b522ef-5cf3-11ce-ade5-00aa0044773d");
  329.         static readonly public Guid ASSERTIONS              = new Guid("c8b52210-5cf3-11ce-ade5-00aa0044773d");
  330.         static readonly public Guid CATALOGS                = new Guid("c8b52211-5cf3-11ce-ade5-00aa0044773d");
  331.         static readonly public Guid CHARACTER_SETS          = new Guid("c8b52212-5cf3-11ce-ade5-00aa0044773d");
  332.         static readonly public Guid COLLATIONS              = new Guid("c8b52213-5cf3-11ce-ade5-00aa0044773d");
  333.         static readonly public Guid COLUMNS                 = new Guid("c8b52214-5cf3-11ce-ade5-00aa0044773d");
  334.         static readonly public Guid CHECK_CONSTRAINTS       = new Guid("c8b52215-5cf3-11ce-ade5-00aa0044773d");
  335.         static readonly public Guid CONSTRAINT_COLUMN_USAGE = new Guid("c8b52216-5cf3-11ce-ade5-00aa0044773d");
  336.         static readonly public Guid CONSTRAINT_TABLE_USAGE  = new Guid("c8b52217-5cf3-11ce-ade5-00aa0044773d");
  337.         static readonly public Guid KEY_COLUMN_USAGE        = new Guid("c8b52218-5cf3-11ce-ade5-00aa0044773d");
  338.         static readonly public Guid REFERENTIAL_CONSTRAINTS = new Guid("c8b52219-5cf3-11ce-ade5-00aa0044773d");
  339.         static readonly public Guid TABLE_CONSTRAINTS       = new Guid("c8b5221a-5cf3-11ce-ade5-00aa0044773d");
  340.         static readonly public Guid COLUMN_DOMAIN_USAGE     = new Guid("c8b5221b-5cf3-11ce-ade5-00aa0044773d");
  341.         static readonly public Guid INDEXES                 = new Guid("c8b5221e-5cf3-11ce-ade5-00aa0044773d");
  342.         static readonly public Guid COLUMN_PRIVILEGES       = new Guid("c8b52221-5cf3-11ce-ade5-00aa0044773d");
  343.         static readonly public Guid TABLE_PRIVILEGES        = new Guid("c8b52222-5cf3-11ce-ade5-00aa0044773d");
  344.         static readonly public Guid USAGE_PRIVILEGES        = new Guid("c8b52223-5cf3-11ce-ade5-00aa0044773d");
  345.         static readonly public Guid PROCEDURES              = new Guid("c8b52224-5cf3-11ce-ade5-00aa0044773d");
  346.         static readonly public Guid SCHEMATA                = new Guid("c8b52225-5cf3-11ce-ade5-00aa0044773d");
  347.         static readonly public Guid SQL_LANGUAGES           = new Guid("c8b52226-5cf3-11ce-ade5-00aa0044773d");
  348.         static readonly public Guid STATISTICS              = new Guid("c8b52227-5cf3-11ce-ade5-00aa0044773d");
  349.         static readonly public Guid TABLES                  = new Guid("c8b52229-5cf3-11ce-ade5-00aa0044773d");
  350.         static readonly public Guid TRANSLATIONS            = new Guid("c8b5222a-5cf3-11ce-ade5-00aa0044773d");
  351.         static readonly public Guid PROVIDER_TYPES          = new Guid("c8b5222c-5cf3-11ce-ade5-00aa0044773d");
  352.         static readonly public Guid VIEWS                   = new Guid("c8b5222d-5cf3-11ce-ade5-00aa0044773d");
  353.         static readonly public Guid VIEW_COLUMN_USAGE       = new Guid("c8b5222e-5cf3-11ce-ade5-00aa0044773d");
  354.         static readonly public Guid VIEW_TABLE_USAGE        = new Guid("c8b5222f-5cf3-11ce-ade5-00aa0044773d");
  355.         static readonly public Guid PROCEDURE_PARAMETERS    = new Guid("c8b522b8-5cf3-11ce-ade5-00aa0044773d");
  356.         static readonly public Guid FOREIGN_KEYS            = new Guid("c8b522c4-5cf3-11ce-ade5-00aa0044773d");
  357.         static readonly public Guid PRIMARY_KEYS            = new Guid("c8b522c5-5cf3-11ce-ade5-00aa0044773d");
  358.         static readonly public Guid PROCEDURE_COLUMNS       = new Guid("c8b522c9-5cf3-11ce-ade5-00aa0044773d");
  359.     }
  360.  
  361.  
  362.  
  363. }
  364.  
  365.  
  366.