|
xxx
Easy Grid Display from
SQL7/2000 bypassing OLEDB
by Charles Carroll
This page demonstrates the capabilities how to
display a SQL7/2000 database as a easy to read grid.
filename=/experiments/databinding/cs_sqlclientgrid.aspx
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat="server">
protected void Page_Load(Object Src, EventArgs E)
{
SqlConnection Conn= null;
SqlDataReader Rdr=null;
try
{
string strConn=ConfigurationSettings.AppSettings["LearnAspSamples"];
string strSQL;
strSQL="select * from publishers where state='NY'";
Conn=new SqlConnection(strConn);
SqlCommand Cmd=new SqlCommand(strSQL,Conn);
Conn.Open();
Rdr=Cmd.ExecuteReader();
// -or-
// Rdr=Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
D1.DataSource = Rdr;
D1.DataBind();
} // end try
catch (Exception exc1)
{
litExc.Text=exc1.ToString();
} // end catch
finally
{
if (Rdr != null)
{
if (Rdr.IsClosed==false) {Rdr.Close();}
}
if (Conn != null)
{
if (Conn.State==System.Data.ConnectionState.Open) {Conn.Close();}
}
} // end finally
} // end page_load
</script>
<html><head><title>SQLclient DataGrid Example</title></head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>My First Database Sample</h3></font>
<asp:literal id="litExc" runat="server" />
<ASP:DataGrid id="D1" runat="server" />
</body></html>
This assumes you have connection
information stored in your web.config ala
<?xml version="1.0"?>
<configuration>
<appSettings>
<add key="LearnaspSamples" value="server=206.xxx.xxx.xxx;Initial Catalog=Learnasp;UID=xxxx;Pwd=xxxx;application Name={0}" />
<add key="LearnaspSamplesFull" value="server=206.xxx.xxx.xxx;Initial Catalog=Learnasp;UID=xxxx;Password=xxxx;application Name={0}" />
</appSettings>
</configuration>
Here is a page that reduces the code and has improved error trapping utilizing the UtilityBelt Library available for FREE from our site:
filename=/experiments/databinding/sqlclientgrid_ub.aspx
<%@ Assembly src="/experiments/utilitybelt/vercurrent/utilitybelt.vb" %>
<script language="VB" runat="server">
dim ub1 as new learnasp.utilitybelt()
dim strConnect as string="LearnAspSamples"
Sub Page_Load(S As Object, E As EventArgs)
ub1.DBPopulate(strConnect,"select * from publishers where state='NY'",MyDataGrid)
End Sub
</script>
<html><head>
<title>Grid of New York Data</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>New York Data</h3></font>
<ASP:PlaceHolder id="plcErr" runat="server" />
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</body></html>
Additional formatting besides tables
is readily available. Additional Reading:
ASP.net has two
assemblies/namespaces (see http://www.aspng.com/learn/assemblynamespace.aspx)
to talk to databases:
|