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