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(""); } } } }
自己调试正确, 着急下班,嘿嘿 如有错误,多多指正
种一棵树最好的时间是十年前,其次是现在.