Programs Knowledge

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" } });

        }

    }

}