Getting column names of a table in C# using OleDB

[Joe]

Hi All,

I am new to using the Access DB and I need some help if someone
is able to give it to me. What I want to do is get the names of
the columns of certain tables. Not the data in the table but the
table column names.

I've seen other posts that suggest using the SQL command DESCRIBE
but I can't get it to work for some reason. Other posts have code
samples but they're written in VB which I am not familiar with. I
am looking for a piece of sample code written in C# using the
OleDbConnection classes, if that is possible. An SQL command would
be great but from what I've read there is no DESCRIBE command or
an equivalent. Can someone help me out?

Thanks in advance for any help or direction!

Sincerely,
Joe

[Salad]

Re: Getting column names of a table in C# using OleDB

Joe wrote:
[color=blue]
> Hi All,
>
> I am new to using the Access DB and I need some help if someone
> is able to give it to me. What I want to do is get the names of
> the columns of certain tables. Not the data in the table but the
> table column names.
>
> I've seen other posts that suggest using the SQL command DESCRIBE
> but I can't get it to work for some reason. Other posts have code
> samples but they're written in VB which I am not familiar with. I
> am looking for a piece of sample code written in C# using the
> OleDbConnection classes, if that is possible. An SQL command would
> be great but from what I've read there is no DESCRIBE command or
> an equivalent. Can someone help me out?
>
> Thanks in advance for any help or direction!
>
> Sincerely,
> Joe[/color]

This link may help. I've cut some of the code out from one of the
response that may help you. I think part of it should show you the
properties in the collection you need. Not my forte tho.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=uspIcgx5DHA.1804%40TK2MSFTNGP12.phx.gbl&rnum=7&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26q%3DC%2523%2BOleDbConnection%2Btable%2Bfield%2Bnames%26btnG%3DSearch

Option Strict On
Module Main
'microsoft adox ext 2.7 for dll and security
Public Sub Main()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File. Exists("C:\db1. mdb") Then
System.IO.File. Delete("C:\db1. mdb")
System.IO.File. Delete("C:\db2. mdb")
End If
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db1.m db")
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db2.m db")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConn ection = New OleDb.OleDbConn ection
Dim conn2 As OleDb.OleDbConn ection = New OleDb.OleDbConn ection
conn1.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=C:\db1.m db;User Id=admin;Passwo rd=;"
conn2.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=C:\db2.m db;User Id=admin;Passwo rd=;"
conn1.Open()
Dim cmd1 As New OleDb.OleDbComm and( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNon Query()
conn2.Open()
Dim cmd2 As New OleDb.OleDbComm and( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
cmd2.ExecuteNon Query()
Try
For i As Integer = 1 To 9
cmd1.Parameters .Clear()
cmd1.CommandTex t = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters .Add(New OleDb.OleDbPara meter("@a",
OleDb.OleDbType .Integer)).Valu e = i
cmd1.Parameters .Add(New OleDb.OleDbPara meter("@b",
OleDb.OleDbType .Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNon Query()
Next
For i As Integer = 4 To 12 Step 2
cmd2.Parameters .Clear()
cmd2.CommandTex t = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd2.Parameters .Add(New OleDb.OleDbPara meter("@a",
OleDb.OleDbType .Integer)).Valu e = i
cmd2.Parameters .Add(New OleDb.OleDbPara meter("@b",
OleDb.OleDbType .Char, 20)).Value = Chr(75 + i)
cmd2.ExecuteNon Query()
Next
cmd1.CommandTex t = "Select * from tbl1"
Dim da1 As OleDb.OleDbData Adapter = New
OleDb.OleDbData Adapter(cmd1)
Dim ds1 As DataSet = New DataSet
Dim dt1 As New DataTable("tbl1 ")
Dim dca As New DataColumn("a", Type.GetType("S ystem.Int32"))
Dim dcb As New DataColumn("b", Type.GetType("S ystem.String"))
dt1.Columns.Add (dca)
dt1.Columns.Add (dcb)
ds1.Tables.Add( dt1)
cmd1.CommandTex t = "Select a, b from tbl1"
Dim rdr As OleDb.OleDbData Reader
rdr = cmd1.ExecuteRea der()
While rdr.Read()
Dim dr As DataRow
dr = ds1.Tables(0).N ewRow
dr("a") = rdr.GetInt32(0)
dr("b") = rdr.GetString(1 )
ds1.Tables(0).R ows.Add(dr)
End While
rdr.Close()
ds1.AcceptChang es()
cmd2.CommandTex t = "Select * from tbl1"
cmd2.Connection = conn2
Dim da2 As OleDb.OleDbData Adapter = New
OleDb.OleDbData Adapter(cmd2)
Dim cmb2 As OleDb.OleDbComm andBuilder = New
OleDb.OleDbComm andBuilder(da2)
Dim ds2 As DataSet = New DataSet
conn2.Close()
da2.Fill(ds2)
For i As Integer = 0 To ds1.Tables(0).R ows.Count - 1
Dim swN As Boolean = True
For y As Integer = 0 To ds2.Tables(0).R ows.Count - 1
Dim a As Integer = ds2.Tables(0).R ows.Count - 1
If CInt(ds1.Tables (0).Rows(i).Ite m(0)) =
CInt(ds2.Tables (0).Rows(y).Ite m(0)) Then
ds2.Tables(0).R ows(y).Item(1) =
ds1.Tables(0).R ows(i).Item(1)
swN = False
Exit For
End If
Next
If swN Then
Dim dr As DataRow
dr = ds2.Tables(0).N ewRow
For y As Integer = 0 To
ds1.Tables(0).R ows(i).ItemArra y.Length - 1
dr(y) = ds1.Tables(0).R ows(i).ItemArra y(y)
Next
ds2.Tables(0).R ows.Add(dr)
End If
Next
da2.Update(ds2)
Catch ex As Exception
MessageBox.Show (ex.ToString)
End Try
conn1.Close()
conn2.Close()
End Sub
End Module

[Joe]

Re: Getting column names of a table in C# using OleDB

Salad <oil@vinegar.co m> wrote in message news:<mxNtc.148 27$be.7102@news read2.news.pas. earthlink.net>. ..[color=blue]
> Joe wrote:
>[color=green]
> > Hi All,
> >
> > I am new to using the Access DB and I need some help if someone
> > is able to give it to me. What I want to do is get the names of
> > the columns of certain tables. Not the data in the table but the
> > table column names.
> >
> > I've seen other posts that suggest using the SQL command DESCRIBE
> > but I can't get it to work for some reason. Other posts have code
> > samples but they're written in VB which I am not familiar with. I
> > am looking for a piece of sample code written in C# using the
> > OleDbConnection classes, if that is possible. An SQL command would
> > be great but from what I've read there is no DESCRIBE command or
> > an equivalent. Can someone help me out?
> >
> > Thanks in advance for any help or direction!
> >
> > Sincerely,
> > Joe[/color]
>
> This link may help. I've cut some of the code out from one of the
> response that may help you. I think part of it should show you the
> properties in the collection you need. Not my forte tho.
>
> http://groups.google.com/groups?hl=e...6btnG%3DSearch
>
> Option Strict On
> Module Main
> 'microsoft adox ext 2.7 for dll and security
> Public Sub Main()
> Dim catNewDB As ADOX.Catalog
> catNewDB = New ADOX.Catalog
> If System.IO.File. Exists("C:\db1. mdb") Then
> System.IO.File. Delete("C:\db1. mdb")
> System.IO.File. Delete("C:\db2. mdb")
> End If
> catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
> Source=C:\db1.m db")
> catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
> Source=C:\db2.m db")
> catNewDB = Nothing
> catNewDB = Nothing
> Dim conn1 As OleDb.OleDbConn ection = New OleDb.OleDbConn ection
> Dim conn2 As OleDb.OleDbConn ection = New OleDb.OleDbConn ection
> conn1.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
> Source=C:\db1.m db;User Id=admin;Passwo rd=;"
> conn2.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
> Source=C:\db2.m db;User Id=admin;Passwo rd=;"
> conn1.Open()
> Dim cmd1 As New OleDb.OleDbComm and( _
> "CREATE TABLE tbl1 (a int NOT NULL," & _
> "b Char(20)," & _
> "CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
> cmd1.ExecuteNon Query()
> conn2.Open()
> Dim cmd2 As New OleDb.OleDbComm and( _
> "CREATE TABLE tbl1 (a int NOT NULL," & _
> "b Char(20)," & _
> "CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
> cmd2.ExecuteNon Query()
> Try
> For i As Integer = 1 To 9
> cmd1.Parameters .Clear()
> cmd1.CommandTex t = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
> cmd1.Parameters .Add(New OleDb.OleDbPara meter("@a",
> OleDb.OleDbType .Integer)).Valu e = i
> cmd1.Parameters .Add(New OleDb.OleDbPara meter("@b",
> OleDb.OleDbType .Char, 20)).Value = Chr(64 + i)
> cmd1.ExecuteNon Query()
> Next
> For i As Integer = 4 To 12 Step 2
> cmd2.Parameters .Clear()
> cmd2.CommandTex t = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
> cmd2.Parameters .Add(New OleDb.OleDbPara meter("@a",
> OleDb.OleDbType .Integer)).Valu e = i
> cmd2.Parameters .Add(New OleDb.OleDbPara meter("@b",
> OleDb.OleDbType .Char, 20)).Value = Chr(75 + i)
> cmd2.ExecuteNon Query()
> Next
> cmd1.CommandTex t = "Select * from tbl1"
> Dim da1 As OleDb.OleDbData Adapter = New
> OleDb.OleDbData Adapter(cmd1)
> Dim ds1 As DataSet = New DataSet
> Dim dt1 As New DataTable("tbl1 ")
> Dim dca As New DataColumn("a", Type.GetType("S ystem.Int32"))
> Dim dcb As New DataColumn("b", Type.GetType("S ystem.String"))
> dt1.Columns.Add (dca)
> dt1.Columns.Add (dcb)
> ds1.Tables.Add( dt1)
> cmd1.CommandTex t = "Select a, b from tbl1"
> Dim rdr As OleDb.OleDbData Reader
> rdr = cmd1.ExecuteRea der()
> While rdr.Read()
> Dim dr As DataRow
> dr = ds1.Tables(0).N ewRow
> dr("a") = rdr.GetInt32(0)
> dr("b") = rdr.GetString(1 )
> ds1.Tables(0).R ows.Add(dr)
> End While
> rdr.Close()
> ds1.AcceptChang es()
> cmd2.CommandTex t = "Select * from tbl1"
> cmd2.Connection = conn2
> Dim da2 As OleDb.OleDbData Adapter = New
> OleDb.OleDbData Adapter(cmd2)
> Dim cmb2 As OleDb.OleDbComm andBuilder = New
> OleDb.OleDbComm andBuilder(da2)
> Dim ds2 As DataSet = New DataSet
> conn2.Close()
> da2.Fill(ds2)
> For i As Integer = 0 To ds1.Tables(0).R ows.Count - 1
> Dim swN As Boolean = True
> For y As Integer = 0 To ds2.Tables(0).R ows.Count - 1
> Dim a As Integer = ds2.Tables(0).R ows.Count - 1
> If CInt(ds1.Tables (0).Rows(i).Ite m(0)) =
> CInt(ds2.Tables (0).Rows(y).Ite m(0)) Then
> ds2.Tables(0).R ows(y).Item(1) =
> ds1.Tables(0).R ows(i).Item(1)
> swN = False
> Exit For
> End If
> Next
> If swN Then
> Dim dr As DataRow
> dr = ds2.Tables(0).N ewRow
> For y As Integer = 0 To
> ds1.Tables(0).R ows(i).ItemArra y.Length - 1
> dr(y) = ds1.Tables(0).R ows(i).ItemArra y(y)
> Next
> ds2.Tables(0).R ows.Add(dr)
> End If
> Next
> da2.Update(ds2)
> Catch ex As Exception
> MessageBox.Show (ex.ToString)
> End Try
> conn1.Close()
> conn2.Close()
> End Sub
> End Module[/color]



Hi Salad,

Thanks you for the reply and you help. After some more research, I
was able to find a solution. I will post it here for anyone else who
may be looking into the same problem. This little snipet of code gets
the name of the table, does some filtering, and gets the column name
and its type in its ordinal position. In other words, the columns
wind up being in the order in which they were created. If you do not
end up doing this sorting the columns wind up in aphabetical order.
The columns data type is stored as an integer and I don't know what
the correct mapping it so I just store the info as such in a class
called TableInfo that I created. Anyway, here it is.


private void GetTableInfo()
{
DataRowView drvCols = null;
DataTable dtCols = null;
DataView dvColumns = null;
TableInfo tabInfoObj = null;
string sTableNm = null;
string sNamePrefix = null;
string sColName = null;
int nColType = 0;

// Get the list of tables that there are.
DataTable schemaTab =
m_oleDbSrcConn. GetOleDbSchemaT able(OleDbSchem aGuid.Tables,
new Object[] {null, null, null, "TABLE"});

for (int i = 0; i < schemaTab.Rows. Count; i++)
{
sTableNm = schemaTab.Rows[i].ItemArray[2].ToString();
sNamePrefix = sTableNm.Substr ing(0, 4);

if ((sTableNm != "_NEW_TABLE S") && (sNamePrefix != "temp"))
{
tabInfoObj = new TableInfo(sTabl eNm);

// Get the column name and its type and sort them
// according to their position in the table.
dtCols = m_oleDbSrcConn. GetOleDbSchemaT able(
OleDbSchemaGuid .Columns,
new Object[]{null,null, sTableNm, null});
dvColumns = new DataView(dtCols );
dvColumns.Sort = "ORDINAL_POSITI ON";

// Get the column names and their types.
for (int j=0; j< dvColumns.Count ; j++)
{
// Get the name of the column.
drvCols = dvColumns[j];
sColName = drvCols.Row.Ite mArray[3].ToString();

// Get columns data type code and save it off.
nColType = Convert.ToInt32 (drvCols.Row.It emArray[11]);
tabInfoObj.AddC olumn(sColName, nColType);
m_alTables.Add( tabInfoObj);
}
} // if

} // for
}


I hope this helps someone!

Later,
Joe