public static IDbCommand GenerateInsertCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts)
{
string sTemplate = @"
INSERT INTO
[{0}] ({1})
VALUES
({2})
";
IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty);
dbc.CommandTimeout = 1000;
List <string> lColStrings = new List <string>();
List <string> lSetStrings = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (ts.IsAutoGeneratedIdentity && ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant()))
{
continue;
}
switch (ConnectionManager.Dialect)
{
case DatabaseDialect.MsSql:
case DatabaseDialect.PgSql:
lColStrings.Add(string.Format("[{0}]", dc.ColumnName));
lSetStrings.Add(string.Format("@{0}", dc.ColumnName));
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
break;
case DatabaseDialect.MySql:
case DatabaseDialect.LtSql:
lColStrings.Add(dc.ColumnName);
lSetStrings.Add(string.Format("@{0}", dc.ColumnName));
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
break;
default:
Debug.Assert(false);
break;
}
}
string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",", lColStrings.ToArray()),
string.Join(",", lSetStrings.ToArray()));
dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery);
return(dbc);
}
public static IDbCommand GenerateUpdateCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts)
{
string sTemplate = @"
UPDATE
[{0}]
_SET_
{1}
WHERE
{2}
";
IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty);
dbc.CommandTimeout = 1000;
List <string> lSetStrings = new List <string>();
List <string> lIdentityStrings = new List <string>();
//sIdentityName = sIdentityName.ToLowerInvariant();
foreach (DataColumn dc in dt.Columns)
{
if (ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant()))
{
lIdentityStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName));
}
else
{
lSetStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName));
}
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
}
string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",\r\n", lSetStrings.ToArray()),
string.Join("\r\nAND\r\n", lIdentityStrings.ToArray()));
dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery);
return(dbc);
}