using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
namespace ExecuteSqlIntoDB
{
class Program
{
///<summary>
/// insert/update into DB
///</summary>
///<param>0=insert, 1=update</param>
///<param></param>
///<param></param>
///<param></param>
///<param></param>
///<returns></returns>
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)
{
return 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" } });
}
}
}
留言列表
DATABASES (4)
