|
Anytime, glad to be of help
|
|
|
|
|
With ADO.NET, the DataReader's
GetSchemaTable() method can be used to retrieve column information for a specific table. For example,
DataTable dt = reader.GetSchemaTable();
foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns) {
Console.WriteLine(dc.ColumnName + " = " + dr[dc].ToString());
}
Console.WriteLine();
}
|
|
|
|
|
Thank you for your reply Ginger. I will add your code to my snippets for future use. The simple string from Shameel added to my own standard population method solved the problem for me. Thank you for your input and advice....Best Regards, Pat
|
|
|
|
|
Is it a must in star-schema modeling that we have a single fact table?In essence can we have multiple fact tables in a cube and still arrive at a star-schema model.
Thanks
Current
|
|
|
|
|
|
Dear all,
Please advise how to get the record count of Cursor ?
DECLARE @vendor_cursor CURSOR FOR
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;
OPEN @vendor_cursor;
FETCH NEXT FROM @vendor_cursor
INTO @vendor_id, @vendor_name;
Thanks and best regards
|
|
|
|
|
Do you need the count before, during or after the cursor has run.
Before - use select count(*) with your cursor query
During and After - set up a @Count variable and increment it within your cursor
Set @Count = @Count + 1
This question shows a dramatic lack of thinking, the solution is so simple.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thank you for the reply
@@CURSOR_ROWS works for me.
|
|
|
|
|
Excellent, there is another potentially useful keyword I have never used (or knew about).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI all, I am new to cursor and i have a doubt... I have one vb code in which few sql statements are written. I need to convert these sql codes into pl/sql sotred procedures. But, in vb, the sql query is dynamic on the basis of values passed like..
If Trim(txtEqpId) <> "" Then
Sql = Sql & vbCrLf & " AND c.eqpid LIKE '" & Trim(txtEqpId) & "%'"
End If
This sql query is first generated and then it is being called from vb code.
Now the problem is while creating pl/sql procedures, I need to make a cursor where the cursor definition has to be created dynamically {not in BEGIN block and it is not fixed}...
So, can we add if condition in cursor definition statement or any other way to achieve it...
csetopper_bhanu
|
|
|
|
|
You are looking for a dynamic where clause, take a look at come of these responses[^]
Most of these will work with a cursor.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a query that returns a table something like this:
102
NULL
101
NULL
the 101 & 102 are FKs into another table, and I need to "expand" those... so what I want to end up with is:
102 b
102 a
<some kind="" of="" marker="" to="" indicate="" the="" null="">
101 b
101 a
<some kind="" of="" marker="" to="" indicate="" the="" null="">
so I figured I'd create a cursor on the first table and loop through the 102, 101, etc. and then do an inner select to "expand" the 101 and 102... this works fine... I end up with 4 result sets though. One for the 102, one for the first null, one of the 101 and one for the second null.
The empty result sets are significant to the query... I figured I could insert a dummy record in there with a special ID... unless there is a better way, but not sure how to combine the 4 result sets into a single one with out losing the empty ones?
|
|
|
|
|
I would as a default try and avoid cursors, they really are a last resort tool.
Have you tried UNION or UNION ALL on the 2 queries.
Select This, null That
from SomeTable
Union
Select This, That
from SomeTable
left join ThatTable on ...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear you try.......
you can reduce your null selection by is null clause.
you can use union or union all are two method combine multiple results set.
select 101
union
select null
union
select 102
union
select null
union all result will be as...
null,
null
101
102
101
102
create table temp table
insert into @temptable(ID)
select 101
union
select null
union
select 102
union
select null
select * from @temptable where id is not null
.........................
try this......
|
|
|
|
|
Hello,
I am trying to return the values of the column names from a table in an Oracle 10g database. I have Googled this "ad-nausium" and have found some code which seems to be most preferred:
select column_name from all_tab_columns where table_name = 'TABLE_NAME' order by column_id"
However, this returns the Column Header Stamp "Column Name" for me instead, and I have seen others reply with this same issue; I have not yet found anyone that can explain or follow this up with a correction. I certainly can not figure it out. The complete context code is below as follows:
listViewColumns.Items.Clear();
try
{
OleDbDataAdapter da = new OleDbDataAdapter(Sql, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (object col in dt.Columns)
{
listViewColumns.Items.Add(col.ToString());
}
}
For background, the program is a C# 4.0 Framwork application and I am using an OLEDB provider for Oracle. Some assistance would be greatly appreciated. Thank You, Pat
|
|
|
|
|
Hi,
I don't know for Oracle, however I did similar things with OLEDB for Access, basically it consisted of:
- opening a connection
- creating an OleDbCommand with the appropriate SQL, probably SELECT * FROM tableName
- getting a OleDbDataReader by executing OleDbCommand.ExecuteReader (CommandBehavior.SchemaOnly should suffice);
- getting the table's schema from OleDbDataReader.GetSchemaTable.
- cleaning up.
The schema itself is a DataTable that holds all the field information. You'll have to work out the details and try it on Oracle.
|
|
|
|
|
Thank you for your input Luc. I recall that you have supplied me with some other solutions in times past and I appreciate your input. I will try your scripting later or tomorrow as time permits and post it here if it works. Thanks again...Pat
|
|
|
|
|
You're welcome.
In the mean time I found a matching code snippet:
using (OleDbConnection con=new OleDbConnection(connectionString)) {
con.Open();
using (OleDbCommand cmd=new OleDbCommand("SELECT * FROM ["+tableName+"]", con)) {
DbDataReader rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema=rdr.GetSchemaTable();
foreach (DataColumn col in schema.Columns) log("colName="+col.ColumnName);
}
}
|
|
|
|
|
Luc,
A minute of my time to thank you for your time. I received this solution (below) from Mr Holmes that is short and sweet. I am sure that if I had a better understanding of the "schema" calls, this would have worked as well, but when I ran it, it placed all the schema headings into the listview. I am sure there is some small argument that would fix it, and I should look nto it as this might help with some other calls. Thank you for your help...I hope that you will answer my 'other issues' as they come up. I appreciate your time and skill...Pat
|
|
|
|
|
I have the script in the office that gets the table names of tables and views - if you don't have an answer I'll post it tomorrow.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank You RAH,
I am going to try Luc's Schema solution later and see if it works, but I would still like to have a look at your proven solution. A post tomorrow, as well as your reply today, is much appreciated. Thank You, Pat
|
|
|
|
|
Not sure if this is relevant anymore but here is the code that gets the tables and views from an oracle database. DBOpsO creates a valid oracle connection and executes a sql string to return a datatable.
StringBuilder sSQL = new StringBuilder();
TableDB oTableDB;
sSQL.AppendLine("select T.owner,T.table_name, NVL (V.HasView, 0)HasView");
sSQL.AppendLine("From SYS.ALL_TABLES T ");
sSQL.AppendLine("left join (select owner,view_name, 1 HasView from SYS.ALL_VIEWS where owner in ('SchemaName1','SchemaName2')) V on upper(V.View_Name) = 'VW' || T.table_name ");
sSQL.AppendFormat("and V.owner = T.owner").AppendLine();
sSQL.AppendFormat("where T.owner in ({0})", sSchema.ToString()).AppendLine();
sSQL.AppendLine("order by table_name");
if (oDBOpsO == null || oDBOpsO.Creds.Database != oDB.DatabaseName)
{
oDBOpsO = clsMain.GetDBOpsO(oDB.Server.ServerName, oDB.DatabaseName);
}
DataTable dtData = oDBOpsO.GetTableSQL(sSQL.ToString());
TableDB oTable = new TableDB();
List<TableDB> lTbl = new List<TableDB>();
foreach (DataRow orow in dtData.Rows)
Note I have a convention that all views are prefixed with 'vw'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Another trouble: if there are more than one table visible with thesame name (in other schemas for instance) you got mixed output from those tables. Try use user_tab_columns instead or add next condition to WHERE clause
AND owner = 'myowner'
where 'myowner' is owner name.
---
AW
|
|
|
|
|
I think I found out your problem. Your select statement is correct and should return the names of all columns in your table. You can verify this by running the query in a client tool like SQL * Plus or TOAD.
It's how you bind the data table to the ListView that makes the difference. Instead of querying the DataTable's columns, you should query its rows because the column information is returned as rows.
Try this:
foreach (DataRow row dt.Rows) {
listViewColumns.Items.Add(row[0].ToString());
}
modified on Sunday, August 28, 2011 6:22 AM
|
|
|
|