SQL Question 1

[mrscary]

Using SQL server and ADO.NET, how can I get the column names and the type that they store in a particular table?

Many thanks.
Mark.

Hollingside Technologies, Making Technology work for you.

http://www.hollingside.com

(No, I am not scary at all! See

http://www.stircrazy.org)

 

[onpnt]

Use the sp_columns procedure

sp_columns reference

further info on T-SQL procedures and such would be better suited for the SQL Server programming forum (forum 183)

___________________________________________________________________

_{onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811}

 

[obislavu]

Another easy way is to use "SELECT TOP 0 ..." and iterate through all Ciolumns collection , like here:

string connString="user id=JoeBlue;Password=admin;Initial Catalog=myDB;Data Source=serverax790;Connect Timeout=30" ;
SqlConnection con = null;
SqlCommand oCommand= null;
SqlDataAdapter DataAdapter = null;
// Retreive Schema to get columns properties
/////////////////////////////////////////////
SqlDataAdapter DataAdapter	= new SqlDataAdapter();
DataTable dtTableForSchema	= new DataTable("TableForScheme");
try	
{
	con = new SqlConnection(connString);
	con.Open();
	oCommand= new SqlCommand();
	oCommand.Connection		= con;
	DataAdapter	= new SqlDataAdapter();
	DataTable dtSchema	= new DataTable();
	oCommand.CommandText = "SELECT TOP 0 * FROM " + "MySourceTable";
	DataAdapter.SelectCommand = oCommand;
	DataAdapter.FillSchema(dtSchema,SchemaType.Source);
	
	foreach(DataColumn Col in SourceTable.Columns)
	{
	    string sColName = Col.ColumnName;
	    System.Type type = Col.DataType;
	}
}
catch(Exception e)
{
	// Process error		
}
finally 
{
   if (con !=null)
   {
      con.Close();
      con.Dispose();
    }
   // same for oCommand and DataAdapter;
}

-obislavu-

 

[mrscary]

Many thanks. Have a star.

Hollingside Technologies, Making Technology work for you.

http://www.hollingside.com

(No, I am not scary at all! See

http://www.stircrazy.org)