Check if a table or field exists in a database
来源:互联网 发布:java工程师的发展方向 编辑:程序博客网 时间:2024/05/23 11:43
Function to check if a table or field exists in an MS Access or SQL Server database.
Introduction
These are two functions I wrote in VB.NET using ADO.NET to check and see if a table or a field exists in a database. It can work with MS Access, or SQL Server, or any other OLE database.
Background
Checking if a table or a field exists in an Access database should be a very simple task, but it can become very complicated with ADO.NET. With DAO or ADO in VB 6.0, this was an extremely easy task. Those who have used it will agree. So, I am posting these functions to help other programmers. Hopefully, it will help out some.
Using the code
Here are the functions:
''' <summary>''' Checks to see if a table exists in Database or not.''' </summary>''' <param name="tblName">Table name to check</param>''' <param name="cnnStr">Connection String to connect to</param>''' <returns>Works with Access or SQL</returns>''' <remarks></remarks>Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean ' For Access Connection String, ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ' accessFilePathAndName ' Open connection to the database Dim dbConn As New OleDbConnection(cnnStr) dbConn.Open() ' Specify restriction to get table definition schema ' For reference on GetSchema see: ' http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx Dim restrictions(3) As String restrictions(2) = tblName Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions) If dbTbl.Rows.Count = 0 Then 'Table does not exist DoesTableExist = False Else 'Table exists DoesTableExist = True End If dbTbl.Dispose() dbConn.Close() dbConn.Dispose()End Function''' <summary>''' Checks to see if a field exists in table or not.''' </summary>''' <param name="tblName">Table name to check in</param>''' <param name="fldName">Field name to check</param>''' <param name="cnnStr">Connection String to connect to</param>''' <returns></returns>''' <remarks></remarks>Public Function DoesFieldExist(ByVal tblName As String, _ ByVal fldName As String, _ ByVal cnnStr As String) As Boolean ' For Access Connection String, ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ' accessFilePathAndName ' Open connection to the database Dim dbConn As New OleDbConnection(cnnStr) dbConn.Open() Dim dbTbl As New DataTable ' Get the table definition loaded in a table adapter Dim strSql As String = "Select TOP 1 * from " & tblName Dim dbAdapater As New OleDbDataAdapter(strSql, dbConn) dbAdapater.Fill(dbTbl) ' Get the index of the field name Dim i As Integer = dbTbl.Columns.IndexOf(fldName) If i = -1 Then 'Field is missing DoesFieldExist = False Else 'Field is there DoesFieldExist = True End If dbTbl.Dispose() dbConn.Close() dbConn.Dispose()End Function
0 0
- Check if a table or field exists in a database
- Ant Task check if a file exists
- Check if a database and tables exist in sql server in a vb .net project
- Oracle database does not have a "drop table if exists" clause
- Makefile – Check if a file exists using wildcard function
- How to check if a Number is Positive or Negative in Java - Interview Question
- Check if a File is in Internet Explorers Cache
- confirm if a file exists
- A program to check if a binary tree is BST or not
- MySQL how to show table in a database
- check if filed exists in all tables in current DB
- Creating a Stored Procedure or Function in an Oracle Database
- How To: Check if a user is part of a SharePoint group in InfoPath
- Java – Check if key exists in HashMap
- Python – Check if key exists in dictionary
- Given two binary trees, write a function to check if they are equal or not.
- A. Maximum in Table
- A. Maximum in Table
- Java代码 实现两个二进制数的加法
- 剑指offer-39 二叉树的深度
- 人生第一次面试TCL-实习生笔试及面试经验分享
- 黑马程序员---Java之多线程
- android基础--拍照功能
- Check if a table or field exists in a database
- 文件操作 课程成绩平均分统计并排名
- 我的第一个博客,也是人生的第一篇博客
- cocos2dx 项目打包成apk
- Jedis使用总结【pipeline】【分布式的id生成器】【分布式锁【watch】【multi】】【redis分布式】
- python学习笔记
- Posts Tagged 【bfs】Binary Tree Level Order Traversal I && II
- 基于Internet Explorer内核的网页信息抓取程序
- hdu1231