Sybase,Oracle,SQL 调用带参数的SQL语句和存储过程的区别

最近工作上需要熟悉Oracle.,Sybase,之前自己一直用SQL ,虽然三者之间大方向上没什么不同

但是在一些小细节上需要多加注意, 比如参数的调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;

namespace SybaseTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //Console.WriteLine("测试SQLexpress");
            //TestSQLExpress();
            Console.WriteLine("测试Sybase");
            TestSybase();
            //Console.WriteLine("测试Oracle");
            //TestOracle();
            Console.WriteLine("测试成功完成");
        }

        #region
        //测试SQL调用存储过程
        //SQL调用存储过程直接写存储过程名 并添加参数
        public static void TestSQLExpress()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = null;
            try
            {
                conn  = new SqlConnection(@"Data Source=iran\sqlexpress;Initial Catalog=test;Integrated Security=True;Pooling=False");

                SqlCommand cmd = new SqlCommand("procReturn", conn);

                SqlParameter p = new SqlParameter("@tid", 2);
                p.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(p);

                //输出参数需要指定长度
                SqlParameter p1 = new SqlParameter("@tname", SqlDbType.VarChar,12);
                p1.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p1);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                cmd.ExecuteNonQuery();

                string s = (string)p1.Value; //获取输出参数的值
                Console.WriteLine(s);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
        }
        #endregion
        
        #region
        //测试oracle调用存储过程
        //oracle调用带参数的SQL语句 注意格式 赋值方法不一样
        public static void TestOracle()
        {
            DataTable dt = new DataTable();

            try
            {
                 //带参数的SQL语句
                //OracleConnection conn = new OracleConnection("Data Source=CRM; uid=crmuser; pwd=13921");

                //OracleCommand cmd = new OracleCommand("select SMSDEFNAME from sms_def_1 where smsdef_id=:tid", conn);

                //OracleParameter p = new OracleParameter("tid", 856);
                //p.OracleType = OracleType.Number;

                //cmd.Parameters.Add(p);
                //OracleDataAdapter da = new OracleDataAdapter(cmd);

                //da.Fill(dt);
                
                //带参数的存储过程
                OracleConnection conn = new OracleConnection("Data Source=CRM; uid=crmuser; pwd=13921");

                OracleCommand cmd = new OracleCommand("TESTPROC1", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter p = new OracleParameter("tid", OracleType.Number);
                p.Value = 2;
                p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(p);


                OracleParameter p1 = new OracleParameter("tname", OracleType.VarChar,12);
                p1.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p1);
                conn.Open();
                cmd.ExecuteNonQuery();

                string s = p1.Value.ToString();  //获取输出参数的值
              
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
        }
        #endregion


        //测试Sybase调用存储过程
        //Sybase调用带参数的存储过程 其ComnmandText 格式为 存储过程名+"此处为空格" + ?<, ?>
        //例如  procname+" ?,?" 两个参数 (注意参数顺序 据说ODBC是有顺序调用参数 而并非参数名称 反正我调换位置会出错)
        public static void TestSybase()
        {
            OdbcConnection SybaseConn = null;
            OdbcCommand odbccmd = null;
            DataTable dt = new DataTable();
            DataTable dtproc = new DataTable();
            try
            {
                //连接串
                string strconn = "DSN=TEST;SRVR=TEST;DB=BFV752_T_JXC;UID=sa;PWD=;";

                SybaseConn = new OdbcConnection(strconn);

                SybaseConn.Open();
                string strProc = "BFBHDD.testproc4";
                odbccmd = new OdbcCommand();

                odbccmd.CommandType = CommandType.StoredProcedure;
                odbccmd.Connection = SybaseConn;
                odbccmd.CommandText = strProc + " ?,?";

                OdbcParameter odbcp1 = new OdbcParameter("@tid", OdbcType.Int, 4);
                odbcp1.Value = 3;
                odbcp1.Direction = ParameterDirection.Input;
                odbccmd.Parameters.Add(odbcp1);


                OdbcParameter odbcp2 = new OdbcParameter("@tname", OdbcType.VarChar, 12);
                odbcp2.Value = "testname";
                odbcp2.Direction = ParameterDirection.Input;
                odbccmd.Parameters.Add(odbcp2);
  
                OdbcDataAdapter da = new OdbcDataAdapter(odbccmd);

                da.Fill(dt); 
            }
            catch (OdbcException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                SybaseConn.Close();
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
        }

    }
}

 

自己调试正确, 着急下班,嘿嘿   如有错误,多多指正

转载于:https://www.cnblogs.com/ShuiMu/articles/2513706.html