Determining a SQL Server Identity Column Name in .NET

Question

Hi, I'm trying to write some generic code in VB.NET that determines whether or not a SQL server database table contains an identity column and, if so, to return the name of that column.

I'm working in Visual Basic 2008 Express and have created a SQL database, "MyDatabase" with 1 table called "MyTable". Within that table, I've got 3 columns, "ID", "Column1" and "Column2". I know, I know... inventive names. Under column properties in the Database Explorer, I've set the "ID" column "Identity Specification" to "yes" and have set the "Is Identity" value to "yes".

I need for the .NET code to return "ID" as the identity column. Can this be done through LINQ or some other means?

Thanks very much in advance!

Luck

Answer 1

Use SQLDataReader to open MyTable (SELECT ID from myTable WHERE 1 = 2) & use GetSchemaTable method, which will give you the datatable.

As per the docs, check for the content of a column named IsAutoIncrement. If it returns true, it should be an Identity column.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx

Note: This is purely based on my memory & help from the MSDN docs.

Answer 2

If you have a Linq to SQL DataContext, you can obtain table metadata by querying the data context MappingSource:

var ctx = new YourDataContext();
var identityAndKey = ctx.Mapping.MappingSource
                                .GetModel(typeof(YourDataContext)) 
                                .GetMetaType(typeof(YourTable))
                                .DataMembers
                                .SingleOrDefault(i => i.IsDbGenerated &&
                                                      i.IsPrimaryKey);

You will get a MetaDataMember instance, which is the Primary Key and DbGenerated (Identity) property of your data class. The column name is available on the Name property.

With a little bit of reflection, you could be able to re-utilize this code for any table/data context.

Answer 3

You could call sp_columns stored procedure to obtain this informatión and more:

SqlConnection sqlConx = new SqlConnection(p_conectionStrWithDB);
sqlConx.Open();
SqlCommand cmd = new SqlCommand("sp_columns", sqlConx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@table_name", TableName));
DataTable tblColumns = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tblColumns);

Edit: You can also obtain information about primary Keys like this:

SqlConnection sqlConx = new SqlConnection(p_conectionStrWithDB);
sqlConx.Open();
SqlCommand cmd = new SqlCommand("sp_pkeys", sqlConx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@table_name", tableName));
DataTable tblConstraints = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tblConstraints);
return tblConstraints;