DalAccess.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using Test.Biz;
using System.Data.SqlClient;
namespace Test.Dal
{
partial class DaAccessDB
{
//Oracle server ------------------------------------------
private static string CONNECTION_STRING = "DATA SOURCE=10.3.6.77/orcl;PASSWORD=123456;PERSIST SECURITY INFO=True;USER ID=uthv;";
private static OracleConnection conn = new OracleConnection(CONNECTION_STRING);
////SQL server ------------------------------------------
//private static string ConnectString = "Data Source=localhost;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=1";
//private static SqlConnection conn = new SqlConnection(ConnectString);
internal static List<Biz.BizProduct> GetALL()
{
List<BizProduct> pros = new List<BizProduct>();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//Oracle server ------------------------------------------
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "SELECT * FROM PRODUCTS";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
OracleDataAdapter da = new OracleDataAdapter(cmd);
//end Oracle server ------------------------------------------
////SQL server ------------------------------------------
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "select * from PRODUCTS";
//cmd.CommandType = CommandType.Text;
//cmd.Connection = conn;
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//end SQL server ------------------------------------------
DataTable tb = new DataTable();
da.Fill(tb);
conn.Close();
if (tb.Rows.Count != 0)
{
for (int i = 0; i < tb.Rows.Count; i++)
{
BizProduct pro = new BizProduct();
pro.ProID = tb.Rows[i][0].ToString();
pro.ProCode = tb.Rows[i][1].ToString();
pro.ProName = tb.Rows[i][2].ToString();
pro.ProLine = tb.Rows[i][3].ToString();
pros.Add(pro);
}
return pros;
}
return null;
}
internal static string Add(BizProduct bizProduct)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//Oracle server ------------------------------------------
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "PRODUCT_ADD";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_PROID", OracleType.Char, 36).Direction = ParameterDirection.Output;
cmd.Parameters.Add("v_PROCODE", OracleType.Char, 200).Value = bizProduct.ProCode;
cmd.Parameters.Add("v_PRONAME", OracleType.NVarChar).Value = bizProduct.ProName;
cmd.Parameters.Add("v_PROLINE", OracleType.NVarChar).Value = bizProduct.ProLine;
cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
int rowsAffected = cmd.ExecuteNonQuery();
conn.Close();
// output the result
return cmd.Parameters["v_PROID"].Value.ToString();
//end Oracle server ------------------------------------------
////SQL server ------------------------------------------
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "dbo.PRODUCT_ADD";
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Direction = ParameterDirection.Output;
//cmd.Parameters.Add("@ProCode", SqlDbType.NVarChar).Value = bizProduct.ProCode;
//cmd.Parameters.Add("@Proname", SqlDbType.NVarChar).Value = bizProduct.ProName;
//cmd.Parameters.Add("@ProLine", SqlDbType.NVarChar).Value = bizProduct.ProLine;
//cmd.Connection = conn;
//int rowsAffected = cmd.ExecuteNonQuery();
//conn.Close();
//// output the result
//return cmd.Parameters["@ProID"].Value.ToString();
}
internal static string Update(BizProduct bizProduct)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//Oracle server ------------------------------------------
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "PRODUCT_UPDATE";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_PROID", OracleType.Char, 36).Value = bizProduct.ProID;
cmd.Parameters.Add("v_PROCODE", OracleType.Char, 200).Value = bizProduct.ProCode;
cmd.Parameters.Add("v_PRONAME", OracleType.NVarChar).Value = bizProduct.ProName;
cmd.Parameters.Add("v_PROLINE", OracleType.NVarChar).Value = bizProduct.ProLine;
cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
//cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
conn.Close();
// output the result
return cmd.Parameters["v_RETURN"].Value.ToString();
//End Oracle server ------------------------------------------
//SQL server ------------------------------------------
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "dbo.PRODUCT_UPDATE";
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Value = bizProduct.ProID;
//cmd.Parameters.Add("@ProCode", SqlDbType.NVarChar).Value = bizProduct.ProCode;
//cmd.Parameters.Add("@Proname", SqlDbType.NVarChar).Value = bizProduct.ProName;
//cmd.Parameters.Add("@ProLine", SqlDbType.NVarChar).Value = bizProduct.ProLine;
//cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
//cmd.Connection = conn;
////cmd.ExecuteNonQuery();
//cmd.ExecuteNonQuery();
//conn.Close();
//// output the result
//return cmd.Parameters["@Return"].Value.ToString();
}
internal static string Delete(BizProduct bizProduct)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//Oracle server ------------------------------------------
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "PRODUCT_DEL";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_PROID", OracleType.Char, 36).Value = bizProduct.ProID;
cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
//cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
conn.Close();
// output the result
return cmd.Parameters["v_RETURN"].Value.ToString();
//end Oracle server ------------------------------------------
////SQL server ------------------------------------------
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "dbo.PRODUCT_DEL";
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Value = bizProduct.ProID;
//cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
//cmd.Connection = conn;
////cmd.ExecuteNonQuery();
//cmd.ExecuteNonQuery();
//conn.Close();
//// output the result
//return cmd.Parameters["@Return"].Value.ToString();
////End SQL server ------------------------------------------
}
}
}
Bạn đang đọc truyện trên: AzTruyen.Top