C# 连接Oracle，并调用存储过程（存在返回值），C# 调用sql存储过程

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1.获取Oracle表格信息

public OracleHelpers(string ConnStr)
{
ConnectionString = ConnStr;
conn = new OracleConnection(ConnectionString);
error = "";
//String connString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.210)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=TEST;Password=TEST123";
//Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.50.23)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=未知)));User ID=gadata0004;Password=gold; }
public bool CheckConn(out string err)
{
try
{
conn.Open();
err = "";
return true;
}
catch (Exception ex)
{
err = ex.Message;
return false;
}
finally
{
conn.Close();
}
}
//获取表名列表
public DataTable GetConnectionTables()
{
return GetDataTable("SELECT TABLE_NAME 表名 FROM USER_ALL_TABLES");
} //获取表名列表
public DataTable GetConnectionTables(string sqlStr)
{
return GetDataTable(sqlStr);
} //获取Oracle某连接下的所有视图名
public DataTable GetConnectionViews()
{
return GetDataTable("select View_Name from user_views");
} //根据sql语句返回datatable
public DataTable GetDataTable(string sqlStr)
{
DataTable dt = null;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
} OracleDataAdapter da = new OracleDataAdapter(sqlStr, conn); dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
error += "GetConnectionViews:" + ex.Message + "\r\n";
MessageBox.Show(error);
}
finally
{
conn.Close();
}
return dt;
} /// <summary>
/// 返回DataSet对象
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <returns>DataSet对象</returns>
public DataSet GetDataSet(string sqlStr)
{
DataSet ds = null;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
} OracleDataAdapter da = new OracleDataAdapter(sqlStr, conn); ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
error += "GetConnectionViews:" + ex.Message + "\r\n";
MessageBox.Show(error);
}
finally
{
conn.Close();
}
return ds;
} /// <summary>
/// 获取表的字段名
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>DataTable</returns>
public DataTable GetColumns(string tableName)
{
//string sSql = "SELE CT USER_TAB_COLS.COLUMN_NAME as 列名 FROM USER_TAB_COLS "
//+ "inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME "
//+ "and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME and USER_TAB_COLS.TABLE_NAME = '" + tableName + "'";
string sSql = "select distinct COLUMN_NAME as 列名 from all_TAB_COLUMNS where table_name = '" + tableName + "'";
return GetDataTable(sSql); ////获取表信息的T-SQL语句备用
//string sql = "SELECT USER_TAB_COLS.TABLE_NAME as 表名, USER_TAB_COLS.COLUMN_NAME as 列名, "
// + "USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH as 长度, "
// + "USER_TAB_COLS.NULLABLE as 是否为空, USER_TAB_COLS.COLUMN_ID as 列序号, user_col_comments.comments as 备注 "
// + "inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME "
// + "and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME and USER_TAB_COLS.TABLE_NAME = 'T_COMPANY_DETAIL" + "'";
}

2.调用三个存储过程增、删、改

public void item_update(string myitemcode,
string myitemcode_new, string myitemname, string myitemstyle,
string myitemnaturename, string myitemtypename, string mypositionname,
out int intmark, out string strreason)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "item_Update";
OracleParameter[] parm = new OracleParameter[];
parm[] = new OracleParameter("myitemcode", OracleType.VarChar);
parm[] = new OracleParameter("myitemcode_new", OracleType.VarChar);
parm[] = new OracleParameter("myitemname", OracleType.VarChar);
parm[] = new OracleParameter("myitemstyle", OracleType.VarChar);
parm[] = new OracleParameter("myitemnaturename", OracleType.VarChar);
parm[] = new OracleParameter("myitemtypename", OracleType.VarChar);
parm[] = new OracleParameter("mypositionname", OracleType.VarChar);
parm[] = new OracleParameter("intmark", OracleType.Int32);
parm[] = new OracleParameter("strreason", OracleType.VarChar,35);
for (int i = ; i < parm.Length - ; i++)
{
parm[i].Direction = ParameterDirection.Input;
}
//指定返回参数
parm[].Direction = ParameterDirection.Output;
parm[].Direction = ParameterDirection.Output;
//传入参数赋值
parm[].Value = myitemcode;
parm[].Value = myitemcode_new;
parm[].Value = myitemname;
parm[].Value = myitemstyle;
parm[].Value = myitemnaturename;
parm[].Value = myitemtypename;
parm[].Value = mypositionname;
//将对象放到cmd里面
for (int i = ; i < parm.Length; i++)
{
cmd.Parameters.Add(parm[i]);
}
if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery(); //返回参数
intmark = Convert.ToInt32(parm[].Value);//res
strreason = parm[].Value.ToString();//err
}
catch
{ intmark = ;
strreason = "";
} finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
}
public void item_deleted(string itemcode, out int intmark, out string strreason)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "item_deleted";
OracleParameter[] parm = new OracleParameter[];
parm[] = new OracleParameter("itemcode", OracleType.VarChar);
parm[] = new OracleParameter("intmark", OracleType.Int32);
parm[] = new OracleParameter("strreason", OracleType.VarChar,35);
for (int i = ; i < parm.Length - ; i++)
{
parm[i].Direction = ParameterDirection.Input;
}
parm[].Direction = ParameterDirection.Output;
parm[].Direction = ParameterDirection.Output; parm[].Value = itemcode;
for (int i = ; i < parm.Length; i++)
{
cmd.Parameters.Add(parm[i]);
}
if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery(); //返回参数
intmark = Convert.ToInt32(parm[].Value);//res
strreason = parm[].Value.ToString();//err
}
catch
{
intmark = ;
strreason = "";
} finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
} public void item_insert(string myitemcode, string myitemname, string myitemstyle, string myitemnaturename,
string myitemtypename, string mypositionname, string myitemunitgroupname, string myunitname, string mycustomname0, string mycustomname2,
out int intmark, out string strreason)
{
//ConnectionString
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "item_insert";
OracleParameter[] parm = new OracleParameter[];
parm[] = new OracleParameter("myitemcode", OracleType.VarChar);
parm[] = new OracleParameter("myitemname", OracleType.VarChar);
parm[] = new OracleParameter("myitemstyle", OracleType.VarChar);
parm[] = new OracleParameter("myitemnaturename", OracleType.VarChar);
parm[] = new OracleParameter("myitemtypename", OracleType.VarChar);
parm[] = new OracleParameter("mypositionname", OracleType.VarChar);
parm[] = new OracleParameter("myitemunitgroupname", OracleType.VarChar);
parm[] = new OracleParameter("myunitname", OracleType.VarChar);
parm[] = new OracleParameter("mycustomname0", OracleType.VarChar);//out //out
parm[] = new OracleParameter("mycustomname2", OracleType.VarChar);//out
intmark = ;
strreason = "";
parm[] = new OracleParameter("intmark", OracleType.Int32);
parm[] = new OracleParameter("strreason", OracleType.VarChar,35);
for (int i = ; i < parm.Length - ; i++)
{
parm[i].Direction = ParameterDirection.Input;
}
parm[].Direction = ParameterDirection.Output;
parm[].Direction = ParameterDirection.Output; parm[].Value = myitemcode;
parm[].Value = myitemname;
parm[].Value = myitemstyle;
parm[].Value = myitemnaturename;
parm[].Value = myitemtypename;
parm[].Value = mypositionname;
parm[].Value = myitemunitgroupname;
parm[].Value = myunitname;
parm[].Value = mycustomname0;
parm[].Value = mycustomname2; for (int i = ; i < parm.Length; i++)
{
cmd.Parameters.Add(parm[i]);
}
if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery(); //返回参数
intmark = Convert.ToInt32(parm[].Value);//res
strreason = parm[].Value.ToString();//err
}
catch
{
intmark = ;
strreason = "";
} finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
} }

3.三个存储过程整合成一个

public void OrecalNew(string CunChuName, OracleParameter[] parm, int OutCount, out List<string> Outlist)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CunChuName;
for (int i = ; i < parm.Length; i++)
{
cmd.Parameters.Add(parm[i]);
}
if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery(); //返回参数
if (OutCount != )
{
List<string> listOut = new List<string>();
for (int a = parm.Length - OutCount; a < parm.Length; a++)
{
listOut.Add(parm[a].Value.ToString().Trim());
}
Outlist = listOut;
}
else
{
Outlist = new List<string>();
}
}
catch
{
Outlist = new List<string>();
} finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
} }

4.C# 调取本地SQL server 存储过程

注释：C# 在调取SQL存储过程时，与调取Oracle存储过程完全一致

SQL 存储过程

ALTER PROCEDURE [dbo].[Get_Data]
@QQ int,
@WW int,
@EE VARCHAR(50),
@RR VARCHAR(50),
@TT int out,
@YY int out
as
begin
set @TT = @QQ * @WW
set @YY = @WW
end

调取

public void Begin()
{
SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=123;database=DB_Config");
//建立一个空的SqlCommand对象
SqlCommand comm = new SqlCommand();
//制定调用类型为存储过程
comm.CommandType = CommandType.StoredProcedure;
//存储过程的名称
comm.CommandText = "Get_Data";
//定义数组
SqlParameter[] parm = new SqlParameter[];
//指定变量的类型
parm[] = new SqlParameter("QQ", SqlDbType.BigInt);
parm[] = new SqlParameter("WW", SqlDbType.BigInt);
parm[] = new SqlParameter("EE", SqlDbType.VarChar);
parm[] = new SqlParameter("RR", SqlDbType.VarChar);
parm[] = new SqlParameter("TT", SqlDbType.BigInt);
parm[] = new SqlParameter("YY", SqlDbType.BigInt);
//设置变量为传入参数还是传出参数
parm[].Direction = ParameterDirection.Input;
parm[].Direction = ParameterDirection.Input;
parm[].Direction = ParameterDirection.Input;
parm[].Direction = ParameterDirection.Input;
parm[].Direction = ParameterDirection.Output;
parm[].Direction = ParameterDirection.Output;
//给传入参数赋值
parm[].Value = TT1.Text.ToString();
parm[].Value = TT2.Text.ToString();
parm[].Value = TT3.Text.ToString();
parm[].Value = TT4.Text.ToString(); //将对象放到Comm里面
for (int a = ; a < parm.Length; a++)
{
comm.Parameters.Add(parm[a]);
}
//执行
if (conn.State != ConnectionState.Open)
conn.Open(); comm.Connection = conn;
comm.ExecuteNonQuery();
//执行完接收返回参数
MessageBox.Show("信息："+ parm[].Value +"and"+parm[].Value);
}