[Implement] Insert/Update into DB
方法1:
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
namespace ExecuteSqlIntoDB
{
class Program
{
///<summary>
/// insert/update into DB
///</summary>
public string SaveData(int[] type, string[] targetTable, string[] whereCondition, string[][] columns, object[][] values)
{
string result = "error";
string queryStr = string.Empty;
//PLSQL使用username = system, password = oracle, database = xe, connect as = normal
OracleConnection oConn = new
OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnectionStr"].ToString());
OracleTransaction trans = null;
try
{
oConn.Open();
trans = oConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = oConn.CreateCommand();
command.Transaction = trans;
for (int k = 0; k < type.Length; k++)
{
command.Parameters.Clear();
//Insert data into DB
if (type[k] == 0)
{
queryStr = string.Format("insert into {0} ( ", targetTable[k]);
for (int i = 0; i < columns[k].Length; i++)
{
queryStr += string.Format("{0}", columns[k][i].Trim());
if (i != (columns[k].Length - 1))
queryStr += ", ";
else
queryStr += ") values( ";
}
for (int i = 0; i < values[k].Length; i++)
{
if (values[k][i].GetType().Name.Equals("String"))
queryStr += string.Format("UTL_RAW.CAST_TO_VARCHAR2(:{0})", columns[k][i].Trim());
else if (values[k][i].GetType().Name.Equals("DateTime"))
queryStr += string.Format("TO_TIMESTAMP('{0}','yyyy-mm-dd hh24:mi:ss')", ((DateTime)values[k][i]).ToString("yyyy-MM-dd HH:mm:ss"));
else
queryStr += string.Format("{0}", values[k][i].ToString());
if (i != (values[k].Length - 1))
queryStr += ", ";
else
queryStr += ") ";
}
command.CommandText = queryStr;
for (int i = 0; i < values[k].Length; i++)
{
if (values[k][i].GetType().Name.Equals("String"))
{
command.Parameters.Add(columns[k][i].Trim(), OracleType.Raw).Value =
System.Text.Encoding.GetEncoding("UTF-8").GetBytes(values[k][i].ToString());
}
}
}
//Update data into DB
if (type[k] == 1)
{
queryStr = string.Format("update {0} set ", targetTable[k]);
for (int i = 0; i < values[k].Length; i++)
{
if (values[k][i].GetType().Name.Equals("String"))
queryStr += string.Format("{0} = UTL_RAW.CAST_TO_VARCHAR2(:{0})", columns[k][i].Trim());
else if (values[k][i].GetType().Name.Equals("DateTime"))
queryStr += string.Format("{0} = TO_TIMESTAMP('{1}','yyyy-mm-dd hh24:mi:ss')", columns[k][i].Trim(), ((DateTime)values[k][i]).ToString("yyyy-MM-dd HH:mm:ss"));
else
queryStr += string.Format("{0} = {1}", columns[k][i].Trim(), values[k][i].ToString());
}
if (!string.IsNullOrEmpty(whereCondition[k]))
queryStr += string.Format(" where {0}", whereCondition[k]);
command.CommandText = queryStr;
for (int i = 0; i < values[k].Length; i++)
{
if (values[k][i].GetType().Name.Equals("String"))
{
command.Parameters.Add(columns[k][i].Trim(), OracleType.Raw).Value =
System.Text.Encoding.GetEncoding("UTF-8").GetBytes(values[k][i].ToString());
}
}
}
command.ExecuteNonQuery();
}
trans.Commit();
result = "succeed";
}
catch (Exception ex)
{
result = ex.Message;
}
finally
{
oConn.Close();
if(trans != null)
trans.Dispose();
}
return result;
}
static void Main(string[] args)
{
Program p = new Program();
//insert data
string result = p.SaveData(new int[] { 0, 0 },
new string[] { "test", "test" },
new string[] { string.Empty },
new string[][] { new string[] { "c1", "c2" },
new string[] { "c1", "c2" } },
new object[][] { new object[] { 3, "jack" }, new object[] { 4, "max" }}
);
result = p.SaveData(new int[] { 1, 1 },
new string[] { "test", "test" },
new string[] { "c1 = 3", "c1 = 4" },
new string[][] { new string[] { "c2" }, new string[] { "c2" } },
new object[][] { new object[] {"jack_new" }, new object[] { "max_new" } }
);
}
}
}方法2:
可使用子查詢更新資料
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
namespace ExecuteSqlIntoDB
{
class Program
{
///<summary>
/// insert/update into DB
///</summary>
public string SaveData(int[] type, string[] targetTable, string[] condition, string[][] columns, object[][] values, string[][] valuesType)
{
string result = "error";
string queryStr = string.Empty;
OracleConnection oconn = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["oraclestr"].ToString());
OracleTransaction transaction = null;
try
{
oconn.Open();
transaction = oconn.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand command = oconn.CreateCommand();
command.Transaction = transaction;
for (int k = 0; k < type.Length; k++)
{
command.Parameters.Clear();
//Insert data into DB
if (type[k] == 0)
{
queryStr = string.Format("insert into {0} ( ", targetTable[k]);
//ex. update item_20 set sign_comment = UTL_RAW.CAST_TO_VARCHAR2(:sign_comment) where f1 = '1049'
for (int i = 0; i < columns[k].Length; i++)
{
queryStr += string.Format("{0}", columns[k][i].Trim());
if (i != (columns[k].Length - 1))
queryStr += ", ";
else
queryStr += ") values( ";
}
for (int i = 0; i < values[k].Length; i++)
{
if (valuesType[k][i].ToUpper().Equals("STRING"))
queryStr += string.Format("UTL_RAW.CAST_TO_VARCHAR2(:{0})", columns[k][i].Trim());
else if (valuesType[k][i].ToUpper().Equals("DATETIME"))
queryStr += string.Format("TO_TIMESTAMP('{0}', 'yyyy-mm-dd hh24:mi:ss')", ((DateTime)values[k][i]).ToString("yyyy-MM-dd HH:mm:ss"));
else
queryStr += string.Format("{0}", values[k][i].ToString());
if (i != (columns[k].Length - 1))
queryStr += ", ";
else
queryStr += ") ";
}
command.CommandText = queryStr;
for (int i = 0; i < values[k].Length; i++)
{
if (valuesType[k][i].ToUpper().Equals("STRING"))
{
command.Parameters.Add(columns[k][i].Trim(), OracleType.Raw).Value =
System.Text.Encoding.GetEncoding("UTF-8").GetBytes(values[k][i].ToString());
}
}
}
//Update data into DB
if (type[k] == 1)
{
queryStr = string.Format("update {0} set ", targetTable[k]);
for (int i = 0; i < values[k].Length; i++)
{
if (valuesType[k][i].ToUpper().Equals("STRING"))
queryStr += string.Format("{0} = UTL_RAW.CAST_TO_VARCHAR2(:{0})", columns[k][i].Trim());
else if (valuesType[k][i].ToUpper().Equals("DATETIME"))
queryStr += string.Format("{0} = TO_TIMESTAMP('{1}', 'yyyy-mm-dd hh24:mi:ss')", columns[k][i].Trim(), ((DateTime)values[k][i]).ToString("yyyy-MM-dd HH:mm:ss"));
else
queryStr += string.Format("{0} = {1}", columns[k][i].Trim(), values[k][i].ToString());
if (i != (values[k].Length - 1))
queryStr += ", ";
}
if (!string.IsNullOrEmpty(condition[k]))
queryStr += string.Format(" where {0}", condition[k]);
command.CommandText = queryStr;
for (int i = 0; i < values[k].Length; i++)
{
if (valuesType[k][i].ToUpper().Equals("STRING"))
{
command.Parameters.Add(columns[k][i].Trim(), OracleType.Raw).Value =
System.Text.Encoding.GetEncoding("UTF-8").GetBytes(values[k][i].ToString());
}
}
}
command.ExecuteNonQuery();
}
//command.Transaction = transaction;
transaction.Commit();
result = "success";
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
transaction.Dispose();
oconn.Close();
}
return result;
}
static void Main(string[] args)
{
Program p = new Program();
//insert data
string result = p.SaveData(new int[] { 0, 0 },
new string[] { "table_A", "table_B" },
new string[] { string.Empty, string.Empty },
new string[][] { new string[] { "columnA1", "columnA2" }, new string[] { "columnB1", "columnB2" } },
new object[][] { new object[] { 3, "(SELECT columnC2 FROM table_C WHERE columnC1 = '1')" },
new object[] { 4, "(SELECT columnD2 FROM table_D WHERE columnD1 = '1')" } },
new string[][] { new string[] { "string", string.Empty }, new string[] { "string", string.Empty } });
}
}
}
方法3:
//連線設定分別需要輸入HOST、PORT、SID、User Id and Password
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1521)))(CONNECT_DATA=(SID=XE)));
User Id=system;Password=oracle");
oConn.Open();
string queryStr = "update TableName set ColumnName2 = 'test' where ColumnName1 = '1'";
OracleTransaction transaction = null;
transaction = oConn.BeginTransaction();
OracleCommand command = oConn.CreateCommand();
command.CommandText = queryStr;
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
if(transaction != null)
transaction.Dispose();
oConn.Close();