Esempio n. 1
0
        public List <DB.Coding.Field> GetFields(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement)
        {
            List <DB.Coding.Field> fields = new List <DB.Coding.Field>();
            string selectFiledsSql        = string.Format("select {0} from {1} where 1>2", selectStatement.selectClause, selectStatement.fromClause);

            System.Data.Common.DbCommand    cmd    = dataUtility.GetDbCommand(FormatSql(selectFiledsSql), dataUtility.con);
            System.Data.DataTable           dt     = new System.Data.DataTable();
            System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
            dt = reader.GetSchemaTable();
            reader.Read();
            Regex nameReg = new Regex(@"^[_0-9a-zA-Z]+$");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DB.Coding.Field field = new DB.Coding.Field();
                field.name            = dt.Rows[i]["BaseColumnName"].ToString();
                field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
                //查看名称是否合法,不合法则重命名.
                if (!nameReg.IsMatch(field.name))
                {
                    field.name = "_column" + i.ToString();
                }
                if (!nameReg.IsMatch(field.structFieldName))
                {
                    field.structFieldName = "_column" + i.ToString();
                }
                field.allowNull  = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
                field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
                if (dataUtility.dbType == DB.DBType.Sqlite)
                {
                    field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
                }
                else
                {
                    field.sqlType = reader.GetDataTypeName(i);
                }
                field.isId      = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                field.length    = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
                field.localType = dt.Rows[i]["DataType"].ToString();
                field.isId      = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                field.position  = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
                field.dbType    = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
                //field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
                //field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
                //如果没有相对应的数据库类型
                if (!string.IsNullOrEmpty(field.sqlType))
                {
                    if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
                    {
                        field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
                    }
                    else
                    {
                        switch (field.localType)
                        {
                        case "System.Int16": field.simpleType = "Number"; break;

                        case "System.Int32": field.simpleType = "Number"; break;

                        case "System.Int64": field.simpleType = "Number"; break;

                        case "System.UInt16": field.simpleType = "Number"; break;

                        case "System.UInt32": field.simpleType = "Number"; break;

                        case "System.UInt64": field.simpleType = "Number"; break;

                        case "System.Byte": field.simpleType = "Number"; break;

                        case "System.SByte": field.simpleType = "Number"; break;

                        case "System.Single": field.simpleType = "Number"; break;

                        case "System.Decimal": field.simpleType = "Number"; break;

                        case "System.Double": field.simpleType = "Number"; break;

                        case "System.TimeSpan": field.simpleType = "Time"; break;

                        case "System.DateTime:": field.simpleType = "Time"; break;

                        default: field.simpleType = field.simpleType = "String"; break;
                        }
                    }
                }
                else
                {
                    switch (field.localType)
                    {
                    case "System.Int16": field.simpleType = "Number"; break;

                    case "System.Int32": field.simpleType = "Number"; break;

                    case "System.Int64": field.simpleType = "Number"; break;

                    case "System.UInt16": field.simpleType = "Number"; break;

                    case "System.UInt32": field.simpleType = "Number"; break;

                    case "System.UInt64": field.simpleType = "Number"; break;

                    case "System.Byte": field.simpleType = "Number"; break;

                    case "System.SByte": field.simpleType = "Number"; break;

                    case "System.Single": field.simpleType = "Number"; break;

                    case "System.Decimal": field.simpleType = "Number"; break;

                    case "System.Double": field.simpleType = "Number"; break;

                    case "System.TimeSpan": field.simpleType = "Time"; break;

                    case "System.DateTime:": field.simpleType = "Time"; break;

                    default: field.simpleType = field.simpleType = "String"; break;
                    }
                }
                fields.Add(field);
            }
            reader.Close();
            return(fields);
        }
Esempio n. 2
0
        /// <summary>
        /// Generate CSV formatted output for the given reader.
        /// </summary>
        public string Generate(System.Data.Common.DbDataReader reader)
        {
            var builder = new StringBuilder();

            var schema   = reader.GetSchemaTable();
            var colcount = reader.FieldCount;
            var nullable = new bool[colcount];
            var datatype = new Type[colcount];
            var typename = new string[colcount];

            for (int c = 0; c < colcount; c++)
            {
                nullable[c] = true;
                datatype[c] = reader.GetFieldType(c);
                typename[c] = reader.GetDataTypeName(c);

                if (c == 0)
                {
                    if (this.Settings.AddLineNumbers)
                    {
                        if (this.Settings.QuotedStrings)
                        {
                            builder.Append(this.Settings.StringQuote);
                        }
                        builder.Append("Line");
                        if (this.Settings.QuotedStrings)
                        {
                            builder.Append(this.Settings.StringQuote);
                        }
                        builder.Append(this.Settings.FieldSeparator);
                    }
                }
                else
                {
                    builder.Append(this.Settings.FieldSeparator);
                }
                if (this.Settings.QuotedStrings)
                {
                    builder.Append(this.Settings.StringQuote);
                }
                builder.Append(reader.GetName(c));
                if (this.Settings.QuotedStrings)
                {
                    builder.Append(this.Settings.StringQuote);
                }
            }

            builder.Append(this.Settings.LineSeparator);

            var lineNumber = 0;

            while (reader.Read())
            {
                lineNumber++;

                for (int c = 0; c < colcount; c++)
                {
                    if (c == 0)
                    {
                        if (this.Settings.AddLineNumbers)
                        {
                            builder.Append(lineNumber);
                            builder.Append(this.Settings.FieldSeparator);
                        }
                    }
                    else
                    {
                        builder.Append(this.Settings.FieldSeparator);
                    }

                    if (nullable[c] && reader.IsDBNull(c))
                    {
                    }
                    else
                    {
                        if (datatype[c] == typeof(String))
                        {
                            if (this.Settings.QuotedStrings)
                            {
                                builder.Append(this.Settings.StringQuote);
                            }
                            builder.Append(ToCsvableString(reader.GetString(c)));
                            if (this.Settings.QuotedStrings)
                            {
                                builder.Append(this.Settings.StringQuote);
                            }
                        }
                        else if (datatype[c] == typeof(DateTime))
                        {
                            builder.Append(reader.GetDateTime(c).ToString(this.Settings.DateTimeFormat, this.Settings.FormatProvider));
                        }
                        else if (datatype[c] == typeof(Boolean))
                        {
                            builder.Append(reader.GetBoolean(c) ? this.Settings.BooleanTrue : this.Settings.BooleanFalse);
                        }
                        else
                        {
                            builder.AppendFormat(this.Settings.FormatProvider, "{0}", reader.GetValue(c));
                        }
                    }
                }

                builder.Append(this.Settings.LineSeparator);
            }

            return(builder.ToString());
        }
Esempio n. 3
0
        public List <SqlVarParameter> GetParameters(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement, ref List <SqlVarParameter> allSqlVarParameters)
        {
            List <SqlVarParameter> sqlVarParameters = ParseVarName(selectStatement.sqlWithOutSubSelect);

            if (sqlVarParameters.Count > 0)
            {
                string sqlParametersSelect = "select ";
                for (int i = 0; i < sqlVarParameters.Count; i++)
                {
                    if (i != 0)
                    {
                        sqlParametersSelect += ",";
                    }
                    sqlParametersSelect += sqlVarParameters[i].fullName;
                }
                sqlParametersSelect += " from " + selectStatement.fromClause + " where 1>2 ";


                Regex fromReg = new Regex(@"\s+from\s+", RegexOptions.IgnoreCase);

                System.Data.Common.DbCommand    cmd    = dataUtility.GetDbCommand(FormatSql(sqlParametersSelect), dataUtility.con);
                System.Data.DataTable           dt     = new System.Data.DataTable();
                System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
                dt = reader.GetSchemaTable();
                reader.Read();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SqlVarParameter parameter = sqlVarParameters[i];

                    DB.Coding.Field field = new DB.Coding.Field();
                    field.name            = dt.Rows[i]["BaseColumnName"].ToString();
                    field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
                    field.allowNull       = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
                    field.csTypeLink      = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
                    if (dataUtility.dbType == DB.DBType.Sqlite)
                    {
                        field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
                    }
                    else
                    {
                        field.sqlType = reader.GetDataTypeName(i);
                    }
                    field.isId      = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                    field.length    = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
                    field.localType = dt.Rows[i]["DataType"].ToString();
                    field.isId      = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                    field.position  = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
                    field.dbType    = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
                    //field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
                    //field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
                    if (!string.IsNullOrEmpty(field.sqlType))
                    {
                        if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
                        {
                            field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
                        }
                        else
                        {
                            field.simpleType = "String";
                        }
                    }
                    else
                    {
                        field.simpleType = "";
                    }
                    parameter.field = field;
                    allSqlVarParameters.Add(parameter);
                }
                reader.Close();
            }
            //分析子查询里的参数
            for (int i = 0; i < selectStatement.selects.Count; i++)
            {
                GetParameters(dataUtility, selectStatement.selects[i], ref allSqlVarParameters);
            }
            return(sqlVarParameters);
        }