mysql Retrieving the Columns of the table with GetSchema

gpfsuwkq 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(187)

I would like to retrieve a list of the columns for all the tables in the list matchingTableList:

foreach (var table in matchingTableList)
{
    Console.WriteLine("Analyzing " + table);
    var sourceTableRows = sqlSourceConnection.GetSchema("Columns", new[] {SourceDatabase, null, table, null});
    var destTableRows = sqlDestConnection.GetSchema("Columns", new[] { DestDatabase, null, table, null });
    Console.WriteLine("Number of fields " + sourceTableRows.Rows.Count);
    //iterate through every field in the table
    foreach (var row in sourceTableRows.Rows)
    {
        Console.WriteLine("Analyzing col " + row);
        //if the field is not present, add it to the list containing the added fields for the current table
        if (destTableRows.Columns.Contains((row.ToString()))) continue;
        if (!changedTablesAddedFields[table].Contains(row.ToString()))
        {
            changedTablesAddedFields[table].Add(row.ToString());
        }
    }
}

The count of the sourceTableRows.Rows is always 0, even if the tables have columns in the database. What am I missing?
EDIT1: I can retrieve the values when I do

var sourceTableRows = sqlSourceConnection.GetSchema("Columns", new[] {null, null, table, null});
var destTableRows = sqlDestConnection.GetSchema("Columns", new[] { null, null, table, null });

But why does it work like that?
EDIT2: I found out that this is the syntax for MSSQL:

var sourceTableRows = sqlSourceConnection.GetSchema("Columns", new[] {SourceDatabase, null, table, null});
var destTableRows = sqlDestConnection.GetSchema("Columns", new[] { DestDatabase, null, table, null });

While MySQL requires this:

var sourceTableRows = sqlSourceConnection.GetSchema("Columns", new[] { null, SourceDatabase, table, null});
var destTableRows = sqlDestConnection.GetSchema("Columns", new[] { null, DestDatabase, table, null });

Still, when I try to get the name of the column with

var rowName = (string) row[2];

The only thing I get is the name of the table. I debugged the application and I cannot find the columns name in the row variable. What am I missing?

mysql

来源：https://stackoverflow.com/questions/24415102/retrieving-the-columns-of-the-table-with-getschema

1条答案

按热度按时间

enyaitl31#

Try the following code snippet (used for SqlCE but applicable to other versions of SQL Server: just use the corresponding SQL objects). Also, pay attention to the comments as they pretty much explain the code operational logic:

// `ConnectionString` specifies the Database
        SqlCeConnection connectionSql = new SqlCeConnection(ConnectionString);
        connectionSql.Open();

        // create new `DataAdapter` on `connectionSql` and Your `SelectQuery` text
        SqlCeDataAdapter dataAdapterSql = new SqlCeDataAdapter();
        dataAdapterSql.SelectCommand = new SqlCeCommand(SelectQuery, connectionSql);

        // create DataSet
        DataSet dataSet= new DataSet();

        // retrieve TableSchema
        DataTable[] _dataTablesSchema = dataAdapterSql.FillSchema(dataSet, SchemaType.Source);

        // there is only one Table in DataSet, so use 0-index
        DataTable  sourceDataTable = _dataTablesSchema[0];

        // use DataAdapter to Fill Dataset
        dataAdapterSql.Fill(sourceDataTable );

You can also use a short-cut to shorten the code:

// there is only one Table in DataSet, so use 0-index
    DataTable sourceDataTable= = dataAdapterSql.FillSchema(dataSet, SchemaType.Source)[0];

Hope this will help. Rgds,
PS. In your own code, as a debugging recommendation: put a break line and make sure that sourceDataTable !=null

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Retrieving the Columns of the table with GetSchema

1条答案

相关问题

热门标签

最新问答