/// <summary>
/// 执行返回数据架构的查询，注意，不返回任何行
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>数据架构</returns>
public virtual DataSet ExecuteDataSetSchema(string SQL, CommandType commandType, IDataParameter[] parameters)
{
if (!OnCommandExecuting(ref SQL, commandType, parameters))
{
return(null);
}
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, SQL, commandType, parameters);
IDataAdapter ada = GetDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
ada.FillSchema(ds, SchemaType.Mapped);
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
OnCommandExecuteError(cmd, ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ErrorMessage, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString, ex);
}
}
finally
{
OnCommandExected(cmd, 0);
CloseConnection(conn, cmd);
}
return(ds);
}
/// <summary>
/// 执行返回数据架构的查询，注意，不返回任何行
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>数据架构</returns>
public virtual DataSet ExecuteDataSetSchema(string SQL, CommandType commandType, IDataParameter[] parameters)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
IDataAdapter ada = GetDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
ada.FillSchema(ds, SchemaType.Mapped);
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
CommandLog.Instance.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
return(ds);
}
//Tables
protected override DataTable GetDataTableForUpdateTable(CStructureTable structure)
{
IDataAdapter adapter = Connection.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataTable dtAcharger = new DataTable(structure.NomTableInDb);
DataSet ds = new DataSet();
ds.Tables.Add(dtAcharger);
adapter.FillSchema(ds, SchemaType.Mapped);
ds.Tables[structure.NomTableInDb].TableName = structure.NomTable;
return(ds.Tables[structure.NomTable]);
}
//Table
protected override DataTable GetDataTableForUpdateTable(CStructureTable structure)
{
IDataAdapter adapter = Connection.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables["TABLE"];
return(dt);
}
/// /////////////////////////////////////////////////////////////////////////////
/// Modifié le 17 juillet 2003 par Stéphane : ajout du mot clé virtual
public virtual DataTable[] FillSchema(DataSet dsDest, SchemaType schema)
{
string strReq = "select * from " + m_structure.NomTableInDb;
IDataAdapter adapter = m_connexion.GetSimpleReadAdapter(strReq);
foreach (DataTableMapping tm in TableMappings)
{
adapter.TableMappings.Add(tm);
}
adapter.MissingMappingAction = MissingMappingAction;
adapter.MissingSchemaAction = MissingSchemaAction;
DataTable[] tables = adapter.FillSchema(dsDest, schema);
CUtilDataAdapter.DisposeAdapter(adapter);
return(tables);
}
//------------------------------------------------------------------------------------------------
public List <string> GetNomColonnes(string strNomTable)
{
List <string> cols = new List <string>();
IDataAdapter adapter = m_connection.GetTableAdapter(strNomTable);
adapter.TableMappings.Add("Table", strNomTable);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable tbl = ds.Tables[strNomTable];
foreach (DataColumn col in tbl.Columns)
{
cols.Add(col.ColumnName);
}
return(cols);
}
/// <summary>
/// Generates a DataSet with exactly the same structure elements as the database.
/// It maps all tables, columns, primary keys and foreign key constraints of the database
/// to the DataSet.
/// </summary>
/// <remarks>
/// This implementation fetches only the tables and columns.
/// </remarks>
public virtual DataSet GetDatabaseStructure(IDbConnection conn, string ownerName)
{
bool wasOpen = false;
if (conn.State == ConnectionState.Open)
{
wasOpen = true;
}
else
{
conn.Open();
}
DataSet ds = new DataSet();
foreach (string tableName in this.GetTableNames(conn, ownerName))
{
string sql;
if (ownerName != null && ownerName.Trim() != "")
{
sql = "SELECT * FROM " + this.GetQuotedName(ownerName) + "." + this.GetQuotedName(tableName);
}
else
{
sql = "SELECT * FROM " + this.GetQuotedName(tableName);
}
IDbCommand cmd = this.NewSqlCommand(conn);
cmd.CommandText = sql;
IDataAdapter da = this.NewDataAdapter(cmd, null, null, null);
da.FillSchema(ds, SchemaType.Source);
ds.Tables[ds.Tables.Count - 1].TableName = tableName;
}
if (!wasOpen)
{
conn.Close();
}
return(ds);
}
protected void init(string TableName, Db Database)
{
//this.ConnStr = ConnStr;
//BelongDbType = Type;
this.Database = Database;
ds = new DataSet();
try
{
da = Database.GetAdapter("select top 1 * from " + TableName);
//da = DbHelper.GetAdapter(`"select top 1 * from " + TableName, ConnStr, Type);
}
catch (Exception e)
{
Exceptions.LogOnly(e);
throw e;
}
dt = da.FillSchema(ds, SchemaType.Source)[0];
this.TableName = TableName;
this.PrimaryField = dt.Columns[0].ColumnName;
}
/// ///////////////////////////////////////////////////////
/*public override DataTable Read ( CFiltreData filtre, int nStart, int nEnd )
* {
* IDatabaseConnexion connexion = CSc2iDataServer.GetInstance().GetDatabaseConnexion (IdSession, GetType());
* string str
* IDataAdapter adapter = connexion.GetSimpleReadAdapter( strReq, filtre );
* DataSet ds = new DataSet();
* adapter.TableMappings.Add("Table",GetNomTable());
* adapter.FillSchema( ds, SchemaType.Mapped );
* adapter.Fill(ds);
* DataTable tbl = ds.Tables[GetNomTable()];
* foreach ( string strChampBlob in lstBlobs )
* tbl.Columns.Add ( strChampBlob, typeof(CDonneeBinaireInRow)).DefaultValue = DBNull.Value;
*
* nEnd = Math.Min ( nEnd, tbl.Rows.Count );
* if ( nStart >= 0 && nEnd >= 0 )
* {
* DataTable tblNew = tbl.Clone();
*
* for ( int n = nStart; n < nEnd; n++ )
* tblNew.ImportRow ( tbl.Rows[n] );
* tbl = tblNew;
* }
* return tbl;
* }*/
//////////////////////////////////////////////////
public override CDataTableFastSerialize FillSchema()
{
DataTable tbl = (DataTable)m_tableSchema[GetNomTable()];
if (tbl != null)
{
return(tbl.Clone());
}
IDataAdapter adapter = CSc2iDataServer.GetInstance().GetDatabaseConnexion(IdSession, GetType()).GetTableAdapter(GetNomTableInDb());
adapter.TableMappings.Add("Table", GetNomTable());
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable table = ds.Tables[GetNomTable()];
if (table == null)
{
table = ds.Tables[GetNomTableInDb()];
if (table != null)
{
table.TableName = GetNomTable();
}
}
CStructureTable structure = CStructureTable.GetStructure(GetTypeObjets());
foreach (CInfoChampTable info in structure.Champs)
{
if (info.TypeDonnee == typeof(CDonneeBinaireInRow) && table.Columns[info.NomChamp] != null)
{
table.Columns[info.NomChamp].DataType = typeof(CDonneeBinaireInRow);
}
}
tbl = ds.Tables[GetNomTable()];
FaitLesCorrectionsSurLeSchema(tbl);
m_tableSchema[GetNomTable()] = tbl.Clone();
return(tbl);
}
//Table
protected override DataTable GetDataTableForUpdateTable(CStructureTable structure)
{
DataTable schema = GetOleDbSchemaColonnes();
IDataAdapter adapter = Connection.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
DataTable dt = ds.Tables["TABLE"];
DataView view = new DataView(schema);
foreach (DataColumn col in dt.Columns)
{
view.RowFilter = "TABLE_NAME='" + structure.NomTableInDb + "' and " +
"COLUMN_NAME='" + col.ColumnName + "'";
if (view.Count > 0)
{
col.AllowDBNull = (bool)view[0]["IS_NULLABLE"];
}
}
CUtilDataAdapter.DisposeAdapter(adapter);
return(dt);
}
protected void init(string TableName, Db Database)
{
//this.ConnStr = ConnStr;
//BelongDbType = Type;
this.Database = Database;
ds = new DataSet();
try
{
da = Database.GetAdapter("select top 1 * from " + TableName);
//da = DbHelper.GetAdapter(`"select top 1 * from " + TableName, ConnStr, Type);
}
catch (Exception e)
{
Exceptions.LogOnly(e);
throw e;
}
dt = da.FillSchema(ds, SchemaType.Source)[0];
this.TableName = TableName;
this.PrimaryField = dt.Columns[0].ColumnName;
}
/// ////////////////////////////////
public CResultAErreur ExecuteRequete(C2iRequete requete, IElementAVariablesDynamiquesAvecContexteDonnee elementAVariables, bool bStructureOnly)
{
CResultAErreur result = CResultAErreur.True;
Type typeReference = requete.TypeReferencePourConnexion;
if (typeReference != null)
{
///TODO
///Problème VersionObjet
object objServeur = CContexteDonnee.GetTableLoader(CContexteDonnee.GetNomTableForType(typeReference), null, IdSession);
if (objServeur != null)
{
typeReference = objServeur.GetType();
}
else
{
typeReference = null;
}
}
IDatabaseConnexion con;
if (typeReference == null)
{
con = CSc2iDataServer.GetInstance().GetDatabaseConnexion(IdSession, "");
}
else
{
con = CSc2iDataServer.GetInstance().GetDatabaseConnexion(IdSession, typeReference);
}
object[] lstParams = null;
string strRequete = GetTexteRequeteFinaleSql(requete, elementAVariables, ref lstParams, con);
int nOldTimeOut = con.CommandTimeOut;
con.CommandTimeOut = 60 * 10;
IDataAdapter adapter = con.GetAdapterForRequete(strRequete, lstParams);
DataSet ds = new DataSet();
try
{
lock (con)
{
if (bStructureOnly)
{
adapter.FillSchema(ds, SchemaType.Source);
}
else
{
adapter.Fill(ds);
}
}
if (ds.Tables.Count > 0)
{
DataTable table = ds.Tables[0];
if (requete.TableauCroise != null)
{
result = requete.TableauCroise.CreateTableCroisee(table);
if (result)
{
table = (DataTable)result.Data;
}
}
table.TableName = "DONNEES_REQUETE";
result.Data = table;
}
}
catch (Exception e)
{
result.EmpileErreur(new CErreurException(e));
result.EmpileErreur(I.T("Error in the request '@1'|102", strRequete));
}
finally
{
con.CommandTimeOut = nOldTimeOut;
CUtilDataAdapter.DisposeAdapter(adapter);
}
return(result);
}
//------------------------------------------------------------------------------------------------
public static CResultAErreur UpdateTable(CStructureTable structure, IDatabaseConnexion connexion, ArrayList strChampsAutorisesANull)
{
ArrayList champsCrees = new ArrayList();
CResultAErreur result = CResultAErreur.True;
IDataAdapter adapter = connexion.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
DataTable dt = ds.Tables["TABLE"];
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region M.A.J des champs de la table
foreach (CInfoChampTable info in structure.Champs)
{
bool bExists = true;
bool bModifiedType = false;
bool bModifiedNotNull = false;
bool bModifiedLength = false;
foreach (DataColumn colonne in dt.Columns)
{
if (info.NomChamp == colonne.ColumnName)
{
if (info.IsLongString)
{
if (colonne.MaxLength < 10000)
{
bModifiedLength = true;
}
}
else
if (colonne.MaxLength != info.Longueur && info.TypeDonnee == typeof(string))
{
bModifiedLength = true;
}
if (colonne.DataType != info.TypeDonnee)
{
if (info.TypeDonnee != typeof(CDonneeBinaireInRow) && colonne.DataType != typeof(byte[]))
{
bModifiedType = true;
}
}
if (colonne.AllowDBNull != info.NullAuthorized)
{
bModifiedNotNull = true;
}
bExists = true;
break;
}
bExists = false;
}
if (!bExists)
{
string strRequeteUpdate = "ALTER TABLE " + structure.NomTableInDb + " ADD " + info.NomChamp + " ";
string strType = (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(info.TypeDonnee);
if (info.TypeDonnee == typeof(string))
{
if (!info.IsLongString)
{
strType += "(" + info.Longueur.ToString() + ") ";
}
else
{
strType = "NText";
}
}
strRequeteUpdate += strType;
if (!info.NullAuthorized && !strChampsAutorisesANull.Contains(info.NomChamp))
{
strRequeteUpdate += " NOT NULL DEFAULT ''";
}
result = connexion.RunStatement(strRequeteUpdate);
if (!result)
{
result.EmpileErreur(I.T("Error while fields updating of table @1|131", structure.NomTableInDb));
return(result);
}
champsCrees.Add(info.NomChamp);
}
else if (bModifiedType || bModifiedNotNull || bModifiedLength)
{
if (info.IsIndex)
{
SupprimeIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
string strRequeteUpdate = "ALTER TABLE " + structure.NomTableInDb + " ALTER COLUMN " + info.NomChamp + " ";
string strType = (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(info.TypeDonnee);
if (info.TypeDonnee == typeof(string))
{
if (!info.IsLongString)
{
strType += "(" + info.Longueur.ToString() + ") ";
}
else
{
strType = "NText";
}
}
strRequeteUpdate += strType;
if (!info.NullAuthorized && !strChampsAutorisesANull.Contains(info.NomChamp))
{
strRequeteUpdate += " NOT NULL ";
}
result = connexion.RunStatement(strRequeteUpdate);
if (!result)
{
result.EmpileErreur(I.T("Error while fields updating of table @1|131", structure.NomTableInDb));
return(result);
}
}
if (info.IsIndex)
{
result = CreateOrUpdateIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
else
{
result = SupprimeIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
}
// Suppression d'une colonne de la table
foreach (DataColumn colonne in dt.Columns)
{
bool bExists = false;
foreach (CInfoChampTable info in structure.Champs)
{
if (info.NomChamp == colonne.ColumnName)
{
bExists = true;
break;
}
}
if (!bExists)
{
CResultAErreur tempResult = CAccessTableCreatorOld.SupprimeDependances(structure.NomTableInDb, colonne.ColumnName, connexion);
if (!tempResult)
{
result.Erreur.EmpileErreurs(tempResult.Erreur);
result.SetFalse();
return(result);
}
tempResult = CAccessTableCreatorOld.SupprimeForeignKeys(structure.NomTableInDb, colonne.ColumnName, connexion);
if (!tempResult)
{
result.Erreur.EmpileErreurs(tempResult.Erreur);
result.SetFalse();
return(result);
}
string strReq = "ALTER TABLE " + structure.NomTableInDb + " DROP COLUMN " + colonne.ColumnName;
result = connexion.RunStatement(strReq);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting column @1 of table @2|132", colonne.ColumnName, structure.NomTableInDb));
return(result);
}
}
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region M.A.J des contraintes de la table
foreach (CInfoRelation rel in structure.RelationsParentes)
{
foreach (string strChampCree in champsCrees)
{
foreach (string strChamp in rel.ChampsFille)
{
if (strChampCree == strChamp)
{
string strRequeteRelation = GetRequeteRelation(rel);
result = connexion.RunStatement(strRequeteRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while creating foreign key of @1 (@2)|127", structure.NomTableInDb, strRequeteRelation));
return(result);
}
break;
}
}
}
if (result)
{
string strNomTableFilleInDb = CContexteDonnee.GetNomTableInDbForNomTable(rel.TableFille);
if (rel.Indexed)
{
result = CreateOrUpdateIndex(strNomTableFilleInDb, connexion, rel.ChampsFille);
}
else
{
result = SupprimeIndex(strNomTableFilleInDb, connexion, rel.ChampsFille);
}
}
if (!result)
{
return(result);
}
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
return(result);
}
public TableNode(DataTable dt, DatabaseNode parent, string ownerName, IDbConnection conn, IProvider provider) : base(dt.TableName, parent)
{
string tableName = dt.TableName;
SetImageIndex(15); // unmapped
this.myObject = new Table(tableName, string.Empty, (string)dt.ExtendedProperties["summary"]);
this.Table.OnIsMappedChanged += new EventHandler(OnIsMappedChanged);
#if DontUseDataSets
DataTable dt = null;
if (!parent.Database.IsXmlSchema)
{
string sql;
if (ownerName != null && ownerName.Trim() != "")
{
sql = "SELECT * FROM " + provider.GetQuotedName(ownerName) + "." + provider.GetQuotedName(tableName);
}
else
{
sql = "SELECT * FROM " + provider.GetQuotedName(tableName);
}
DataSet ds = new DataSet();
IDbCommand cmd = provider.NewSqlCommand(conn);
cmd.CommandText = sql;
IDataAdapter da = provider.NewDataAdapter(cmd, null, null, null);
da.FillSchema(ds, SchemaType.Source);
dt = ds.Tables[0];
}
else
{
dt = parent.Database.DataSet.Tables[tableName];
#endif
if (parent.Database.IsXmlSchema && dt.PrimaryKey.Length == 0)
{
string pkColumnName = GeneratePkName(dt);
DataColumn pkColumn = dt.Columns.Add(pkColumnName, typeof(int));
dt.PrimaryKey = new DataColumn[] { pkColumn };
}
foreach (DataColumn column in dt.Columns)
{
bool isPrimary = false;
bool isAutoIncremented = false;
foreach (DataColumn pkColumn in dt.PrimaryKey)
{
if (pkColumn == column)
{
isPrimary = true;
isAutoIncremented = pkColumn.AutoIncrement;
}
}
ColumnNode cn = new ColumnNode(column.ColumnName, isPrimary, isAutoIncremented, column.DataType.ToString(), (string)column.ExtendedProperties["summary"], this);
if (parent.Database.IsXmlSchema && column.ExtendedProperties.Contains("schemaType"))
{
cn.UserData.Add("schemaType", "Element");
}
cn.IsPrimaryChanged += new EventHandler(OnIsPrimaryChanged);
this.Nodes.Add(cn);
}
}
private void buttonReadDB_Click(object sender, EventArgs e)
{
if (connection != null)
{
try
{
dataSet = new DataSet();
IDbCommand command = connection.CreateCommand();
command.CommandText = string.Format("SELECT ID, IDENTIFICATION, KEYS, EN as Description FROM SYS_LANGUAGE WHERE IDENTIFICATION = '{0}'", ID);
adapter = CreateDbDataAdapter(command);
adapter.FillSchema(dataSet, SchemaType.Mapped);
adapter.Fill(dataSet);
dataGridView.DataSource = dataSet;
dataGridView.DataMember = dataSet.Tables[0].TableName;
dataGridView.Columns[0].Visible = false;
dataGridView.Columns[1].Visible = false;
dataSet.Tables[0].TableNewRow += delegate(object table, DataTableNewRowEventArgs dre)
{
dre.Row["IDENTIFICATION"] = ID;
};
buttonWriteDB.Enabled = true;
buttonRefresh.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}