温故而知新，WebConfig数据库配置及页面的调用

一、SQLServer2000数据库的配置和页面调用

<?xml version="1.0" encoding="big5" ?>
<configuration>
   <appSettings>
   <add key="connstr" value="server=127.0.0.1;database=designeng;uid=sa;pwd=zhoutao;"/>
   </appSettings> 

Private Sub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim str, sql As String
        Dim conn As SqlConnection
        Dim da As SqlDataAdapter
        Dim ds As New DataSet
        Dim dv As DataView
        If txt1.Text = "" Or txt2.Text = "" Then
            Response.Write("<script language=javascript>alert('用戶名或密碼為空！');</script>")
        Else
            str = ConfigurationSettings.AppSettings("connstr")
            conn = New SqlConnection(str)
            sql = "select * from usertable where design_worknum='" + txt1.Text + "'"
            da = New SqlDataAdapter(sql, conn)
            da.Fill(ds, "tables")
            dv = ds.Tables("tables").DefaultView
            If dv.Count - 1 >= 0 Then
                Session("password") = ds.Tables("tables").Rows(0).Item(3).ToString               
                If Session("password") = txt2.Text Then
                    Session("headship") = ds.Tables("tables").Rows(0).Item(4).ToString
                    Session("names") = ds.Tables("tables").Rows(0).Item(0).ToString
                    Session("partment") = ds.Tables("tables").Rows(0).Item(2).ToString
                    Session("worknum") = ds.Tables("tables").Rows(0).Item(1).ToString
                    Session("computerIP") = ds.Tables("tables").Rows(0).Item(5).ToString
                    Session("group") = ds.Tables("tables").Rows(0).Item(7).ToString
                    lab1.Text = Session("names")
                    lab2.Text = Session("worknum")
                    lab3.Text = Session("headship")
                    Panel1.Visible = False
                    panel2.Visible = True
                    txt1.Text = ""

                End If
            End If
        End If
    End Sub

 二、oracle数据库webconfig配置及页面调用示例

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <appSettings>
        <add key="ConnString" value="user id=dbauser;data source=DBHM;password=password"/>
        <add key="SqlConnString" value="packet size=4096;user id=GQT;data source=127.0.0.1;persist security info=True;initial catalog=HumanDB;password=password"/>
<add key="HrmString" value="user id=HRM;data source=DBRM;password=DBRM"/>
    </appSettings>
    
  <system.web>

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient ;

namespace eElectrode
...{
    /**//// <summary>
    /// Summary description for LoginForm.
    /// </summary>
    public class LoginForm : System.Web.UI.Page
    ...{
        protected System.Web.UI.WebControls.ImageButton btnConfirm;
        protected System.Web.UI.WebControls.TextBox txtPassword;
        protected System.Web.UI.WebControls.TextBox txtUser;

        private string connString = string.Empty;
        private OracleConnection conn    = new OracleConnection();
    
        private void Page_Load(object sender, System.EventArgs e)
        ...{
            Response.Expires = -1;
            connString = System.Configuration.ConfigurationSettings.AppSettings["ConnString"].ToString();
            conn.ConnectionString = connString;
            Session.RemoveAll();
        }
        private void CheckUser()
        ...{
            if (txtUser.Text.Length <1)
            ...{
                Response.Write("<script language='javascript'>alert('用戶名不允許為空!')</script>");
                return;
            }

            try
            ...{
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                string selString = "Select * from UserInformation where UserId = '" + txtUser.Text + "' and Password = '" + txtPassword.Text + "'";
                cmd.CommandText = selString;
                OracleDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                ...{
                    while (dr.Read())
                    ...{
                        Session.Remove("UserId");
                        Session.Remove("GroupName");
                        Session.Remove("UserType");

                        Session.Add("UserId",dr["UserId"].ToString());
                        HttpCookie cook = new HttpCookie("UserId",dr["UserId"].ToString());
                        Response.Cookies.Add(cook);

                        Session.Add("GroupName",dr["GroupName"].ToString());
                        HttpCookie cook1 = new HttpCookie("GroupName",dr["GroupName"].ToString());
                        Response.Cookies.Add(cook1);

                        Session.Add("UserType",dr["UserType"].ToString());
                        HttpCookie cook2 = new HttpCookie("UserType",dr["UserType"].ToString());
                        Response.Cookies.Add(cook2);
                    }
                    dr.Close();
                    string updateString = "Update UserInformation set LastLoginDate = sysdate where UserId = '" + txtUser.Text + "'";
                    cmd.CommandText = updateString;
                    cmd.ExecuteNonQuery();
                    updateString = "Insert into LOGIN_LOG(USERNAME,LOGTYPE,LOGTIME)" +
                        " values('" + txtUser.Text + "','IN',sysdate)";
                    cmd.CommandText = updateString;
                    cmd.ExecuteNonQuery();
                    Response.Write("<script language='javascript'>window.location = 'Default.htm';</script>");
                }
                else
                ...{
                    Response.Write("<script language='javascript'>alert('用戶名或密碼不正確,請重新輸入!')</script>");
                    txtUser.Text = "";
                    txtPassword.Text = "";
                    dr.Close();
                    conn.Close();
                    return;
                }
            }
            catch (System.Exception ex)
            ...{
                Response.Write("<script language='javascript'>alert('"+ex.Message+"')</script>");
            }
            finally
            ...{
                conn.Close();
            }
            
        }
        private void btnConfirm_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        ...{
            CheckUser();
        }

三、oracle數據庫webconfig調用的另一個配置和頁面調用，因此方法是微軟退出的一個跟數據庫類型無關的鏈接方式，故需要在Bin目錄文件夾中加載微軟的相應Dll文件，此處欠奉。

<?xml version="1.0"?>
<!-- 
    注意: 除了手动编辑此文件以外，您还可以使用 
    Web 管理工具来配置应用程序的设置。可以使用 Visual Studio 中的
     “网站”->“Asp.Net 配置”选项。
    设置和注释的完整列表在 
    machine.config.comments 中，该文件通常位于 
    WindowsMicrosoft.NetFrameworkv2.xConfig 中
-->
<configuration>
    <configSections>
        <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"/>
    </configSections>
    <appSettings>
  <add key="CrystalImageCleaner-AutoStart" value="true" />
  <add key="CrystalImageCleaner-Sleep" value="60000" />
  <add key="CrystalImageCleaner-Age" value="120000" />
 </appSettings>
    <dataConfiguration defaultDatabase="OracleConnection"/>
    <connectionStrings>
        <add name="OracleConnection" connectionString="user id=test;data source=TEST;password=test;Pooling=true;Min Pool Size=0;Max Pool Size=200;" providerName="System.Data.OracleClient"/>
        <add name="Connection" connectionString="user id=DBconnect;data source=dbconn;password=conn;" providerName="System.Data.OracleClient"/>
    </connectionStrings>
    <system.web>

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Configuration;
using System.Data.Common;

public partial class Login : System.Web.UI.Page
...{
    protected void Page_Load(object sender, EventArgs e)
    ...{
        Session.RemoveAll();
    }
    private void CheckUser()
    ...{
        if (txtUser.Text.Length < 1)
        ...{
            Response.Write("<script language='javascript'>alert('用戶名不允許為空!')</script>");
            return;
        }

        try
        ...{
            Database db = DatabaseFactory.CreateDatabase();
            string selString = "Select * from USERINFORMATION where USERID = '" + Helper.LoginLetterSelected(txtUser.Text) + "' and Password = '" + Helper.LoginLetterSelected(txtPassword.Text) + "'";
            DbCommand selCmd = db.GetSqlStringCommand(selString);
            IDataReader selDr = db.ExecuteReader(selCmd);
            if (selDr.Read())
            ...{
                Session.Remove("UserId");
                Session.Remove("UserName");
                Session.Remove("GroupName");
                Session.Remove("UserType");

                Session.Add("UserName", selDr["USERNAME"].ToString());
                HttpCookie cook3 = new HttpCookie("UserName", selDr["USERNAME"].ToString());
                cook3.Expires = DateTime.Now.AddDays(10);
                Response.Cookies.Add(cook3);

                Session.Add("UserId", selDr["UserId"].ToString());
                HttpCookie cook = new HttpCookie("UserId", selDr["UserId"].ToString());
                cook.Expires = DateTime.Now.AddDays(10);
                Response.Cookies.Add(cook);

                Session.Add("GroupName", selDr["GroupName"].ToString());
                HttpCookie cook1 = new HttpCookie("GroupName", selDr["GroupName"].ToString());
                cook1.Expires = DateTime.Now.AddDays(10);
                Response.Cookies.Add(cook1);

                Session.Add("UserType", selDr["UserType"].ToString());
                HttpCookie cook2 = new HttpCookie("UserType", selDr["UserType"].ToString());
                cook2.Expires = DateTime.Now.AddDays(10);
                Response.Cookies.Add(cook2);
                selDr.Close();
                string updateString = "Update USERINFORMATION set LastLoginDate = sysdate where UserId = '" + txtUser.Text + "'";
                selCmd.CommandText = updateString;
                db.ExecuteNonQuery(selCmd);
                Session["url"] = Request.QueryString["url"];
                Response.Write("<script language='javascript'>window.location = 'Default.htm';</script>");
            }
            else
            ...{
                Response.Write("<script language='javascript'>alert('用戶名或密碼不正確,請重新輸入!')</script>");
                txtUser.Text = "";
                selDr.Close();
                return;
            }
        }
        catch (System.Exception ex)
        ...{
            Response.Write("<script language='javascript'>alert('" + ex.Message + "')</script>");
        }
    }
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    ...{
        CheckUser();
    }
}

四、oracle中不使用webconfig連接數據庫，而是直接把連接語句寫到一個類里，然後調用的方法

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using System.Data.Common;

/**//// <summary>
/// 創建數據庫聯接、執行存儲過程
/// </summary>
public class DB
...{
    public DB()
    ...{
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    public static OracleConnection Oraconn()
    ...{
        return new OracleConnection("user id=test;data source=DBA_192.168.0.1;password=test;Pooling=true;Min Pool Size=0;Max Pool Size=200;");
        
    }

    /**////<summary>
    ///名稱：CreatCommand
    ///描述：創建一個OracleCommand對象來執行存儲過程。
    ///</summary>
    ///<param name="procName">存儲過程名稱</param>
    ///<param name="prams">存儲過程所需要的參數</param>
    ///<returns>返回sqlcommand對象></returns>
    
    public OracleCommand CreateCommand(string procName, OracleParameter[] prams)
    ...{
        OracleConnection oraconn = DB.Oraconn();
        oraconn.Open();
        OracleCommand cmd = new OracleCommand(procName,oraconn);
        cmd.CommandType = CommandType.StoredProcedure;

        if (prams != null)
        ...{
            foreach (OracleParameter parameter in prams)
            ...{
                cmd.Parameters.Add(parameter);
            }
        }
        return cmd;
    }

 

五、c#連接Access

using System.Data;
using System.Data.OleDb;
..
string strConnection="Provider=Microsoft.Jet.OleDb.4.0;";strConnection+=@"DataSource=C:BegASPNETNorthwind.mdb";
OleDbConnection objConnection=new OleDbConnection(strConnection);
..
objConnection.Open();
...
objConnection.Close();

解释：

连接Access数据库需要导入额外的命名空间，所以有了最前面的两条using命令，这是必不可少的!

strConnection这个变量里存放的是连接数据库所需要的连接字符串，他指定了要使用的数据提供者和要使用的数据源。

“Provider=Microsoft.Jet.OleDb.4.0;”是指数据提供者，这里使用的是Microsoft Jet引擎，也就是Access中的数据引擎，asp.net就是靠这个和Access的数据库连接的。

“Data Source=C:/BegASPNET/Northwind.mdb”是指明数据源的位置，他的标准形式是“Data Source=MyDrive:MyPath/MyFile.MDB”。

PS:
1.“+=”后面的“@”符号是防止将后面字符串中的“/”解析为转义字符。
2.如果要连接的数据库文件和当前文件在同一个目录下，还可以使用如下的方法连接：

strConnection+="Data Source=";
strConnection+=MapPath("Northwind.mdb");

3.要注意连接字符串中的参数之间要用分号来分隔。

“OleDbConnection objConnection=new OleDbConnection(strConnection);”这一句是利用定义好的连接字符串来建立了一个链接对象，以后对数据库的操作我们都要和这个对象打交道。

“objConnection.Open();”这用来打开连接。至此，与Access数据库的连接完成。