ASP ODBC

Document Sample
ASP ODBC Powered By Docstoc
					Data Objects and Data Base
  (Show database table in the web page)

                Chapter 7




                                          1
                 What is ODBC ?
ODBC stands for Open Data Base Connection, which
is an interface of a database for easily connecting the
programs.
Now all existing major databases have ODBC, such as
MS Access, MS SQL 2000, Oracle or MySQL.
In order to use ODBC of a database, we must register
this database first in window system.
The ODBC manager, which is named Data Source, is
in the Administrative Tolls Directory.


                                                          2
How to register a database ?

                Inside Control Panel
                Open administrative
                tools




                  Click Data Sources
                  (ODBC) Manager




                                       3
First Click System DSN
                         Then Click Add




                                          4
 Click Microsoft Access Driver




Then click Finish
                                 5
                                      Register database name
Select database from Hard Disk




              We could set user id and password here

                                                           6
Look for physical address of Access database (*.mdb)
The name could be different from the register name




                                                       Then Click OK




                                                                7
student DSN is registered




                            8
     Microsoft SQL 2000 Server
In Window 2000, during the installation we need to
to choose the In NT system, we usually set up all
users of
SQL must be users of NT user too.
In Windows95/98, SQL user no restriction.
Before setting OBDC for SQL, we need set up
Login process for SQL.
Now suppose we want to setup user "mike" and
password "java" for SQL database "Northwind"


                                                     9
Right mouse click Logins




                           10
 mike here




java here



Northwind here




             11
Click Northwind




    Click all here




  Confirm password

                     12
13
Choose table product and set permissions




                                           14
We can click SQL Server and Finish
                                     15
             Northwind




Click Next


                  16
Click Next, Next, Next

                         17
Click this to test connection
                                18
Now it is registered
                       19
20
          Three database objects
In order to use a database we need three objects :
   1. Connection object will build connecting between the
   database and the programs ,
   2. Command object will execute the SQL commands,
   such as SELECT, INSERT, DELETE and UPDATE .
   However, all those command executions are through
   connection object.
   3. DataReader object will read the database to get the
   data table values after SELECT execution
In C# those three objects are:
  OleDbConnection      OleDbCommand      OleDbDataReader
In other language, those three objects are different

                                                            21
             Basic working process
string sqlConn = "Provider=SQLOLEDB;"
                        + "dsn=publications;uid=mike;pwd=java";
        // define connection parameters
OleDbConnection objConn = new OleDbConnection(sqlConn);
        // use parameters to create connection object
objConn.Open();
        // open connection to database
string sqlCmd = "select * from authors order by au_lname";
        // define execution string
OleDbCommand objCmd = new OleDbCommand(sqlCmd, objConn);
        // use parameters to create command object
OleDbDataReader objRs = objCmd.ExecuteReader();
        // execute command to return data reader object



                                                             22
         How to read date base table
Once obtaining OleDbDataReader object objRs, we can use
               int k = objRs.FieldCount;
to get total count of fields. Each field name in index i can get by
                string name = objRs.GetName(i);
In order to read data base, we must call
               objRs.Read();
which will move data cursor down by one row.
Then we can call
               string value = objRs.GetString(i);
to get string value.
We also can get any native value by
               objRs.GetValue(i);
                                                                23
<%@ Page Language="c#" %>                         Readsql.aspx
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
  string table="" ;
  void Page_Load()
  {
      string sqlConn = "Provider=SQLOLEDB;"+
                        "DSN=pubs;uid=mike;pwd=java";
      OleDbConnection objConn = new OleDbConnection(sqlConn);
      objConn.Open();
      string sqlCmd = "select * from authors order by au_lname";
      OleDbCommand objCmd = new OleDbCommand(sqlCmd, objConn);
      OleDbDataReader objRs = objCmd.ExecuteReader();
table = "<table cellspacing=0 border=1 style='background-color:
        #DDFFDD;font-family:arial;font-size:10pt;'>\n";
                                                                   24
table += "<tr style='background-color:#FFCCCC;'><td>#</td>";
int j=0;
int k = objRs.FieldCount;
 for (int i=0; i<k; i++)
 {   table += "<td>" + objRs.GetName(i)+ "</td>";   }
 table +="</tr>";
while(objRs.Read())
 {
     j++;
     table +="<tr><td>" + j + "</td>";
     for (int i=0; i<k; i++)
     { table += "<td>" + objRs.GetValue(i) + "</td>"; }
     table +="</tr>";
 }
 table +="</table>" ;


                                                          25
       objRs.Close();
       objConn.Close();
  }
</script>
<html><body>
<center>
<b style="FONT-SIZE:18pt; COLOR:red; FONT-FAMILY:arial">
Read   SQL 2000 Database Table by ASP.NET C#</b>
<p>
 <font color="blue" size="4">
Using ODBC-DSN method and drawing HTML table
</font>
<p>
          <%=table%>
</center>
</body></html>                                             26
27
             Use DataGrid object
Object DataGrid is a web control to show the table
values
         <asp:DataGrid id=table runat=server>
         table.DataSource = objRs;
         table.DataBind() ;
DataGrid object can be also to change its font or
color by specifying its attributes values

<asp:DataGrid id="table" runat="server" Font-Size="10pt"
       Font-Name="arial" BackColor="#ddffdd"
        HeaderStyle-BackColor="#ffcccc" />



                                                           28
<%@ Page Language="c#" %>                         Readsql.aspx
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
  void Page_Load()
  {
      string sqlConn = "Provider=SQLOLEDB;"+
                        "DSN=pubs;uid=mike;pwd=java";
      OleDbConnection objConn = new OleDbConnection(sqlConn);
      objConn.Open();
      string sqlCmd = "select * from authors order by au_lname";
      OleDbCommand objCmd = new OleDbCommand(sqlCmd, objConn);
      OleDbDataReader objRs = objCmd.ExecuteReader();
      table.DataSource = objRs;
      table.DataBind() ;
   }
</script>                                                          29
<html><body>
<center>
<b style="FONT-SIZE:18pt; COLOR:red; FONT-FAMILY:arial; Font-
Face:bold"> Read SQL 2000 Database Table by ASP.NET C#</b>
<p>
 <font color="blue" size="4">
Using ODBC DSN and DataGrid Object
</font>
<p>
<asp:DataGrid id="table" runat="server" Font-Size="10pt"
       Font-Name="arial" BackColor="#ddffdd"
      HeaderStyle-BackColor="#ffcccc" />


</center>
</body>
</html>
                                                            30
31
                 DSN less method
If a database was not registered, it is still can be accessed by
Programs. If data base is MS SQL use
  string sqlConn = "Provider=SQLOLEDB;Driver=SQL Server;"+
       "Server=(local);Database=pubs;uid=mike;Pwd=java";
               //Use the exact name of the database.
If data base is MS Access use
string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;"+
        "Data Source=C:\\pubs.mdb;User ID=;Password=";
                //Use absolute data Source directory
If Access database is in the same directory, use
string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;"+
        "Data Source=" + MapPath("pubs.mdb") +
        ";User ID=;Password=";
        //Use MapPath() to get absolute data Source directory

                                                                32
<%@ Page Language="c#" %>                           SQL DSN less
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
  void Page_Load()
  {                     // the only change is this string
      string sqlConn = "Provider=SQLOLEDB;Driver=SQL Server;"+
                 "Server=(local);Database=pubs;uid=mike;Pwd=java";
      OleDbConnection objConn = new OleDbConnection(sqlConn);
      objConn.Open();
      string sqlCmd = "select * from authors order by au_lname";
      OleDbCommand objCmd = new OleDbCommand(sqlCmd, objConn);
      OleDbDataReader objRs = objCmd.ExecuteReader();
      table.DataSource = objRs;
      table.DataBind() ;
   }
</script>
                                                                   33
34
<%@ Page Language="c#" %>                         Access DSN less
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
  void Page_Load()
  {                     // the only change is this string
      string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;"+
                 "Data Source=" + MapPath("pubs.mdb") +
                 ";User ID=;Password=";
      OleDbConnection objConn = new OleDbConnection(sqlConn);
      objConn.Open();
      string sqlCmd = "select * from authors order by au_lname";
      OleDbCommand objCmd = new OleDbCommand(sqlCmd, objConn);
      OleDbDataReader objRs = objCmd.ExecuteReader();
      table.DataSource = objRs;
      table.DataBind() ;
   }
                                                                   35
</script>
36
            Read Excel Spreadsheet
We can use OleDbAdapter to read Excel Spreadsheet and show
the contents in the web page. Suppose that we have Excel file
named address in Sheet1




                                                                37
<%@ Page Language="c#" %>                           ReadExcel.aspx
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
  void Page_Load()
  {
       string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;"+
                  "Data Source=" + Page.MapPath("address.xls") +
                  ";Extended Properties=Excel 8.0;";
      string sqlCmd = "select * from [Sheet2$]", sqlConn);
      OleDbDataAdapter Adapter = new OleDbDataAdapter (sqlCmd, sqlConn);
      DataSet myDataSet = new DataSet();
      Adapter.Fill(myDataSet, "ExcelInfo");
      table.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
      table.DataBind();
  }
</script>
                                                                     38
<html><body>
<center>
<b style="FONT-SIZE:18pt; COLOR:red; FONT-FAMILY:arial; Font-
Face:bold"> Read Excel Spreadsheet by ASP.NET C#</b>
<p/>
<hr width="600" color="red" />
<p>
<asp:DataGrid id="table" runat="server" Font-Size="10pt"
       Font-Name="arial" BackColor="#ddffdd"
      HeaderStyle-BackColor="#ffcccc" />


</center>
</body>
</html>



                                                            39
40
               Data base program
The basic operation for web database program are:
   1. Read database, whole or partial
   2. Search database, such as check password
   3. Insert new data into database.
   4. Delete data from database
   5. Update data of database.
Any data base will be no difference if registered in ODBC.
The command should use the SQL standard.
However, if we already know that the database is Microsoft
SQL or Access, then we can use ADODB special command to
Make code simple.

                                                        41
           Use ADODB objects
• There are three ADODB objects
     Connection, RecordSet and Command
• ADODB objects can work with any database.
  However, they have some special commands for
  SQL and Access only and make easy to add data
• The ADODB objects are in
C:\Program Files\Common Files\System\ado\msado15.dll
• Then make assembly dll from it (pick any name)
      tlbimp msado15.dll /out: adodb.dll
• At last copy adodb.dll to the subdirectory bin

                                                       42
<%@ Page Language="c#" %>                       adodbdsnless.aspx
<script runat="server">
 string table="" ;
void Page_Load()
{
     string sqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
         + MapPath("pubs.mdb")+";User ID=;Password=";
         Connection objConn = new Connection();
      Recordset = new Recordset();
      objConn.Open(sqlConn,"","",0);
      objRs.Open("select * from authors order by au_lname",
                 objConn, CursorTypeEnum.adOpenForwardOnly ,
                 LockTypeEnum.adLockReadOnly, 1);
    table ="<table cellspacing=0 border=1 style='background-color:
         #DDFFDD;font-family:arial;font-size:10pt;'>";
    table += "<tr style='background-color:#FFCCCC;'><td>#</td>";
                                                                 43
int j=0;
int k = objRs.Fields.Count;
for (int i=0; i<k; i++)
{ table += "<td>"+objRs.Fields[i].Name.ToString()+ "</td>";}
    table +="</tr>";
if (!objRs.EOF) objRs.MoveFirst();
while (!objRs.EOF)
{
        j++;
        table +="<tr><td>" + j + "</td>";
        for (int i=0; i<k; i++)
        { table +="<td>"+objRs.Fields[i].Value.ToString()+"</td>"; }
        table +="</tr>";
        objRs.MoveNext();
    }
 table +="</table>" ;
                                                                44
       objRs.Close();     objConn.Close();
       objRs = null;     objConn = null;
  }
</script>
<html><body>
<center>
<b style="FONT-SIZE:18pt; COLOR:red; FONT-FAMILY:arial">
Read Access 2000 Database Table by ASP.NET C#</b>
<p>
 <font color="blue" size="4">
Using ADODB objects and DSN less method
</font> <p>
       <%=table%>
</center>
</body></html>
                                                           45
46

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:6/28/2012
language:English
pages:46