using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MS.Components.Data;
using MS.Components.Data.Base;
using MS.Components.Data.SQLServer;
using MS.Components.Data.Oracle;
using MS.Components.Data.ODBC;
using MS.Components.Data.OLEDB;
namespace MS.Components.Testing
{
/// <summary>
/// The functions implemented in this class are illustrations only.
/// </summary>
public partial class FormUsage : Form
{
public FormUsage()
{
InitializeComponent();
}
DataAccess da = null;
string conStr = "Data Source=172.30.136.6;Initial Catalog=ff_HuaWei;User ID=flexflow;Password=FF@cisco";
private static DataAccess _CreateDataSource(BackendType type)
{
DataAccess ret = null;
switch (type)
{
case BackendType.SQL: ret = new SQLServerDB(); break;
case BackendType.Oracle: ret = new OracleDB(); break;
case BackendType.Odbc: ret = new OdbcAccess(); break;
case BackendType.OleDb: ret = new OleDbAccess(); break;
}
return ret;
}
private void Init()
{
this.da = _CreateDataSource(BackendType.SQL);
}
// ********************* Connecting & Disconnecting *********************
private void Connect()
{
this.da.ConnectionString = conStr;
this.da.Connect(false);
// OR
if (this.da.Connect(conStr, true))
{
MessageBox.Show("successfull!");
}
;
// Note : The 憆econnect?parameter indicates whether any existing connection to be dropped and new connection to be created.
// When you specify false and there already an open connection available, then an exception will be generated.
}
private void Disconenct()
{
this.da.Disconnect();
}
// ********************* Retrieving Data *********************
private void GetData()
{
// 1. Basic data access with SQL statements
DataTable dt1 = this.da.GetData("SELECT * FROM Users");
// 2. Basic data access with SQL statements, passing an existing data table to be filled up with
DataTable dt2 = new DataTable();
this.da.GetData("SELECT * FROM Users", ref dt2);
// 3. Data access using DB commands.
DbCommand cmd1 = this._GenCommand();
DataTable dt3 = this.da.GetData(cmd1);
// 4. Data access using DB commands, , passing an existing data table to be filled up with
DbCommand cmd2 = this._GenCommand();
DataTable dt4 = new DataTable();
this.da.GetData(cmd2, ref dt4);
}
private void GetSingleValue()
{
// 1. Using commands
DbCommand cmd1 = this._GenCommand();
object o1 = this.da.GetSingleValue(cmd1);
// 2. Using direct SQL statement or procedure without parameters
object o2 = this.da.GetSingleValue("Query", CommandType.Text);
object o3 = this.da.GetSingleValue("Procedure", CommandType.StoredProcedure);
// 3. Using procedure with parameters
ParameterEngine pe = this._CreateParamEngine();
object o4 = this.da.GetSingleValue("Procedure", CommandType.StoredProcedure, pe);
}
// ********************* Executing Other Queries *********************
private void ExecuteNonQuery()
{
int result = 0;
// 1. Using commands
DbCommand cmd1 = this._GenCommand();
result = this.da.ExecuteNonQuery(cmd1);
// 2. Using direct SQL statement or procedure without parameters
result = this.da.ExecuteNonQuery("Query", CommandType.Text);
result = this.da.ExecuteNonQuery("Procedure", CommandType.StoredProcedure);
// 3. Using procedure with parameters
ParameterEngine pe = this._CreateParamEngine();
result = this.da.ExecuteNonQuery("Procedure", CommandType.StoredProcedure, pe);
// Checking error
if (result == DataAccess.ERROR_RESULT)
{
// Represents the last occured exception.
// LastException is available only for ExecuteNonQuery command.
// Data retrieval commands throws out the exceptions immediately.
throw this.da.LastException;
}
}
// ********************* DBCommand Generation *********************
private DbCommand _GenCommand()
{
// Direct SQL statements
DbCommand cmd1 = this.da.GenerateCommand("SELECT * FROM Users", CommandType.Text, null);
// Stored procedures
ParameterEngine pe = this._CreateParamEngine();
DbCommand cmd2 = this.da.GenerateCommand("spMyProcedure", CommandType.StoredProcedure, pe);
return null;
}
// ********************* Parameter Engine Creation *********************
private ParameterEngine _CreateParamEngine()
{
ParameterEngine pe = ParameterEngine.New(this.da);
// Specifying input parameters
pe.Add("param1", 1);
pe.Add("param1", DateTime.Now);
pe.Add("param1", "Hello");
// Specifying output parameter
pe.AddOutputParameter("OutParam1");
pe.AddOutputParameter("OutParam2", DbType.Int32, 4);
return pe;
}
// ********************* Retrieving Output Values From ParameterEngine *********************
private object _RetrieveOutputValue(ParameterEngine pe, string paramName)
{
object ret = pe.RetrieveOutputParameterValue(paramName);
return ret;
}
// ********************* Retrieving Output Values From ParameterEngine *********************
private void Transaction()
{
// Note : Only one transaction can be opened for a particular connection
// Begins a transaction on the existing connection
DbTransaction transaction = this.da.BeginTransaction();
// Commits the begun transaction
this.da.CommitTransaction();
// Rollbacks the changes made in the existing transaction
this.da.RollbackTransaction();
}
private void FormUsage_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
Init();
Connect();
}
}
}
联系客服
