|
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(); } } } |
|
|
