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