/// <summary> /// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ); select c1, c2, ... 回读,自增主键将使用 LAST_INSERT_ID() 作为条件 /// </summary> /// <param name="replace">是否为替换模式</param> /// <param name="t">表</param> /// <param name="insertCols">要插入的字段列表(不能包含自增字段)。如果为空表示所有字段(跳过只读字段)</param> /// <param name="selectCols">回读字段列表。传入 null, 则不回读,不生成 ; select 这部分语句</param> /// <returns>查询</returns> public static MyQuery Insert(bool replace, DbTable t, IEnumerable <DbColumn> insertCols = null, IEnumerable <DbColumn> selectCols = null) { var q = new MyQuery((replace ? "replace" : "insert") + " into ", t, " ( "); var sq = new MyQuery(" ) values ( "); if (insertCols == null) { insertCols = t.columns; } foreach (var c in insertCols) // 跳过 AutoIncrease 字段(指定要插入哪些字段,将无视数据库默认值,除了自增不应该无视) { if (c.isReadonly) { continue; //throw new Exception( "只读字段无法插入" ); } q.AppendExceptTableName(c); sq.Append(c.GetParmType()); } q.Append(sq); q.Append(" )"); if (selectCols != null) { if (t.primaryKeys.Count == 0) { throw new Exception("无主键的表无法于 insert 后 select 回插入行的数据"); } q.Append(";"); if (t.primaryKeys.Count(a => a.autoIncrement) == 0) // 如果主键中不含自增字段,可直接使用 Select 的拼接物来做读回 { q.Append(Select(false, t, selectCols)); } else { sq.Clear(); if (t.primaryKeys.Count > 0) { for (int i = 0; i < t.primaryKeys.Count; ++i) { var pk = t.primaryKeys[i]; sq.Append((i == 0 ? " where " : " and "), pk, " = "); if (pk.autoIncrement) { sq.Append("LAST_INSERT_ID()"); } else { sq.Append(pk.GetParmType()); } } } q.Append(Select(false, t, selectCols, sq)); } } return(q); }
/// <summary> /// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ), ( PT.typeN, ... ), ... /// </summary> /// <param name="replace">是否为替换模式</param> /// <param name="t">表</param> /// <param name="insertCols">要插入的字段列表(不能包含自增字段)。如果为空表示所有字段(跳过只读字段)</param> /// <returns>查询</returns> public static MyQuery Inserts(bool replace, DbTable t, int rowCount, IEnumerable <DbColumn> insertCols = null) { var q = new MyQuery((replace ? "replace" : "insert") + " into ", t, " ( "); if (insertCols == null) { insertCols = t.columns; } foreach (var c in insertCols) { if (c.isReadonly) { continue; // 跳过 AutoIncrease 字段 } q.AppendExceptTableName(c); } q.Append(" ) values "); for (int i = 0; i < rowCount; ++i) { if (i > 0) { q.Append(", "); } q.Append("( "); foreach (var c in insertCols) { if (c.isReadonly) { continue; // 跳过 AutoIncrease 字段 } q.Append(c.GetParmType()); } q.Append(" )"); } return(q); }
/// <summary> /// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ), ( PT.typeN, ... ), ... /// </summary> /// <param name="replace">是否为替换模式</param> /// <param name="t">表</param> /// <param name="insertCols">要插入的字段列表(不能包含自增字段)。如果为空表示所有字段(跳过只读字段)</param> /// <returns>查询</returns> public static MyQuery Inserts( bool replace, DbTable t, int rowCount, IEnumerable<DbColumn> insertCols = null ) { var q = new MyQuery( ( replace ? "replace" : "insert" ) + " into ", t, " ( " ); if( insertCols == null ) insertCols = t.columns; foreach( var c in insertCols ) { if( c.isReadonly ) continue; // 跳过 AutoIncrease 字段 q.AppendExceptTableName( c ); } q.Append( " ) values " ); for( int i = 0; i < rowCount; ++i ) { if( i > 0 ) q.Append( ", " ); q.Append( "( " ); foreach( var c in insertCols ) { if( c.isReadonly ) continue; // 跳过 AutoIncrease 字段 q.Append( c.GetParmType() ); } q.Append( " )" ); } return q; }
/// <summary> /// 生成 insert into t ( c1, c2, ... ) values ( PT.type1, PT.type2, ... ); select c1, c2, ... 回读,自增主键将使用 LAST_INSERT_ID() 作为条件 /// </summary> /// <param name="replace">是否为替换模式</param> /// <param name="t">表</param> /// <param name="insertCols">要插入的字段列表(不能包含自增字段)。如果为空表示所有字段(跳过只读字段)</param> /// <param name="selectCols">回读字段列表。传入 null, 则不回读,不生成 ; select 这部分语句</param> /// <returns>查询</returns> public static MyQuery Insert( bool replace, DbTable t, IEnumerable<DbColumn> insertCols = null, IEnumerable<DbColumn> selectCols = null ) { var q = new MyQuery( ( replace ? "replace" : "insert" ) + " into ", t, " ( " ); var sq = new MyQuery( " ) values ( " ); if( insertCols == null ) insertCols = t.columns; foreach( var c in insertCols ) // 跳过 AutoIncrease 字段(指定要插入哪些字段,将无视数据库默认值,除了自增不应该无视) { if( c.isReadonly ) continue; //throw new Exception( "只读字段无法插入" ); q.AppendExceptTableName( c ); sq.Append( c.GetParmType() ); } q.Append( sq ); q.Append( " )" ); if( selectCols != null ) { if( t.primaryKeys.Count == 0 ) throw new Exception( "无主键的表无法于 insert 后 select 回插入行的数据" ); q.Append( ";" ); if( t.primaryKeys.Count( a => a.autoIncrement ) == 0 ) // 如果主键中不含自增字段,可直接使用 Select 的拼接物来做读回 { q.Append( Select( false, t, selectCols ) ); } else { sq.Clear(); if( t.primaryKeys.Count > 0 ) { for( int i = 0; i < t.primaryKeys.Count; ++i ) { var pk = t.primaryKeys[ i ]; sq.Append( ( i == 0 ? " where " : " and " ), pk, " = " ); if( pk.autoIncrement ) sq.Append( "LAST_INSERT_ID()" ); else sq.Append( pk.GetParmType() ); } } q.Append( Select( false, t, selectCols, sq ) ); } } return q; }