public HttpResponseMessage Get(string report)
        {
            var connection              = SqlLib.getConnection();
            var commandText             = @"SELECT * FROM [dbo].[Reports] WHERE [name] = @name";
            HttpResponseMessage result  = new HttpResponseMessage(HttpStatusCode.OK);
            SqlCommand          command = new SqlCommand(commandText, connection);

            command.Parameters.AddWithValue("@name", report);
            try
            {
                connection.Open();
                using (var sqlQueryResult = command.ExecuteReader())
                    if (sqlQueryResult != null)
                    {
                        Debug.WriteLine("SqlResult is successfulr");
                        var    reportObj = sqlQueryResult.Read();
                        byte[] template  = (byte[])sqlQueryResult["template"];
                        var    startRow  = Int32.Parse(sqlQueryResult["startRow"].ToString());
                        var    table     = sqlQueryResult["table"].ToString();
                        var    map       = sqlQueryResult["map"].ToString();


                        using (var fs = new FileStream(@"c:\temp\Sample.xlsx", FileMode.Create, FileAccess.Write))
                            fs.Write(template, 0, template.Length);
                        DataTable dt = getColumns(table, map);
                        result = writeToExcel(dt);
                    }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Ëxception is thrown");
                Debug.WriteLine(ex.Message);
            }
            return(result);
        }
Beispiel #2
0
        public HttpResponseMessage GetCategoryId(string table)
        {
            List <object> columnNames   = new List <object>();
            var           sqlConnection = SqlLib.getConnection();
            var           commandText   = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table  AND TABLE_SCHEMA='dbo'";
            SqlCommand    command       = new SqlCommand(commandText, sqlConnection);

            command.Parameters.AddWithValue("@table", table);
            try
            {
                sqlConnection.Open();
                using (var sqlQueryResult = command.ExecuteReader())
                    if (sqlQueryResult != null)
                    {
                        Debug.WriteLine("SqlResult is successfull");


                        while (sqlQueryResult.Read())
                        {
                            var tableName = sqlQueryResult.GetValue(0).ToString();
                            columnNames.Add(tableName);
                        }

                        Debug.WriteLine("Printing all the tableNames...");
                        Debug.WriteLine(columnNames);
                        Debug.WriteLine("finished");
                    }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Ëxception is thrown");
                Debug.WriteLine(ex.Message);
            }
            var response = Request.CreateResponse(HttpStatusCode.OK);

            response.Content = new ObjectContent <List <object> >(columnNames, Configuration.Formatters.JsonFormatter, "application/json");
            return(response);
        }
Beispiel #3
0
        static void Main(string[] args)
        {
            string server   = "hds-039.hichina.com";
            string database = "hds0390432_db";
            string username = "******";
            string password = "******";

            Console.WriteLine(123);
            List <string> itemList = new List <string> {
                "id", "item1", "item2"
            };
            List <string> valueList1 = new List <string> {
                "4", "tracy", "test1"
            };
            List <string> valueList2 = new List <string> {
                "5", "judy", "test2"
            };
            List <string> valueList3 = new List <string> {
                "6", "jorden", "test3"
            };
            List <List <string> > valueListArr = new List <List <string> >();

            valueListArr.Add(valueList1);
            valueListArr.Add(valueList2);
            valueListArr.Add(valueList3);
            //SqlConnection conn = new SqlConnection();
            //conn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\mssqlsystemresource.mdf;Integrated Security=True;Connect Timeout=3";
            //conn.Open();
            SqlLib sl = new SqlLib(server, database, username, password);

            Console.WriteLine("this is MSSqlLib test fun!");
            //Console.WriteLine(conn.ServerVersion);
            Console.WriteLine(sl.Version);
            sl.Insert("test", itemList, valueListArr);
            Console.WriteLine(sl.EchoSql());
            sl.CloseConn();
        }
        public void deleteOrderHistAllInsurantData(SqlLib lib, IDb db, object obj, ref DataTable outInsuranceUsrDt)
        {
            string sql = "";
            bool ret = true;
            try
            {
                OrderInfo localOrderInfo = new OrderInfo();
                ret = DataHandle.CompareObjectType(obj, localOrderInfo);
                if (ret)
                {
                    localOrderInfo = (OrderInfo)obj;
                }
                else
                {
                    return;
                }
                if (outInsuranceUsrDt == null)
                {
                    throw new NullReferenceException("outInsuranceUsrDt is null");
                }

                string InsuranceUsrID = "";

                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetOrderHistInfo");
                sql = sql + string.Format(" and orderid = '{0}'", localOrderInfo.ORDERID);


                /// 保险人信息
                InsuranceUsrInfo localInsuranceUsrInfo = new InsuranceUsrInfo();

                CTrace.WriteLine(CTrace.TraceLevel.Info, "insurance.outOrderInfo sql={0}.", sql);
                DataTable dt = db.GetTable(sql);

                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow locaorderDr = dt.Rows[0];

                    localOrderInfo = DataRowToOrderModel(locaorderDr);

                    if (localOrderInfo.BENEFICIARIES != null && !string.IsNullOrEmpty(localOrderInfo.BENEFICIARIES))
                    {
                        InsuranceUsrID = "'" + localOrderInfo.BENEFICIARIES.ToString() + "',";
                    }
                    if (localOrderInfo.BENEFICIARIES2 !=null && !string.IsNullOrEmpty(localOrderInfo.BENEFICIARIES2))
                    {
                        InsuranceUsrID =  InsuranceUsrID +"'" + localOrderInfo.BENEFICIARIES2.ToString() + "',";
                    }
                    if (localOrderInfo.BENEFICIARIES3 != null && !string.IsNullOrEmpty(localOrderInfo.BENEFICIARIES3))
                    {
                        InsuranceUsrID = InsuranceUsrID + "'" + localOrderInfo.BENEFICIARIES3.ToString() + "',";
                    }
                    if (localOrderInfo.INSURANT != null && !string.IsNullOrEmpty(localOrderInfo.INSURANT))
                    {
                        InsuranceUsrID = InsuranceUsrID + "'" + localOrderInfo.INSURANT.ToString() + "',";
                    }
                    if (localOrderInfo.POLICYHOLDER !=null && !string.IsNullOrEmpty(localOrderInfo.POLICYHOLDER))
                    {
                        InsuranceUsrID = InsuranceUsrID + "'" + localOrderInfo.POLICYHOLDER.ToString() + "',";
                    }
                }


                if (string.IsNullOrEmpty(InsuranceUsrID))
                {
                    return;
                }
                DataTable localdt = new DataTable();
                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrInfo");


                if (!string.IsNullOrEmpty(InsuranceUsrID))
                {
                    InsuranceUsrID = InsuranceUsrID.Substring(0, InsuranceUsrID.Length - 1);
                    sql += " and INSURANCE_USRID in (" + InsuranceUsrID + " )";
                }
                else
                {
                    throw new NullReferenceException("保单详细信息不能为空");
                }
                if (!string.IsNullOrEmpty(sql))
                {
                    localdt = db.GetTable(sql);
                }

                for (int i = 0; i < localdt.Rows.Count; i++)
                {
                    DataRow dr = localdt.Rows[i];
                    dr.Delete();
                }
                outInsuranceUsrDt.Merge(localdt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
                throw;
            }
        }
        public void deleteOrderHistAllDetData(SqlLib lib, IDb db, object obj, ref DataTable outOrderDetDt)
        {
            string sql = "";
            bool ret = true;
            try
            {
                OrderInfo localOrderinfo = new OrderInfo();
                ret = DataHandle.CompareObjectType(obj, localOrderinfo);
                if (ret)
                {
                    localOrderinfo = (OrderInfo)obj;
                }
                else
                {
                    return;
                }
                if (outOrderDetDt == null)
                {
                    throw new NullReferenceException("outOrderDetInfo is null");
                }
                DataTable localdt = new DataTable();
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetOrderDetInfo");

                if (!string.IsNullOrEmpty(localOrderinfo.ORDERID))
                {
                    sql += " and ORDERID='" + localOrderinfo.ORDERID + "'";
                }
                else
                {
                    throw new NullReferenceException("保单信息不能为空");
                }
                if (!string.IsNullOrEmpty(sql))
                {
                    localdt = db.GetTable(sql);
                }

                for (int i = 0; i < localdt.Rows.Count; i++)
                {
                    DataRow dr = localdt.Rows[i];
                    dr.Delete();
                }

                outOrderDetDt.Merge(localdt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteOrderHistAllDetData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteOrderHistAllDetData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteOrderHistAllDetData" + ex.Message);
                throw;
            }


        }
        public void deleteOrderHistData(SqlLib lib, IDb db, object obj, ref DataTable outDt)
        {
            string sql = "";
            bool ret = true;

            try
            {
                OrderInfo orderInfo = new OrderInfo();
                ret = DataHandle.CompareObjectType(obj, orderInfo);

                if (ret)
                {
                    //get the correct list.
                    orderInfo = (OrderInfo)obj;
                }
                else
                {
                    return;
                }

                if (outDt == null)
                {
                    throw new NullReferenceException("outDt is null");
                }

                DataTable localDt = new DataTable();

                //每次删除一条或者多条.
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetOrderInfo");

                if (!string.IsNullOrEmpty(orderInfo.ORDERID))
                {
                    sql = sql + " and ORDERID= '" + orderInfo.ORDERID + "'";
                }
                else
                {
                    //不允许都为空的情况,那样就全部删除了。抛出异常。
                    throw new NullReferenceException("产品详细关联号不可以为空!");
                }

                if (!string.IsNullOrEmpty(sql))
                {
                    localDt = db.GetTable(sql);
                }

                for (int i = 0; i < localDt.Rows.Count; i++)
                {
                    DataRow dr = localDt.Rows[i];

                    dr.Delete();
                }
                //ScmProviderData li = new ScmProviderData();

                //ret = DataHandle.CompareObjectType(obj, li);

                //if (ret)
                //{
                //    //get the correct list.
                //    li = (ScmProviderData)obj;
                //}
                //else
                //{
                //    return;
                //}

                //if (outDtPlace == null)
                //{
                //    throw new NullReferenceException("outDtPlace is null");
                //}

                //DataTable localDt = new DataTable();

                ////每次删除一条或者多条.
                //sql = lib.GetSql("SqlPlace/PlaceProdInfo", "GetPlaceProdRelationInfo");

                //if (!string.IsNullOrEmpty(li.PROVIDERID))
                //{
                //    sql = sql + " and PROVIDERID= '" + li.PROVIDERID + "'";
                //}
                //else
                //{
                //    //不允许都为空的情况,那样就全部删除了。抛出异常。
                //    throw new NullReferenceException("场所号和场所产品关联号不可以同时为空!");
                //}

                //if (!string.IsNullOrEmpty(sql))
                //{
                //    localDt = db.GetTable(sql);
                //}


                //for (int i = 0; i < localDt.Rows.Count; i++)
                //{
                //    DataRow dr = localDt.Rows[i];

                //    dr.Delete();
                //}

                //outDtPlace.Merge(localDt, true);

                outDt.Merge(localDt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteContractData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteContractData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteContractData" + ex.Message);
                throw;
            }
        }
        public void updateOrderHistData(SqlLib lib, IDb db, object obj, ref DataTable outOrderHistDt)
        {
            string sql = "";
            bool ret = true;

            try
            {

                OrderInfo li = new OrderInfo();

                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    //get the correct list.
                    li = (OrderInfo)obj;
                }
                else
                {
                    return;
                }

                if (outOrderHistDt == null)
                {
                    throw new NullReferenceException("outOrderHistDt is null");
                }


                DataTable localDt = new DataTable();
                //每次更新一条数据
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetOrderInfo");
                if (!string.IsNullOrEmpty(li.ORDERID))
                {
                    sql = sql + " and orderid= '" + li.ORDERID + "'";
                    localDt = db.GetTable(sql);
                    if (localDt.Rows.Count == 1)
                    {
                        DataRow dr = localDt.Rows[0];
                        dr.BeginEdit();
                        dr["orderid"] = DataHandle.EmptyString2DBNull(li.ORDERID);
                        dr["contactid"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                        dr["addressid"] = DataHandle.EmptyString2DBNull(li.ADDRESSID);

                        dr["ACCDT"] = DataHandle.EmptyString2DBNull(li.ACCDT);
                        dr["AMORTISATION"] = DataHandle.EmptyString2DBNull(li.AMORTISATION);
                        dr["BENEFICIARIES"] = DataHandle.EmptyString2DBNull(li.BENEFICIARIES);
                        dr["BILL"] = DataHandle.EmptyString2DBNull(li.BILL);
                        dr["BILLDEMONDDSC"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDDSC);
                        dr["BILLDEMONDED"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDED);
                        dr["BILLTITLE"] = DataHandle.EmptyString2DBNull(li.BILLTITLE);
                        dr["CARDID"] = DataHandle.EmptyString2DBNull(li.CARDID);
                        dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                        dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);

                        dr["CONFIRM"] = DataHandle.EmptyString2DBNull(li.CONFIRM);
                        dr["CONSIGNEE"] = DataHandle.EmptyString2DBNull(li.CONSIGNEE);
                        dr["CONSIGNPHN"] = DataHandle.EmptyString2DBNull(li.CONSIGNPHN);
                        dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                        dr["DEMONDDT"] = DataHandle.EmptyString2DBNull(li.DEMONDDT);
                        dr["DISCOUNT"] = DataHandle.EmptyString2DBNull(li.DISCOUNT);
                        dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                        dr["GRPID"] = DataHandle.EmptyString2DBNull(li.GRPID);
                        dr["HEALTHINTRO"] = DataHandle.EmptyString2DBNull(li.HEALTHINTRO);
                        dr["INSURANCEID"] = DataHandle.EmptyString2DBNull(li.INSURANCEID);

                        dr["INSURANT"] = DataHandle.EmptyString2DBNull(li.INSURANT);
                        dr["MAILPRICE"] = DataHandle.EmptyString2DBNull(li.MAILPRICE);
                        dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                        dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                        dr["MONITORRECORDER"] = DataHandle.EmptyString2DBNull(li.MONITORRECORDER);
                        dr["NOTE"] = DataHandle.EmptyString2DBNull(li.NOTE);
                        dr["NOWMONEY"] = DataHandle.EmptyString2DBNull(li.NOWMONEY);
                        dr["POLICYHOLDER"] = DataHandle.EmptyString2DBNull(li.POLICYHOLDER);
                        dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                        dr["PRODPRICE"] = DataHandle.EmptyString2DBNull(li.PRODPRICE);

                        dr["PRODUCTINTRO"] = DataHandle.EmptyString2DBNull(li.PRODUCTINTRO);
                        dr["RESULT"] = DataHandle.EmptyString2DBNull(li.RESULT);
                        dr["SENDDT"] = DataHandle.EmptyString2DBNull(li.SENDDT);
                        dr["SPECIALDSC"] = DataHandle.EmptyString2DBNull(li.SPECIALDSC);
                        dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                        dr["TOTALPRICE"] = DataHandle.EmptyString2DBNull(li.TOTALPRICE);
                        dr["URGENT"] = DataHandle.EmptyString2DBNull(li.URGENT);
                        dr["ordertype"] = DataHandle.EmptyString2DBNull(li.ordertype);
                        dr["paytype"] = DataHandle.EmptyString2DBNull(li.paytype);
                        dr["mailtype"] = DataHandle.EmptyString2DBNull(li.mailtype);
                        dr["CRDT"] = DataHandle.EmptyString2DBNull(li.crdt);
                        dr.EndEdit();
                        outOrderHistDt.Merge(localDt, true);
                    }
                    else
                    {
                        //do nothing
                    }
                }
                else
                {
                    //do nothing
                }

                //ScmProviderData li = new ScmProviderData();

                //ret = DataHandle.CompareObjectType(obj, li);

                //if (ret)
                //{
                //    //get the correct list.
                //    li = (ScmProviderData)obj;
                //}
                //else
                //{
                //    return;
                //}

                //if (outDtPlace == null)
                //{
                //    throw new NullReferenceException("outDtPlace is null");
                //}

                //DataTable localDt = new DataTable();

                ////每次更新一条。
                //sql = lib.GetSql("SqlPlace/PlaceProdInfo", "GetPlaceProdRelationInfo");

                //if (!string.IsNullOrEmpty(li.PROVIDERID))
                //{
                //    sql = sql + " and PROVIDERID= '" + li.PROVIDERID + "'";
                //    localDt = db.GetTable(sql);

                //    //have one record
                //    if (localDt.Rows.Count == 1)
                //    {
                //        // only one record.
                //        DataRow dr = localDt.Rows[0];

                //        dr.BeginEdit();
                //        dr["PROVIDERID"] = DataHandle.EmptyString2DBNull(li.PROVIDERID);

                //        dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                //        dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                //        dr["DSC"] = DataHandle.EmptyString2DBNull(li.DSC.ToString());


                //        dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                //        dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                //        dr["NAME"] = DataHandle.EmptyString2DBNull(li.NAME.ToString());
                //        dr.EndEdit();

                //        outDtPlace.Merge(localDt, true);
                //    }
                //    else
                //    {
                //        //do nothing.
                //    }
                //}
                //else
                //{
                //    // do nothing
                //}


            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderHistData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderHistData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderHistData" + ex.Message);
                throw;
            }
        }
        public void addNewOrderHistData(SqlLib lib, IDb db, object obj, ref DataTable outDt)
        {
            string sql = "";
            string localID = "";

            bool ret = false;

            try
            {
                OrderInfo li = new OrderInfo();

                ret = DataHandle.CompareObjectType(obj, li);

                if (ret)
                {
                    //get the correct list.
                    li = (OrderInfo)obj;
                }
                else
                {
                    return;
                }

                if (outDt == null)
                {
                    throw new NullReferenceException("outDt is null");
                }


                DataTable localDt = new DataTable();

                //只取一条记录,以便得到表结构,如果一条都没有,那么会有什么结果?能够取出表结构。
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetOrderHistStructInfo");
                localDt = db.GetTable(sql);

                //新增加的记录,在oracle中需要取到sequence编号
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetNewOrderHistId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }

                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }

                DataRow dr = localDt.NewRow();

                dr["orderid"] = DataHandle.EmptyString2DBNull(localID);
                dr["contactid"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                dr["addressid"] = DataHandle.EmptyString2DBNull(li.ADDRESSID);

                dr["ACCDT"] = DataHandle.EmptyString2DBNull(li.ACCDT);
                dr["AMORTISATION"] = DataHandle.EmptyString2DBNull(li.AMORTISATION);
                dr["BENEFICIARIES"] = DataHandle.EmptyString2DBNull(li.BENEFICIARIES);
                dr["BILL"] = DataHandle.EmptyString2DBNull(li.BILL);
                dr["BILLDEMONDDSC"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDDSC);
                dr["BILLDEMONDED"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDED);
                dr["BILLTITLE"] = DataHandle.EmptyString2DBNull(li.BILLTITLE);
                dr["CARDID"] = DataHandle.EmptyString2DBNull(li.CARDID);
                dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);

                dr["CONFIRM"] = DataHandle.EmptyString2DBNull(li.CONFIRM);
                dr["CONSIGNEE"] = DataHandle.EmptyString2DBNull(li.CONSIGNEE);
                dr["CONSIGNPHN"] = DataHandle.EmptyString2DBNull(li.CONSIGNPHN);
                dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                dr["DEMONDDT"] = DataHandle.EmptyString2DBNull(li.DEMONDDT);
                dr["DISCOUNT"] = DataHandle.EmptyString2DBNull(li.DISCOUNT);
                dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                dr["GRPID"] = DataHandle.EmptyString2DBNull(li.GRPID);
                dr["HEALTHINTRO"] = DataHandle.EmptyString2DBNull(li.HEALTHINTRO);
                dr["INSURANCEID"] = DataHandle.EmptyString2DBNull(li.INSURANCEID);

                dr["INSURANT"] = DataHandle.EmptyString2DBNull(li.INSURANT);
                dr["MAILPRICE"] = DataHandle.EmptyString2DBNull(li.MAILPRICE);
                dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                dr["MONITORRECORDER"] = DataHandle.EmptyString2DBNull(li.MONITORRECORDER);
                dr["NOTE"] = DataHandle.EmptyString2DBNull(li.NOTE);
                dr["NOWMONEY"] = DataHandle.EmptyString2DBNull(li.NOWMONEY);
                dr["POLICYHOLDER"] = DataHandle.EmptyString2DBNull(li.POLICYHOLDER);
                dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                dr["PRODPRICE"] = DataHandle.EmptyString2DBNull(li.PRODPRICE);

                dr["PRODUCTINTRO"] = DataHandle.EmptyString2DBNull(li.PRODUCTINTRO);
                dr["RESULT"] = DataHandle.EmptyString2DBNull(li.RESULT);
                dr["SENDDT"] = DataHandle.EmptyString2DBNull(li.SENDDT);
                dr["SPECIALDSC"] = DataHandle.EmptyString2DBNull(li.SPECIALDSC);
                dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                dr["TOTALPRICE"] = DataHandle.EmptyString2DBNull(li.TOTALPRICE);
                dr["URGENT"] = DataHandle.EmptyString2DBNull(li.URGENT);
                dr["ordertype"] = DataHandle.EmptyString2DBNull(li.ordertype);
                dr["paytype"] = DataHandle.EmptyString2DBNull(li.paytype);
                dr["mailtype"] = DataHandle.EmptyString2DBNull(li.mailtype);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.crdt);

                localDt.Rows.Add(dr);

                outDt.Merge(localDt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
                throw;
            }
        }
        public bool updataInsuranceUsrDataTable(SqlLib lib, IDb db, DataTable indt)
        {
            bool ret = false;
            try
            {

                if (indt.Rows.Count == 0)
                {
                    //没有记录,相当于这方法没有执行。
                    return ret;
                }

                //更新OrderHist表
                //循环更新所有的。
                for (int i = 0; i < indt.Rows.Count; i++)
                {
                    DataRow dr = indt.Rows[i];

                    string localOrderid = "";

                    if (dr.RowState == DataRowState.Unchanged)
                    {
                        //do nothing.jump out the loop.
                        continue;
                    }
                    else
                        if (dr.RowState == DataRowState.Deleted)
                        {
                            //do nothing.
                        }
                        else
                        {
                            localOrderid = dr["INSURANCE_USRID"].ToString();
                        }

                    string sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrStructInfo");
                    CTrace.WriteLine(CTrace.TraceLevel.Info, "updateOrderDetDataTable sql={0}.", sql);

                    ret = db.Save(indt, sql);
                    CTrace.WriteLine(CTrace.TraceLevel.Info, "updataInsuranceUsrDataTable result={0}.", ret.ToString());
                    if (ret)
                    {
                        //return true.
                    }
                    else
                    {
                        db.Rollback();
                        localOrderid = "";
                        CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updataInsuranceUsrDataTable" + db.Error.Message);
                    }
                }

            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updataInsuranceUsrDataTable" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updataInsuranceUsrDataTable" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updataInsuranceUsrDataTable" + ex.Message);
                throw;
            }

            return ret;

        }
        public void deleteInsuranceUsrData(SqlLib lib, IDb db, object obj, ref DataTable outInsuranceUsrDt)
        {
            string sql = "";
            bool ret = true;
            try
            {
                InsuranceUsrInfo InsuranceUsr = new InsuranceUsrInfo();
                ret = DataHandle.CompareObjectType(obj, InsuranceUsr);
                if (ret)
                {
                    InsuranceUsr = (InsuranceUsrInfo)obj;
                }
                else
                {
                    return;
                }
                if (outInsuranceUsrDt == null)
                {
                    throw new NullReferenceException("outInsuranceUsrDt is null");
                }
                DataTable localdt = new DataTable();
                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrInfo");

                if (!string.IsNullOrEmpty(InsuranceUsr.INSURANCE_USRID))
                {
                    sql += " and INSURANCE_USRID='" + InsuranceUsr.INSURANCE_USRID + " '";
                }
                else
                {
                    throw new NullReferenceException("保单详细信息不能为空");
                }
                if (!string.IsNullOrEmpty(sql))
                {
                    localdt = db.GetTable(sql);
                }

                for (int i = 0; i < localdt.Rows.Count; i++)
                {
                    DataRow dr = localdt.Rows[i];
                    dr.Delete();
                }
                outInsuranceUsrDt.Merge(localdt);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in deleteInsuranceUsrData" + ex.Message);
                throw;
            }
        }
 public void updateInsuranceUsrData(SqlLib lib, IDb db, object obj, ref DataTable outInsuranceUsrDt)
 {
     string sql = "";
     bool ret = true;
     try
     {
         InsuranceUsrInfo li = new InsuranceUsrInfo();
         ret = DataHandle.CompareObjectType(obj, li);
         if (ret)
         {
             li = (InsuranceUsrInfo)obj;
         }
         else
         {
             return;
         }
         if (outInsuranceUsrDt == null)
         {
             throw new NullReferenceException("outInsuranceUsrDt is null");
         }
         DataTable localDt = new DataTable();
         //根据Id更新一条数据信息
         sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrInfo");
         localDt = db.GetTable(sql);
         if (!string.IsNullOrEmpty(li.INSURANCE_USRID))
         {
             sql += " and INSURANCE_USRID='" + li.INSURANCE_USRID + "'";
             localDt = db.GetTable(sql);
             if (localDt.Rows.Count > 0)
             {
                 DataRow dr = localDt.NewRow();
                 dr["INSURANCE_USRID"] = DataHandle.EmptyString2DBNull(li.INSURANCE_USRID);
                 dr["Proportion"] = DataHandle.EmptyString2DBNull(li.Proportion);
                 dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                 dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                 dr["DSC"] = DataHandle.EmptyString2DBNull(li.DSC);
                 dr["IDCARDNO"] = DataHandle.EmptyString2DBNull(li.IDCARDNO);
                 dr["IDCARDTYPE"] = DataHandle.EmptyString2DBNull(li.IDCARDTYPE);
                 dr["IDPERIOD"] = DataHandle.EmptyString2DBNull(li.IDPERIOD);
                 dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                 dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                 dr["NAME"] = DataHandle.EmptyString2DBNull(li.NAME);
                 dr["POST"] = DataHandle.EmptyString2DBNull(li.POST);
                 dr["PROFESSION"] = DataHandle.EmptyString2DBNull(li.PROFESSION);
                 dr["SEX"] = DataHandle.EmptyString2DBNull(li.SEX);
                 outInsuranceUsrDt.Merge(localDt, true);
             }
         }
     }
     catch (NullReferenceException ex)
     {
         CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
     }
     catch (ArrayTypeMismatchException ex)
     {
         CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
     }
     catch (Exception ex)
     {
         CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
         throw;
     }
 }
        public void addNewInsuranceUsrData(SqlLib lib, IDb db, object obj, ref DataTable outInsuranceUsrDt)
        {
            string sql = "";
            string localID = "";
            bool ret = false;
            try
            {
                InsuranceUsrInfo li = new InsuranceUsrInfo();
                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    li = (InsuranceUsrInfo)obj;
                }
                else
                {
                    return;
                }
                if (outInsuranceUsrDt == null)
                {
                    throw new NullReferenceException("outInsuranceUsrDt is null");
                }
                DataTable localdt = new DataTable();
                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrStructInfo");
                localdt = db.GetTable(sql);

                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetNewInsuranceUsrId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }
                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }
                DataRow dr = localdt.NewRow();
                dr["INSURANCE_USRID"] = DataHandle.EmptyString2DBNull(localID);
                dr["Proportion"] = DataHandle.EmptyString2DBNull(li.Proportion);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                dr["DSC"] = DataHandle.EmptyString2DBNull(li.DSC);
                dr["IDCARDNO"] = DataHandle.EmptyString2DBNull(li.IDCARDNO);
                dr["IDCARDTYPE"] = DataHandle.EmptyString2DBNull(li.IDCARDTYPE);
                dr["IDPERIOD"] = DataHandle.EmptyString2DBNull(li.IDPERIOD);
                dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                dr["NAME"] = DataHandle.EmptyString2DBNull(li.NAME);
                dr["POST"] = DataHandle.EmptyString2DBNull(li.POST);
                dr["PROFESSION"] = DataHandle.EmptyString2DBNull(li.PROFESSION);
                dr["SEX"] = DataHandle.EmptyString2DBNull(li.SEX);
                localdt.Rows.Add(dr);
                outInsuranceUsrDt.Merge(localdt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
                throw;
            }
        }
        public void updateOrderDetData(SqlLib lib, IDb db, object obj, ref DataTable outOrderDetDt)
        {
            string sql = "";
            bool ret = true;
            try
            {
                OrderDetInfo li = new OrderDetInfo();
                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    li = (OrderDetInfo)obj;
                }
                else
                {
                    return;
                }
                if (outOrderDetDt == null)
                {
                    throw new NullReferenceException("outOrderDerDt is null");
                }
                DataTable localDt = new DataTable();
                //根据Id更新一条数据信息
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetOrderDetInfo");
                localDt = db.GetTable(sql);
                if (!string.IsNullOrEmpty(li.ORDERID))
                {
                    sql += " and ORDERDETID='" + li.ORDERDETID + "'";
                    localDt = db.GetTable(sql);
                    if (localDt.Rows.Count > 0)
                    {
                        DataRow dr = localDt.NewRow();
                        dr["ORDERDETID"] = DataHandle.EmptyString2DBNull(li.ORDERDETID);
                        dr["ACCOUNTINGCOST"] = DataHandle.EmptyString2DBNull(li.ACCOUNTINGCOST);
                        dr["BACKDT"] = DataHandle.EmptyString2DBNull(li.BACKDT);
                        dr["BACKMONEY"] = DataHandle.EmptyString2DBNull(li.BACKMONEY);
                        dr["BREASON"] = DataHandle.EmptyString2DBNull(li.BREASON);
                        dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                        dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);
                        dr["CONTACTID"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                        dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                        dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                        dr["FEEDBACK"] = DataHandle.EmptyString2DBNull(li.FEEDBACK);
                        dr["FREIGHT"] = DataHandle.EmptyString2DBNull(li.FREIGHT);
                        dr["GOODSBACK"] = DataHandle.EmptyString2DBNull(li.GOODSBACK);
                        dr["ISREFUND"] = DataHandle.EmptyString2DBNull(li.ISREFUND);
                        dr["ORDERID"] = DataHandle.EmptyString2DBNull(li.ORDERID);
                        dr["PAYMENT"] = DataHandle.EmptyString2DBNull(li.PAYMENT);
                        dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                        dr["PRODBANKID"] = DataHandle.EmptyString2DBNull(li.PRODBANKID);
                        dr["PRODDETID"] = DataHandle.EmptyString2DBNull(li.PRODDETID);
                        dr["PRODNUM"] = DataHandle.EmptyString2DBNull(li.PRODNUM);
                        dr["PRODUCTTYPE1"] = DataHandle.EmptyString2DBNull(li.PRODUCTTYPE1);
                        dr["RECKONING"] = DataHandle.EmptyString2DBNull(li.RECKONING);
                        dr["RECKONINGDT"] = DataHandle.EmptyString2DBNull(li.RECKONINGDT);
                        dr["REFUNDDT"] = DataHandle.EmptyString2DBNull(li.REFUNDDT);
                        dr["SOLDWITH"] = DataHandle.EmptyString2DBNull(li.SOLDWITH);
                        dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                        dr["UPRICE"] = DataHandle.EmptyString2DBNull(li.UPRICE);
                        dr.EndEdit();
                        outOrderDetDt.Merge(localDt, true);
                    }
                }
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in updateOrderDetData" + ex.Message);
                throw;
            }

        }
        public void addNewOrderDetData(SqlLib lib, IDb db, object obj, ref DataTable outOrderDetDt)
        {
            string sql = "";
            string localID = "";
            bool ret = false;
            try
            {
                OrderDetInfo li = new OrderDetInfo();
                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    li = (OrderDetInfo)obj;
                }
                else
                {
                    return;
                }
                if (outOrderDetDt == null)
                {
                    throw new NullReferenceException("outOrderDetDt is null");
                }
                DataTable localDt = new DataTable();
                //只取一条记录,以便得到表结构,如果一条都没有,那么会有什么结果?能够取出表结构。
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetOrderDetStructInfo");
                localDt = db.GetTable(sql);

                //新增加的记录,在oracle中需要取到sequence编号
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetNewOrderDetId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }
                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }
                DataRow dr = localDt.NewRow();
                dr["ORDERDETID"] = DataHandle.EmptyString2DBNull(localID);
                dr["ACCOUNTINGCOST"] = DataHandle.EmptyString2DBNull(li.ACCOUNTINGCOST);
                dr["BACKDT"] = DataHandle.EmptyString2DBNull(li.BACKDT);
                dr["BACKMONEY"] = DataHandle.EmptyString2DBNull(li.BACKMONEY);
                dr["BREASON"] = DataHandle.EmptyString2DBNull(li.BREASON);
                dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);
                dr["CONTACTID"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                dr["FEEDBACK"] = DataHandle.EmptyString2DBNull(li.FEEDBACK);
                dr["FREIGHT"] = DataHandle.EmptyString2DBNull(li.FREIGHT);
                dr["GOODSBACK"] = DataHandle.EmptyString2DBNull(li.GOODSBACK);
                dr["ISREFUND"] = DataHandle.EmptyString2DBNull(li.ISREFUND);
                dr["ORDERID"] = DataHandle.EmptyString2DBNull(li.ORDERID);
                dr["PAYMENT"] = DataHandle.EmptyString2DBNull(li.PAYMENT);
                dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                dr["PRODBANKID"] = DataHandle.EmptyString2DBNull(li.PRODBANKID);
                dr["PRODDETID"] = DataHandle.EmptyString2DBNull(li.PRODDETID);
                dr["PRODNUM"] = DataHandle.EmptyString2DBNull(li.PRODNUM);
                dr["PRODUCTTYPE1"] = DataHandle.EmptyString2DBNull(li.PRODUCTTYPE1);
                dr["RECKONING"] = DataHandle.EmptyString2DBNull(li.RECKONING);
                dr["RECKONINGDT"] = DataHandle.EmptyString2DBNull(li.RECKONINGDT);
                dr["REFUNDDT"] = DataHandle.EmptyString2DBNull(li.REFUNDDT);
                dr["SOLDWITH"] = DataHandle.EmptyString2DBNull(li.SOLDWITH);
                dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                dr["UPRICE"] = DataHandle.EmptyString2DBNull(li.UPRICE);

                localDt.Rows.Add(dr);
                outOrderDetDt.Merge(localDt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
                throw;
            }
        }
        /// <summary>
        /// updateDataTableOrderInfo. ORM 2 Data Table.
        /// </summary>
        /// <param name="lib">The lib.</param>
        /// <param name="db">The db.</param>
        /// <param name="obj">The obj.</param>
        /// <returns>
        /// System.Boolean
        /// </returns>
        /// <author>
        /// Stone   
        /// </author>
        /// <remarks>
        /// 2012/10/29 17:54 TUTU-PC
        /// </remarks>
        private bool updateDataTableOrderInfo(SqlLib lib, IDb db, Object obj)
        {
            bool ret = false;

            try
            {
                DataList<OrderInfo> DTlistOrderInfo = new DataList<OrderInfo>();
                Type typeObj = obj.GetType();

                if (DTlistOrderInfo == null)
                {
                    //没有记录,相当于这方法没有执行。
                    return ret;
                }

                if (typeObj.Equals(DTlistOrderInfo.GetType()))
                {
                    //get the correct list.
                    DTlistOrderInfo = (DataList<OrderInfo>)obj;
                }
                else
                {
                    throw new ArrayTypeMismatchException("wrong object type!");
                }

                OrderInfoDB localOrderInfoDB = new OrderInfoDB();

                DataTable dtOrderHist = new DataTable();
                DataTable dtOrderDet = new DataTable();

                // 被保人
                DataTable dtinsurant = new DataTable();
                
                // 受益人
                DataTable dtbeneficiaries = new DataTable();
                
                // 被保人
                DataTable dtpolicyholder = new DataTable();



                //整合保险人的信息 最后在进行提交
                DataTable dtInsurance_usr = new DataTable();


                //将Data List的OrderInfo对象映射为多个DataTable.
                if (DTlistOrderInfo.Count > 0)
                {
                    foreach (OrderInfo li in DTlistOrderInfo)
                    {
                        dataHandler.MapSingleObjDataTable(lib, db, li.DataState, li, localOrderInfoDB.addNewOrderHistData, localOrderInfoDB.updateOrderHistData, localOrderInfoDB.deleteOrderHistData, ref dtOrderHist);

                        //如果有orderid,那么直接往下传,必须有。

                        //如果有过保单明细,那么需要先删除,以便后续操作.
                        if (!string.IsNullOrEmpty(li.ORDERID))
                        {
                            dataHandler.MapSingleObjDataTable(lib, db, CommonDefineData.DELETE_DATA, li, null, null, localOrderInfoDB.deleteOrderHistAllDetData, ref dtOrderDet);
                        }

                        foreach (OrderDetInfo liOrderDet in li.ORDERDETINFO)
                        {
                            if (li.DataState == CommonDefineData.INSERT_DATA)
                            {
                                liOrderDet.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else if (li.DataState == CommonDefineData.UPDATE_DATA)
                            {
                                liOrderDet.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else
                            {
                                liOrderDet.DataState = CommonDefineData.NOTDO_DATA;
                            }

                            dataHandler.MapSingleObjDataTable(lib, db, liOrderDet.DataState, liOrderDet, localOrderInfoDB.addNewOrderDetData, localOrderInfoDB.updateOrderDetData, localOrderInfoDB.deleteOrderDetdata, ref dtOrderDet);
                        }
                        if (li.DataState != CommonDefineData.DELETE_DATA)
                        {
                            foreach (DataRow localrow in dtOrderDet.Rows)
                            {
                                if (dtOrderHist != null && dtOrderHist.Rows.Count > 0)
                                {

                                    DataRow rowHist = dtOrderHist.Rows[0];
                                    if (rowHist.RowState != DataRowState.Deleted)
                                    {
                                        if (localrow.RowState != DataRowState.Deleted)
                                        {
                                            localrow.BeginEdit();
                                            localrow["ORDERID"] = rowHist["ORDERID"].ToString();
                                            localrow.EndEdit();
                                        }
                                    }

                                }

                            }
                        }



                        ///如果保险人信息不为空则需全部delete 一次 到下面在进行新增

                        if (!string.IsNullOrEmpty(li.ORDERID))
                        {

                            //如果有被保人信息 则首先需要删除保险人所有信息
                            if (li.Insurant_usr != null && !string.IsNullOrEmpty(li.Insurant_usr.INSURANCE_USRID))
                            {
                                dataHandler.MapSingleObjDataTable(lib, db, CommonDefineData.DELETE_DATA, li, null, null, localOrderInfoDB.deleteOrderHistAllInsurantData, ref dtinsurant);
                            }
                           
                        }

                        // 操作受益人信息
                        foreach (InsuranceUsrInfo liInUser in li.Lstbeneficiaries)
                        {

                            if (li.DataState == CommonDefineData.INSERT_DATA)
                            {
                                liInUser.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else if (li.DataState == CommonDefineData.UPDATE_DATA)
                            {
                                liInUser.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else
                            {
                                liInUser.DataState = CommonDefineData.NOTDO_DATA;
                            }
                            dataHandler.MapSingleObjDataTable(lib, db, liInUser.DataState.Trim(), liInUser, localOrderInfoDB.addNewInsuranceUsrData, localOrderInfoDB.updateInsuranceUsrData, localOrderInfoDB.deleteInsuranceUsrData, ref dtbeneficiaries);
                        }

                        //把受受益人编号信息需要写入到con_orderhist.
                        if (dtOrderHist != null && dtOrderHist.Rows.Count > 0)
                        {

                            // 定义变量  确定受益人是第几个
                            int beneficiariescount = 0;
                            

                            if (li.DataState != CommonDefineData.DELETE_DATA)
                            {
                                if (dtbeneficiaries != null && dtbeneficiaries.Rows.Count > 0)
                                {
                                    foreach (DataRow drbeneficiaries in dtbeneficiaries.Rows)
                                    {
                                        //DataRow drbeneficiaries = dtbeneficiaries.Rows[0];

                                        if (drbeneficiaries.RowState != DataRowState.Deleted)
                                        {
                                            DataRow localrow = dtOrderHist.Rows[0];
                                            {
                                                if (localrow.RowState != DataRowState.Deleted)
                                                {
                                                    beneficiariescount ++;
                                                    if (beneficiariescount == 1)
                                                    { 
                                                    localrow.BeginEdit();
                                                    localrow["beneficiaries"] = drbeneficiaries["insurance_usrid"];
                                                    localrow.EndEdit();
                                                    
                                                    }

                                                    if (beneficiariescount == 2)
                                                    {
                                                        localrow.BeginEdit();
                                                        localrow["beneficiaries2"] = drbeneficiaries["insurance_usrid"];
                                                        localrow.EndEdit();

                                                    }

                                                    if (beneficiariescount == 3)
                                                    {
                                                        localrow.BeginEdit();
                                                        localrow["beneficiaries3"] = drbeneficiaries["insurance_usrid"];
                                                        localrow.EndEdit();

                                                    }
                            
                            


                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                    


                 

               



                        // 操作投保人信息

                        if (li.Policyholder_usr != null)
                        {

                            if (li.DataState == CommonDefineData.INSERT_DATA)
                            {
                                li.Policyholder_usr.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else if (li.DataState == CommonDefineData.UPDATE_DATA)
                            {
                                li.Policyholder_usr.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else
                            {
                                li.Policyholder_usr.DataState = CommonDefineData.NOTDO_DATA;
                            }
                            dataHandler.MapSingleObjDataTable(lib, db, li.Policyholder_usr.DataState, li.Policyholder_usr, localOrderInfoDB.addNewInsuranceUsrData, localOrderInfoDB.updateInsuranceUsrData, localOrderInfoDB.deleteInsuranceUsrData, ref dtpolicyholder);
                        }

                        //把投保人编号信息需要写入到con_orderhist.
                        if (dtOrderHist != null && dtOrderHist.Rows.Count > 0)
                        {
                            if (li.DataState != CommonDefineData.DELETE_DATA)
                            {
                                if (dtpolicyholder != null && dtpolicyholder.Rows.Count > 0)
                                {
                                    foreach (DataRow drpolicyholder in dtpolicyholder.Rows)
                                    {

                                        if (drpolicyholder.RowState != DataRowState.Deleted)
                                        {

                                            DataRow localrow = dtOrderHist.Rows[0];

                                            if (localrow.RowState != DataRowState.Deleted)
                                            {
                                                localrow.BeginEdit();
                                                localrow["policyholder"] = drpolicyholder["insurance_usrid"];

                                                if (li.Insurantrelation != null && li.Insurantrelation == "1")
                                                {
                                                    localrow["INSURANT"] = drpolicyholder["insurance_usrid"];
                                                }

                                                localrow.EndEdit();
                                            }

                                        }
                                    }

                                }
                            }
                        }




                        // 如果操作的保单与被保人的关系为本人, 将被保人保人的对象赋值为空,

                        if (li.Insurantrelation != null && li.Insurantrelation == "1")
                        {
                            li.Insurant_usr = null;
                        }
                        else
                        {
                            // do nothing
                        }

                        // 操作被保人信息 
                        if (li.Insurant_usr != null)
                        {
                            if (li.DataState == CommonDefineData.INSERT_DATA)
                            {
                                li.Insurant_usr.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else if (li.DataState == CommonDefineData.UPDATE_DATA)
                            {
                                li.Insurant_usr.DataState = CommonDefineData.INSERT_DATA;
                            }
                            else
                            {
                                li.Insurant_usr.DataState = CommonDefineData.NOTDO_DATA;
                            }

                            dataHandler.MapSingleObjDataTable(lib, db, li.Insurant_usr.DataState, li.Insurant_usr, localOrderInfoDB.addNewInsuranceUsrData, localOrderInfoDB.updateInsuranceUsrData, localOrderInfoDB.deleteInsuranceUsrData, ref dtinsurant);
                        }


                        if (li.DataState != CommonDefineData.DELETE_DATA)
                        {
                            //把被保人信息需要写入到con_orderhist.
                            if (dtOrderHist != null && dtOrderHist.Rows.Count > 0)
                            {
                                if (dtinsurant != null && dtinsurant.Rows.Count > 0)
                                {

                                    foreach (DataRow drInsurant in dtinsurant.Rows)
                                    {
                                        if (drInsurant.RowState != DataRowState.Deleted)
                                        {
                                            DataRow localrow = dtOrderHist.Rows[0];
                                            if (localrow.RowState != DataRowState.Deleted)
                                            {
                                                localrow.BeginEdit();

                                                localrow["INSURANT"] = drInsurant["insurance_usrid"].ToString();
                                                localrow.EndEdit();
                                            }
                                        }
                                    }
                                }

                            }
                        }



                    }

                }

                else
                {
                    //没有记录,相当于这方法没有执行。
                    return ret;
                }

                if (DTlistOrderInfo != null && DTlistOrderInfo.Count > 0)
                {
                    OrderInfo localOrderInfo = DTlistOrderInfo[0];


                    dtInsurance_usr.Merge(dtinsurant, true);
                    dtInsurance_usr.Merge(dtbeneficiaries, true);
                    dtInsurance_usr.Merge(dtpolicyholder, true);
                    // 主键约束 需要判断哪个表先删除
                    if (localOrderInfo.DataState == CommonDefineData.DELETE_DATA)
                    {
                        ////update datatable orderdet.
                        ret = localOrderInfoDB.updateOrderDetDataTable(lib, db, dtOrderDet);
                        //update datatable orderhist.
                        ret &= localOrderInfoDB.updateOrderHistDataTable(lib, db, dtOrderHist);
                        ret &= localOrderInfoDB.updataInsuranceUsrDataTable(lib, db, dtInsurance_usr);

                    }
                    else
                    {
                        ret = localOrderInfoDB.updataInsuranceUsrDataTable(lib, db, dtInsurance_usr);
                        ret &= localOrderInfoDB.updateOrderHistDataTable(lib, db, dtOrderHist);
                        ret &= localOrderInfoDB.updateOrderDetDataTable(lib, db, dtOrderDet);
                    }

                }


            }


            catch (FormatException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (ArgumentNullException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (BadImageFormatException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (IndexOutOfRangeException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo  Update" + ex.Message);
            }
            catch (TimeoutException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo DataTable Update" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in OrderInfo DataTable Update" + ex.Message);
                throw;
            }

            return ret;
        }
Beispiel #16
0
        private DataTable getColumns(string tableName, string colArray)
        {
            DataTable     dt       = new DataTable();
            List <string> colNames = new List <string>();

            colArray = colArray.Replace("[", "");
            colArray = colArray.Replace("]", "");

            Debug.WriteLine(colArray);
            string[] newColArray = colArray.Split(',');
            Debug.WriteLine(newColArray);

            for (int i = 0; i < newColArray.Length; i++)
            {
                var column = JObject.Parse(newColArray[i]);
                Debug.WriteLine(column["name"].ToString());
                colNames.Add(column["name"].ToString());
            }

            string colQuery = string.Join(",", colNames.ToArray());

            Debug.WriteLine(colNames);
            Debug.WriteLine("Printed colNames.......");
            Debug.WriteLine(colQuery);


            var        connection  = SqlLib.getConnection();
            var        commandText = @"SELECT " + colQuery + " FROM [dbo].[" + tableName + "]";
            SqlCommand command     = new SqlCommand(commandText, connection);

            command.Parameters.AddWithValue("@tableName", tableName);
            command.Parameters.AddWithValue("@colQuery", colQuery);

            try
            {
                connection.Open();
                using (var sqlQueryResult = command.ExecuteReader())
                    if (sqlQueryResult != null)
                    {
                        Debug.WriteLine("SqlResult is successfulr");
                        for (int i = 0; i < colNames.Count; i++)
                        {
                            dt.Columns.Add(colNames[i]);
                        }
                        int count = 0;
                        while (sqlQueryResult.Read() && count < 300)
                        {
                            DataRow dataRow = dt.NewRow();
                            for (int i = 0; i < colNames.Count; i++)
                            {
                                dataRow[colNames[i]] = sqlQueryResult[colNames[i]].ToString();
                            }
                            count++;
                            dt.Rows.Add(dataRow);
                        }
                        //var reportObj = sqlQueryResult.Read();

                        //var template = sqlQueryResult["template"];
                        //var startRow = Int32.Parse(sqlQueryResult["startRow"].ToString());
                        //var table = sqlQueryResult["table"].ToString();
                        //var map = sqlQueryResult["map"].ToString();


                        // DataTable dt = getColumns(table, map);
                    }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Ëxception is thrown");
                Debug.WriteLine(ex.Message);
            }
            foreach (DataRow dataRow in dt.Rows)
            {
                foreach (var item in dataRow.ItemArray)
                {
                    Debug.WriteLine(item);
                }
            }
            return(dt);
        }