Hiển thị danh sách toàn bộ bảng và cột trong SQL Server trên Gridview
(List of Tables in Database in SQL Server) – Bài viết dưới đây sẽ hướng dẫn các bạn các lấy toàn bộ danh sách các bảng trong 1 Database SQL, và danh sách các trường trong 1 bảng dữ liệu được chọn. Toàn bộ danh sách các bảng được hiển thị trong Control Dropdownlist và khi chọn tên bảng tại đây danh sách các cột trong bảng được chọn sẽ hiển thị tại Gridview.
- B1: Download CSDL Northwind
- B2: Tạo Project trong Microsoft Visual Studio 2010
Trong Visual Studio tạo 1 Class có tên: Utility và nhập đoạn Code phía dưới cho Class này.
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace ListofTablesinDatabase
{
public class SqlDataProvider
{
#region
"Membres Prives"
private string
_connectionString;
#endregion
#region
"Constructeurs"
public SqlDataProvider()
{
try
{
_connectionString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
}
catch
{
}
}
#endregion
#region
"Proprietes"
public string
ConnectionString
{
get { return
_connectionString; }
}
#endregion
#region
"Functions"
public DataTable
FillTable(string sql)
{
try
{
DataTable tb = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(sql,
_connectionString);
adap.Fill(tb);
return tb;
}
catch
{
return null;
}
}
#endregion
}
}
VB.NET Code
Imports System.Data.SqlClient
Imports System.Data
Namespace ListofTablesinDatabase
Public Class SqlDataProvider
#Region "Membres
Prives"
Shared _IsError As Boolean = False
Private _connectionString As
String
#End Region
#Region "Constructeurs"
Public Sub New()
Try
_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
_IsError = False
Catch ex As Exception
_IsError = True
End Try
End Sub
#End Region
#Region "Proprietes"
Public ReadOnly Property ConnectionString() As
String
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public Function
FillTable(ByVal sql As
String) As DataTable
Try
Dim tb As
New DataTable
Dim adap As
New SqlDataAdapter(sql,
_connectionString)
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
#End Region
End Class
End Namespace
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B3: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ Page
Title="List of
Tables in Database in Gridview" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ListofTablesinDatabase._Default" %>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"
runat="server">
</asp:ScriptManager>
<h3>
List
of Tables in Database in Gridview
</h3>
<asp:UpdatePanel ID="updatePanel"
runat="server"
UpdateMode="Conditional">
<ContentTemplate>
<table cellpadding="2"
cellspacing="3"
width="100%">
<tr>
<td>
<asp:Label ID="plTableName" runat="server" Text="Table Name"></asp:Label>
<asp:DropDownList ID="ddlTables"
AutoPostBack="true"
OnSelectedIndexChanged="ddlTables_SelectedIndexChanged"
CssClass="form-control"
runat="server"
Width="250px">
</asp:DropDownList>
</td>
</tr>
<tr
id="trMessage"
runat="server"
visible="false">
<td
colspan="2">
<asp:Label ID="lblMessage" runat="server" Text="No Data"></asp:Label>
</td>
</tr>
<tr>
<td
colspan="2">
<asp:GridView ID="grvObject" runat="server"
CssClass="GridStyle"
BorderColor="#cbcbcb"
BorderStyle="solid"
BorderWidth="1"
AutoGenerateColumns="true"
width="100%">
<AlternatingRowStyle
CssClass="GridStyle_AltRowStyle"
/>
<HeaderStyle CssClass="GridStyle_HeaderStyle"
/>
<RowStyle CssClass="GridStyle_RowStyle"
/>
<pagerstyle cssclass="GridStyle_pagination" />
</asp:GridView>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>- B4: Viết Code cho file Default.aspx
C# Code
//Visit http://www.laptrinhdotnet.com
for more ASP.NET Tutorials
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
namespace ListofTablesinDatabase
{
public partial class _Default :
System.Web.UI.Page
{
#region
"Private Methods"
private void
PopulateDropdownlist()
{
SqlDataProvider objSQL = new
SqlDataProvider();
DataTable objBind = objSQL.FillTable("SELECT * FROM information_schema.tables");
ddlTables.DataTextField = "Table_Name";
ddlTables.DataValueField = "Table_Name";
ddlTables.DataSource = objBind;
ddlTables.DataBind();
}
#endregion
#region
"Bind Data"
private DataTable
BindData(string TableName)
{
SqlDataProvider objSQL = new
SqlDataProvider();
DataTable objBind = objSQL.FillTable("Select C.COLUMN_NAME as 'Column name', C.DATA_TYPE
as 'Data type', C.CHARACTER_MAXIMUM_LENGTH as 'Character maximum length',
C.IS_NULLABLE as 'IsNullable' , TC.CONSTRAINT_NAME as 'Constraint Name' From
INFORMATION_SCHEMA.COLUMNS As C Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS
As TC On TC.TABLE_SCHEMA = C.TABLE_SCHEMA And TC.TABLE_NAME = C.TABLE_NAME And
TC.CONSTRAINT_TYPE = 'PRIMARY KEY' Where C.TABLE_NAME ='" +
TableName + "'");
return
objBind;
}
private void
BindColumns()
{
DataTable objBind = new
DataTable();
string TableName = ddlTables.SelectedValue;
objBind = BindData(TableName);
if (objBind != null)
{
if (objBind.Rows.Count > 0)
{
grvObject.DataSource = objBind;
grvObject.DataBind();
trMessage.Visible = false;
grvObject.Visible = true;
}
else
{
trMessage.Visible = true;
grvObject.Visible = false;
}
updatePanel.Update();
}
}
#endregion
#region
"Event Handles"
protected void
Page_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
PopulateDropdownlist();
BindColumns();
}
}
catch
{
}
}
protected void
ddlTables_SelectedIndexChanged(object sender,
System.EventArgs e)
{
BindColumns();
}
#endregion
}
}
VB.NET Code
'Visit http://www.laptrinhdotnet.com
for more ASP.NET Tutorials
Imports System.Data.SqlClient
Namespace ListofTablesinDatabase
Public Class _Default
Inherits System.Web.UI.Page
#Region "Private
Methods"
Private Sub
PopulateDropdownlist()
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("SELECT * FROM information_schema.tables")
With ddlTables
.DataTextField = "Table_Name"
.DataValueField = "Table_Name"
.DataSource = objBind
.DataBind()
End With
End Sub
#End Region
#Region "Bind Data"
Private Function
BindData(ByVal TableName As String) As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Select C.COLUMN_NAME as 'Column name', C.DATA_TYPE
as 'Data type', C.CHARACTER_MAXIMUM_LENGTH as 'Character maximum length',
C.IS_NULLABLE as 'IsNullable' , TC.CONSTRAINT_NAME as 'Constraint Name' From
INFORMATION_SCHEMA.COLUMNS As C Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS
As TC On TC.TABLE_SCHEMA = C.TABLE_SCHEMA And TC.TABLE_NAME = C.TABLE_NAME And
TC.CONSTRAINT_TYPE = 'PRIMARY KEY' Where C.TABLE_NAME ='" &
TableName & "'")
Return objBind
End Function
Private Sub
BindColumns()
Dim objBind As New DataTable
Dim TableName As String = ddlTables.SelectedValue
objBind = BindData(TableName)
If Not objBind Is Nothing Then
If objBind.Rows.Count > 0 Then
grvObject.DataSource = objBind
grvObject.DataBind()
trMessage.Visible = False
grvObject.Visible = True
Else
trMessage.Visible = True
grvObject.Visible = False
End If
updatePanel.Update()
End If
End Sub
#End Region
#Region "Event
Handles"
Protected Sub
Page_Load(ByVal sender As
Object, ByVal e
As System.EventArgs)
Handles Me.Load
Try
If Page.IsPostBack = False Then
PopulateDropdownlist()
BindColumns()
End If
Catch ex As Exception
End Try
End Sub
Private Sub
ddlTables_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles
ddlTables.SelectedIndexChanged
BindColumns()
End Sub
#End Region
End Class
Chúc các bạn thành công!
Quang Bình
Share This:
No Comment to " Hiển thị danh sách toàn bộ bảng và cột trong SQL Server trên Gridview "