GetSchema VB Subroutine

by Bill Dowling 30. July 2010 13:56
This VB Subroutine, gets Schema information (Column Name, Type, Size) from an Access DB Table. Private m_connDB As OleDbConnection = Nothing ' Current DB Connection Private m_sSchemaTable(1, 2) As String ' Table Schema Info Private Enum eSchemaFields As Integer ColumnName = 0 ColumnSize = 1 DataType = 2 End Enum ''' <summary> ''' Get the Field Definition for Record ''' </summary> ''' <param name="_TableName">Name of Table for Schema</param> ''' <param name="_SchemaTable">String Array to Save data to</param> ''' <remarks>The Schema Table must be ByRef NOT ByVal</remarks> Private Sub GetSchema(ByVal _TableName As String, _ ByRef _SchemaTable(,) As String) Dim sSQL As String = "SELECT * FROM " + _TableName + ";" Dim cmd As New OleDbCommand cmd.Connection = m_connDB cmd.CommandText = sSQL '********************************************************* ' Get the column and primary key information for our table '********************************************************* Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo) Dim dt As DataTable = rdr.GetSchemaTable rdr.Close() ReDim _SchemaTable(dt.Rows.Count - 1, 2) Dim nIndex As Integer = 0 'For Each dc As DataColumn In dt.Columns ' Debug.Print(dc.ColumnName) 'Next '************************************************************** ' Each DataRow is a column in our table, save what info we need '************************************************************** For Each dr As DataRow In dt.Rows _SchemaTable(nIndex, eSchemaFields.ColumnName) = dr.Item("ColumnName").ToString _SchemaTable(nIndex, eSchemaFields.ColumnSize) = dr.Item("ColumnSize").ToString _SchemaTable(nIndex, eSchemaFields.DataType) = dr.Item("DataType").ToString nIndex += 1 Next End Sub

Tags:

.Net Coding Examples | Access DB

SQLdbCopy Tech Info

by Bill Dowling 28. July 2010 06:19
Different SQL Server statements need to be performed depending on what version of SQL Server you are running. Below are some of the statements used in SQLdbCopy to perform specific tasks, both for SQL Server and Visual Basic with Visual Studio 2005 and 2010.   Sql Server 2000 Version 8 Sql Server 2005 Version 9 Sql Server 2008 Version 10   OleDB Provider statement for Access Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};  [{0} = Full Filename] .Net Date/Time Formatting MM/dd/yyyy HH:mm:ss .Net List of SQL Servers Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources 2000 List Databases SELECT * FROM dbo.sysdatabases; 2005 List Databases SELECT * FROM sys.sysdatabases; 2000 List Tables SELECT u.name AS SchemaName, o.name AS TableName , SUM(CASE WHEN (c.status & 0x80 = 0x80) THEN 1 ELSE 0 END) AS SetIdentity FROM sysobjects AS o INNER JOIN sysusers AS u ON o.uid = u.uid INNER JOIN syscolumns AS c ON o.id = c.id WHERE (o.xtype = 'U') AND (o.name NOT LIKE 'dtproperties') AND (o.name NOT LIKE 'tmp%') AND (o.name NOT LIKE 'sys%') GROUP BY u.name, o.name ORDER BY u.name, o.name; 2005 List Tables SELECT s.name AS SchemaName, t.name AS TableName, SUM(CASE WHEN (c.is_identity = 1) THEN 1 ELSE 0 END) AS SetIdentity FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.columns AS c on t.object_id = c.object_id WHERE (t.type = 'U') AND (t.name NOT LIKE 'dtproperties') AND (t.name NOT LIKE 'tmp%') AND (t.name NOT LIKE 'sys%') GROUP BY s.name, t.name ORDER BY s.name, t.name;td> 2000 List Primary Key SELECT u.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ik.keyno AS ColumnNum, c.name AS ColumnName FROM dbo.sysobjects AS t INNER JOIN dbo.sysindexes AS ix ON t.id = ix.id INNER JOIN dbo.sysindexkeys AS ik ON ix.id = ik.id AND ix.indid = ik.indid INNER JOIN dbo.syscolumns AS c ON ik.id = c.id AND ik.colid = c.colid INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE ((ix.status & 2048) <> 0) AND (u.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ik.keyno;  [{0}=SchemaName, {1}=TableName] 2005 List Primary Key SELECT s.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ic.index_column_id AS ColumnNum, c.name AS ColumnName FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.object_id = ix.object_id INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE (ix.is_primary_key = 1) AND (s.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ic.index_column_id;  [{0}=SchemaName, {1}=TableName] 2000 List Columns SELECT u.name AS SchemaName, t.name AS TableName, c.colid AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.length AS MaxLength, c.prec AS ColumnPrec, c.scale AS ColumnScale FROM dbo.syscolumns AS c INNER JOIN dbo.sysobjects AS t ON c.id = t.id INNER JOIN dbo.systypes AS dt On c.xusertype = dt.xusertype INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE (u.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.colid;  [{0}=SchemaName, {1}=TableName] 2005 List Columns SELECT s.name AS SchemaName, t.name AS TableName, c.column_id AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.max_length AS MaxLength, c.precision AS ColumnPrec, c.scale AS ColumnScale FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.object_id = c.object_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.types AS dt ON c.system_type_id = dt.user_type_id WHERE (s.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.column_id;  [{0}=SchemaName, {1}=TableName] We have added a SetIdentity field in the "List Tables" code to identify tables that have a column with "IDENTITY" set. This allows us to identify which tables need "SET IDENTITY_INSERT [schemaname].[tablename] On/Off" statements when inserting data. For Access Database output, we currently process the following SQL data types and what ADO format we convert them to. If a character (adVarWChar) based field is larger then 255 characters, it will be converted to an Access Memo (adLongVarWChar) field.   SQL TypeDataTypeEnumProcedure TypeOleDbType char adVarWChar TEXT VarWChar nchar adVarWChar TEXT VarWChar text adLongVarWChar MEMO LongVarWChar ntext adLongVarWChar MEMO LongVarWChar varchar adVarWChar TEXT VarWChar nvarchar adVarWChar TEXT VarWChar bigint adNumeric NUMERIC Numeric int adInteger LONG Integer smallint adSmallInt INTEGER SmallInt tinyint adUnsignedTinyInt NUMERIC UnsignedTinyInt bit adBoolean BIT Boolean decimal adNumeric NUMERIC Numeric numeric adNumeric NUMERIC Numeric money adCurrency CURRENCY Currency smallmoney adCurrency CURRENCY Currency float adDouble DOUBLE Double real adDouble DOUBLE Double date adDate DATE Date datetime adDate DATE Date smalldatetime adDate DATE Date binary adLongVarBinary BINARY LongVarBinary varbinary adLongVarBinary LONGBINARY LongVarBinary image adLongVarBinary LONGBINARY LongVarBinary timestamp adVarBinary BINARY VarBinary uniqueidentifier adGUID GUID Guid cursor not translated sql_variant not translated table not translated xml adLongVarWChar MEMO LongVarWChar

Tags: , ,

Access DB | SQL Coding Examples

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List