分享

C# & SQL

 昵称3773528 2010-10-07

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

        }

    }

}

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约