public static string CreateSelectCommandText(DataTable table, string Suffix, DBAbstractDataLayer.DataAccessRules.Syntax.DataDeletionStatus deletionStatus)
{
string command = string.Empty;
if (deletionStatus != Syntax.DataDeletionStatus.All)
{
var selectQuery = new System.Text.StringBuilder();
var tmpApp = new System.Text.StringBuilder();
selectQuery.AppendFormat("SELECT {2} FROM {0} {1} ", table.TableName, Suffix, getAllColumnNames(table));
if (columnNames.ContainsKey(table.TableName))
{
tmpApp = columnNames[table.TableName];
}
else
{
columnNames[table.TableName] = tmpApp;
}
selectQuery.Append(tmpApp.ToString());
selectQuery.AppendFormat(" {0} {1}.isDeleted=@isDeleted ",
Suffix.Contains("WHERE ") ? "AND": "WHERE",
table.TableName);
return(selectQuery.ToString());
}
else
{
SQLCustomCommandBuilder cb = new SQLCustomCommandBuilder(table, DALRule.MetaModel.getColumnTypes(table.TableName, "TransactSQL"));
return(((SqlCommand)(cb.GetSelectWithFilterCommand(Suffix))).CommandText);
}
}
public static SqlCommand CreateInsertCommand(DataTable table)
{
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
SQLCustomCommandBuilder cb = new SQLCustomCommandBuilder(table, DALRule.MetaModel.getColumnTypes(table.TableName, "TransactSQL"));
da.InsertCommand = (SqlCommand)cb.InsertCommand;
return(da.InsertCommand);
}
public static DbDataAdapter GetDataAdapter(string TableName, string Suffix, IDbConnection Conn, IDbTransaction Trans, DataDeletionStatus deletionStatus)
{
if (Conn == null)
{
Conn = GetConnection();
}
string command = string.Empty;
DataTable table = GetInstance().Tables[TableName];
string tableAlias = "__gisatable__";
if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All))
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.AppendFormat("SELECT {3} FROM {0} INNER JOIN (SELECT {3} FROM {0} {2}) {1} ON ", TableName, tableAlias, Suffix, getAllPrimaryKeys(table,null));
foreach (DataColumn column in table.PrimaryKey)
{
if (column.Ordinal > 0)
{
selectQuery.Append(" AND");
}
selectQuery.AppendFormat(" {0}.{2}={1}.{2}", TableName, tableAlias, column.ColumnName);
}
if (deletionStatus == DataDeletionStatus.Exists)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=0", tableAlias);
}
else if (deletionStatus == DataDeletionStatus.Deleted)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=1", tableAlias);
}
command = selectQuery.ToString();
}
else if (Suffix.Length == 0 && !(deletionStatus == DataDeletionStatus.All))
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.AppendFormat("SELECT {0} FROM {1}", getAllPrimaryKeys(table,null), TableName);
if (deletionStatus == DataDeletionStatus.Exists)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=0", TableName);
}
else if (deletionStatus == DataDeletionStatus.Deleted)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=1", TableName);
}
command = selectQuery.ToString();
}
if (workingDBMS == "SQLServer")
{
SqlDataAdapter da = new SqlDataAdapter();
SQLCustomCommandBuilder cb = new SQLCustomCommandBuilder(GetInstance().Tables[TableName], ((SqlConnection)(Conn)), MetaModelHelper.getColumnTypes(TableName, "TransactSQL"), ((SqlTransaction)(Trans)));
if (Suffix.Length > 0 && deletionStatus == DataDeletionStatus.All)
{
da.SelectCommand = ((SqlCommand)(cb.GetSelectWithFilterCommand(Suffix)));
}
else if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All) && command.Length > 0)
{
da.SelectCommand = new SqlCommand(command);
da.SelectCommand.Connection = ((SqlConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((SqlTransaction)(Trans));
}
else if (Suffix.Length == 0 && command.Length == 0)
{
da.SelectCommand = ((SqlCommand)(cb.SelectAllCommand));
da.SelectCommand.CommandText += "WHERE isDeleted = 0";
}
else if (Suffix.Length == 0 && command.Length > 0)
{
da.SelectCommand = new SqlCommand(command);
da.SelectCommand.Connection = ((SqlConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((SqlTransaction)(Trans));
}
da.UpdateCommand = ((SqlCommand)(cb.UpdateCommand));
da.DeleteCommand = ((SqlCommand)(cb.DeleteCommand));
da.InsertCommand = ((SqlCommand)(cb.InsertCommand));
Trace.WriteLineIf(SqlTrace.Enabled, da.SelectCommand.CommandText);
return da;
}
else if (workingDBMS == "Others")
{
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCustomCommandBuilder cb = new OleDbCustomCommandBuilder(GetInstance().Tables[TableName], ((OleDbConnection)(Conn)), MetaModelHelper.getColumnTypes(TableName, "oleDB"), ((OleDbTransaction)(Trans)));
if (Suffix.Length > 0 && deletionStatus == DataDeletionStatus.All)
{
da.SelectCommand = ((OleDbCommand)(cb.GetSelectWithFilterCommand(Suffix)));
}
else if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All) && command.Length > 0)
{
da.SelectCommand = new OleDbCommand(command);
da.SelectCommand.Connection = ((OleDbConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((OleDbTransaction)(Trans));
}
else if (Suffix.Length == 0 && command.Length == 0)
{
da.SelectCommand = ((OleDbCommand)(cb.SelectAllCommand));
da.SelectCommand.CommandText += "WHERE isDeleted = 0";
}
else if (Suffix.Length == 0 && command.Length > 0)
{
da.SelectCommand = new OleDbCommand(command);
da.SelectCommand.Connection = ((OleDbConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((OleDbTransaction)(Trans));
}
da.UpdateCommand = ((OleDbCommand)(cb.UpdateCommand));
da.DeleteCommand = ((OleDbCommand)(cb.DeleteCommand));
da.InsertCommand = ((OleDbCommand)(cb.InsertCommand));
Trace.WriteLineIf(SqlTrace.Enabled, da.SelectCommand.CommandText);
return da;
}
else
{
Debug.Assert(false, "(DataAdapter) SGBD desconhecido");
return null;
}
}
public static DbDataAdapter GetDataAdapter(string TableName, string Suffix, IDbConnection Conn, IDbTransaction Trans, DataDeletionStatus deletionStatus)
{
if (Conn == null)
{
Conn = GetConnection();
}
string command = string.Empty;
DataTable table = GetInstance().Tables[TableName];
string tableAlias = "__gisatable__";
if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All))
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.AppendFormat("SELECT {3} FROM {0} INNER JOIN (SELECT {3} FROM {0} {2}) {1} ON ", TableName, tableAlias, Suffix, getAllPrimaryKeys(table, null));
foreach (DataColumn column in table.PrimaryKey)
{
if (column.Ordinal > 0)
{
selectQuery.Append(" AND");
}
selectQuery.AppendFormat(" {0}.{2}={1}.{2}", TableName, tableAlias, column.ColumnName);
}
if (deletionStatus == DataDeletionStatus.Exists)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=0", tableAlias);
}
else if (deletionStatus == DataDeletionStatus.Deleted)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=1", tableAlias);
}
command = selectQuery.ToString();
}
else if (Suffix.Length == 0 && !(deletionStatus == DataDeletionStatus.All))
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.AppendFormat("SELECT {0} FROM {1}", getAllPrimaryKeys(table, null), TableName);
if (deletionStatus == DataDeletionStatus.Exists)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=0", TableName);
}
else if (deletionStatus == DataDeletionStatus.Deleted)
{
selectQuery.AppendFormat(" WHERE {0}.isDeleted=1", TableName);
}
command = selectQuery.ToString();
}
if (workingDBMS == "SQLServer")
{
SqlDataAdapter da = new SqlDataAdapter();
SQLCustomCommandBuilder cb = new SQLCustomCommandBuilder(GetInstance().Tables[TableName], ((SqlConnection)(Conn)), MetaModelHelper.getColumnTypes(TableName, "TransactSQL"), ((SqlTransaction)(Trans)));
if (Suffix.Length > 0 && deletionStatus == DataDeletionStatus.All)
{
da.SelectCommand = ((SqlCommand)(cb.GetSelectWithFilterCommand(Suffix)));
}
else if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All) && command.Length > 0)
{
da.SelectCommand = new SqlCommand(command);
da.SelectCommand.Connection = ((SqlConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((SqlTransaction)(Trans));
}
else if (Suffix.Length == 0 && command.Length == 0)
{
da.SelectCommand = ((SqlCommand)(cb.SelectAllCommand));
da.SelectCommand.CommandText += "WHERE isDeleted = 0";
}
else if (Suffix.Length == 0 && command.Length > 0)
{
da.SelectCommand = new SqlCommand(command);
da.SelectCommand.Connection = ((SqlConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((SqlTransaction)(Trans));
}
da.UpdateCommand = ((SqlCommand)(cb.UpdateCommand));
da.DeleteCommand = ((SqlCommand)(cb.DeleteCommand));
da.InsertCommand = ((SqlCommand)(cb.InsertCommand));
Trace.WriteLineIf(SqlTrace.Enabled, da.SelectCommand.CommandText);
return(da);
}
else if (workingDBMS == "Others")
{
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCustomCommandBuilder cb = new OleDbCustomCommandBuilder(GetInstance().Tables[TableName], ((OleDbConnection)(Conn)), MetaModelHelper.getColumnTypes(TableName, "oleDB"), ((OleDbTransaction)(Trans)));
if (Suffix.Length > 0 && deletionStatus == DataDeletionStatus.All)
{
da.SelectCommand = ((OleDbCommand)(cb.GetSelectWithFilterCommand(Suffix)));
}
else if (Suffix.Length > 0 && !(deletionStatus == DataDeletionStatus.All) && command.Length > 0)
{
da.SelectCommand = new OleDbCommand(command);
da.SelectCommand.Connection = ((OleDbConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((OleDbTransaction)(Trans));
}
else if (Suffix.Length == 0 && command.Length == 0)
{
da.SelectCommand = ((OleDbCommand)(cb.SelectAllCommand));
da.SelectCommand.CommandText += "WHERE isDeleted = 0";
}
else if (Suffix.Length == 0 && command.Length > 0)
{
da.SelectCommand = new OleDbCommand(command);
da.SelectCommand.Connection = ((OleDbConnection)(Conn));
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Transaction = ((OleDbTransaction)(Trans));
}
da.UpdateCommand = ((OleDbCommand)(cb.UpdateCommand));
da.DeleteCommand = ((OleDbCommand)(cb.DeleteCommand));
da.InsertCommand = ((OleDbCommand)(cb.InsertCommand));
Trace.WriteLineIf(SqlTrace.Enabled, da.SelectCommand.CommandText);
return(da);
}
else
{
Debug.Assert(false, "(DataAdapter) SGBD desconhecido");
return(null);
}
}
