(1)Oracle数据库应用ADO.NET
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace SJTSoft.SSO.BLL
{
public class MISOracleHelper
{
/// <summary>
/// 数据库连接
/// </summary>
private OracleConnection SqlConn = null;
/// <summary>
/// 连接字符串
/// </summary>
public string strConnString = null;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="strConnString">数据库连接字符串</param>
public MISOracleHelper(string strConnString)
{
this.strConnString = strConnString;
}
/// <summary>
/// 构造函数
/// </summary>
public MISOracleHelper()
{
}
/// <summary>
/// 打开一个数据库连接
/// </summary>
private void Open()
{
if (SqlConn == null)
{
SqlConn = new OracleConnection(this.strConnString);
SqlConn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void Close()
{
if (SqlConn != null)
{
SqlConn.Close();
SqlConn = null;
}
}
/// <summary>
/// 释放非托管数据库连接
/// </summary>
public void Dispose()
{
if (SqlConn != null)
{
SqlConn.Dispose();
SqlConn = null;
}
}
/// <summary>
/// 取得数据库连接
/// <returns>所取得的数据库连接</returns>
/// </summary>
public OracleConnection GetConnection()
{
return SqlConn;
}
/// <summary>
/// 执行SQL语句,同时带出一定数据
/// </summary>
/// <param name="procName">Prac语句</param>
/// <param name="tableName">DataTable的名称</param>
/// <param name="dsReturn">存有数据的DataSet</param>
/// <returns>操作成功:0,操作失败:<> 0</returns>
public void RunSProc(string strProcName, string tableName, OracleParameter[] prams, out DataSet dsReturn)
{
dsReturn = null;
try
{
dsReturn = new DataSet();
this.Open();
OracleCommand cmd = new OracleCommand(strProcName, SqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("myCursor", OracleType.Cursor);
cmd.Parameters["myCursor"].Direction = ParameterDirection.Output;
if (prams != null)
{
foreach (OracleParameter MyParameter in prams)
cmd.Parameters.Add(MyParameter);
}
OracleDataAdapter da = new OracleDataAdapter(cmd);
if (tableName != null && tableName.Trim() != "")
{
da.Fill(dsReturn, tableName);
}
else
{
da.Fill(dsReturn);
}
}
catch (Exception e)
{
throw e;
}
finally
{
this.Close();
}
}
}
}
(1)SQL Server数据库应用ADO.NET
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace SJTSoft.SSO.BLL
{
public class SISSQLHelper
{
/// <summary>
/// 数据库连接
/// </summary>
private SqlConnection SqlConn = null;
/// <summary>
/// 连接字符串
/// </summary>
public string strConnString = null;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="strConnString">数据库连接字符串</param>
public SISSQLHelper(string strConnString)
{
this.strConnString = strConnString;
}
/// <summary>
/// 构造函数
/// </summary>
public SISSQLHelper()
{
}
/// <summary>
/// 打开一个数据库连接
/// </summary>
private void Open()
{
if (SqlConn == null)
{
SqlConn = new SqlConnection(this.strConnString);
SqlConn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void Close()
{
if (SqlConn != null)
{
SqlConn.Close();
SqlConn = null;
}
}
/// <summary>
/// 执行SQL语句
/// <param name='strSQL'>SQL语句</param>
/// </summary>
public string RunSelect(string strSQL)
{
string selectResult = "";
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSQL, SqlConn);
cmd.CommandType = CommandType.Text;
object oj= cmd.ExecuteScalar();
if (oj == null)
{
selectResult= "";
}
else
{
selectResult = oj.ToString();
}
}
catch (Exception e)
{
throw e;
}
finally
{
this.Close();
}
return selectResult;
}
}
}
(3)ADO.NET的一些小知识点:
1、ExecuteScalar 返回值问题:用于只返回一个数据的情形下,非常好!
ExecuteScalar方法返回的类型是object类型,这个方法返回sql语句执行后的第一行第一列的值,由于不知到sql语句到底是什么样的结构(有可能是int,有可能是char等等),所以ExecuteScalar方法返回一个最基本的类型object,这个类型是所有类型的基类,换句话说:可以转换为任意类型。2、 ExecuteNonQuery 返回值问题 :
ExecuteNonQuery方法是用来执行insert、delete、update语句的,由于这些语句执行后只有一个结果:“影响了**行”,所以ExecuteNonQuery方法返回的是影响的行数(int)。
3、ExecuteNonQuery返回值问题:
ExecuteReader是执行只读向前的查询(SELECT语句),返回值是一个DataReader.