Please let other users know how useful this tip is by rating it below. Do you have a tip or code of your own you'd like to share? Submit it here.
We all use components and databases these days. Hence, I thought, why not make a generic component that everybody can use? Yes, you can actually use this component for database access. You can connect to any database with a valid connection string and pass any valid SQL statement. The method DataReader takes two arguments:
- DBString (the database connection string)
- SQL (the SQL statement)
The component returns to you an ADO DataReader, which you can databind to a ASP Datagrid. Here is the code for the component (DBAccess.cs):
Namespace DBAccess
{
using System;
using System.Data;
using System.Data.ADO; public
class DBRead
{
public ADODataReader DataReader(string DBStr,string
strSQL)
{
// Create the connection
ADOConnection oCn = new ADOConnection(DBStr);
//Create the Command Object
ADOCommand oCm = new ADOCommand(strSQL,oCn);
//Open the connection
oCn.Open();
//Create a null DataReader Object
ADODataReader oDr ;
//Execute the SQL Query
oCm.Execute(out oDr);
// Return the DataReader
return oDr;
}
}
}
You can compile the above code with the following command:
csc /r:System.Data.dll /t:library DBAccess.cs
We have imported the System.Data NameSpace in our component, now we need to give its reference. Here is the ASP.NET code (Client.aspx):
<%@Page language= "c#" AutoEventWireup="false" debug= "true" %>
<%@ Import NameSpace= "DBAccess"%>
<%@ Import NameSpace= "System.Data"%>
<%@ Import NameSpace= "System.Data.ADO"%>
<script language=c#" runat="server" >
protected void Submit_OnClick(Object s, EventArgs e)
{
// Create an instance of our component
DBAccess.DBRead oDBr = new DBAccess.DBRead();
//Create a null DataReader
ADODataReader oDr;
// Use the DataReader Method of our component
oDr = oDBr.DataReader(dbstr.Text,SQL.Text);
// DataBind the DataReader with the DataGrid
oDg.DataSource = oDr;
oDg.DataBind();
}
</script>
<html>
<head>
<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
</head>
<body>
<form method="post" runat="server">
DB String : <asp:textbox id="dbstr" runat="Server"/><br/>
SQL Query : <asp:textbox id="SQL" runat="Server"/><br/>
<asp:button text="Submit" runat="Server" onclick="Submit_OnClick"/>
<br/>
<ASP:DataGrid id="oDg" runat="server"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Size="9pt"
HeaderStyle-BackColor="#aaafff"
Headerstyle-font-bold
/>
</form>
</body>
</html>
You don't have to separately compile the ASP.NET client. It automatically gets compiled with the first request. Happy coding!
Source: DotNetExtreme.com
