数据库技术(con(sql,Oracle),com,parmter,DataTable,prc

8 数据库技术（CSJ_0808)

1 连接SQL Server数据库示例
 <configuration>
<appSettings>
 <add key="ConnectionSqlServer" value="Server=(local);User id=sa;Pwd=sa;Database=Northwind"></add>
</appSettings>
 ------------------------------------------------
 // 连接字符串
 string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];

    // 创建SqlConnection对象
    SqlConnection connection = new SqlConnection(ConnectionString);

    try
    {
     // 打开数据库连接
     connection.Open();
     myLabel.Text = "连接数据库成功";
    }
    catch
    {
     myLabel.Text = "连接数据库失败";
    }
    finally
    {
     // 关闭数据库连接
     connection.Close();
    }
---------------------------------------------------------------
2 连接ACCESS数据库示例
// 连接到 ACCESS 的连接字符串
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" + Server.MapPath("grocertogo.mdb");
// 使用OleDb .NET数据提供程序创建连接
OleDbConnection oleConnection = new OleDbConnection(ConnStr);
oleConnection.Open();
// 显示连接成功信息
myLabel.Text = "Access数据库连接状态：" + oleConnection.State;
--------------------------------------------------------------------
3 连接到 Oracle 数据库示例
// 连接字符串
string ORACLE_ConnStr = "Data Source=Oracle8i;Integrated Security=yes";
// 创建 OracleConnection 对象
OracleConnection oConnection = new OracleConnection(ORACLE_ConnStr);

    try
    {
     oConnection.Open();
     myLabel.Text = "连接到 Oracle 数据库";
    }
    catch(Exception ex)
    {
     myLabel.Text = ex.ToString();
    }
    finally
    {
     oConnection.Close();
    }
------------------------------------------------------------------------
4 使用 SqlCommand 执行SQL命令示例
   // 连接字符串
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
    // 创建SqlConnection对象
    // 创建Command对象
    SqlConnection thisConnection = new SqlConnection(ConnStr);
    SqlCommand thisCommand = new SqlCommand();
    // 关联Connection对象
    // 赋值SQL语句到CommandText属性
    // 指定命令类型是Sql语句
    thisCommand.Connection = thisConnection;
    thisCommand.CommandText = "SELECT COUNT(*) FROM Employees";
    thisCommand.CommandType = CommandType.Text;
    try
    {
    // 打开数据库连接
     thisCommand.Connection.Open();

// 获取查询结果
 myLabel.Text = thisCommand.ExecuteScalar().ToString();
 }
 catch(SqlException ex)
 {
 // 如果出现异常，在Label标签中显示异常信息
 myLabel.Text = ex.ToString();
 }
 finally
 {
 // 关闭数据库连接
 thisCommand.Connection.Close();
 }
-------------------------------------------------------------------------------
5 使用 SqlDataReader 读取数据示例 (快速的只向前读一次，快）
SqlCommand thisCommand = new SqlCommand(Sql, thisConnection);
thisCommand.CommandType = CommandType.Text;
thisCommand.Connection.Open();
SqlDataReader dr = thisCommand.ExecuteReader();
myLabel.Text = "LastName FirstName ";
// 循环读取结果集
 while(dr.Read())
 {
 // 读取两个列值并输出到Label中
 myLabel.Text += dr["LastName"] + " " + dr["FirstName"] + " ";
 }
 // 关闭DataReader
 dr.Close();
------------------------------------------------------------------------- OS_SOURCE Source
6 使用DataAdapter填充数据到DataSet示例
SqlDataAdapter adapter = new SqlDataAdapter(Sql, thisConnection);
 DataSet data = new DataSet();
 adapter.Fill(data);
 myDataGrid.DataSource = data;
 myDataGrid.DataBind();
-------------------------------------------------------------------------
7 使用DataTable存储数据库表内容的示例
DataTable table = new DataTable(); //表
// 填充数据到DataTable
 adapter.Fill(table);
// 将DataTable绑定到DataGrid控件
 myDataGrid.DataSource = table;
 myDataGrid.DataBind();
-------------------------------------------------------------------------
8 使用DataTable进行检索和排序示例
adapter.Fill(table);
// 定义筛选条件字符串和排序字符串
string strExpr = "Country = 'USA'";
string strSort = "CompanyName DESC";
// 获得经过筛选和排序后的数据
 DataRow [] rows = table.Select(strExpr, strSort);
// 检查返回数据是否为空
 if(rows.Length <= 0)
 {
 label.Text = "没有数据";
 return;
 }
 ------------------------------------------
 label.Text = "";
 // 遍历DataRow数组的行和列，显示数据
label.Text += "<Table border='1'>";
label.Text += "<TR><TH>CustomerID</TH><TH>CompanyName</TH><TH>Country</TH></TR>";
foreach(DataRow row in rows)
 {
 label.Text += "<TR>";
 for(int i=0; i<row.Table.Columns.Count; i++)
 {
 label.Text += "<TD>";
 label.Text += row[i]; //每一行里都有数据
 label.Text += "</TD>";
 }
 label.Text += "</TR>";
 }
label.Text += "</Table>";2007EMMS
------------------------------------------------------------------------
9 使用DataView对数据进行排序和检索示例
// 填充数据到DataTable
 adapter.Fill(table);
// 创建DataView，并在构造函数中得到排序和检索条件等参数
 DataView dataView = new DataView(table, "Country='USA'", "CompanyName DESC", DataViewRowState.CurrentRows);
// 数据绑定
 myDataGrid.DataSource = dataView;
 myDataGrid.DataBind();
---------------------------------------------------------------------------
10 使用DataRelation类创建关系并利用父/子关系读取数据示例
string ConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
 string Sql = "SELECT * FROM Customers; SELECT * FROM Orders";

    // 创建 Connection 和 DataAdapter 对象
    SqlConnection myConn = new SqlConnection(ConnString);
    SqlDataAdapter sqlAdapter = new SqlDataAdapter(Sql, myConn);

    // 填充数据
    DataSet dataSet = new DataSet();
    sqlAdapter.Fill(dataSet, "Table");

    // 命名表名
    dataSet.Tables[0].TableName = "Customers";
    dataSet.Tables[1].TableName = "Orders";

    // 创建 Customers 和 Orders 的父/子表关系
dataSet.Relations.Add("CustomersOrders", dataSet.Tables["Customers"].Columns["CustomerID"],
     dataSet.Tables["Orders"].Columns["CustomerID"]);

// 使用 GetChildRows() 方法遍历子行
 foreach(DataRow custRow in dataSet.Tables["Customers"].Rows)
 {
myLabel.Text += "Parent Row: " + custRow["CustomerID"] + "  " + custRow["CompanyName"] + " ";
 myLabel.Text += "Child Row: ";
 foreach(DataRow orderRow in custRow.GetChildRows("CustomersOrders"))
 {
myLabel.Text += "     " + orderRow["OrderID"] + "  " + orderRow["EmployeeID"] + " ";
 }
 }
-------------------------------------------------------------------------------------------
11 数据库异常处理示例
 catch(SqlException ex)
 {
 // 异常信息显示
 myLabel.Text = "数据库执行错误 ";
 myLabel.Text += "错误信息：" + ex.Source + " ";
 myLabel.Text += "错误行号：" + ex.LineNumber +"行 ";
 myLabel.Text += "详细信息：" + ex.Message + " ";
 }
----------------------------------------------------------------------------------------
12 将数据库数据填充到 XML 文件示例
 // 创建 DataSet 对象
 DataSet data = new DataSet();
 // 填充 DataSet
 adapter.Fill(data, "Customers");
 // 将 DataSet 数据其及架构填充到 Xml 文件
 data.WriteXml(Server.MapPath(".") + "\\myXml.xml", XmlWriteMode.WriteSchema);
----------------------------------------------------------------------------
13 使用ConfigurationSettings.AppSettings属性读取自定义配置节示例
string ConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
 <appSettings>
 <add key="ConnectionSqlServer" value="Server=(local);User id=sa;Pwd=sa;Database=Northwind"></add>
</appSettings>
-----------------------------------------------
14 在 ASP.NET 使用存储过程示例
 myCommand.Connection = myConn;
// 指定要执行的存储过程名称
myCommand.CommandText = "CustomersProc";
// 使用要执行的是存储过程
myCommand.CommandType = CommandType.StoredProcedure;
 // 创建DataAdapter对象填充数据
DataSet myDS = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
adapter.Fill(myDS, "Customers");
15使用带输入参数的存储过程示例
// 指定要调用的存储过程名称 "Customer_Select"
// 指定SqlCommand对象的命令类型为 "StoredProcedure"枚举值
myCommand.CommandText = "Customer_Select";
myCommand.CommandType = CommandType.StoredProcedure;
// 创建SqlParameter对象，指定参数名称、数据类型、长度及参数值
SqlParameter para = new SqlParameter("@country", SqlDbType.NVarChar, 15);
para.Value = DropDownList1.SelectedValue;
myCommand.Parameters.Add(para);
// 关联SqlDataAdapter与SqlCommand对象
myAdapter.SelectCommand = myCommand;
------------------------------------------------------------
16 使用带输入、输出参数的存储过程示例
// 指定要执行的命令为存储过程
myCommand.CommandType = CommandType.StoredProcedure;
// 增加输入参数并赋值
myCommand.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 20);
myCommand.Parameters["@TitleOfCourtesy"].Value = myDropDownList.SelectedItem.Text;
myCommand.Parameters["@TitleOfCourtesy"].Direction = ParameterDirection.Input;
// 增加输出参数
myCommand.Parameters.Add("@empCount", SqlDbType.Int);
myCommand.Parameters["@empCount"].Direction = ParameterDirection.Output;
// 创建 DataAdapter 对象填充数据
DataSet myDS = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
adapter.Fill(myDS, "Customers");
// 使用 Label 控件显示输出参数的输出值
rtnLabel.Text = myCommand.Parameters["@empCount"].Value.ToString();
// 将返回的数据和 DataGrid 绑定显示
myDataGrid.DataSource = myDS.Tables["Customers"];
myDataGrid.DataBind();
-------------------------------------------------------
17 获得插入记录标识号的示例
 // 创建插入SQL语句及调用@@identity函数返回标识值
string insert_query = "insert into Categories (CategoryName,Description) values ('IT', 'Internet');"
+ "SELECT @@identity AS 'identity';";
-------------------------------------------------------
18 如何读取Excel表格中的数据
 void SubmitBtn_Click(object sender, System.EventArgs e)
 {
// 获取Excep文件的完整路径
string source = File1.Value;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + source + ";Extended Properties=Excel 8.0";
string query = "SELECT * FROM [Sheet1$]";
 OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(ConnStr));
 OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
 DataSet myDataSet = new DataSet();
 // 将 Excel 的[Sheet1]表内容填充到 DataSet 对象
 oleAdapter.Fill(myDataSet, "[Sheet1$]");
 // 数据绑定
 DataGrid1.DataSource = myDataSet;
 DataGrid1.DataMember = "[Sheet1$]";
 DataGrid1.DataBind();
}
------------------------------------------------------