ASP.Net/ADO.net Database/SQLDataAdapter
Содержание
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 = "
" + Title[0] + " | "; Output.Text += "" + String.Format("{0:c}", Title[1]) + " | "; Output.Text += "
} </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>