How to describe user tables MS SQL Server in ASP.NET and stored procedure? RSS

• • Reply
  NataliaZ

  None

  0 Points

  1 Post

  How to describe user tables MS SQL Server in ASP.NET and stored procedure?

  Sep 02, 2005 05:15 PM|NataliaZ|LINK

  ----------------------Stored procedure--------------------------------------------

  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS OFF
  GO

  ALTER   PROCEDURE [selectTableColumnsNamesTypesSizes]
   (
   @InputTable varchar(200) = NULL,
   @ColumnName varchar(200) = NULL output,
   @ColumnType varchar(15) = NULL output,
   @ColumnSize int=NULL output
   )
  AS

      IF @InputTable IS NULL

    SELECT distinct
    tab.name as 'table'
    
    FROM [thewebsitedb].[dbo].[sysobjects] tab,
           [thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
           [thewebsitedb].[dbo].[syscomments] com INNER JOIN
           [thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
           [thewebsitedb].[dbo].[systypes] typ
    
     WHERE tab.id = col.id
       AND tab.xtype = 'U'
       AND tab.name <> 'dtproperties'
       AND col.xusertype = typ.xusertype
      ELSE

    SELECT
    tab.name as 'table',
    col.name as 'column',
    typ.name as 'column_type',
    typ.length as 'column_size'
    
    FROM [thewebsitedb].[dbo].[sysobjects] tab,
           [thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
           [thewebsitedb].[dbo].[syscomments] com INNER JOIN
           [thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
           [thewebsitedb].[dbo].[systypes] typ
    
     WHERE tab.id = col.id
       AND tab.name = @InputTable
       AND tab.xtype = 'U'
       AND tab.name <> 'dtproperties'
       AND col.xusertype = typ.xusertype
     ORDER BY col.colid
  GO
  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO

  
  -------------------------------------ASPX.VB - code behind---------------------------------------------------

  Option Explicit On
  Option Strict On
  Option Compare Text

  Imports System
  Imports System.Configuration
  Imports System.Data
  Imports System.Data.SqlClient

  Namespace SQLSERVER

  Public Class ConnectionTest : Inherits System.Web.UI.Page

   '-----DECLARING connection string value --- 
   
   Public Const strConnectionInfo As String = "Data Source=localhost;Database=MyDB;Integrated Security=SSPI"
   '--- to avoid direct exposure of your conncetion string - change it to something like:
   'Public Const strConnectionInfo As String = ConfigurationSettings.AppSettings.Get("ConnectionString")
   
   
   '-----DECLARING default values
   Public Const intConnectionTimeout As Short = 10
   Public Const intCommandTimeout As Short = 60
   Public Const strProjectName As String = "THEDBData"
   Public Const DefaultValue_String As String = "**** NULL VALUE ****"
   Public Const DefaultValue_Double As Double = -2147483648.0#
   Public Const DefaultValue_Single As Single = -2147483648.0#
   Public Const DefaultValue_Date As Date = #1/1/1753#
   Public Const DefaultValue_Integer As Integer = -2147483648
   Public Const DefaultValue_Short As Short = -32768
   Public Const DefaultValue_Byte As Byte = 0
   Public Const DefaultValue_Currency As Decimal = -2147483648
   Public Const DefaultValue_TimeStamp As String = "FFFFFFFF"
   Public Const DefaultValue_Boolean As Short = -32768

   

    Protected ddlTable As System.Web.UI.WebControls.DropDownList 
    Protected txtTable As System.Web.UI.WebControls.TextBox
    Protected dtgTables As System.Web.UI.WebControls.DataGrid
    Dim objConn As SqlConnection
    Dim objCmd As SqlCommand
    Dim dataReader As SqlDataReader
    Dim strSql As String

    Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

      If Not IsPostBack Then
        Dim objConn As SqlConnection
        Dim dataReader As SqlDataReader

        objConn = New SqlConnection(strConnectionInfo)

        strSql = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes]"
        objCmd = New SqlCommand(strSql, objConn)
        Try
          objConn.Open()
          dataReader = objCmd.ExecuteReader
          With ddlTable
            .DataSource = dataReader
            .DataTextField = "table"
            .DataValueField = "table"
            .DataBind()
          End With
        Catch
        Finally
          objConn.Close()
          objConn.Dispose()
        End Try
      End If
    End Sub

    Public Sub btnGetTable_Click(sender as Object, e As EventArgs)
      objConn = New SqlConnection(strConnectionInfo)
     
      if txtTable.Text <> "" then
           strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes]  '" & txtTable.Text & "','',''"
      else
           strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes]  '" & ddlTable.SelectedItem.Text & "','',''"   
      end if
     
     
      objCmd = New SqlCommand(strSQL, objConn)
      Try
        objConn.Open()
        dataReader = objCmd.ExecuteReader()
        'Bind to DataGrid
        dtgTables.DataSource = dataReader
        dtgTables.DataBind()
      Catch
      Finally
        objConn.Close()
        objConn.Dispose()
      End Try
    End Sub

  End Class

  End Namespace

  ---------------------------------------------------ASPX---------------------------------------------------------

  <%@ Page Language="vb" Src="DescribeDatabase.aspx.vb" Inherits="SQLSERVER.DescribeDatabase" AutoEventWireup="false"%>
  <% 'Next 3 lines prevent caching in IIS. IE caching is handled by meta tags %>
  <% Response.CacheControl = "no-cache" %>
  <% Response.AddHeader("Pragma", "no-cache")%>
  <% Response.Expires = -1 %>
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
  <html>
  <head>
      <title>DocumentAdmin</title>
      <meta content="True" name="vs_showGrid">
      <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
      <meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
      <meta content="JavaScript" name="vs_defaultClientScript">
      <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
  <LINK href="styles.css" type="text/css" rel="stylesheet">
  </head>
  <body MS_POSITIONING="GridLayout" style="margin: 30 30 30 30;" oncontextmenu="return false;">
  <form id="Form1" method="post" runat="server" enctype="multipart/form-data">
  <asp:Label id="lblSelect" text="Select a Table: " runat="server" />
  <asp:DropDownList id="ddlTable" runat="server" />&nbsp;
  or type table name:
  <asp:TextBox id="txtTable" runat="server" />&nbsp;
  <asp:Button id="btnGetTable" text="Describe Table" OnClick="btnGetTable_Click" runat="server" />
  <br>
  <asp:DataGrid ID="dtgTables" runat="server"
                AutoGenerateColumns=True
                HeaderStyle-BackColor="IndianRed"
                HeaderStyle-Font-Bold="True"
                HeaderStyle-Font-Name="Verdana"
                HeaderStyle-Font-Size="12px"
                HeaderStyle-ForeColor="White"
                ItemStyle-BackColor=Gainsboro
                ItemStyle-Font-Name="verdana"
                ItemStyle-Font-Size="12px"
                Width="75%">
  </asp:DataGrid>
  </form>
  </body>
  </html>