/*
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this file,
 * You can obtain one at http://mozilla.org/MPL/2.0/.
 *
 * Portions created by the Initial Developer are
 * Copyright (C) 2012 the Initial Developer.
 * All Rights Reserved.
 *
 * The Original Code is this.
 *
 * The Initial Developer of the Original Code is the author below.
 */

/**
 * @file SqlServerEntity.cs
 * @brief auto generated.
 * @author mornongcloud
 * @date 2011/07/21
 */

using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace masterframe.model.entity
{
    /// <summary>
    /// Master Table Model Basement Entitiy
    /// </summary>
    public class SqlServerEntity
    {
        /// <summary>
        /// Connection string
        /// </summary>
        protected string datasource = "";

        SqlConnection connection;
        SqlDataAdapter da;
        ////Specify the DataAdapter, create a command builder to generate automatically update command
        //SqlCommandBuilder cb;

        /// <summary>
        /// Instantiation with a connection string
        /// </summary>
        /// <param name="ds">Connect string</param>
        public SqlServerEntity(string ds)
        {
            if (0 == ds.Length) return;
            datasource = ds;

            // create connection
            //cnn = new SqlConnection("Data Source=localhost;Database=test;User ID=root;password=root");
            this.connection = new SqlConnection(datasource);
        }

        /// <summary>
        /// Dispose a dataAdapter
        /// </summary>
        public void Dispose()
        {
            //破棄可能なフィールドを所有する型は、破棄可能でなければなりません	次の IDisposable 型のメンバーを作成するため、IDisposable を 'SqlServerEntity' で実装します: 'SqlConnection', 'SqlDataAdapter'。'SqlServerEntity' が以前に出荷されている場合、IDisposable を実装する新しいメンバーを追加することは、既存のコンシューマーにとって重大な変更になります。	AVMCS_CORE	SqlServerEntity.cs	8
            if (null != this.da)
            {
                this.da.Dispose();
                this.da = null;
            }
            if (null == this.connection)
            {
                this.connection.Dispose();
                this.connection = null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void Close()
        {
            if (null != this.da)
            {
                this.da.Dispose();
                this.da = null;
            }

            if (null == this.connection)
                this.connection = new SqlConnection(datasource);
            else
                this.connection.Close();
        }

        /// <summary>
        /// cf "SELECT * FROM sample"
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable OpenSelect(string sql)
        {
            if (null == this.connection)
                this.connection = new SqlConnection(datasource);

            try
            {
                //lock (this.connection)
                //{
                if (null != this.da)
                {
                    this.da.Dispose();
                    this.da = null;
                }
                this.da = new SqlDataAdapter(sql, this.connection);
                //cb = null;
                DataTable dt = new DataTable();
                // select
                this.da.Fill(dt);
                return dt;
            }
            //}
            catch (System.Exception e)
            {
                this.da.Dispose();
                this.da = null;
                this.connection.Close();
                throw e;
            }
        }

        /// <summary>
        /// Update with a DataTable
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>Success/Failure</returns>
        public bool Update(DataTable dt)
        {
            if (null == this.connection || null == da) return false;

            try
            {
                //lock (this.connection)
                //{
                //if (null == cb) cb = new SqlCommandBuilder(da);
                using (var cb = new SqlCommandBuilder(da))
                {
                    int ret = this.da.Update(dt);
                    dt.AcceptChanges();
                    return 0 < ret;
                }
                //}
            }
            catch (System.Exception e)
            {
                this.da.Dispose();
                this.da = null;
                this.connection.Close();
                throw e;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>Success/Failure</returns>
        public bool Update(string sql)
        {
            //if (null == this.connection) return false;

            //lock (this.connection)
            //{
            using (var conn = new SqlConnection(this.datasource))
            {
                // create command
                var cmd = new SqlCommand(sql, conn);

                // open
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                // execute
                cmd.ExecuteNonQuery();

                // close
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();

                return true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="sql"></param>
        /// <returns>Success/Failure</returns>
        public static bool Update(SqlConnection cn, string sql)
        {
            if (null == cn) return false;

            //lock (cn)
            //{
            // create command
            using (var cmd = new SqlCommand(sql, cn))
            {
                // open
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                // execute
                cmd.ExecuteNonQuery();

                // close
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
            }
            return true;
            //}
        }

        /// <summary>
        /// Update by sql
        /// </summary>
        /// <param name="cn">Connection</param>
        /// <param name="dt">DataTable</param>
        /// <param name="sql">Sql</param>
        /// <returns>Success/Failure</returns>
        public static bool Update(SqlConnection cn, DataTable dt, string sql)
        {
            if (null == cn) return false;

            //lock (cn)
            //{
            // create a command
            using (var cmd = new SqlCommand(sql, cn))
            {
                // set parameter
                foreach (DataRow row in dt.Rows)
                {
                    if (System.Data.DataRowState.Unchanged == row.RowState) continue;
                    foreach (DataColumn col in dt.Columns)
                    {
                        var nm = col.ColumnName;
                        var vl = row[nm];
                        if (null == vl)
                            continue;
                        //if ("string" == col.DataType.Name && 0 == ((string)vl).Length)
                        //    continue;
                        cmd.Parameters.Add(new SqlParameter(nm, vl));
                    }
                    // open
                    if (cmd.Connection.State != ConnectionState.Open)
                        cmd.Connection.Open();
                    // execute
                    cmd.ExecuteNonQuery();
                }

                // close
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();

                return true;
            }
            //}
        }

        /// <summary>
        /// Update by sql
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="dt"></param>
        /// <param name="ins"></param>
        /// <param name="upd"></param>
        /// <param name="del"></param>
        /// <returns>Success/Failure</returns>
        public static bool Update(SqlConnection cn, DataTable dt
            , string ins = "", string upd = "", string del = "")
        {
            if (null == cn) return false;

            //lock (cn)
            //{
            if (0 < ins.Length)
            {
                // create a command
                using (var cmd1 = new SqlCommand(ins, cn))
                {

                    // set parameter
                    foreach (DataRow row in dt.Rows)
                    {
                        if (System.Data.DataRowState.Added != row.RowState) continue;

                        cmd1.Parameters.Clear();
                        foreach (DataColumn col in dt.Columns)
                        {
                            var nm = col.ColumnName;
                            var vl = row[nm];
                            if (null == vl)
                                continue;
                            cmd1.Parameters.Add(new SqlParameter(nm, vl));
                        }
                        // open
                        if (cmd1.Connection.State != ConnectionState.Open)
                            cmd1.Connection.Open();
                        // execute
                        int ret = cmd1.ExecuteNonQuery();

                    }
                    // close
                    if (cmd1.Connection.State == ConnectionState.Open)
                        cmd1.Connection.Close();

                    cmd1.Dispose();
                }
            }

            if (0 < upd.Length)
            {
                // create a command
                using (var cmd2 = new SqlCommand(upd, cn))
                {

                    // set parameter
                    //cmd.Parameters.Add(new MySqlParameter("id", 13));
                    foreach (DataRow row in dt.Rows)
                    {
                        if (System.Data.DataRowState.Modified != row.RowState) continue;

                        cmd2.Parameters.Clear();
                        foreach (DataColumn col in dt.Columns)
                        {
                            var nm = col.ColumnName;
                            var vl = row[nm];
                            if (null == vl)
                                continue;
                            cmd2.Parameters.Add(new SqlParameter(nm, vl));
                        }
                        // open
                        if (cmd2.Connection.State != ConnectionState.Open)
                            cmd2.Connection.Open();
                        // execute
                        cmd2.ExecuteNonQuery();

                    }
                    // close
                    if (cmd2.Connection.State == ConnectionState.Open)
                        cmd2.Connection.Close();

                    cmd2.Dispose();
                }
            }


            if (0 < del.Length)
            {
                using (var cmd3 = new SqlCommand(del, cn))
                {
                    // set parameter
                    //cmd.Parameters.Add(new MySqlParameter("id", 13));
                    foreach (DataRow row in dt.Rows)
                    {
                        if (System.Data.DataRowState.Deleted != row.RowState) continue;

                        cmd3.Parameters.Clear();
                        // Primary key
                        if (0 < dt.PrimaryKey.Length)
                        {
                            foreach (DataColumn col in dt.PrimaryKey)
                            {
                                var nm = col.ColumnName;
                                var vl = row[nm, DataRowVersion.Original];
                                if (null == vl)
                                    continue;
                                cmd3.Parameters.Add(new SqlParameter(nm, vl));
                            }
                        }
                        // Key has not been set(Get all the other)
                        else
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                var nm = col.ColumnName;
                                var vl = row[nm];
                                if (null == vl)
                                    continue;
                                cmd3.Parameters.Add(new SqlParameter(nm, vl));
                            }
                        }

                        //open
                        if (cmd3.Connection.State != ConnectionState.Open)
                            cmd3.Connection.Open();
                        // execute
                        cmd3.ExecuteNonQuery();
                    }
                    // close
                    if (cmd3.Connection.State == ConnectionState.Open)
                        cmd3.Connection.Close();

                    cmd3.Dispose();
                }
            }
            return true;
            //}
        }

        /// <summary>
        /// InsertOrUpdate by DataTable
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>Success/Failure</returns>
        public bool InsertOrUpdate(DataTable dt)
        {
            if (null == this.connection) return false;
            using (var cb = new SqlCommandBuilder(da))
            {
                var rows = new List<DataRow>();
                foreach (DataRow row in dt.Rows)
                {
                    if (System.Data.DataRowState.Unchanged == row.RowState) continue;
                    if (System.Data.DataRowState.Deleted == row.RowState) continue;
                    if (System.Data.DataRowState.Detached == row.RowState) continue;
                    rows.Add(row);
                }
                int ret = this.da.Update(rows.ToArray());
                dt.AcceptChanges();
                return 0 < ret;
            }
        }

        /// <summary>
        /// Delete/Insert by DataTable
        /// </summary>
        /// <param name="cn">Connection</param>
        /// <param name="dt">DataTable</param>
        /// <returns>Success/Failure</returns>
        public static bool DeleteInsert(SqlConnection cn, DataTable dt)
        {
            // delete
            string sql1 = "delete from " + dt.TableName;
            SqlServerEntity.Update(cn, sql1);

            // insert
            foreach (DataRow row in dt.Rows)
            {
                string cols = "";
                string vals = "";
                foreach (DataColumn col in dt.Columns)
                {
                    var nm = col.ColumnName;
                    var vl = row[nm];
                    if (null == vl)
                        continue;
                    if (0 < cols.Length) cols = cols + ", ";
                    cols = cols + nm.ToString();
                    if (0 < vals.Length) vals = vals + ", ";
                    vals = vals + "'" + vl.ToString() + "'";
                }
                //string sql2 = "insert into " + dt.TableName + " (`MobileId`, `Type`, `Number`, `Name`) VALUES (" + vals + ")";
                string sql2 = "insert into " + dt.TableName + " (" + cols + ") VALUES (" + vals + ")";
                SqlServerEntity.Update(cn, sql2);
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="delrows"></param>
        /// <param name="sql">delete from [PROC_PARAM] where [PROC]=@PROC and [PARAM]=@PARAM</param>
        /// <returns>Success/Failure</returns>
        protected bool Delete(DataTable tbl, List<int> delrows, string sql)
        {
            return masterframe.model.entity.SqlServerEntity.Delete(new SqlConnection(datasource)
                , tbl, delrows, sql);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="dt"></param>
        /// <param name="delrows"></param>
        /// <param name="sql1">delete from [PROC_PARAM] where [PROC]=@PROC and [PARAM]=@PARAM</param>
        /// <returns>Success/Failure</returns>
        protected static bool Delete(SqlConnection cn, DataTable dt, List<int> delrows, string sql1)
        {
            using (var cmd1 = new SqlCommand(sql1, cn))
            {
                foreach (int idx in delrows)
                {
                    var row = dt.Rows[idx];

                    cmd1.Parameters.Clear();
                    // Primary key
                    if (0 < dt.PrimaryKey.Length)
                    {
                        foreach (DataColumn col in dt.PrimaryKey)
                        {
                            var nm = col.ColumnName;
                            var vl = row[nm, DataRowVersion.Original];
                            if (null == vl)
                                continue;
                            cmd1.Parameters.Add(new SqlParameter(nm, vl));
                        }
                    }
                    // Key has not been set(Get all the other)
                    else
                    {
                        foreach (DataColumn col in dt.Columns)
                        {
                            var nm = col.ColumnName;
                            var vl = row[nm];
                            if (null == vl)
                                continue;
                            cmd1.Parameters.Add(new SqlParameter(nm, vl));
                        }
                    }

                    // open
                    if (cmd1.Connection.State != ConnectionState.Open)
                        cmd1.Connection.Open();
                    // execute
                    cmd1.ExecuteNonQuery();
                }
                // close
                if (cmd1.Connection.State == ConnectionState.Open)
                    cmd1.Connection.Close();

                cmd1.Dispose();

                return true;
            }
        }

    }
}
