internal static string FormatOracleDateTime(string where, MDataColumn mdc)
{
if (where.IndexOf(':') > -1 && where.IndexOfAny(new char[] { '>', '<', '-', '/' }) > -1)//判断是否存在日期的判断
{
foreach (MCellStruct item in mdc)
{
if (DataType.GetGroup(item.SqlType) == 2 && where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
{
string pattern = @"(\s?" + item.ColumnName + @"\s*[><]{1}[=]?\s*)('.{19,23}')";
Regex reg = new Regex(pattern, RegexOptions.IgnoreCase);
if (reg.IsMatch(where))
{
where = reg.Replace(where, delegate(Match match)
{
if (item.SqlType == SqlDbType.Timestamp)
{
return(match.Groups[1].Value + "to_timestamp(" + match.Groups[2].Value + ",'yyyy-MM-dd HH24:MI:ss.ff')");
}
else
{
return(match.Groups[1].Value + "to_date(" + match.Groups[2].Value + ",'yyyy-mm-dd hh24:mi:ss')");
}
});
}
}
}
}
return(where);
}
/// <summary>
/// Mysql Bit 类型不允许条件带引号 （字段='0' 不可以）
/// </summary>
/// <param name="where"></param>
/// <param name="mdc"></param>
/// <returns></returns>
internal static string FormatMySqlBit(string where, MDataColumn mdc)
{
if (where.Contains("'0'"))
{
foreach (MCellStruct item in mdc)
{
int groupID = DataType.GetGroup(item.SqlType);
if (groupID == 1 || groupID == 3)//视图模式里取到的bit是bigint,所以数字一并处理
{
if (where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
{
string pattern = @"\s?" + item.ColumnName + @"\s*=\s*'0'";
where = Regex.Replace(where, pattern, " " + item.ColumnName + "=0", RegexOptions.IgnoreCase);
}
}
}
}
return(where);
}
/// <summary>
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
/// </summary>
/// <param name="flag">[0:转成标准值],[1:转成各数据库值],[2:转成各数据库值并补充字符串前后缀]</param>
/// <param name="sqlDbType">该列的值</param>
/// <returns></returns>
public static string FormatDefaultValue(DataBaseType dalType, object value, int flag, SqlDbType sqlDbType)
{
string defaultValue = Convert.ToString(value).Trim().TrimEnd('\n');//oracle会自带\n结尾
if (dalType != DataBaseType.Access)
{
defaultValue = defaultValue.Replace("GenGUID()", string.Empty);
}
if (defaultValue.Length == 0)
{
return(null);
}
int groupID = DataType.GetGroup(sqlDbType);
if (flag == 0)
{
#region 转标准值
if (groupID == 2)//日期的标准值
{
return(SqlValue.GetDate);
}
else if (groupID == 4)
{
return(SqlValue.Guid);
}
switch (dalType)
{
case DataBaseType.MySql: //用转\' \"，所以不用替换。
defaultValue = defaultValue.Replace("\\\"", "\"").Replace("\\\'", "\'");
break;
case DataBaseType.Access:
case DataBaseType.SQLite:
defaultValue = defaultValue.Replace("\"\"", "≮");
break;
default:
defaultValue = defaultValue.Replace("''", "≯");
break;
}
switch (defaultValue.ToLower().Trim('(', ')'))
{
case "newid":
case "guid":
case "sys_guid":
case "genguid":
case "uuid":
return(SqlValue.Guid);
}
#endregion
}
else
{
if (defaultValue == SqlValue.Guid)
{
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
return(SqlCompatible.FormatGUID(defaultValue, dalType));
default:
return("");
}
}
}
switch (dalType)
{
case DataBaseType.Access:
if (flag == 0)
{
if (defaultValue[0] == '"' && defaultValue[defaultValue.Length - 1] == '"')
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "Now()").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.MsSql:
case DataBaseType.Sybase:
if (flag == 0)
{
if (defaultValue.StartsWith("(") && defaultValue.EndsWith(")")) //避免 (newid()) 被去掉()
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
if (defaultValue.StartsWith("N'"))
{
defaultValue = defaultValue.TrimStart('N');
}
defaultValue = defaultValue.Trim('\''); //'(', ')',
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "getdate()").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "(N'" + defaultValue + "')";
}
}
break;
case DataBaseType.Oracle:
if (flag == 0)
{
defaultValue = defaultValue.Trim('\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "'" + defaultValue + "'";
}
}
break;
case DataBaseType.MySql:
if (flag == 0)
{
defaultValue = defaultValue.Replace("b'0", "0").Replace("b'1", "1").Trim(' ', '\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("'", "\\'").Replace("\"", "\\\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.SQLite:
if (flag == 0)
{
defaultValue = defaultValue.Trim('"');
if (groupID > 0) //兼容一些不规范的写法。像数字型的加了引号 '0'
{
defaultValue = defaultValue.Trim('\'');
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.PostgreSQL:
if (flag == 0)
{
defaultValue = defaultValue.Trim('"');
if (groupID == 0)
{
defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
}
if (groupID > 0) //兼容一些不规范的写法。像数字型的加了引号 '0'
{
defaultValue = defaultValue.Trim('\'');
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "now()").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
defaultValue = "'" + defaultValue.Trim('\'') + "'";
}
else if (groupID == 3) // bool
{
defaultValue = defaultValue.Replace("1", "true").Replace("0", "false");
}
}
break;
}
if (flag == 0)
{
return(defaultValue.Replace("≮", "\"").Replace("≯", "'"));
}
return(defaultValue);
}
private string GetWhereFromObj(object whereObj)
{
if (whereObj == null)
{
return(string.Empty);
}
else if (whereObj is String || (whereObj is ValueType && !(whereObj is Enum)))
{
return(Convert.ToString(whereObj));
}
else if (whereObj is IField)
{
return(SqlFormat.GetIFieldSql(whereObj));
}
MDataCell cell = null;
if (whereObj is Enum)
{
cell = _action.Data[(int)whereObj];
}
else if (whereObj is MDataCell)
{
cell = whereObj as MDataCell;
}
else
{
string propName = MBindUI.GetID(whereObj);
if (!string.IsNullOrEmpty(propName))
{
_action.UI.Get(whereObj, null, null);
cell = _action.Data[propName];
}
}
string where = string.Empty;
if (cell != null)
{
#region 从单元格里取值。
if (cell.IsNullOrEmpty)
{
isCanDo = false;
_action.dalHelper.recordsAffected = -2;
_action.dalHelper.debugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR);
return("1=2 and " + cell.ColumnName + " is null");
}
switch (_action.dalHelper.dalType)
{
case DalType.Txt:
case DalType.Xml:
switch (DataType.GetGroup(cell.Struct.SqlType))
{
case 1:
case 3:
where = cell.ColumnName + "=" + cell.Value;
break;
default:
where = cell.ColumnName + "='" + cell.Value + "'";
break;
}
break;
default:
where = cell.ColumnName + "=" + _action.dalHelper.Pre + cell.ColumnName;
_action.dalHelper.AddParameters(cell.ColumnName, cell.Value, DataType.GetDbType(cell.Struct.ValueType), cell.Struct.MaxSize, System.Data.ParameterDirection.Input);
break;
}
#endregion
}
return(where);
}
/// <summary>
/// 返回插入的字符串
/// </summary>
/// <returns>结果如:insert into tableName(ID,Name,Value) values(@ID,@Name,@Value)</returns>
internal string GetInsertSql()
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
StringBuilder _TempSql2 = new StringBuilder();
_TempSql.Append("insert into " + TableName + "(");
_TempSql2.Append(") Values(");
MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName];
int groupID = DataType.GetGroup(primaryCell.Struct.SqlType);
string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue);
if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) &&
(defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型
{
primaryCell.Value = Guid.NewGuid();
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID)
{
continue;//跳过自增列。
}
if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null)
{
_action.dalHelper.debugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR);
_action.dalHelper.recordsAffected = -2;
isCanDo = false;
break;
}
if (cell.cellValue.State > 0)
{
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DalType) + ",");
_TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ",");
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DalType);
if (_action.DalType == DalType.Oracle && dbType == DbType.String && cell.strValue == "" && !cell.Struct.IsCanNull)
{
value = " ";
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
isCanDo = true;
}
}
switch (_action.dalHelper.dalType)
{
case DalType.Oracle:
if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1)
{
_TempSql.Append(primaryCell.ColumnName + ",");
_TempSql2.Append(AutoID + ".nextval,");
}
break;
}
string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";
switch (_action.dalHelper.dalType)
{
case DalType.MsSql:
case DalType.Sybase:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
if (_action.dalHelper.dalType == DalType.Sybase)
{
sql = sql + " select @@IDENTITY as OutPutValue";
}
else
{
sql += " select cast(scope_Identity() as int) as OutPutValue";
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value);
}
if (_action.AllowInsertID && !_action.dalHelper.isOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时
{
sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " off";
}
break;
//if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入ID的，跳过，主操作会自动返回ID。
//{
// sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_Identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value));
//}
//case DalType.Oracle:
// sql += string.Format("BEGIN;select {0}.currval from dual; END;", AutoID);
// break;
}
return(sql);
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
MCellStruct mStruct = null;
SqlDbType sqlType = SqlDbType.NVarChar;
if (isView)
{
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
mdcs = GetViewColumns(sqlText, ref helper);
}
else
{
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
string[] items = realTableName.Split('.');
tableName = realTableName;
if (items.Length > 0) //跨库了
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
tableName = realTableName;
}
sql = GetOracleColumns();
}
else if (dalType == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.PostgreSQL)
{
sql = GetPostgreColumns();
}
helper.AddParameters("TableName", SqlFormat.NotKeyword(tableName), DbType.String, 150, ParameterDirection.Input);
DbDataReader sdr = helper.ExeDataReader(sql, false);
if (sdr != null)
{
long maxLength;
bool isAutoIncrement = false;
short scale = 0;
string sqlTypeName = string.Empty;
while (sdr.Read())
{
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength)) //mysql的长度可能大于int.MaxValue
{
maxLength = -1;
}
else if (maxLength > int.MaxValue)
{
maxLength = int.MaxValue;
}
sqlTypeName = Convert.ToString(sdr["SqlType"]);
sqlType = DataType.GetSqlType(sqlTypeName);
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
mStruct = new MCellStruct(mdcs.DataBaseType);
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
mStruct.OldName = mStruct.ColumnName;
mStruct.SqlType = sqlType;
mStruct.IsAutoIncrement = isAutoIncrement;
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
mStruct.MaxSize = (int)maxLength;
mStruct.Scale = scale;
mStruct.Description = Convert.ToString(sdr["Description"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.Oracle:
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
break;
}
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
sdr.Close();
if (dalType == DataBaseType.Oracle && mdcs.Count > 0) //默认没有自增概念，只能根据情况判断。
{
MCellStruct firstColumn = mdcs[0];
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
{
firstColumn.IsAutoIncrement = true;
}
}
}
#endregion
break;
case DataBaseType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(tableName) });
if (!helper.IsOpenTrans)
{
helper.Con.Close();
}
int size;
short sizeScale;
string dataTypeName = string.Empty;
foreach (DataRow row in sqliteDt.Rows)
{
object len = row["NUMERIC_PRECISION"];
if (len == null || len == DBNull.Value)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于int.MaxValue
{
size = -1;
}
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
if (dataTypeName == "text" && size > 0)
{
sqlType = DataType.GetSqlType("varchar");
}
else
{
sqlType = DataType.GetSqlType(dataTypeName);
}
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
mStruct.Scale = sizeScale;
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType); //"COLUMN_DEFAULT"
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
mStruct.SqlTypeName = dataTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
#endregion
break;
case DataBaseType.Access:
#region Access
DataTable keyDt, valueDt;
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
OleDbCommand com = new OleDbCommand(sqlText, con);
con.Open();
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
con.Close();
con.Dispose();
if (keyDt != null && valueDt != null)
{
string columnName = string.Empty, sqlTypeName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
foreach (DataRow row in keyDt.Rows)
{
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]); //IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
sqlTypeName = Convert.ToString(row["DataType"]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
if (Convert.ToInt32(row["NumericPrecision"]) > 0) //NumericPrecision
{
maxSize = Convert.ToInt32(row["NumericPrecision"]);
}
else
{
long len = Convert.ToInt64(row["ColumnSize"]);
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
foreach (DataRow item in valueDt.Rows)
{
if (columnName == item[3].ToString()) //COLUMN_NAME
{
if (item[8].ToString() != "")
{
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType); //"COLUMN_DEFAULT"
}
break;
}
}
mdcs.Add(mStruct);
}
}
#endregion
break;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
if (mdcs.Count > 0)
{
//移除被标志的列：
string[] fields = AppConfig.DB.HiddenFields.Split(',');
foreach (string item in fields)
{
string field = item.Trim();
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
{
mdcs.Remove(field);
}
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
return(mdcs);
}
private static string GetKey(MCellStruct column, DalType dalType, ref List <MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType); //列名。
int groupID = DataType.GetGroup(column.SqlType); //数据库类型。
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement; //是否主键或自增列。
if (dalType != DalType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DalType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DalType.MySql))//MySql 的自增必须是主键.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DalType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else // 主键。
{
if (groupID == 4) //主键又是GUID
{
key += " default GenGUID()";
}
}
break;
case DalType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.GUID) //主键又是GUID
{
key += " Default (SYS_GUID())";
}
break;
case DalType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DalType.SQLite: //sqlite的AUTOINCREMENT不能写在primarykey前,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear(); //如果有自增加，只允许存在这一个主键。
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && groupID < 5)//默认值只能是基础类型有。
{
if (dalType == DalType.MySql)
{
if ((groupID == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (groupID == 2 && key.Contains("datetime"))) //只能对TIMESTAMP类型的赋默认值。
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DalType.MySql)
{
defaultValue = defaultValue.Trim('(', ')');
}
key += " Default " + defaultValue;
}
}
er:
if (dalType != DalType.Access)
{
if (dalType == DalType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit 不允许为Null
}
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DalType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return(key + ",");
}
/// <summary>
/// 返回插入的字符串
/// </summary>
/// <returns>结果如:insert into tableName(id,Name,Value) values(@id,@Name,@Value)</returns>
internal string GetInsertSql()
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
StringBuilder _TempSql2 = new StringBuilder();
_TempSql.Append("insert into " + TableName + "(");
_TempSql2.Append(") Values(");
MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName];
int groupID = DataType.GetGroup(primaryCell.Struct.SqlType);
string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue);
if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) &&
(defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型
{
primaryCell.Value = Guid.NewGuid();
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID)
{
continue;//跳过自增列。
}
if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null)
{
string err = AppConst.HR + string.Format("error : {0} {1} can't be insert null", TableName, cell.ColumnName) + AppConst.BR;
Log.Write(err, LogType.DataBase);
_action.dalHelper.DebugInfo.Append(err);
_action.dalHelper.RecordsAffected = -2;
isCanDo = false;
break;
}
if (cell.State > 0)
{
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + ",");
_TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ",");
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
if (dbType == DbType.String && cell.StringValue == "")
{
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
{
value = " ";//Oracle not null 字段，不允许设置空值。
}
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
{
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
}
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
isCanDo = true;
}
}
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.Oracle:
if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1)
{
_TempSql.Append(primaryCell.ColumnName + ",");
_TempSql2.Append(AutoID + ".nextval,");
}
break;
}
string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.PostgreSQL:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
string key = Convert.ToString(primaryCell.Struct.DefaultValue);
if (!string.IsNullOrEmpty(key))
{
key = key.Replace("nextval", "currval");
sql = sql + "; select " + key + " as OutPutValue";
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format("; select '{0}' as OutPutValue", primaryCell.Value);
}
break;
case DataBaseType.MsSql:
case DataBaseType.Sybase:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
if (_action.dalHelper.DataBaseType == DataBaseType.Sybase)
{
sql = sql + " select @@idENTITY as OutPutValue";
}
else if (_action.dalHelper.DataBaseType == DataBaseType.MsSql)
{
sql += " select cast(scope_identity() as bigint) as OutPutValue"; //改成bigint避免转换数据溢出
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value);
}
if (_action.AllowInsertID && !_action.dalHelper.IsOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时
{
sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " off";
}
break;
//if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入id的，跳过，主操作会自动返回id。
//{
// sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value));
//}
//case DalType.Oracle:
// sql += string.Format("BEGIN;select {0}.currval from dual; END;", Autoid);
// break;
}
return(sql);
}
