如何读取数据库中的所有表并在Excel中显示字段？ [英] How to Read all the tables in the database and display fields in Excel ?

问题描述

嗨朋友们，

任何人都可以帮我理念吗？我有一个带有四个文本框和一个按钮（在Excel中显示）的Default.aspx页面（UI页面）？文本框是1. ServerNameTextbox 2. DatabaseNameTextBox 3.UserNametextbox 4.PasswordTextbox。我需要的是如果我在文本框中提供servername，databasename，username，password值并单击Button然后我需要从sql server获取有关数据库的所有详细信息，包括表名，列名和列数据类型，并将此信息绑定到Excel ??

Hi Friends,
Can Anyone help me with Idea ? I am having an Default.aspx Page ( UI Page ) with four textbox and a button(Show in Excel) ? The Textbox are 1. ServerNameTextbox 2. DatabaseNameTextBox 3.UserNametextbox 4.PasswordTextbox . And My Need is If I give the servername,databasename,username,password values in the textbox and click the Button then I need to get all details about the database from sql server with table name, column name and column datatypes and bind this information in Excel ??

推荐答案

问题是您无法直接在客户端上显示excel：您可以向客户端发送Excel文件，但它已经完成了对他来说，如果他想打开它，保存它或者他甚至安装了Excel。



您可以在代码中使用Excel，但它将在服务器上运行，而不是客户，因此生产它将无法供用户查看。

The problem is that you can't "show in excel" on the client directly: you can send the client an Excel file, but it's up to him if he wants to open it, save it or if he even has Excel installed.

You can use Excel in your code, but it will run on the Server, not the Client, so it production it won't be available for the user to view.

Hi Murugappan，



使用DatabaseName，用户名和密码通过连接字符串传递来检查连接，

如果连接成功，则使用以下查询获取有关数据库的详细信息

Hi Murugappan,

Use DatabaseName,Username and password to check the connection by passing it with connection string,
If connection is successful then use the below query to fetch the details about database

USE DatabaseName;
SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION%' OR type_desc IN ('USER_TABLE','SQL_STORED_PROCEDURE','VIEW') order by type_desc asc;

以上查询将获取所有详细信息属于数据库给予



如果你想基于类别获取，那么你可以使用下面的查询

the above query will fetch all details that belongs to the Database given

If you want to fetch on category based then you can make use of the below queries

USE DatabaseName;
SELECT * FROM sys.tables
SELECT * FROM sys.procedures
SELECT * FROM sys.views
SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION%';

一旦提取，您可以使用服务器端代码将其导出到Excel中。您可以参考这里 [ ^ 为此。

希望这对你有所帮助。



Re gards，

RK

Once fetched you can export it into Excel using server side code.You can refer here[^] for this.
Hope this helps you a bit.

Regards,
RK

这篇关于如何读取数据库中的所有表并在Excel中显示字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！