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);
}
/// <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());
}
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);
}