ASP.Net/ADO.net Database/SQLDataAdapter

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Bind Data from Sql Server (VB.net)

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   Dim DBConn as SQLConnection
   Dim DBCommand As SQLDataAdapter
   Dim DSPageData as New DataSet

" DBConn = New SQLConnection("server=localhost;" _ 

"           & "Initial Catalog=TT;" _
 "          & "User Id=sa;" _
  "         & "Password=yourpassword;")
   DBConn = New SQLConnection("Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";")
   DBCommand = New SQLDataAdapter _
       ("Select Count(ID) as TheCount " _
       & "from Employee", DBConn)
   DBCommand.Fill(DSPageData, _
       "EmpCount")
   DBCommand = New SQLDataAdapter _
       ("Select Max(Salary) as HighSal " _
       & "from Employee", DBConn)
   DBCommand.Fill(DSPageData, _
       "HighSal")
   lblMessage.Text = "Total Employees: " _
       & DSPageData.Tables("EmpCount"). _
       Rows(0).Item("TheCount") _
       & "

Highest Salary: " _
       & FormatCurrency(DSPageData.Tables("HighSal"). _
       Rows(0).Item("HighSal"))

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Values in an SQL Server Table</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:label 

   id="lblMessage"
   runat="Server"

/> </form> </BODY> </HTML> 

      </source>


Get data in database through SqlDataAdapter (C#)

   <source lang="csharp">

<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> 

  <title>DataSet Example</title>
  <head>
     <script runat="server">
        void Page_Load()
        {
           String ConnStr = "Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";";
           String SQL = "SELECT ID, FirstName FROM Employee " 
              + "WHERE ID IS NOT NULL";
           SqlDataAdapter TitlesAdpt = new SqlDataAdapter(SQL, ConnStr);
           DataSet Titles = new DataSet();
           // No need to open or close the connection
           //   since the SqlDataAdapter will do this automatically.
           TitlesAdpt.Fill(Titles);
Output.Text = ""; foreach (DataRow Title in Titles.Tables[0].Rows) { Output.Text += ""; Output.Text += ""; Output.Text += ""; Output.Text += ""; } Output.Text += "
" + Title[0] + "" + String.Format("{0:c}", Title[1]) + "
"; 
        }
     </script>
  </head>

<body>

DataSet Example

  <asp:label id="Output" runat="server"/>

</body> </html> 

      </source>


Read data from SqlConnection (VB.net)

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   If Not IsPostBack Then
       Dim DBConn as SQLConnection
       Dim DBCommand As SQLDataAdapter
       Dim DSPageData as New DataSet

" DBConn = New SQLConnection("server=localhost;" _ 

"           & "Initial Catalog=TT;" _
 "          & "User Id=sa;" _
  "         & "Password=yourpassword;")
       DBConn = New SQLConnection("Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";")
       DBCommand = New SQLDataAdapter _
           ("Select LastName + ", " + FirstName " _
           & "as EmpName, ID " _
           & "From Employee " _
           & "Order By LastName, FirstName", DBConn)
       DBCommand.Fill(DSPageData, _
           "Employees")
       ddlEmps.DataSource = _
           DSPageData.Tables("Employees").DefaultView
       ddlEmps.DataBind()
   End If

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Displaying SQL Server Data in a DropDownList Control</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:dropdownlist 

   id="ddlEmps"
   datatextfield="EmpName" 
   datavaluefield="ID"
   runat="server"

/> </form> </BODY> </HTML> 

      </source>


Retrieving SQL Server Data (VB.net)

   <source lang="csharp">

<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) 

   If Not IsPostBack Then
       Dim DBConn as SQLConnection
       Dim DBCommand As SQLDataAdapter
       Dim DSPageData as New DataSet

" DBConn = New SQLConnection("server=localhost;" _ 

"           & "Initial Catalog=TT;" _
 "          & "User Id=sa;" _
  "         & "Password=yourpassword;")
       DBConn = New SQLConnection("Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";")
   
       DBCommand = New SQLDataAdapter _
           ("Select LastName + ", " + FirstName " _
           & "as EmpName, ID " _
           & "From Employee " _
           & "Order By LastName, FirstName", DBConn)
       DBCommand.Fill(DSPageData, _
           "EmployeeNames")
       DBCommand = New SQLDataAdapter _
           ("Select * from Employee", DBConn)
       DBCommand.Fill(DSPageData, _
           "EmployeesAll")
       DBCommand = New SQLDataAdapter _
           ("Select LastName from Employee Where " _
           & "Salary > 25000", DBConn)
       DBCommand.Fill(DSPageData, _
           "Emp3")
       ddlEmps.DataSource = _
           DSPageData.Tables("EmployeeNames").DefaultView
       ddlEmps.DataBind()
       dgEmps2.DataSource = _
           DSPageData.Tables("EmployeesAll").DefaultView
       dgEmps2.DataBind()
       dgEmps3.DataSource = _
           DSPageData.Tables("Emp3").DefaultView
       dgEmps3.DataBind()
   End If

End Sub </SCRIPT> <HTML> <HEAD> <TITLE>Retrieving SQL Server Data</TITLE> </HEAD> <Body LEFTMARGIN="40"> <form runat="server">

<asp:dropdownlist 

   id="ddlEmps"
   datatextfield="EmpName" 
   datavaluefield="ID"
   runat="server"

/>

<asp:datagrid 

   id="dgEmps2" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid>

<asp:datagrid 

   id="dgEmps3" 
   runat="server" 
   autogeneratecolumns="True"

> </asp:datagrid> </form> </BODY> </HTML> 

      </source>
Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/SQLDataAdapter&oldid=1565»