I was wondering how I could get the columns of a database table and store each of them in a string or string array. I have the following code but I believe it does not work. I’m using the default table that is given in asp.net. I’ve been able to write to this table no problem but I cannot figure out how to select from it and save the values retrieved. here is what I have in my code behind
string connection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnection conn = null;
conn = new SqlConnection(connection);
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
string query = String.Format("SELECT column_name FROM USER_TAB_COLUMN WHERE table_name = 'TestTable'");
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
columns.Text = cmd.ExecuteNonQuery().ToString();
conn.Close();
}
the error is Invalid object name 'USER_TAB_COLUMN'. I’ve tried removing this and using "SELECT column_name FROM TestTable" then it complains about column_name. Columns is a text box by the way.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You can use DbDateReader.GetSchemaTable.
DataTable schema = null;
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connection))
{
using (var schemaCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM TestTable", con))
{
con.Open();
using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
}
}
foreach (DataRow col in schema.Rows)
{
Console.WriteLine("ColumnName={0}", col.Field<String>("ColumnName"));
}
The column name is in the first column of every row.
I am trying your method but
MySql.Data.MySqlClient.MySqlConnection(connection)) is throwing type
or namespace could not be found
I could have sworn that i have seen MySqlCommand and MySqlConnection. So you are using SQL-Server as rdbms instead?
using (var con = new SqlConnection(connection))
{
using (var schemaCommand = new SqlCommand("SELECT * FROM TestTable;", con))
{
con.Open();
using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
}
}
// DataTable part is the same
Method 2
Unless you’ve defined it, USER_TAB_COLUMN is not a table or view in MySQL.
To get column names, query the information_schema.columns view.
e.g.
to get a list of column names that in the foo.bar table:
SELECT column_name FROM information_schema.columns WHERE table_schema = 'foo' AND table_name = 'bar'
(Since the same table_name can appear in multiple databases, to ensure you will get the column names from a a single table, you’d need to specify the database the table is in. This also improves efficiency of the query, if you have lots of databases, because it limits the databases that MySQL needs to check.)
To check for columns of a table in the “current” database, you can make use of the DATABASE() function:
SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'bar'
(This would be the table referenced by SELECT * FROM bar from the current database connection.)
Method 3
Very Simply U can do it like below code..
DataTable table = new DataTable();
foreach (DataColumn column in table .Columns)
{
Console.Write("Item: ");
Console.Write(column.ColumnName);
Console.Write(" ");
Console.WriteLine(row<div class="su-column su-column-size-1-2"><div class="su-column-inner su-u-clearfix su-u-trim"></div></div>);
}
Method 4
You can get column name by using MySqlDataReader and GetSchemaTable.
Not only column name, you can access almost every information of table.
Code is also very simple and understandable.
string query = String.Format("SELECT * FROM testTable");
MySqlConnection conn = new MySqlConnection(strCon);
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
DataTable schema = rdr.GetSchemaTable();
conn.Close();
foreach (DataRow rdrColumn in schema.Rows)
{
String columnName = rdrColumn[schema.Columns["ColumnName"]].ToString();
String dataType = rdrColumn[schema.Columns["DataType"]].ToString();
}
Method 5
I will show you how to do it with MySQL database table. Similar code structure can be used for your case as well.
Basically you should run “DESC table_name” query. Then you have a table description as a resulting table. You can read dataReader and retrieve whatever information filed from the Table Description. In this example I retrieve column names of the specific DB table. Hope this will save someones time. 🙂 Happy coding!
var tableDesc = "DESC table_name";
try
{
Mclient.MySqlCommand tableDescCmd = new Mclient.MySqlCommand(tableDesc, mCon);
Mclient.MySqlDataReader tableDescDR = tableDescCmd.ExecuteReader();
while (tableDescDR.Read())
{
searchFields.Items.Add(tableDescDR.GetFieldValue<String>(tableDescDR.GetOrdinal("Field")));
}
tableDescDR.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve DB Table fields.n" + ex.Message);
}
Method 6
In addition to previous answers here is another way. For me it works faster (not much) than alternatives with DataReader. “WHERE FALSE” tells to bring no data.
Note that there are no need to iterate through DataTable.Rows as the result DataTable already describes the target table’s schema (see the DataTable.Columns), not the schema of the current DataReader results
DataTable GetDataTableScheme(string tableName)
{
var table = new DataTable();
using (var connection = new MySqlConnection(*[ConnectionString]*))
using (var dataAdapter = new MySqlDataAdapter($"SELECT * FROM {tableName} WHERE FALSE", connection))
{
// Adds additional info, like auto-increment
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dataAdapter.Fill(table);
}
return table;
}
Method 7
private void leavestat()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string query = "SELECT * FROM leaverecord";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Columns.Count; i++)
{
listBox1.Items.Add(dt.Columns[i].ToString());
}
conn.Close();
}
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0