» » » 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.


Nghe những bài hát đỉnh nhất về Thấy cô giáo - Nghe trên Youtube



Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET



- 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 stringConnectionString
        {
            get { return_connectionString; }
        }

        #endregion

        #region"Functions"

        public DataTableFillTable(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 AsString

#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() AsString
            Get
                Return _connectionString
            End Get
        End Property

#End Region

#Region "Functions"

        Public FunctionFillTable(ByVal sql AsString) As DataTable
            Try
                Dim tb AsNew DataTable
                Dim adap AsNew 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
<%@ PageTitle="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>
                <trid="trMessage"runat="server"visible="false">
                    <tdcolspan="2">
                        <asp:Label ID="lblMessage" runat="server" Text="No Data"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <tdcolspan="2">
                        <asp:GridView ID="grvObject" runat="server"
                            CssClass="GridStyle"BorderColor="#cbcbcb"BorderStyle="solid"
                            BorderWidth="1"AutoGenerateColumns="true"width="100%">
                            <AlternatingRowStyleCssClass="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 voidPopulateDropdownlist()
        {
            SqlDataProvider objSQL = newSqlDataProvider();
            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 DataTableBindData(string TableName)
        {
            SqlDataProvider objSQL = newSqlDataProvider();
            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 + "'");
            returnobjBind;
        }

        private voidBindColumns()
        {
            DataTable objBind = newDataTable();
            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 voidPage_Load(object sender, System.EventArgs e)
        {
            try
            {
                 if (!IsPostBack)
                {
                    PopulateDropdownlist();
                    BindColumns();
                }
            }
            catch
            {
            }
        }

        protected voidddlTables_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 SubPopulateDropdownlist()
            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 FunctionBindData(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 SubBindColumns()
            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 SubPage_Load(ByVal sender AsObject, 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 SubddlTables_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) HandlesddlTables.SelectedIndexChanged
            BindColumns()
        End Sub

#End Region

    End Class

End Namespace

Bây giờ chạy Project bạn sẽ có kết quả như ảnh phía dưới.

Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET



Chúc các bạn thành công!

Quang Bình
10:10:00 AM
Gia Khang
,

0 comments Blogger 0 Facebook

Post a Comment

Subscribe to: Post Comments ( Atom )
 
lập trình đốt nét © 2013. All Rights Reserved. Powered by Blogger
Top