using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.IO;
using System.Globalization;
using System.Threading;
using BaseEntity;

namespace Kenneth.DotNet.EntityTool
{
    public class Interval
    {
        public DateTime Start { get; set; }
        public DateTime End { get; set; }

        public Interval(DateTime start, DateTime end)
        {
            Start = start;
            End = end;
        }
    }
    class SqlClassCreate
    {
        private string showtag = "Displayer";
        private string querytag = "Query";
        private string _tableTestColumnName = "sys_define_column";
        private string _tableTestName = "sys_define_table";
        //private string _tableTestColumnName = "sys_define_column_test";
        //private string _tableTestName = "sys_define_table_test";
        private string _connStr;
        private string _tableName;
        private string _filePath;
        private string _fileName;
        private string _kparam;

        private int columnIndex = 1;

        public string ConnStr
        {
            get { return _connStr; }
            set { _connStr = value; }
        }

        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }

        public string FilePath
        {
            get { return _filePath; }
            set { _filePath = value; }
        }

        public string KeyParameter
        {
            get { return _kparam; }
            set
            {
                _kparam = value;// string.Format("{0}_{1}", value, TableName); 
            }
        }

        public SqlClassCreate()
        {
            this._connStr = string.Empty;
            this._fileName = string.Empty;
            this._filePath = string.Empty;
            this._tableName = string.Empty;
        }

        private void CreateFile(string tableName)
        {
            //处理文件夹
            if (!System.IO.Directory.Exists(this._filePath))
            {
                System.IO.DirectoryInfo _info = System.IO.Directory.CreateDirectory(this._filePath);
                if (!_info.Exists)
                {
                    System.Windows.Forms.MessageBox.Show("目录创建错误", "错误");
                    return;
                }
            }
            TextInfo tInfo = Thread.CurrentThread.CurrentCulture.TextInfo;
            string bigname = tInfo.ToTitleCase(tableName).Replace("_", "");
            string groupname = tInfo.ToTitleCase(tableName.Split('_')[0]);
            if (groupname.Equals("Sys"))
            {
                groupname = "System";
            }

            SqlHelper.connectionString = this._connStr;
            //for get length
            string SQL = string.Format("SELECT * FROM " + _tableTestColumnName + " WHERE TableName='{0}' order by ColumnIndex asc", tableName);
            DataTable dt = SqlHelper.ExecuteDataTable(SQL);
            List<SysDefineColumnEntity> columns = SqlHelper.DataTableToIList<SysDefineColumnEntity>(dt) as List<SysDefineColumnEntity>;
            columns.Insert(0, new SysDefineColumnEntity() { ColumnName = "RecId", ColumnType = "varchar", ColumnLength = 36, ColumnDesc = "主键" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "OrderBy", ColumnType = "int" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "CreateBy", ColumnType = "varchar", ColumnLength = 36, ColumnDesc = "创建人ID" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "CreateTime", ColumnType = "datetime", ColumnLength = 36, ColumnDesc = "创建时间" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "ModifyBy", ColumnType = "varchar", ColumnLength = 36, ColumnDesc = "修改人ID" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "ModifyTime", ColumnType = "datetime", ColumnLength = 36, ColumnDesc = "修改时间" });
            columns.Add(new SysDefineColumnEntity() { ColumnName = "IsActive", ColumnType = "int" });
            string name;
            string length = string.Empty;
            //int index = 0;                   

            //fill js
            string ClearInputInformation = "";
            string SendInputId = "";
            string LoadResult = "";
            string SaveParameters = "";
            string UpdateSingleRow = "";
            string FillResult = "";
            string InitGridcolNames = "";
            string InitGrid1 = "";
            string InitLogGrid1 = "";
            string InitGrid2 = "";
            string InitLogGrid2 = "";
            string SetValidations = "";
            string RequiredInformation = "";
            string ToNumeral = "";
            string ReleaseInputInformation = "";
            string DyUploadControl = "";
            string HtmlViewOpened = "";
            string HtmlViewShow = "";

            //fill aspx
            string TableContents = "";

            string SaveColumnInfoBase = "";
            string SaveColumnInfoFull = "";
            string AlterColumnInfoFull = "";
            string ExcelHeader = "";
            string ExcelContent = "";
            string AllFields = "";
            string InsertAllFields = "";
            string PoolFields = "";
            string All__Fields = "";
            string QueryField1 = "";
            string QueryField2 = "";
            string SqlParameters = "";
            string AddWithParameters = "";
            string ConvertSingleData = "";
            string LogSqlParameters = "";
            string SqlParametersNames = "";
            string LogSqlParametersNames = "";
            string UpdateSqlParametersNames = "";
            string Update__SqlParametersNames = "";
            string ImportColumns = "";
            string UpdateBuffers = "";
            string QueryFromWhereString = "";
            string QueryFromWhereSql = "";
            string QueryFromWhereKeys = "";
            string QueryFromWhereCondition = "";
            string QueryConditionScript = "";
            string QueryConditionDialog = "";

       
            //entity
            string GetSet = "";
            string ShowTag = "";
            string Properties = "";
            var codes = columns.Find(q => q.IsCode == 1);
            if (codes != null)
            {
                QueryField1 = codes.ColumnName;
            }
            var keywords = columns.Find(q => q.IsKeyWord == 1);
            if (keywords != null)
            {
                QueryField2 = keywords.ColumnName;
            }

            for (int i = 0; i < columns.Count; i++)
            {
                SysDefineColumnEntity column = columns[i];

                name = column.ColumnName;
                bool isDateTime = (column.ColumnType.ToLower().Contains("date") || column.ColumnType.ToLower().Contains("time"));
                bool isNumber = (column.ColumnType.ToLower().Contains("int") || column.ColumnType.ToLower().Contains("byte") || column.ColumnType.ToLower().Contains("decimal"));
                length = column.ColumnLength.ToString();
                AllFields = AllFields + "t." + name + ",";
                InsertAllFields = InsertAllFields + name + ",";
                PoolFields = PoolFields + name + ",";
                All__Fields = All__Fields + "@" + name + ",";

                if (name.Equals("RecId"))
                {
                    SendInputId = "\"" + name + "\": currentModifyID" + KeyParameter + "[0]";
                    InitGrid1 = "colModel.push({ sortable: true, name: '" + name + "', index: 'Key', key: true, hidden: true });";
                    InitLogGrid1 = "colModel.push({ sortable: false, name: '" + name + "', index: '" + name + "', hidden: true });";
                }
                else
                {
                    string modelhide = column.IsShowInGrid == 1 ? "false" : "true";
                    string defaultwidth = column.DefaultGridLength > 0 ? string.Format(", width: {0}", column.DefaultGridLength) : "";
                    if (isDateTime)
                    {
                        InitGrid2 = InitGrid2 + "colModel.push({ sortable: true" + defaultwidth + ", name: '" + name + "', hidden: " + modelhide + ", index: '" + name + "', formatter: 'date', formatoptions: { srcformat: 'Y-m-d H:i:s', newformat: 'Y-m-d H:i:s'} });\r\n        ";
                        InitLogGrid2 = InitLogGrid2 + "colModel.push({ sortable: false" + defaultwidth + ", name: '" + name + "', hidden: " + modelhide + ", index: '" + name + "', formatter: 'date', formatoptions: { srcformat: 'Y-m-d H:i:s', newformat: 'Y-m-d H:i:s'} });\r\n        ";
                    }
                    else
                    {
                        string newname = name;
                        if (name.Equals("CreateBy") || name.Equals("ModifyBy"))
                        {
                            newname = name + showtag;
                        }
                        if (column.ValidationType == 1 || column.ValidationType == 2 || column.ValidationType == 3 || column.ValidationType == 11 || column.ValidationType == 12 || column.ValidationType == 21 || column.ValidationType == 31)
                        {
                            InitGrid2 = InitGrid2 + "colModel.push({ sortable: true" + defaultwidth + ", name: '" + newname + "', hidden: " + modelhide + ", index: '" + name + "' });\r\n        ";
                            InitLogGrid2 = InitLogGrid2 + "colModel.push({ sortable: false" + defaultwidth + ", name: '" + newname + "', hidden: " + modelhide + ", index: '" + name + "' });\r\n        ";

                            string lowname = name.ToLower();
                            QueryFromWhereString = QueryFromWhereString + ", string " + lowname + " = null";
                            QueryFromWhereString = QueryFromWhereString + ", string " + lowname + showtag + " = null";
                            QueryFromWhereSql = QueryFromWhereSql + "fromwhere = fromwhere + ((" + lowname + " == null || " + lowname + " == \"\") ? \"\" : string.Format(\" and t." + name + " = '{0}' \", " + lowname + "));\r\n            ";
                            QueryFromWhereSql = QueryFromWhereSql + "fromwhere = fromwhere + ((" + lowname + showtag + " == null || " + lowname + showtag + " == \"\") ? \"\" : string.Format(\" and t." + name + showtag + " like '%{0}%' \", " + lowname + showtag + "));\r\n            ";
                            QueryFromWhereKeys = QueryFromWhereKeys + ", " + lowname;
                            QueryFromWhereKeys = QueryFromWhereKeys + ", " + lowname + showtag;
                            QueryFromWhereCondition = QueryFromWhereCondition + "string " + lowname + " = GetConditionValue(ce, \"" + name + "\");\r\n            ";
                            QueryFromWhereCondition = QueryFromWhereCondition + "string " + lowname + showtag + " = GetConditionValue(ce, \"" + name + showtag + "\");\r\n            ";
                            if (column.ValidationType != 31)//文件
                            {
                                QueryConditionScript = QueryConditionScript + "{ Name: \"" + name + "\", Fields: \"" + name + "\", Symbol: \"and\", Fuzzy: \"=\", Type: \"String\", Value: " + QueryParameters(name, KeyParameter + querytag, column) + " },\r\n							 ";
                            }
                            QueryConditionDialog = DealTableContents(isDateTime, QueryConditionDialog, length, name, KeyParameter + querytag, column, true);
                        }
                        else
                        {
                            InitGrid2 = InitGrid2 + "colModel.push({ sortable: true" + defaultwidth + ", name: '" + newname + "', hidden: " + modelhide + ", index: '" + name + "' });\r\n        ";
                            InitLogGrid2 = InitLogGrid2 + "colModel.push({ sortable: false" + defaultwidth + ", name: '" + newname + "', hidden: " + modelhide + ", index: '" + name + "' });\r\n        ";
                        }
                    }
                    //
                    if (!name.Equals("OrderBy") && !name.Equals("CreateBy") && !name.Equals("CreateTime") && !name.Equals("ModifyBy") && !name.Equals("ModifyTime") && !name.Equals("IsActive"))
                    {
                        SqlParametersNames = SqlParametersNames + "param" + name + ", ";
                        UpdateSqlParametersNames = UpdateSqlParametersNames + "param" + name + ", ";
                        Update__SqlParametersNames = Update__SqlParametersNames + name + " = " + "@" + name + ", ";
                    }
                }
                ImportColumns = DealImportColumns(isDateTime, isNumber, ImportColumns, name, column);
                ClearInputInformation = DealClearInputInformation(ClearInputInformation, KeyParameter + name, column);
                ReleaseInputInformation = DealReleaseInputInformation(ReleaseInputInformation, KeyParameter + name, column);
                DyUploadControl = DealDyUploadControl(DyUploadControl, KeyParameter + name, column);
                TableContents = DealTableContents(isDateTime, TableContents, length, name, KeyParameter, column, false);
                FillResult = DealFillResult(FillResult, name, column);
                InitGridcolNames = string.Format("{0}colNames.push(\"{1}\");\r\n        ", InitGridcolNames, column.ColumnHeader);
                ExcelHeader = string.Format("{0}row0.CreateCell(colIndex).SetCellValue(\"{1}\"); colIndex++;\r\n            ", ExcelHeader, name);
                LogSqlParametersNames = LogSqlParametersNames + "param" + name + ", ";
                GetSet = GetSet + "/// <summary>\r\n	/// " + column.ColumnHeader + "\r\n	/// </summary>\r\n	public " + ToType(column.ColumnType) + " " + name + " { get; set; }\r\n        ";
                Properties= Properties  +"/// <summary>\r\n	/// " + column.ColumnHeader + "\r\n	/// </summary>\r\n	private " + ToType(column.ColumnType) + " _" + name + ";\r\n	public " + ToType(column.ColumnType) + " " + name + " { get{return _" + name + ";} \r\nset{\r\n_" + name + "= value;\r\nOnPropertyChanged(\"" + name + "\");}}\r\n        ";
                bool isvalidation = false;
                if (column.ValidationType == 1 || column.ValidationType == 2 || column.ValidationType == 3 || column.ValidationType == 11 || column.ValidationType == 12 || column.ValidationType == 21 || column.ValidationType == 31)
                {
                    isvalidation = true;
                    ShowTag = ShowTag + "public string " + name + showtag + " { get; set; }\r\n        ";
                    if (string.IsNullOrEmpty(column.ValidationName))
                    {
                        throw new Exception(column.ValidationName + "不能为空");
                    }
                    if (column.ValidationPool == "SysCategoryPool")
                    {
                        ConvertSingleData = ConvertSingleData + "var " + name + "obj = " + column.ValidationPool
                        + ".BufferPool.Where(q => q.CategoryParent == \"" + column.ValidationId + "\" && q.RecId == data." + name + @").FirstOrDefault();
                        if (" + name + @"obj != null)
                        {
                            data." + name + @showtag + " = " + name + "obj." + column.ValidationName + @";
                        }" + "\r\n            ";
                    }
                    else
                    {
                        ConvertSingleData = ConvertSingleData + "var " + name + "obj = " + column.ValidationPool
                        + ".GetById(data." + name + @");
                        if (" + name + @"obj != null)
                        {
                            data." + name + @showtag + " = " + name + "obj." + column.ValidationName + @";
                        }" + "\r\n            ";
                    }
                }

                if (isDateTime)
                {
                    if (!name.Equals("RecId") && !name.Equals("OrderBy") && !name.Equals("CreateBy") && !name.Equals("CreateTime") && !name.Equals("ModifyBy") && !name.Equals("ModifyTime") && !name.Equals("IsActive"))
                    {
                        SqlParameters = SqlParameters + "SqlParameter param" + name + " = new SqlParameter(\"@" + name + "\", SqlDbType.DateTime) { Value = send." + name + " };\r\n            ";
                    }
                    LogSqlParameters = LogSqlParameters + "SqlParameter param" + name + " = new SqlParameter(\"@" + name + "\", SqlDbType.DateTime) { Value = data." + name + " };\r\n                ";
                }
                else
                {
                    if (!name.Equals("RecId") && !name.Equals("OrderBy") && !name.Equals("CreateBy") && !name.Equals("CreateTime") && !name.Equals("ModifyBy") && !name.Equals("ModifyTime") && !name.Equals("IsActive"))
                    {
                        if (isvalidation)
                        {
                            SqlParameters = SqlParameters + "SqlParameter param" + name + " = new SqlParameter(\"@" + name + "\", SqlDbType.VarChar) { Value = send." + name + " };\r\n            ";
                            if (column.ValidationPool == "SysCategoryPool")
                            {
                                SqlParameters = SqlParameters + "			var " + name + "obj = " + column.ValidationPool
                                + ".BufferPool.Where(q => q.CategoryParent == \"" + column.ValidationId + "\" && q.RecId == send." + name + @").FirstOrDefault();" +
                                "\r\n						SqlParameter param" + name + showtag + " = new SqlParameter(\"@" + name + showtag + "\", SqlDbType.VarChar);\r\n" +
                                @"						if (" + name + @"obj != null)
						{
						    param" + name + showtag + " = new SqlParameter(\"@" + name + showtag + "\", SqlDbType.VarChar) { Value = " + name + "obj." + column.ValidationName + @" };
						}" + "\r\n" + @"						";
                            }
                            else
                            {
                                SqlParameters = SqlParameters + "			var " + name + "obj = " + column.ValidationPool
                                + ".GetById(send." + name + @");" +
                                "\r\n						SqlParameter param" + name + showtag + " = new SqlParameter(\"@" + name + showtag + "\", SqlDbType.VarChar);\r\n" +
                                @"						if (" + name + @"obj != null)
						{
						    param" + name + showtag + " = new SqlParameter(\"@" + name + showtag + "\", SqlDbType.VarChar) { Value = " + name + "obj." + column.ValidationName + @" };
						}" + "\r\n" + @"						";
                            }
                        }
                        else
                        {
                            if (name.LastIndexOf(showtag + "") == -1)
                            {
                                SqlParameters = SqlParameters + "SqlParameter param" + name + " = new SqlParameter(\"@" + name + "\", SqlDbType.VarChar) { Value = send." + name + " };\r\n						";
                            }
                        }
                    }
                    LogSqlParameters = LogSqlParameters + "SqlParameter param" + name + " = new SqlParameter(\"@" + name + "\", SqlDbType.VarChar) { Value = data." + name + " };\r\n						";
                }
                if (!name.Equals("RecId") && !name.Equals("OrderBy") && !name.Equals("CreateBy") && !name.Equals("CreateTime") && !name.Equals("ModifyBy") && !name.Equals("ModifyTime") && !name.Equals("IsActive"))
                {
                    AddWithParameters = AddWithParameters + "cmd.Parameters.AddWithValue(\"@" + name + "\", segment." + name + ");\r\n";
                }
                LoadResult = DealLoadResult(isDateTime, LoadResult, name, KeyParameter, column);
                SaveParameters = DealSaveParameters(isNumber, SaveParameters, name, KeyParameter, column);
                SaveColumnInfoBase = DealSaveColumnInfoBase(isNumber, SaveColumnInfoBase, name, KeyParameter, column);
                SaveColumnInfoFull = DealSaveColumnInfoFull(isNumber, SaveColumnInfoFull, name, KeyParameter, column);

                HtmlViewOpened = DealHtmlViewOpened(isNumber, HtmlViewOpened, name, KeyParameter, column);
                HtmlViewShow = DealHtmlViewShow(isNumber, HtmlViewShow, name, KeyParameter, column);
                UpdateSingleRow = DealUpdateSingleRow(isNumber, UpdateSingleRow, name, column);
                SetValidations = DealSetValidations(SetValidations, KeyParameter + name, column, bigname, "false");
                SetValidations = DealSetValidations(SetValidations, KeyParameter + querytag + name, column, bigname, "true");//SetQueryValidations

                if (isDateTime || isNumber)
                {
                    ExcelContent = string.Format("{0}row.CreateCell(colIndex).SetCellValue(data.{1}.ToString()); colIndex++;\r\n                ", ExcelContent, name);
                }
                else
                {
                    ExcelContent = string.Format("{0}row.CreateCell(colIndex).SetCellValue(data.{1}); colIndex++;\r\n                ", ExcelContent, name);
                }

                if (column.IsRequired == 1)
                {
                    RequiredInformation = RequiredInformation + "if (!MessageBoxToolTip($('#txt" + KeyParameter + name + "'), $(this), '" + column.ColumnHeader + "为必填项!')) { return false; }\r\n			";
                }

                if (isNumber)
                {
                    if (column.ColumnType.ToLower().Contains("int"))
                    {
                        ToNumeral = ToNumeral + "$('#txt" + KeyParameter + name + "').numeralInt();\r\n		";
                    }
                    else
                    {
                        ToNumeral = ToNumeral + "$('#txt" + KeyParameter + name + "').numeral();\r\n		";
                    }
                }


                string rowfield;
                string rowfieldu;

                if (isDateTime)
                {
                    //crate table
                    rowfield = string.Format("[{0}] [{1}] NULL,", columns[i].ColumnName, columns[i].ColumnType);
                }
                else if (isNumber)
                {
                    if (column.ColumnType.ToLower().Contains("decimal"))
                    {
                        rowfield = string.Format("[{0}] [{1}]({2}) NULL,", columns[i].ColumnName, columns[i].ColumnType, columns[i].ColumnLength == -1 ? "10" : "10," + columns[i].ColumnLength.ToString());
                    }
                    else
                    {
                        rowfield = string.Format("[{0}] [{1}] NULL,", columns[i].ColumnName, columns[i].ColumnType);
                    }
                }
                else
                {
                    //crate table
                    rowfield = string.Format("[{0}] [{1}]({2}) NULL,", columns[i].ColumnName, columns[i].ColumnType, columns[i].ColumnLength == -1 ? "max" : columns[i].ColumnLength.ToString());
                }


                rowfieldu = " Add " + rowfield.Substring(0, rowfield.Length - 1) + ";";

                AlterColumnInfoFull = string.Format("{0}alter table {1} {2}\r\n        ", AlterColumnInfoFull, tableName, rowfieldu);
                AlterColumnInfoFull = string.Format("{0}alter table {1}_log {2}\r\n        ", AlterColumnInfoFull, tableName, rowfieldu);
            }

            string OpenLoadSelections = "";
            string opensql = string.Format("SELECT count(*) FROM sys_define_column WHERE ValidationType=11 and ValidationPool='{0}Pool'", bigname);
            if (Convert.ToInt32(SqlHelper.ExecuteDataTable(opensql).Rows[0][0]) > 0)
            {
                OpenLoadSelections = "/";
            }

            //优先从配置中读取需要导入的内容
            //var temp = DealConfiguredImportResult(tableName, ref UpdateBuffers);
            //if (!string.IsNullOrEmpty(temp))
            //    ImportColumns = temp;

            //deal files
            string[] files = new string[] { "model.js.txt", "Entity.cs.txt", "Property.cs.txt", "model.aspx.cs.txt", "model.aspx.designer.cs.txt", "model.aspx.txt", "model.cs.txt", "modelPartial.cs.txt", "modelpool.cs.txt", "backend.cs.txt" };

            if (tableName.Contains("_log"))
            {
                files = new string[] { "Entity.cs.txt" };
            }
            foreach (var file in files)
            {
                string filename = Path.Combine(Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).FullName.Replace("\\bin\\Debug", ""), file);
                StreamReader sr = File.OpenText(filename);
                string dealstring = sr.ReadToEnd();
                dealstring = dealstring.Replace("//{ClearInputInformation}", ClearInputInformation.Trim());
                dealstring = dealstring.Replace("//{SendInputId}", SendInputId);
                dealstring = dealstring.Replace("//{LoadResult}", LoadResult.Trim());
                dealstring = dealstring.Replace("//{SaveParameters}", SaveParameters.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{SaveColumnInfoBase}", SaveColumnInfoBase.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{SaveColumnInfoFull}", SaveColumnInfoFull.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{AlterColumnInfoFull}", AlterColumnInfoFull.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{FillResult}", FillResult.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{InitGridcolNames}", InitGridcolNames);
                dealstring = dealstring.Replace("//{InitGrid1}", InitGrid1);
                dealstring = dealstring.Replace("//{InitLogGrid1}", InitLogGrid1);
                dealstring = dealstring.Replace("//{InitGrid2}", InitGrid2);
                dealstring = dealstring.Replace("//{InitLogGrid2}", InitLogGrid2);
                dealstring = dealstring.Replace("//{SetValidations}", SetValidations);
                dealstring = dealstring.Replace("//{RequiredInformation}", RequiredInformation);
                dealstring = dealstring.Replace("//{ToNumeral}", ToNumeral);
                dealstring = dealstring.Replace("//{ReleaseInputInformation}", ReleaseInputInformation.Trim());
                dealstring = dealstring.Replace("//{DyUploadControl}", DyUploadControl.Trim());
                dealstring = dealstring.Replace("//{HtmlViewOpened}", HtmlViewOpened.Trim());
                dealstring = dealstring.Replace("//{HtmlViewShow}", HtmlViewShow.Trim());
                
                dealstring = dealstring.Replace("//{TableContents}", TableContents.Trim());
                dealstring = dealstring.Replace("//{GetSet}", GetSet);
                dealstring = dealstring.Replace("//{Properties}", Properties);
                dealstring = dealstring.Replace("//{ShowTag}", ShowTag);
                dealstring = dealstring.Replace("{TableName}", tableName);
                dealstring = dealstring.Replace("{ClassName}", bigname);
                dealstring = dealstring.Replace("{GroupName}", groupname);
                dealstring = dealstring.Replace("//{ExcelHeader}", ExcelHeader.Trim());
                dealstring = dealstring.Replace("//{ExcelContent}", ExcelContent.Trim());
                dealstring = dealstring.Replace("{AllFields}", AllFields.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{InsertAllFields}", InsertAllFields.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{PoolFields}", PoolFields.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{All@Fields}", All__Fields.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{QueryField1}", QueryField1);
                dealstring = dealstring.Replace("{QueryField2}", QueryField2);
                dealstring = dealstring.Replace("//{ConvertSingleData}", ConvertSingleData.Trim());
                dealstring = dealstring.Replace("//{SqlParameters}", SqlParameters.Trim());
                dealstring = dealstring.Replace("//{AddWithParameters}", AddWithParameters.Trim());                
                dealstring = dealstring.Replace("//{LogSqlParameters}", LogSqlParameters.Trim());
                dealstring = dealstring.Replace("{SqlParametersNames}", SqlParametersNames.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{LogSqlParametersNames}", LogSqlParametersNames.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{UpdateSqlParametersNames}", UpdateSqlParametersNames.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{Update__SqlParametersNames}", Update__SqlParametersNames.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("//{ImportColumns}", ImportColumns.Trim().TrimEnd(','));
                dealstring = dealstring.Replace("{OpenLoadSelections}", OpenLoadSelections);
                dealstring = dealstring.Replace("//{UpdateBuffers}", UpdateBuffers);
                dealstring = dealstring.Replace("//{UpdateSingleRow}", UpdateSingleRow);
                dealstring = dealstring.Replace("{QueryFromWhereString}", QueryFromWhereString);
                dealstring = dealstring.Replace("{QueryFromWhereSql}", QueryFromWhereSql);
                dealstring = dealstring.Replace("{QueryFromWhereCondition}", QueryFromWhereCondition);
                dealstring = dealstring.Replace("{QueryFromWhereKeys}", QueryFromWhereKeys);
                dealstring = dealstring.Replace("{QueryConditionScript}", QueryConditionScript);
                dealstring = dealstring.Replace("{QueryConditionDialog}", QueryConditionDialog.Replace("<span class=\"LimitInputItem\">*</span>", string.Empty));
                

                dealstring = dealstring.Replace("@@X", KeyParameter);
                string extion = Path.GetFileName(filename).Replace("modelpool", "").Replace("model", "").Replace(".txt", "");
                string changedfilename = bigname;
                if (extion.Contains(".js"))
                {
                    changedfilename = tableName;
                }

                //string[] files = new string[] { "model.js.txt", "Entity.cs.txt", "model.aspx.cs.txt", "model.aspx.designer.cs.txt", "model.aspx.txt", "model.cs.txt" };

                string outputfile = FilePath;
                if (filename.Contains("model.cs.txt"))
                {
                    outputfile = outputfile + "\\WebBusiness\\" + groupname;
                }
                else if (filename.Contains("modelPartial.cs.txt"))
                {
                    //outputfile = outputfile + "\\WebBusiness\\Partial";
                    outputfile = outputfile + "\\WebBusiness\\" + groupname;
                }
                else if (filename.Contains("modelpool.cs.txt"))
                {
                    changedfilename = changedfilename + "Pool";
                    outputfile = outputfile + "\\WebBusiness\\Buffer";
                }
                else if (filename.Contains("model.js.txt"))
                {
                    outputfile = outputfile + "\\WebPortal\\PageScript\\" + groupname;
                }
                else if (filename.Contains("Entity.cs.txt"))
                {
                    outputfile = outputfile;
                }
                else if (filename.Contains("backend.cs.txt"))
                {
                    outputfile = outputfile;
                    extion = "DA.cs";
                }                
                else if (filename.Contains("Property.cs.txt"))
                {
                    outputfile = outputfile;
                }
                else
                {
                    outputfile = outputfile + "\\WebPortal\\" + groupname;
                }
                if (!Directory.Exists(outputfile))
                {
                    Directory.CreateDirectory(outputfile);
                }
                outputfile = outputfile + @"\" + changedfilename + extion;
                if (File.Exists(outputfile))
                {
                    File.Delete(outputfile);
                }
                FileStream fs = new FileStream(outputfile, FileMode.CreateNew, FileAccess.Write, FileShare.Write);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.Write(dealstring);
                sw.Close();
                fs.Close();
            }
            //dealstring = dealstring.Replace("//{0}", ClearInputInformation.Trim());
        }

        private void CreateTable(string tableName)
        {
            SqlHelper.connectionString = this._connStr;

            TextInfo tInfo = Thread.CurrentThread.CurrentCulture.TextInfo;
            string bigname = tInfo.ToTitleCase(tableName).Replace("_", "");
            string groupname = tInfo.ToTitleCase(tableName.Split('_')[0]);
            if (groupname.Equals("Sys"))
            {
                groupname = "System";
            }

            string tsql = string.Format("SELECT * FROM " + _tableTestName + " WHERE TableName='{0}' ", tableName);
            DataTable dtt = SqlHelper.ExecuteDataTable(tsql);
            SysDefineTableEntity table = SqlHelper.DataRow0ToEntity<SysDefineTableEntity>(dtt) as SysDefineTableEntity;
            string menuname = table.MenuName;
            tableName = table.TableName;
            string recid = Guid.NewGuid().ToString();
            string menuurl = "./../" + groupname + "/" + bigname + ".aspx?Title=" + groupname + "-" + menuname;
            string menuscript = "./../PageScript/" + groupname + "/" + tableName + ".js";

            //for get length
            string SQL = string.Format("SELECT * FROM " + _tableTestColumnName + " WHERE TableName='{0}' order by ColumnIndex asc", tableName);
            DataTable dt = SqlHelper.ExecuteDataTable(SQL);
            List<SysDefineColumnEntity> columns = SqlHelper.DataTableToIList<SysDefineColumnEntity>(dt) as List<SysDefineColumnEntity>;
            string name;
            string length = string.Empty;

            //update table 
            StringBuilder sbu = new StringBuilder();
            sbu.Append(" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "]') AND type in (N'U')) ");
            sbu.Append(" begin ");

            StringBuilder sblogu = new StringBuilder();
            sblogu.Append(" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "_log]') AND type in (N'U')) ");
            sblogu.Append(" begin ");

            //crate table
            StringBuilder sb = new StringBuilder();
            //sb.Append(" IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "]') AND type in (N'U')) ");
            //sb.Append(" DROP TABLE [dbo].[" + tableName + "] ");
            //sb.Append(" begin ");
            //sb.Append(" insert into sys_menu (RecId,MenuName,MenuIcon,MenuUrl,MenuParentId,MenuScripts,IsActive) values ('" + recid + "','" + menuname + "','./../Stylesheet/images/Icon/sys2.gif','" + menuurl + "',999,'" + menuscript + "',100) ");

            sb.Append(" CREATE TABLE [" + tableName.ToLower() + "]( ");
            sb.Append(" [RecId] [varchar](36) NOT NULL, ");

            StringBuilder sblog = new StringBuilder();
            //sblog.Append(" IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "_log]') AND type in (N'U')) ");
            //sblog.Append(" DROP TABLE [dbo].[" + tableName + "_log] ");
            //sblog.Append(" begin ");
            sblog.Append(" CREATE TABLE [" + tableName.ToLower() + "_log]( ");
            sblog.Append("[LogId] [varchar](36) NOT NULL,");
            sblog.Append("[RecId] [varchar](36) NULL,");

            for (int i = 0; i < columns.Count; i++)
            {
                SysDefineColumnEntity column = columns[i];
                string rowfield;
                string rowfieldu;
                name = column.ColumnName;
                bool isDateTime = (column.ColumnType.ToLower().Contains("date") || column.ColumnType.ToLower().Contains("time"));
                bool isNumber = (column.ColumnType.ToLower().Contains("int") || column.ColumnType.ToLower().Contains("byte") || column.ColumnType.ToLower().Contains("decimal"));
                length = column.ColumnLength.ToString();


                if (isDateTime)
                {
                    //crate table
                    rowfield = string.Format("[{0}] [{1}] NULL,", columns[i].ColumnName, columns[i].ColumnType);
                }
                else if (isNumber)
                {
                    if (column.ColumnType.ToLower().Contains("decimal"))
                    {
                        rowfield = string.Format("[{0}] [{1}]({2}) NULL,", columns[i].ColumnName, columns[i].ColumnType, columns[i].ColumnLength == -1 ? "10" : "10," + columns[i].ColumnLength.ToString());
                    }
                    else
                    {
                        rowfield = string.Format("[{0}] [{1}] NULL,", columns[i].ColumnName, columns[i].ColumnType);
                    }
                }
                else
                {
                    //crate table
                    rowfield = string.Format("[{0}] [{1}]({2}) NULL,", columns[i].ColumnName, columns[i].ColumnType, columns[i].ColumnLength == -1 ? "max" : columns[i].ColumnLength.ToString());
                }

                /*
                rowfieldu = " Add " + rowfield.Substring(0, rowfield.Length - 1) + ";";

                sbu.Append(" IF Not EXISTS(SELECT * FROM sys.columns  WHERE Name = N'" + columns[i].ColumnName + "' AND Object_ID = Object_ID(N'" + tableName + "')) ");
                sbu.Append(" BEGIN ");
                sbu.Append(" alter table " + tableName + " ");
                sbu.Append(rowfieldu);
                sbu.Append(" END ");

                sblogu.Append(" IF Not EXISTS(SELECT * FROM sys.columns  WHERE Name = N'" + columns[i].ColumnName + "' AND Object_ID = Object_ID(N'" + tableName + "_log')) ");
                sblogu.Append(" BEGIN ");
                sblogu.Append(" alter table " + tableName + "_log ");
                sblogu.Append(rowfieldu);
                sblogu.Append(" END ");*/
                

                sb.Append(rowfield);
                sblog.Append(rowfield);
            }
            //SqlHelper.ExecuteNonQuery(sbu.ToString());
            //SqlHelper.ExecuteNonQuery(sblogu.ToString());

            //crate table
            sb.Append("[OrderBy] [int] NULL,");
            sb.Append("[CreateBy] [varchar](36) NULL,");
            sb.Append("[CreateTime] [datetime] NULL,");
            sb.Append("[ModifyBy] [varchar](36) NULL,");
            sb.Append("[ModifyTime] [datetime] NULL,");
            sb.Append("[IsActive] [tinyint] NULL,");
            sb.Append(" PRIMARY KEY (RecId)) ");

            sblog.Append("[OrderBy] [int] NULL,");
            sblog.Append("[CreateBy] [varchar](36) NULL,");
            sblog.Append("[CreateTime] [datetime] NULL,");
            sblog.Append("[ModifyBy] [varchar](36) NULL,");
            sblog.Append("[ModifyTime] [datetime] NULL,");
            sblog.Append("[IsActive] [tinyint] NULL,");
            sblog.Append("[OperTime] [datetime] NULL,");
            sblog.Append("[OperUser] [varchar](36) NULL,");
            sblog.Append("[OperType] [varchar](36) NULL,");
            sblog.Append("[OperInfo] [nvarchar](50) NULL,");
            sblog.Append(" PRIMARY KEY (RecId)) ");
            try
            {
                SqlHelper.ExecuteNonQuery(sb.ToString());
                SqlHelper.ExecuteNonQuery(sblog.ToString());
            }
            catch { }
        }

        private string DealClearInputInformation(string copy, string key, SysDefineColumnEntity column)
        {
            if (column.IsShowInEdit != 1)
            {
                return copy;
            }
            if (column.ValidationType == 1 || column.ValidationType == 2)
            {
                return copy + "if ($('#txt" + key + "').get(0) != null) { $('#txt" + key + "').get(0).selectedIndex = 0; }\r\n        ";
            }
            else if (column.ValidationType == 11 || column.ValidationType == 12)
            {
                return string.Format("{0}$('#txt{1}').comboselect('clearValue');\r\n        ", copy, key);
            }
            else if (column.ValidationType == 21)
            {
                return string.Format("{0}$('#txt{1}').combotree('clearValue');\r\n        ", copy, key);
            }
            else if (column.ValidationType == 31)
            {
                return string.Format("{0}$('#txt{1}').val('');\r\n        $('#txt{1}" + showtag + "').val('');\r\n        ", copy, key);
            }
            else if (column.ValidationType == 55)
            {
                return string.Format("{0}$('#txt{1}').fileselect('clearValue');\r\n        ", copy, key);
            }
            else if (column.ValidationType == 56)
            {
                return string.Format("{0}$('#txt{1}').commentselect('clearValue');\r\n        ", copy, key);
            }
            return string.Format("{0}$('#txt{1}').val('');\r\n        ", copy, key);
        }
        private string DealReleaseInputInformation(string copy, string key, SysDefineColumnEntity column)
        {
            return string.Format("{0}$('#txt{1}').remove();\r\n		$('#txt{1}') = null;\r\n		", copy, key);
        }

        private string DealDyUploadControl(string copy, string key, SysDefineColumnEntity column)
        {
            if (column.ValidationType == 31)
            {
                return copy + "new AjaxUpload('div" + key + "', {action: CurrentDefaultUploadSite + '?currentDiskStoreageType=' + (DiskStoreageType == null ? \"\" : DiskStoreageType) + '&rt=' + currentClassType@@X + \"&currentAllocation=\" + currentAllocation + \"&currentUserId=\" + currentUserId + \"&currentSessionId=\" + currentSessionId + \"&currentStoreId=\" + currentStoreId,onComplete: function (file, response) {this.enable();if (response.indexOf('失败') > -1) {MessageBox(response);return false;}var outEntity = JSON.parse(response);if (outEntity.ErrorCode == DataSucceedTag) {var files = JSON.parse(outEntity.ReturnObj);$(\"#txt" + key + showtag + "\").val(file);$(\"#txt" + key + "\").val(files[0].RecId);}else {MessageBox(outEntity.ErrorMessage);}},onSubmit: function (file, ext) {this.disable();},onError: function (e) {MessageBox(e.toString());}});\r\n";
            }

            return copy;
        }

        private string DealLoadResult(bool isDateTime, string copy, string key, string KeyParameter, SysDefineColumnEntity column)
        {
            if (column.IsShowInEdit != 1)
            {
                return copy;
            }
            if (isDateTime)
            {
                return string.Format("{0}$('#txt{2}{1}').val(FormatDateToDateTime(data.{1}));\r\n        ", copy, key, KeyParameter);
            }
            if (column.ValidationType == 11)
            {
                return string.Format("{0}$('#txt{2}{1}').comboselect('setSingleValue', data.{1});\r\n        ", copy, key, KeyParameter);
            }
            else if (column.ValidationType == 12)
            {
                return string.Format("{0}$('#txt{2}{1}').comboselect('setValue', data.{1}.split(','));\r\n        ", copy, key, KeyParameter);
            }
            else if (column.ValidationType == 21)
            {
                return string.Format("{0}$('#txt{2}{1}').combotree('setSingleValue', data.{1});\r\n        ", copy, key, KeyParameter);
            }
            else if (column.ValidationType == 31)
            {
                return string.Format("{0}$('#txt{2}{1}').val(data.{1});\r\n        $('#txt{2}{1}" + showtag + "').val(data.{1}" + showtag + ");\r\n        ", copy, key, KeyParameter);
            }
            else if (column.ValidationType == 55)
            {
                return string.Format("{0}$('#txt{2}{1}').fileselect('setValue', data.{1}.split(','));\r\n        ", copy, key, KeyParameter);
            }
            else if (column.ValidationType == 56)
            {
                return string.Format("{0}$('#txt{2}{1}').commentselect('setValue', data.{1}.split(','));\r\n        ", copy, key, KeyParameter);
            }
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}$('#txt{2}{1}').htmlarea('html', DecodeFrom(data.{1}));\r\n        ", copy, key, KeyParameter);
            }
            return string.Format("{0}$('#txt{2}{1}').val(data.{1});\r\n        ", copy, key, KeyParameter);
        }
        private string DealSaveParameters(bool isNumber, string SaveParameters, string name, string KeyParameter, SysDefineColumnEntity column)
        {
            if (column.IsShowInEdit != 1)
            {
                return SaveParameters;
            }
            if (isNumber)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : Number($.trim($('#txt{2}{1}').val())),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 1)
            {
                return string.Format("{0}{{\"ColumnName\":\"{1}\", \"DefaultValue\" : $.trim($('#txt{2}{1}').val()), \"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 11)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').comboselect('getSingleValue')),\"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			//{{\"ColumnName\" :\"{1}" + showtag + "\",\"DefaultValue\" : $.trim($('#txt{2}{1}').comboselect('getSingleValue')),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            else if (column.ValidationType == 12)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').comboselect('getValue')),\"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			//{{\"ColumnName\" :\"{1}" + showtag + "\",\"DefaultValue\" : $.trim($('#txt{2}{1}').comboselect('getValue')),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 21)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').combotree('getSingleValue')),\"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			//{{\"ColumnName\" :\"{1}" + showtag + "\",\"DefaultValue\" : $.trim($('#txt{2}{1}').combotree('getSingleText')),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 31)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').val()),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 55)
            {
                return string.Format("{0}{{\"ColumnName\" : \"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').fileselect('getValue')),\"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			//{{\"ColumnName\" :\"{1}" + showtag + "\",\"DefaultValue\" : $.trim($('#txt{2}{1}').fileselect('getValue')),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 56)
            {
                return string.Format("{0}{{\"ColumnName\":\"{1}\",\"DefaultValue\" : $.trim($('#txt{2}{1}').commentselect('getValue')),\"DataType\" : \"{3}\",\"IsValidation\" : \"true\",\"ValidationTable\" : \"{4}\"}},\r\n			//{{\"ColumnName\" :\"{1}" + showtag + "\",\"DefaultValue\" : $.trim($('#txt{2}{1}').commentselect('getValue')),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}{{\"ColumnName\":\"{1}\",\"DefaultValue\" : EncodeTo($.trim($('#txt{2}{1}').val())),\"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType);
            }
            return string.Format("{0}{{\"ColumnName\":\"{1}\", \"DefaultValue\" : $.trim($('#txt{2}{1}').val()), \"DataType\" : \"{3}\"}},\r\n			", SaveParameters, name, KeyParameter, column.ColumnType);
        }


        private string DealSaveColumnInfoBase(bool isNumber, string SaveColumnInfoBase, string name, string KeyParameter, SysDefineColumnEntity column)
        {
            columnIndex++;
            if (column.IsShowInEdit != 1)
            {
                return SaveColumnInfoBase;
            }
            if (isNumber)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 1)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 11)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            else if (column.ValidationType == 12)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 21)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 31)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 55)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 56)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType);
            }
            return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = Convert.ToString(row.GetCell(columnIndex)), DataType = \"{3}\"}}); columnIndex++;\r\n			", SaveColumnInfoBase, name, KeyParameter, column.ColumnType);
        }

        private string DealSaveColumnInfoFull(bool isNumber, string SaveColumnInfoFull, string name, string KeyParameter, SysDefineColumnEntity column)
        {
            
            if (column.IsShowInEdit != 1)
            {
                return SaveColumnInfoFull;
            }
            if (isNumber)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = entity.{1}, DataType = \"{3}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 1)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 11)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            else if (column.ValidationType == 12)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 21)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 31)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType);
            }
            if (column.ValidationType == 55)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ValidationType == 56)
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\",IsValidation = true,ValidationTable = \"{4}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType, column.ValidationPool);
            }
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\",DefaultValue = entity.{1}, DataType = \"{3}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType);
            }
            return string.Format("{0}table.FullColumnInfo.Add(new ColumnInfo() {{ ColumnName = \"{1}\", DefaultValue = entity.{1}, DataType = \"{3}\"}}); \r\n			", SaveColumnInfoFull, name, KeyParameter, column.ColumnType);
        }


        private string DealHtmlViewOpened(bool isNumber, string HtmlViewOpened, string name, string KeyParameter, SysDefineColumnEntity column)
        {
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}$('#txt{2}{1}').htmlarea({{css: './../Stylesheet/share.css' }}); $('#txt{2}{1}').htmlarea('updateHtmlArea');\r\n                ", HtmlViewOpened, name, KeyParameter);
            }
            return HtmlViewOpened;
        }
        private string DealHtmlViewShow(bool isNumber, string HtmlViewShow, string name, string KeyParameter, SysDefineColumnEntity column)
        {            
            if (column.ColumnLength == -1 && (string.Equals(column.ColumnType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                string.Equals(column.ColumnType, "nvarchar", StringComparison.CurrentCultureIgnoreCase)))
            {
                return string.Format("{0}$('#txt{2}{1}', d.data[0]).jHtmlArea();\r\n                ", HtmlViewShow, name, KeyParameter);
            }
            return HtmlViewShow;
        }
     
        private string QueryParameters(string name, string KeyParameter, SysDefineColumnEntity column)
        {
            if (column.ValidationType == 11)
            {
                return string.Format("$.trim($('#txt{0}{1}').comboselect('getSingleValue'))", KeyParameter, name);
            }
            else if (column.ValidationType == 12)
            {
                return string.Format("$.trim($('#txt{0}{1}').comboselect('getValue'))", KeyParameter, name);
            }
            if (column.ValidationType == 21)
            {
                return string.Format("$.trim($('#txt{0}{1}').combotree('getSingleValue'))", KeyParameter, name);
            }
            if (column.ValidationType == 31)
            {
                return string.Format("$.trim($('#txt{0}{1}').val())", KeyParameter, name);
            }
            if (column.ValidationType == 55)
            {
                return string.Format("$.trim($('#txt{0}{1}').fileselect('getValue'))", KeyParameter, name);
            }
            if (column.ValidationType == 56)
            {
                return string.Format("$.trim($('#txt{0}{1}').commentselect('getValue'))", KeyParameter, name);
            }
            return string.Format("$.trim($('#txt{0}{1}').val())", KeyParameter, name);
        }

        private string DealUpdateSingleRow(bool isNumber, string SaveParameters, string name, SysDefineColumnEntity column)
        {
            /*
            if (column.ValidationType == 1 || column.ValidationType == 2 || column.ValidationType == 3 || column.ValidationType == 11 || column.ValidationType == 12 || column.ValidationType == 21 || column.ValidationType == 31)
            {
                return string.Format("{0}\"{1}\": data.{1},\r\n                \"{1}" + showtag + "\": data.{1}" + showtag + ",\r\n                ", SaveParameters, name);
            }*/
            return string.Format("{0}\"{1}\": data.{1},\r\n                ", SaveParameters, name);
        }

        private string DealSetValidations(string SetValidations, string name, SysDefineColumnEntity column, string className, string filled)
        {
            if (column.ValidationType == 1)
            {
                return SetValidations + "LoadValidationByKey('#txt" + name + "', 'WebBusiness." + column.ValidationPool + "', '', "+ filled + ");\r\n		";
            }
            if (column.ValidationType == 2)
            {
                return SetValidations + "LoadCategoryByType('#txt" + name + "', '" + column.ValidationId + "', " + filled + ");\r\n		";
            }
            if (column.ValidationType == 11)
            {
                return SetValidations + "$('#txt" + name + "').comboselect({'height': 300,currentAllocation: currentAllocation,currentUserId: currentUserId,currentSessionId: currentSessionId,currentStoreId: currentStoreId,assemblyName: 'WebBusiness',classType: 'WebBusiness." + column.ValidationPool + "',methodName: 'LoadSelections',multiselect: false,singleTextMethodName: 'LoadSelectionText', selectType: '" + column.ValidationId + "'});\r\n		";
            }
            else if (column.ValidationType == 12)
            {
                return SetValidations + "$('#txt" + name + "').comboselect({'height': 300,currentAllocation: currentAllocation,currentUserId: currentUserId,currentSessionId: currentSessionId,currentStoreId: currentStoreId,assemblyName: 'WebBusiness',classType: 'WebBusiness." + column.ValidationPool + "',methodName: 'LoadSelections',multiselect: true,singleTextMethodName: 'LoadSelectionText', selectType: '" + column.ValidationId + "'});\r\n		";
            }
            if (column.ValidationType == 21)
            {
                return SetValidations + "$('#txt" + name + "').combotree({'height': 300,currentAllocation: currentAllocation,currentUserId: currentUserId,currentSessionId: currentSessionId,currentStoreId: currentStoreId,assemblyName: 'WebBusiness',classType: 'WebBusiness." + column.ValidationPool + "',methodName: 'Load" + column.ValidationPool.Replace("Pool", "") + "Tree',multiselect: false});\r\n		";
            }
            if (column.ValidationType == 31)
            {
            }
            if (column.ValidationType == 55)
            {
                return SetValidations + "$('#txt" + name + "').fileselect({'height': 300,currentDefaultUploadSite: CurrentDefaultUploadFullSite,currentAllocation: currentAllocation,currentUserId: currentUserId,currentSessionId: currentSessionId,currentStoreId: currentStoreId,assemblyName: 'WebBusiness',classType: 'WebBusiness." + column.ValidationPool + "',methodName: 'LoadSelections',multiselect: true,singleTextMethodName: 'LoadSelectionText', selectType: '" + column.ValidationId + "'});\r\n		";
            }
            if (column.ValidationType == 56)
            {
                return SetValidations + "$('#txt" + name + "').commentselect({'height': 300,currentAllocation: currentAllocation,currentUserId: currentUserId,currentSessionId: currentSessionId,currentStoreId: currentStoreId,assemblyName: 'WebBusiness',classType: 'WebBusiness." + column.ValidationPool + "',methodName: 'LoadSelections',multiselect: true,singleTextMethodName: 'LoadSelectionText', selectType: '" + column.ValidationId + "'});\r\n		";
            }
            return SetValidations;
        }

        private string DealImportColumns(bool isDateTime, bool isNumber, string ImportColumns, string name, SysDefineColumnEntity column)
        {
            if (name.Equals("RecId") || name.Equals("OrderBy") || name.Equals("CreateBy") || name.Equals("CreateTime") || name.Equals("ModifyBy") || name.Equals("ModifyTime") || name.Equals("IsActive"))
            {
                return ImportColumns;
            }
            if (isDateTime)
            {
                return string.Format("{0}entity.{1} = TypeHelper.ToDateTime(row.GetCell(0).ToString());\r\n					", ImportColumns, name);
            }
            if (isNumber)
            {
                return string.Format("{0}entity.{1} = Convert.ToInt32(row.GetCell(0).ToString());\r\n					", ImportColumns, name);
            }
            return string.Format("{0}entity.{1} = row.GetCell(0).ToString();\r\n					", ImportColumns, name);
        }

        private string DealTableContents(bool isDateTime, string TableContents, string length, string name, string KeyParameter, SysDefineColumnEntity column, bool isQuery)
        {
            if (name.Equals("RecId"))
            {
                return TableContents;
            }

            if (column.IsShowInEdit != 1)
            {
                return TableContents;
            }

            string required = string.Empty;
            if (column.IsRequired == 1)
            {
                required = "<span class=\"LimitInputItem\">*</span>";
            }


            try
            {
                if (column.IsMulitiline == 1)
                {
                    string arealength = length;
                    if (length == "-1")
                    {
                        arealength = "2000";
                    }
                    return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><textarea rows=\"30\" cols=\"\" maxlength=\"{2}\" id=\"txt{3}{4}\" ></textarea></td><td style=\"width: 10%\">{5}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, arealength, KeyParameter, name, required);
                }
            }
            catch { }

            if (isDateTime)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><input type=\"text\" id=\"txt{2}{3}\" class=\"datepickerDate\" /></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (column.ValidationType == 1 || column.ValidationType == 2)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><select id=\"txt{2}{3}\"></select></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (column.ValidationType == 11 || column.ValidationType == 12)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><div id=\"txt{2}{3}\" class=\"comboselect\"></div></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (column.ValidationType == 21)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><div id=\"txt{2}{3}\" class=\"combotree\"></div></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (column.ValidationType == 31 && isQuery)
            {
                return TableContents;
            }
            if (column.ValidationType == 31 && !isQuery)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><input type=\"text\" id=\"txt{2}{3}" + showtag + "\" maxlength=\"{4}\" readonly=\"readonly\" /><input type=\"hidden\" id=\"txt{2}{3}\" /></td><td style=\"width: 10%\">{5}<div id=\"div{2}{3}\" class=\"edit-button\" title=\"选择附件\"></div></td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, length, required);
            }
            if (column.ValidationType == 55)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><div id=\"txt{2}{3}\" class=\"fileselect\"></div></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (column.ValidationType == 56)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><div id=\"txt{2}{3}\" class=\"commentselect\"></div></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }
            if (length == "-1")
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><input type=\"text\" id=\"txt{2}{3}\" /></td><td style=\"width: 10%\">{4}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, required);
            }

            bool isNumber = (column.ColumnType.ToLower().Contains("int") || column.ColumnType.ToLower().Contains("byte") || column.ColumnType.ToLower().Contains("decimal"));
            if (isNumber)
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><input type=\"text\" id=\"txt{2}{3}\" maxlength=\"{4}\" /></td><td style=\"width: 10%\">{5}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, 13, required);
            }
            else
            {
                return string.Format("{0}<tr><td class=\"search_text\" style=\"width: 25%\">{1}:</td><td><input type=\"text\" id=\"txt{2}{3}\" maxlength=\"{4}\" /></td><td style=\"width: 10%\">{5}&nbsp;</td></tr>\r\n            ", TableContents, column.ColumnHeader, KeyParameter, name, length, required);
            }
        }

        private string DealConfiguredImportResult(string tableName, ref string updatePoolStatus)
        {
            updatePoolStatus = "";
            var sql = string.Format("select * from sys_define_import where TableName='{0}'", tableName);
            var dt = SqlHelper.ExecuteDataTable(sql);
            if (dt.Rows.Count == 0)
                return "";
            var strPool = new StringBuilder();
            var strRst = new StringBuilder();
            foreach (DataRow item in dt.Rows)
            {
                var type = Int32.Parse(item["ValidationType"].ToString());
                var colName = item["ColumnName"].ToString();
                var colIndex = Int32.Parse(item["ColIndex"].ToString()) - 1;
                var colPool = item["ValidationPool"].ToString();
                if (!string.IsNullOrEmpty(colPool))
                    strPool.AppendFormat("{0}.InitializeResults();\r\n					", colPool);
                var saveFun = item["SaveValidataion"].ToString();
                var validationColName = item["ValidationColName"].ToString();
                switch (type)
                {
                    case 0://字符串，不需验证
                        strRst.Append(string.Format("entity.{0} = DealCellValue(row,{1});\r\n					", colName, colIndex));
                        break;
                    case 1://整形
                        strRst.Append(string.Format("entity.{0} = Convert.ToInt32(DealCellValue(row,{1}));\r\n					",
                                colName, colIndex));
                        break;
                    case 2://Decimal
                        strRst.Append(string.Format("entity.{0} = Convert.ToDecimal(DealCellValue(row,{1}));\r\n					",
                                colName, colIndex));
                        break;
                    case 3://Datetime
                        strRst.Append(string.Format("entity.{0} = TypeHelper.ToDateTime(DealCellValue(row,{1}));\r\n					",
                                colName, colIndex));
                        break;
                    case 11://Pool验证，如果不存在，需要新增
                        strRst.AppendFormat("tempRec = DealCellValue(row, {0});\r\n					", colIndex);
                        strRst.AppendFormat("var rec{0} = {1}.BufferPool.FirstOrDefault(t => t.{0} == tempRec);\r\n					", validationColName, colPool, colIndex);
                        strRst.Append("targetId = \"\";\r\n					");
                        strRst.AppendFormat("if (rec{0} != null) \r\n					", validationColName);
                        strRst.Append("{ \r\n					");
                        strRst.AppendFormat("targetId = rec{0}.RecId;\r\n					", validationColName);
                        strRst.Append("} \r\n					");
                        strRst.Append("else \r\n					{\r\n					");
                        strRst.AppendFormat("rec{0} = new {1}Entity();\r\n					", validationColName, saveFun);
                        strRst.AppendFormat("rec{0}.{0} = tempRec;\r\n					", validationColName);
                        strRst.AppendFormat("targetId = {1}.SaveResultByCode(connection, currentStoreId, rec{0});\r\n					", validationColName, saveFun);
                        strRst.Append("} \r\n					");
                        strRst.Append(string.Format("entity.{0} = targetId;\r\n					", colName));
                        break;
                    case 12://验证，如果不存在则返回错误
                        strRst.AppendFormat("tempRec = DealCellValue(row, {0});\r\n					", colIndex);
                        strRst.AppendFormat("var rec{0} = {1}.BufferPool.FirstOrDefault(t => t.{0} == tempRec);\r\n					", validationColName, colPool, colIndex);
                        strRst.Append("targetId = \"\";\r\n					");
                        strRst.AppendFormat("if (rec{0} != null) \r\n					", validationColName);
                        strRst.AppendFormat("targetId = rec{0}.RecId;\r\n					", validationColName);
                        strRst.Append("else \r\n					");
                        strRst.AppendFormat("throw new Exception(\"{0}不存在\"+ tempRec);\r\n					", colName);
                        strRst.Append(string.Format("entity.{0} = targetId;\r\n					", colName));
                        break;
                }
            }
            updatePoolStatus = strPool.ToString();
            return strRst.ToString();
        }

        private string DealFillResult(string FillResult, string name, SysDefineColumnEntity column)
        {
            /*
            if (column.ValidationType == 1 || column.ValidationType == 2 || column.ValidationType == 3 || column.ValidationType == 11 || column.ValidationType == 12 || column.ValidationType == 21 || column.ValidationType == 31)
            {
                return string.Format("{0}{1}: item.{1},\r\n                {1}" + showtag + ": item.{1}" + showtag + ",\r\n                ", FillResult, name);
            }*/
            return string.Format("{0}{1}: item.{1},\r\n                ", FillResult, name);
        }

        private string baseString(string inStr)
        {
            string[] str = inStr.Split('_');
            string s = "";
            inStr = "";

            for (int i = 0; i < str.Length; i++)
            {
                if (str[i].Length > 0)
                {
                    s = str[i].Substring(0, 1);

                    if (i == 0)
                    {
                        inStr += s.ToLower() + str[i].Substring(1);
                    }
                    else
                    {
                        inStr += s.ToUpper() + str[i].Substring(1);
                    }
                }
            }

            return inStr;
        }


        public void CreateClassFile(bool isCreateTable)
        {
            if (isCreateTable)
                this.CreateTable(this._tableName);
            this.CreateFile(this._tableName);
        }

        private static DataSet DataReaderToDataSet(IDataReader reader)
        {
            DataTable table = new DataTable();
            int fieldCount = reader.FieldCount;
            for (int i = 0; i < fieldCount; i++)
            {
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            table.BeginLoadData();
            object[] values = new object[fieldCount];
            while (reader.Read())
            {
                reader.GetValues(values);
                table.LoadDataRow(values, true);
            }
            table.EndLoadData();
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            return ds;
        }

        private static SqlDbType ToSqlDbType(Type type)
        {
            SqlParameter p1 = new SqlParameter();
            System.ComponentModel.TypeConverter tc;
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);

            if (tc.CanConvertFrom(type))
                p1.DbType = (DbType)tc.ConvertFrom(type.Name);
            else
            {
                try
                {
                    p1.DbType = (DbType)tc.ConvertFrom(type.Name);
                }
                catch
                {
                }
            }

            return p1.SqlDbType;
        }

        private static string ToType(string typecode)
        {
            typecode = typecode.ToLower();
            if (typecode.Contains("char"))
                return "string";
            if (typecode.Equals("bigint"))
                return "Int64?";
            if (typecode.Contains("date") || typecode.Contains("time"))
                return "DateTime?";
            if (typecode.Contains("bit") || typecode.Contains("byte"))
                return "byte?";
            if (typecode.Contains("int"))
                return "int?";
            if (typecode.Contains("decimal"))
                return "decimal?";

            return "string";
        }

    }
}