locked
Getting the Table Schema RRS feed

  • Question

  • Given a table name (e.g. VOUCHERS), how do I get column names of the table
    and the data types of the columns the table has?

    I am using TALLY and would like to get that info from my C# code

    TallyCollectionConnection = new OdbcConnection("Dsn=TallyODBC_9002;uid=SKracker;pwd=****;");
                    TallyCollectionConnection.Open();

    List<string> TallyTablename= new List<string>();
                    DataTable TallyTables = TallyCollectionConnection.GetSchema("Tables");
                    
                    foreach (DataRow row in TallyTables.Rows)
                    {
                      foreach (DataColumn col in TallyTables.Columns)
                       {
                           if (col.ColumnName == "TABLE_NAME")
                               TallyTablename.Add(row[col].ToString());
                      }
                    }
                    string[] NameTable=TallyTablename.ToArray();
                    DataTable TallyColumnTables = TallyCollectionConnection.GetSchema("Column",new string[]{"NameTable[2]"});
    

    Voucher is present in the NameTable...ie NameTable[2]

    Please help me I'm stuck in this


    Saturday, March 3, 2012 7:27 AM

Answers

  • found out a way...--------------------------------To Get Columns--------------

    string[] NameTable=TallyTablename.ToArray();
                    OdbcCommand TallyDBTCommand = new OdbcCommand("SELECT * FROM " + NameTable[2], TallyCollectionConnection);
                   
                    OdbcDataAdapter TaAdapter = new OdbcDataAdapter(TallyDBTCommand);
                       DataSet ColumnTable = new DataSet();
                       TaAdapter.FillSchema(ColumnTable,SchemaType.Source);
                    

    --------------------------------------To get the column properties-------------------

    OdbcDataReader TallyReader = TallyDBTCommand.ExecuteReader();               
                  
                    DataTable ColumnTable = TallyReader.GetSchemaTable();
    However if you know a better way, or any other way please share it with me...

    Thank you in Advance ,


    $Kracker


    • Edited by SKracker Saturday, March 3, 2012 12:55 PM Spelling mistake
    • Marked as answer by SKracker Wednesday, March 28, 2012 9:34 AM
    Saturday, March 3, 2012 12:54 PM

All replies

  • found out a way...--------------------------------To Get Columns--------------

    string[] NameTable=TallyTablename.ToArray();
                    OdbcCommand TallyDBTCommand = new OdbcCommand("SELECT * FROM " + NameTable[2], TallyCollectionConnection);
                   
                    OdbcDataAdapter TaAdapter = new OdbcDataAdapter(TallyDBTCommand);
                       DataSet ColumnTable = new DataSet();
                       TaAdapter.FillSchema(ColumnTable,SchemaType.Source);
                    

    --------------------------------------To get the column properties-------------------

    OdbcDataReader TallyReader = TallyDBTCommand.ExecuteReader();               
                  
                    DataTable ColumnTable = TallyReader.GetSchemaTable();
    However if you know a better way, or any other way please share it with me...

    Thank you in Advance ,


    $Kracker


    • Edited by SKracker Saturday, March 3, 2012 12:55 PM Spelling mistake
    • Marked as answer by SKracker Wednesday, March 28, 2012 9:34 AM
    Saturday, March 3, 2012 12:54 PM
  • Hello,

    The following provides English names for data types, if that is an issue simply remove the code for that part (in regards to the for-each setting values for DataType column added after the table schema is returned).

    //INSTANT C# TODO TASK: Insert the following converted event handler wireups at the end of the 'InitializeComponent' method for forms, 'Page_Init' for web pages, or into a constructor for other classes:
    Button1.Click += new System.EventHandler(Button1_Click);
    //INSTANT C# NOTE: These were formerly VB static local variables:
    private string[] GetOleDbTypeNamesValues_Names;
    private void Button1_Click(object sender, System.EventArgs e)
    {
    	OleDb.OleDbConnectionStringBuilder cb = new OleDb.OleDbConnectionStringBuilder {DataSource = "Database1.accdb", Provider = "Microsoft.ACE.OLEDB.12.0"};
    	var dt = GetOledbSchema(cb.ConnectionString, "Company", htTypes);
    	DataGridView2.DataSource = dt;
    	// For showing our results
    	DataGridView2.Columns("Column_Name").HeaderText = "Column";
    	DataGridView2.Columns("is_nullable").HeaderText = "Nullable";
    	DataGridView2.Columns("Character_Maximum_Length").HeaderText = "Max Len";
    	DataGridView2.Columns("Description").HeaderText = "Description";
    	DataGridView2.Columns("Description").DisplayIndex = 1;
    	DataGridView2.Columns("DataType").DisplayIndex = 2;
    	DataGridView2.Columns("is_nullable").DisplayIndex = 3;
    	DataGridView2.Columns("is_nullable").DisplayIndex = 4;
    }
    public Hashtable GetOleDbTypeNamesValues(bool DisplayInformation)
    {
    //INSTANT C# NOTE: VB local static variable moved to class level
    //	Static Names As String()
    	int[] Values = null;
    	Hashtable FieldTypes = new Hashtable();
    	if (GetOleDbTypeNamesValues_Names == null)
    	{
    		GetOleDbTypeNamesValues_Names = (string[])(Enum.GetNames(typeof(OleDbType)));
    		Values = (int[])(Enum.GetValues(typeof(OleDbType)));
    		for (int Row = 0; Row < GetOleDbTypeNamesValues_Names.Count; Row++)
    		{
    			FieldTypes.Add(Values[Row], GetOleDbTypeNamesValues_Names(Row));
    		}
    	}
    	return FieldTypes;
    }
    public DataTable GetOledbSchema(string ConnectionString, string TableName, Hashtable FieldTypes)
    {
    	using (OleDbConnection cn = new OleDbConnection(ConnectionString))
    	{
    		DataTable Table = new DataTable {TableName = string.Format("tbl{0}", TableName)};
    		cn.Open();
    		Table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] {null, null, TableName, null});
    		DataTable dt = Table.DefaultView.ToTable(string.Format("{0}_schema", TableName), true, new string[] {"Column_Name", "Data_Type", "is_nullable", "Character_Maximum_Length", "Description"});
    		dt.Columns.Add(new DataColumn {ColumnName = "DataType", DataType = typeof(System.String)});
    		cn.Close();
    		foreach (DataRow row in dt.Rows)
    		{
    			row["DataType"] = FieldTypes[System.Convert.ToInt32(row["Data_Type"])];
    		}
    		dt.AcceptChanges();
    		dt.Columns["Data_Type"].ColumnMapping = MappingType.Hidden;
    		return dt;
    	}
    }

    Just now realized you are working with ODBC and not OleDb provider so this most likely will not assist.

    KSG


    Saturday, March 3, 2012 1:18 PM
  • Thanks Kevin,

    Anyhow I'll try... who knows Tally might provide me more information than I currently have...

    I searched it on

    http://msdn.microsoft.com/en-us/library/ms254969%28VS.80%29.aspx

    Information is only on Oracle and MS-SQL and no other ODBC's are explained

    Any how I'm open to new tryouts and work-arounds which will make my life easy(efficent to be precise)...


    $Kracker

    Saturday, March 3, 2012 1:36 PM
  • Out of all the searches in WWW, this is the ONE answer which helped me. Thanks!

    PL

    Wednesday, December 5, 2012 5:05 PM
  • One easy way if you have MS Excel is to import data to MS Excel using ODBC through the DSN. You will see the full list of collections exposed by Tally. After importing a table you can see all column names & data as well.

    PL

    Thursday, December 6, 2012 12:18 PM