|
public FillSchema ( |
||
| dataTable | ||
| schemaType | SchemaType | |
| return | ||
/// <summary>
/// ���ر�
/// </summary>
/// <param name="da"></param>
/// <param name="blFillSchema">�Ƿ���ؿ��</param>
/// <returns></returns>
public DataTable GetDataTable(DbDataAdapter da, bool blFillSchema)
{
DataTable dt = null;
try
{
dt = new DataTable();
da.Fill(dt);
if (blFillSchema)
{
da.FillSchema(dt, SchemaType.Mapped);
}
}
catch (Exception)
{
throw;
}
return dt;
}
public DataTable GetTableSchema(DbDataAdapter da)
{
DataTable dt = null;
try
{
dt = new DataTable();
da.FillSchema(dt, SchemaType.Mapped);
}
catch (Exception)
{
throw;
}
return dt;
}
public DataSet GetDataSeteSchema(DbDataAdapter da)
{
DataSet ds = null;
try
{
ds = new DataSet();
da.FillSchema(ds, SchemaType.Mapped);
}
catch (Exception)
{
throw;
}
return ds;
}
public DataSet GetDataSet(DbDataAdapter da, bool isFillSchema)
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
if (isFillSchema)
{
da.FillSchema(ds, SchemaType.Mapped);
}
}
catch (Exception)
{
throw;
}
return ds;
}
/// <summary>
/// Build a new set of TableMappings from SELECT's column.
/// Make a copy of the DataAdapter, Command, and Connection
/// objects to avoid accidentally overriding fields in them.
/// </summary>
/// <param name="dbDataAdapter">A writeable DataAdapter.</param>
/// <returns>2-column DataTable to hold the names.</returns>
internal static DataTable BuildMapFromDatabase(DbDataAdapter dbAdapter)
{
DataTable dt = new DataTable("mapping");
dt.Columns.Add( SOURCE_COLUMNS );
dt.Columns.Add( DATASET_COLUMNS );
IDbDataAdapter dataAdapter= (IDbDataAdapter)dbAdapter;
IDbCommand dbCommand = dataAdapter.SelectCommand;
// safety check for a missing or incomplete Command
if (dbCommand == null ||
dbCommand.CommandText == null ||
dbCommand.CommandText.Trim().Length == 0)
{
System.Windows.Forms.MessageBox.Show(
"Data Adapter's SELECT command is null or an empty string; " +
"table mappings are not available.",
"Table Mappings");
return dt;
}
// safety check for a missing Connection
if (dbCommand.Connection == null)
{
System.Windows.Forms.MessageBox.Show(
"Data Adapter's SELECT command's Connection is null; " +
"table mappings are not available.",
"Table Mappings");
return dt;
}
// make a schema table that the DbAdapter can fill in
DataTable dtSchema = new DataTable();
Cursor cursor = Cursor.Current; // save cursor, probably Arrow
Cursor.Current = Cursors.WaitCursor; // hourglass cursor
try
{
dbAdapter.FillSchema(dtSchema, SchemaType.Source);
// copy the schema columns to the 2-column DataTable
// that will be the DataSource for the form's datagrid.
for (int i=0; i<dtSchema.Columns.Count; i++)
{
DataColumn dataColumn = dtSchema.Columns[i];
System.Data.DataRow datarow = dt.NewRow();
datarow[ TableMappings.SOURCE_COLUMNS ] =
dataColumn.ColumnName;
datarow[ TableMappings.DATASET_COLUMNS ] =
dataColumn.ColumnName;
dt.Rows.Add( datarow );
}
}
finally { Cursor.Current = cursor; } // resored Arrow cursor
return dt; // return with all went well
}
public virtual DataTable GetTableFromDb(string tablename, bool with_primary_key, bool with_max_string_length, bool with_default_values, bool CaseSensivity)
{
_dt = new DataTable(tablename);
string sql = "";
if (!CaseSensivity)
{
sql = "select * from {0}";
if (!string.IsNullOrEmpty(_owner))
{
sql = "select * from {0}.{1}";
sql = string.Format(sql, _owner, tablename);
}
else
sql = string.Format(sql, tablename);
}
else
{
sql = "select * from {0}{1}{2}";
if (!string.IsNullOrEmpty(_owner))
{
sql = "select * from {0}{1}{2}.{0}{3}{2}";
sql = string.Format(sql, _open_bracket, _owner, _close_bracket, tablename);
}
else
sql = string.Format(sql, _open_bracket, tablename, _close_bracket);
}
_DA = CreateDataAdapter(sql);
_DA.FillLoadOption = LoadOption.Upsert;//--Важднейшая опция, после которой можно вставить эту таблицу в другую базу
_DA.FillSchema(_dt, SchemaType.Source);
PrepareTableSchemeBeforeFill(_dt);
_DA.Fill(_dt);
#warning перенести код ниже в PrepareTableSchemeBeforeFill
//--описание колонок
foreach (DataColumn col in _dt.Columns)
{
var col_info = GetDbColumnInformation(tablename, col.ColumnName, CaseSensivity);
if (col_info.AdditionalInfo!=null && col_info.AdditionalInfo.ToString() != "default")
col.AllowDBNull = col_info.IsNullable;
if (with_max_string_length)
{
if (col.DataType == typeof(string))
if (col_info.MaxLength > 0 && col_info.MaxLength > col.MaxLength)
col.MaxLength = col_info.MaxLength;
}
if (with_default_values)
{
//--надо будет придумать какой-то флаг, чтобы сообщалось что не все дефолтные значения распарсились
if (col_info.DefaultValue != null && col_info.DefaultValue != DBNull.Value)
{
try
{
col.DefaultValue = col_info.DefaultValue;
}
catch
{
col.DefaultValue = DBNull.Value;
}
}
}
}
if (with_primary_key)
_dt = GetTablePrimaryKey(_dt, CaseSensivity);
return _dt;
}
public void FillSchemaTest()
{
string query = "select * from invalid_table";
// Test if connection is closed if exception occurs during fill schema
m_adapter = GetDataAdapter(query);
DataSet data = new DataSet("test");
try {
m_adapter.FillSchema(data, SchemaType.Source);
Assert.Fail("#A1 Expected to fail due to invalid table name");
} catch (DbException ex) {
Assert.IsNotNull(ex.Message, "#A2");
Assert.IsTrue(ex.Message.Contains("42X05"), "#A3: " + ex.Message);
}
// Test Primary Key is set (since primary key column returned)
query = "select id, type_int from numeric_family where id=1";
m_adapter = GetDataAdapter(query);
data = new DataSet("test1");
m_adapter.FillSchema(data, SchemaType.Source);
Assert.AreEqual(1, data.Tables[0].PrimaryKey.Length,
"#B1 Primary Key property must be set");
// Test Primary Key is not set (since primary key column is returned)
query = "select type_varchar, type_int from numeric_family where id=1";
m_adapter = GetDataAdapter(query);
data = new DataSet("test2");
m_adapter.FillSchema(data, SchemaType.Source);
Assert.AreEqual(0, data.Tables[0].PrimaryKey.Length,
"#B2 Primary Key property should not be set");
// Test multiple tables are created for a batch query
query = "create procedure multiplePKResultSets()" +
" language java parameter style java external name" +
" 'tests.TestProcedures.multiplePKResultSets' dynamic result sets 2";
m_cmd = m_conn.CreateCommand();
m_cmd.CommandText = query;
m_cmd.ExecuteNonQuery();
data = new DataSet("test3");
query = "call multiplePKResultSets()";
m_adapter = GetDataAdapter(query);
m_adapter.FillSchema(data, SchemaType.Source);
Assert.AreEqual(2, data.Tables.Count,
"#B3 A table shud be created for each Result Set");
Assert.AreEqual(2, data.Tables[0].Columns.Count,
"#B4 should have 2 columns");
Assert.AreEqual(3, data.Tables[1].Columns.Count,
"#B5 Should have 3 columns");
Assert.AreEqual(0, data.Tables[0].PrimaryKey[0].Ordinal,
"#B6 Expected PK constraint at first column");
Assert.AreEqual(1, data.Tables[1].PrimaryKey[0].Ordinal,
"#B7 Expected PK constraint at second column");
// Test if table names and column names are filled correctly
query = "create procedure multipleNoColumnResults()" +
" language java parameter style java external name" +
" 'tests.TestProcedures.multipleNoColumnResults' dynamic result sets 2";
m_cmd.CommandText = query;
m_cmd.ExecuteNonQuery();
query = "call multipleNoColumnResults ()";
m_adapter = GetDataAdapter(query);
data = new DataSet("test4");
m_adapter.FillSchema(data, SchemaType.Source);
Assert.AreEqual("Table", data.Tables[0].TableName);
Assert.AreEqual("Table1", data.Tables[1].TableName);
Assert.AreEqual("1", data.Tables[0].Columns[0].ColumnName,
"#C1 Unnamed col shud be named as 'ColumnN'");
Assert.AreEqual("2", data.Tables[0].Columns[1].ColumnName,
"#C2 Unnamed col shud be named as 'ColumnN'");
Assert.AreEqual("1", data.Tables[1].Columns[0].ColumnName,
"#C3 Unnamed col shud be named as 'ColumnN'");
Assert.AreEqual("2", data.Tables[1].Columns[1].ColumnName,
"#C4 Unnamed col shud be named as 'ColumnN'");
}
