/// <summary>
/// Retrieves a list of objects.
/// </summary>
/// <param name="transaction">The transaction to execute the command in.</param>
/// <param name="result">When this method returns, a list of entity objects created
/// by the SelectCommand. This parameter is passed uninitialized..</param>
/// <returns></returns>
public virtual void Get(Transaction transaction, out List <T> result)
{
result = new List <T>();
if (SelectCommand == null)
{
DataTable table = CreateDataTable();
InitializeDataAdapter(transaction);
_dataAdapter.Fill(table);
foreach (DataRow row in table.Rows)
{
result.Add(CreateEntityFromDataRow(row));
}
}
else
{
DataSet set;
if (transaction == null)
{
set = _query.Execute(SelectCommand);
}
else
{
set = _query.Execute(transaction, SelectCommand);
}
foreach (DataRow row in set.Tables[0].Rows)
{
result.Add(CreateEntityFromDataRow(row));
}
}
}
/// <summary>
/// Gets one work item based on job id and email address.
/// </summary>
/// <param name="jobId">The job id.</param>
/// <param name="emailAddress">The email address.</param>
/// <returns>A DataRow with the work item if successfull, null if not found</returns>
public DataRow WorkItemGet(int jobId, string emailAddress)
{
DataTable workItem = new DataTable();
Database.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterWorkItemGet");
cmd.Parameters.Add(base.CreateParameter("jobid", jobId));
cmd.Parameters.Add(base.CreateParameter("emailaddress", emailAddress));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(workItem);
if (workItem.Rows.Count > 1)
{
throw new ApplicationException("More than one (" + workItem.Rows.Count.ToString() +
") work item rows returned for job " + jobId.ToString() + " and email '" + emailAddress +
"'). This is unexpected and might imply data corruption.");
}
if (workItem.Rows.Count == 1)
{
return(workItem.Rows[0]);
}
return(null);
});
return(null);
}
/// <summary>
/// Gets one email address item for a recipient list.
/// </summary>
/// <param name="recipientListId">The recipient list id.</param>
/// <param name="emailAddress">The email address.</param>
/// <returns>One row of data with email address information</returns>
public DataRow RecipientListGetItem(int recipientListId, string emailAddress)
{
DataTable emailAddresses = new DataTable();
return(Executor.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterRecipientListGetItem");
cmd.Parameters.Add(base.CreateParameter("recipientlistid", recipientListId));
cmd.Parameters.Add(base.CreateParameter("emailaddress", emailAddress));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(emailAddresses);
if (emailAddresses.Rows.Count > 1)
{
throw new ApplicationException("More than one (" + emailAddresses.Rows.Count.ToString() +
") email address item rows returned for recipient list " + recipientListId.ToString() + " and email '" + emailAddress +
"'). This is unexpected and might imply data corruption.");
}
if (emailAddresses.Rows.Count == 1)
{
return emailAddresses.Rows[0];
}
return null;
}));
}
public List <CountyList> FillCountryDropDownList()
{
ShomaRMEntities db = new ShomaRMEntities();
List <CountyList> lstData = new List <CountyList>();
System.Data.DataTable dtTable = new System.Data.DataTable();
try
{
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetCountryList";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbDataAdapter da = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (System.Data.DataRow dr in dtTable.Rows)
{
CountyList model = new CountyList();
model.ID = Convert.ToInt64(dr["ID"].ToString());
model.CountryName = dr["CountryName"].ToString();
lstData.Add(model);
}
db.Dispose();
return(lstData.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Adapter = DbProvider.GetDataAdapter("SELECT subject1, subject2, subject3, subject4, subject5 FROM Subjects WHERE (Faculty_ID='" + Faculty_ID.ToString()+ "'AND Spec_ID='" + Spec_ID.ToString() + "'AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
Table = new DataTable();
Adapter.Fill(Table);
if (Table.Rows.Count == 0)
{
throw new Exception("Invalid SesionNo");
}
for (int i = 0; i < 5; i++)
dataGridView1.Columns[i].HeaderText = Table.Rows[0][i].ToString().Trim();
Adapter = DbProvider.GetDataAdapter("SELECT Mark1, Mark2, Mark3, Mark4, Mark5 FROM Marks WHERE (Student_ID='" + Stud_ID.ToString() + "' AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
Table = new DataTable();
Adapter.Fill(Table);
if (Table.Rows.Count == 0)
{
for (int i = 0; i < 5; i++)
dataGridView1[i, 0].Value = "";
MessageBox.Show("0 rows");
return;
}
for (int i = 0; i < 5; i++)
dataGridView1[i, 0].Value = Table.Rows[0][i].ToString().Trim();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string[] df = comboBox1.Text.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
string fid = df[df.Count() - 1].Trim();
df = comboBox2.Text.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
string sid = df[df.Count() - 1].Trim();
Adapter = DbProvider.GetDataAdapter("SELECT subject1, subject2, subject3, subject4, subject5 FROM Subjects WHERE (Faculty_ID='" + fid + "'AND Spec_ID='" + sid + "'AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
Table = new DataTable();
Adapter.Fill(Table);
if (Table.Rows.Count == 0)
{
for (int i = 0; i < 5; i++)
dataGridView1[i, 0].Value = "";
MessageBox.Show("0 rows");
return;
}
for (int i = 0; i < 5; i++)
dataGridView1[i, 0].Value = Table.Rows[0][i].ToString().Trim();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public TableInform( string tableName )
{
try
{
dataAdapter = TableInform.dbProviderFactory.CreateDataAdapter();
command = TableInform.dbProviderFactory.CreateCommand();
command.Connection = TableInform.Connection;
DbCommandBuilder commandBuilder = TableInform.dbProviderFactory.CreateCommandBuilder();
commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
commandBuilder.DataAdapter = dataAdapter;
dataTable = new DataTable();
dataTable.TableName = tableName;
command.CommandText = "Select * from " + Table.TableName;
dataAdapter.SelectCommand = command;
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dataAdapter.Fill( dataTable );
}
catch ( Exception ex )
{ }
}
protected void CreateFile(string database, string tableName, IDbCommand cmd, DbDataAdapter da)
{
cmd.CommandText = string.Format(TableInfoSQL, database.ToLower(), tableName.ToLower());
DataTable dt = new DataTable();
da.Fill(dt);
cmd.CommandText = string.Format(TableCommentSQL, database.ToLower(), tableName.ToLower());
string tableComments = (cmd.ExecuteScalar() ?? string.Empty).ToString();
CreateFile(tableName.ToUpper(), tableComments, dt);
}
private void frmanimals_Load(object sender, EventArgs e)
{
DbCommand idbCommand = Connection.provideConnection().CreateCommand();
idbCommand.CommandText = "select * from `animals`";
ad = returnAdapter();
DbCommandBuilder builder = returnBuilder();
builder.DataAdapter = ad;
ad.SelectCommand = idbCommand;
ad.Fill(this.newDataSet.animals);
ad.DeleteCommand = builder.GetDeleteCommand();
ad.UpdateCommand = builder.GetUpdateCommand();
ad.InsertCommand = builder.GetInsertCommand();
}
public DataSet GetData(DbDataAdapter adapter, string CommandText, CommandType cmdType, DbParameter[] parameters = null)
{
DataSet ds = new DataSet();
_cmd.CommandText = CommandText;
_cmd.CommandType = cmdType;
if (parameters != null && parameters.Count() > 0)
{
_cmd.Parameters.AddRange(parameters);
}
adapter.SelectCommand = _cmd;
adapter.Fill(ds);
return ds;
}
/// <summary>
/// Gets one recipient list from the database
/// </summary>
/// <returns></returns>
public DataTable RecipientListGetById(int recipientListId)
{
DataTable recipientList = new DataTable();
Executor.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterRecipientListGet");
cmd.Parameters.Add(base.CreateParameter("recipientlistid", recipientListId));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(recipientList);
});
return(recipientList);
}
/// <summary>
/// Gets all recipient lists from the database for sepcified user email
/// </summary>
/// <returns>All recipient lists</returns>
public DataTable RecipientListGetAllByEmail(string email)
{
DataTable recipientLists = new DataTable();
Executor.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterRecipientListGetAllByEmail");
cmd.Parameters.Add(base.CreateParameter("email", email));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(recipientLists);
});
return(recipientLists);
}
/// <summary>
/// Searches for work items doing a LIKE query on the
/// email address.
/// </summary>
/// <param name="jobId">The job id.</param>
/// <param name="searchFor">The email to search for.</param>
/// <returns>A DataTable with the results</returns>
public DataTable WorkItemSearch(int jobId, string searchFor)
{
DataTable workItems = new DataTable();
Database.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterWorkItemSearch");
cmd.Parameters.Add(base.CreateParameter("jobid", jobId));
cmd.Parameters.Add(base.CreateParameter("searchfor", searchFor));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(workItems);
});
return(workItems);
}
/// <summary>
/// Gets all work items for a job.
/// </summary>
/// <param name="jobId">The job id</param>
/// <returns></returns>
public DataTable WorkItemGetAllForJob(int jobId)
{
DataTable workItems = new DataTable();
Database.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterWorkItemGetAllForJob");
cmd.Parameters.Add(base.CreateParameter("jobid", jobId));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(workItems);
});
return(workItems);
}
/// <summary>
/// Gets a batch of work items ready for processing. All work items
/// will be updated with the status given
/// </summary>
/// <param name="jobId">The job id</param>
/// <param name="status">The status all the work items in the batch should be set to</param>
/// <returns></returns>
public DataTable WorkItemGetBatchForProcessing(int jobId, JobWorkStatus getStatus, JobWorkStatus setStatus, int count)
{
DataTable workItems = new DataTable();
Database.Execute(() =>
{
DbCommand cmd = base.CreateCommand("NewsletterWorkItemGetBatchForProcessing");
cmd.Parameters.Add(base.CreateParameter("jobid", jobId));
cmd.Parameters.Add(base.CreateParameter("selectStatus", getStatus));
cmd.Parameters.Add(base.CreateParameter("updatedStatus", setStatus));
cmd.Parameters.Add(base.CreateParameter("count", count));
System.Data.Common.DbDataAdapter adapter = base.CreateDataAdapter(cmd);
adapter.Fill(workItems);
});
return(workItems);
}
public SubjectsForm()
{
InitializeComponent();
dataGridView1.Rows.Add(1);
Adapter = DbProvider.GetDataAdapter("SELECT Name, Object_ID FROM UObjects WHERE Class_ID='20'", ORM.DB_University.connection);
Table = new DataTable();
Adapter.Fill(Table);
Fac = (from DataRow c in Table.AsEnumerable()
select (c[0].ToString().Trim() + " " + c[1].ToString().Trim())).ToList();
comboBox1.DataSource = Fac;
Adapter = DbProvider.GetDataAdapter("SELECT Spec, Spec_ID FROM Spec", ORM.DB_University.connection);
Table = new DataTable();
Adapter.Fill(Table);
Spc = (from DataRow c in Table.AsEnumerable()
select (c[0].ToString().Trim() + " " + c[1].ToString().Trim())).ToList();
comboBox2.DataSource = Spc;
}
public static DataTable ReadTable(string sproc, List <ParamStruct> paramList)
{
DataTable dt = new DataTable();
SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider);
SDC.DbCommand comm = factory.CreateCommand();
comm = BuildCommand(sproc, paramList);
SDC.DbDataAdapter da = factory.CreateDataAdapter();
SDC.DbConnection conn = Connection(factory);
comm.Connection = conn;
da.SelectCommand = comm;
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using (conn)
{
conn.Open();
da.Fill(dt);
return(dt);
}
}
public DataSet GetDataSet(string sql)
{
// Description: fill DataSet via OleDbDataAdapter, connect Firebird, Interbase
using (var ds = new DataSet())
{
try
{
OpenFbData();
//Using not work with transaction
Com = GetCommandDb(sql);
Adapter = GetAdapterDb(Com);
Adapter.Fill(ds);
}
finally
{
ReleaseResource();
}
return ds;
}
}
public virtual DataTable GetSchema(string collectionName, string[] restrictionValues)
{
if (collectionName == null)
{
//LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
throw new ArgumentException();
}
String cName = null;
DataTable schemaTable = MetaDataCollections.Instance;
int length = restrictionValues == null ? 0 : restrictionValues.Length;
foreach (DataRow row in schemaTable.Rows)
{
if (String.Compare((string)row ["CollectionName"], collectionName, true) == 0)
{
if (length > (int)row ["NumberOfRestrictions"])
{
throw new ArgumentException("More restrictions were provided " +
"than the requested schema ('" +
row ["CollectionName"].ToString() + "') supports");
}
cName = row ["CollectionName"].ToString();
}
}
if (cName == null)
{
throw new ArgumentException("The requested collection ('" + collectionName + "') is not defined.");
}
DbCommand command = null;
DataTable dataTable = new DataTable();
switch (cName)
{
case "Databases":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select name as database_name, dbid, crdate as create_date " +
"from master.sys.sysdatabases where (name = @Name or (@Name " +
"is null))";
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "ForeignKeys":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
"TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
"IS_DEFERRABLE, INITIALLY_DEFERRED from " +
"INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
" = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
"@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
"@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
" and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
" CONSTRAINT_SCHEMA, CONSTRAINT_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "Indexes":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select distinct db_name() as constraint_catalog, " +
"constraint_schema = user_name (o.uid), " +
"constraint_name = x.name, table_catalog = db_name (), " +
"table_schema = user_name (o.uid), table_name = o.name, " +
"index_name = x.name from sysobjects o, sysindexes x, " +
"sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
"o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
"and (db_name() = @Catalog or (@Catalog is null)) and " +
"(user_name() = @Owner or (@Owner is null)) and (o.name = " +
"@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
"order by table_name, index_name";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "IndexColumns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select distinct db_name() as constraint_catalog, " +
"constraint_schema = user_name (o.uid), constraint_name = x.name, " +
"table_catalog = db_name (), table_schema = user_name (o.uid), " +
"table_name = o.name, column_name = c.name, " +
"ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
"index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
"sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
"and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
"and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
"and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
"= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
" null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
"and (c.name = @Column or (@Column is null)) order by table_name, " +
"index_name";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 8);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 13);
AddParameter(command, "@ConstraintName", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "Procedures":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
"ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
"CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
"(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
"(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
" = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
"is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
AddParameter(command, "@Type", DbType.StringFixedLength, 4000);
break;
case "ProcedureParameters":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
"ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
"PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
"CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
"COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
"CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
"NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
"INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
"(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
"(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
"@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
"@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
" SPECIFIC_NAME, PARAMETER_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
AddParameter(command, "@Parameter", DbType.StringFixedLength, 4000);
break;
case "Tables":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
"from INFORMATION_SCHEMA.TABLES where" +
" (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
"(TABLE_SCHEMA = @owner or (@owner is null))and " +
"(TABLE_NAME = @name or (@name is null)) and " +
"(TABLE_TYPE = @table_type or (@table_type is null))";
AddParameter(command, "@catalog", DbType.StringFixedLength, 8);
AddParameter(command, "@owner", DbType.StringFixedLength, 3);
AddParameter(command, "@name", DbType.StringFixedLength, 11);
AddParameter(command, "@table_type", DbType.StringFixedLength, 10);
break;
case "Columns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
"ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
"CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
"NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
"DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
"CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
" where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
"TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
" or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "Users":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select uid, name as user_name, createdate, updatedate from sysusers" +
" where (name = @Name or (@Name is null))";
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "Views":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
"IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
" = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
"(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
" order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
break;
case "ViewColumns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
"TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
"INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
"@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
" is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
"(COLUMN_NAME = @Column or (@Column is null)) order by " +
"VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "UserDefinedTypes":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select assemblies.name as assembly_name, types.assembly_class " +
"as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
"as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
"as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
"as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
"as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
"as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
"as public_key, is_fixed_length, max_length, Create_Date, " +
"Permission_set_desc from sys.assemblies as assemblies join " +
"sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
" where (assemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
"(types.assembly_class = @UDTName or (@UDTName is null))";
AddParameter(command, "@AssemblyName", DbType.StringFixedLength, 4000);
AddParameter(command, "@UDTName", DbType.StringFixedLength, 4000);
break;
case "MetaDataCollections":
return(MetaDataCollections.Instance);
case "DataSourceInformation":
throw new NotImplementedException();
case "DataTypes":
return(DataTypes.Instance);
case "ReservedWords":
return(ReservedWords.Instance);
case "Restrictions":
return(Restrictions.Instance);
}
for (int i = 0; i < length; i++)
{
command.Parameters [i].Value = restrictionValues [i];
}
DbDataAdapter dataAdapter = DbProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
return(dataTable);
}
// �������ʱ��������ݼ�
private void SearchFriendForm_Load(object sender, EventArgs e)
{
// ʵ�������ݼ������������������
string sql = "SELECT Id, NickName, Age, Sex FROM Users";
dataAdapter = DBHelper.GetDataAdapter(sql, DBHelper.GetConnection());
dataSet = new DataSet("MyQQ");
dataAdapter.Fill(dataSet, "Users");
// ָ��DataGridView������Դ
dgvBasicResult.DataSource = dataSet.Tables[0];
dgvAdvancedResult.DataSource = dataSet.Tables[0];
}
private void LoadData(string sqlText)
{
try
{
if (string.IsNullOrEmpty(sqlText))
return;
dAdapter = BuildDbDataAdapter(sqlText);
dTable = new DataTable();
dAdapter.Fill(dTable);
this.bindingSource1.DataSource = dTable;
if (Properties.Settings.Default.MaxColumnWidth > 0)
{
this.dataGridView1.AutoResizeColumns();
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (dataGridView1.Columns[i].Width > Properties.Settings.Default.MaxColumnWidth)
{
dataGridView1.Columns[i].Width = Properties.Settings.Default.MaxColumnWidth;
}
}
}
else
{
this.dataGridView1.AutoResizeColumns();
}
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, DatabaseInfo.DatabaseType, false);
}
}
/// <summary>
/// Run this Database query.
/// if FReportingQueryCancelFlag is set, this returns immediately with an empty table.
/// The query can be cancelled WHILE IT IS RUNNING. In this case the returned table may be partially filled.
/// </summary>
/// <returns>DataTable. May be empty (with no fields even defined) if cancel happens or has happened.</returns>
public DataTable RunQuery(String Query, String TableName, TDBTransaction Trans)
{
DataTable resultTable = new DataTable(TableName);
if (!FCancelFlag)
{
try
{
FDataAdapter = DBAccess.GDBAccessObj.SelectDA(Query, Trans, null);
FDataAdapter.Fill(resultTable);
}
catch (Exception ex)
{
TLogging.Log("ReportingQueryWithCancelOption: Query Raised exception:" + ex.Message);
/*
* WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE
* PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.)
*
* --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <--
* --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <--
* --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE. <--
*
*/
}
}
return resultTable;
}
/// <summary>
/// Obtiene el conjunto de datos de una tabla o vista
/// </summary>
/// <param name="nombre">Nombre de la tabla</param>
/// <returns>Conjunto de datos obtenido</returns>
public DataTable obtenerTabla(string nombre)
{
m_datatable = new DataTable();
m_comando.CommandText = nombre;
m_comando.CommandType = CommandType.TableDirect;
m_adaptador = base.ObtenerFabrica().CreateDataAdapter();
m_adaptador.SelectCommand = m_comando;
base.Abrir();
try
{
m_adaptador.Fill(m_datatable);
}
catch (Exception ex)
{
throw new Exception("Error al consultar la tabla. " + ex.Message);
}
finally
{
base.Cerrar();
}
return m_datatable;
}
/**
* Creates a DataSet object for the specified adapter.
*
* @param adapter System.Data.DBDataAdapter object.
* @param dset System.Data.DataSet object to fill.
* @return A reference to the System.Data.DataSet object.
*
* @author Martin Turon
* @version 2004/4/13 mturon Initial version
*/
public DataSet FillDataSet(DbDataAdapter dataAdapter, DataSet ds)
{
if (null == ds)
{
ds = new DataSet();
}
try
{
dataAdapter.Fill(ds, m_Table);
}
catch (Exception ex)
{
ErrorLog("\nError: DataSet fill\n" + ex.Message);
ds = null;
}
return ds;
}
/// <summary>
/// 返回DataSet--重载方法三
/// </summary>
/// <param name="sql">SQL语句或者存储过程</param>
/// <param name="startindex">开始行</param>
/// <param name="num">总行数</param>
/// <param name="dataname">虚拟表名</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string sql, int startindex, int num, string dataname)
{
DataSet ds = new DataSet();
IsStoredProcedure(sql);
dbDataAdapter = dbProviderFactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = dbCommand;
dbDataAdapter.Fill(ds, startindex, num, dataname);
dbDataAdapter.Dispose();
m_querycount++;
return ds;
}
public static DataSet FillDataSet(DbDataAdapter dbDataAdapter)
{
DataSet dataSet = new DataSet();
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
dbDataAdapter.Fill(dataSet);
dbConnection.Close();
return dataSet;
}
/// <summary>
/// 执行查询 返回DataTable
/// </summary>
/// <param name="strCmdText"></param>
/// <param name="arg0"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public DataTable ExecuteQuery(string strCmdText, DbParameter arg0, CommandType commandType)
{
DataTable dt = new DataTable();
try
{
Database db = GetDataBase();
_cmd = db.DbProviderFactory.CreateCommand();
_cmd.Connection = db.CreateConnection();
_cmd.CommandText = strCmdText;
_cmd.CommandTimeout = TimeOut;
_cmd.CommandType = commandType;
_cmd.Parameters.Add(arg0);
_da = db.DbProviderFactory.CreateDataAdapter();
_da.SelectCommand = _cmd;
_da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Dispose();
}
return dt;
}
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 FillList(DbDataAdapter dbda)
{
using( DataTable dtbl = new DataTable(tbl_name) )
{
dbda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; ++i)
{
ReadData(dtbl.Rows[i].ItemArray);
}
}
}
virtual public DataSet queryDatabase(string qstring)
{
try
{
int rows;
DataSet ds = new DataSet();
// Create the DbCommand.
DbCommand SelectTableCommand = factory.CreateCommand();
SelectTableCommand.CommandText = qstring;
SelectTableCommand.Connection = connection;
adapter = factory.CreateDataAdapter();
adapter.SelectCommand = SelectTableCommand;
rows = adapter.Fill(ds);
return ds;
}
catch (Exception exp)
{
throw;
}
}
public virtual DataSet GetDataSet(string sql, List<IDataParameter> parms,
CommandType commandType = CommandType.Text)
{
// Description: fill DataSet via OleDbDataAdapter, connect Firebird, Interbase
using (var ds = new DataSet())
{
try
{
OpenFbData();
Com = GetCommandDb(sql, commandType);
SetParameter(parms, ref Com);
Adapter = GetAdapterDb(Com);
Adapter.Fill(ds);
}
finally
{
ReleaseResource();
}
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>
/// Ejecuta un procedimiento almacenado de consulta
/// </summary>
/// <param name="nombrePA">Nombre del procedimiento</param>
/// <returns>Conjunto de datos devuelto por el procedimiento</returns>
public DataSet ejecutarProcedimiento(string nombrePA)
{
m_comando.CommandText = nombrePA;
m_comando.CommandType = CommandType.StoredProcedure;
m_dataset = new DataSet();
m_adaptador = ObtenerFabrica().CreateDataAdapter();
if (m_adaptador != null) m_adaptador.SelectCommand = m_comando;
Abrir();
try
{
if (m_adaptador != null) m_adaptador.Fill(m_dataset);
}
catch (Exception ex)
{
throw new Exception("Error al ejecutar el Procedimiento Almacenado. " + ex.Message);
}
finally
{
Cerrar();
}
return m_dataset;
}
/// <summary>
/// ����DataSet--���ط�����
/// </summary>
/// <param name="sql">SQL�����ߴ洢����</param>
/// <param name="startindex">��ʼ��</param>
/// <param name="num">������</param>
/// <param name="dataname">�������</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string sql, int startindex, int num, string dataname)
{
DataSet MyDataSet = new DataSet();
IsStoredProcedure(sql);
MyDataAdapter = MyFactory.CreateDataAdapter();
MyDataAdapter.SelectCommand = MyCommand;
try
{
Open();
MyDataAdapter.Fill(MyDataSet, startindex, num, dataname);
MyDataAdapter.Dispose();
return MyDataSet;
}
finally
{
Dispose();
}
}
private void ExecuteFullQuery()
{
// TDBTransaction ReadTransaction;
// Boolean NewTransaction = false;
if (FFindParameters.FParametersGivenSeparately)
{
string SQLOrderBy = "";
string SQLWhereCriteria = "";
if (FFindParameters.FPagedTableWhereCriteria != "")
{
SQLWhereCriteria = "WHERE " + FFindParameters.FPagedTableWhereCriteria;
}
if (FFindParameters.FPagedTableOrderBy != "")
{
SQLOrderBy = " ORDER BY " + FFindParameters.FPagedTableOrderBy;
}
FSelectSQL = "SELECT " + FFindParameters.FPagedTableColumns + " FROM " + FFindParameters.FPagedTable +
' ' +
SQLWhereCriteria + SQLOrderBy;
}
else
{
FSelectSQL = FFindParameters.FSqlQuery;
}
TLogging.LogAtLevel(9, (this.GetType().FullName + ".ExecuteFullQuery SQL:" + FSelectSQL));
// create temp table
FTmpDataTable = new DataTable(FFindParameters.FPagedTable + "_for_paging");
TDBTransaction transaction;
Boolean NewTransaction = false;
try
{
transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Fill temporary table with query results (all records)
FDataAdapter = null;
DBAccess.GDBAccessObj.PrepareNextCommand();
DBAccess.GDBAccessObj.SetTimeoutForNextCommand(60);
FDataAdapter = (DbDataAdapter)DBAccess.GDBAccessObj.SelectDA(FSelectSQL, transaction, FFindParameters.FParametersArray);
if ((FFindParameters.FColumNameMapping != null) && (FDataAdapter != null))
{
PerformColumnNameMapping();
}
//
// Actual DB call for execution of SELECT query
//
FTotalRecords = FDataAdapter.Fill(FTmpDataTable);
}
catch (NpgsqlException Exp)
{
if (Exp.Code == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general NpgsqlException occured: " + Exp.ToString());
}
FAsyncExecProgress.ProgressInformation = "Query cancelled!";
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Stopped;
return;
}
catch (Exception Exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general Exception occured: " + Exp.ToString());
FAsyncExecProgress.ProgressInformation = "Query cancelled!";
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Stopped;
return;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
/*
* Check if query execution cancellation was requested - this is only a back-stop for the corner case where
* the user cancelled the query after the query execution was finished by the DB (if the user had cancelled
* while the query was still being executed by the DB, the appropriate Exception would have be handled in the
* Exception handler above and this Method would have already been exited!)
*/
if (FAsyncExecProgress.FCancelExecution)
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled (FCancelExecution = true)!");
FAsyncExecProgress.ProgressInformation = "Query cancelled!";
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Stopped;
return;
}
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".ExecuteFullQuery: FDataAdapter.Fill finished. FTotalRecords: " + FTotalRecords.ToString()));
FPageDataTable = FTmpDataTable.Clone();
FPageDataTable.TableName = FFindParameters.FSearchName;
FAsyncExecProgress.ProgressInformation = "Query executed.";
FAsyncExecProgress.ProgressPercentage = 100;
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Finished;
}
/// <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;
}
/// <summary>
/// Obtiene un conjunto de datos resultado de una consulta SQL
/// </summary>
/// <param name="consultaSQL">Consulta SQL que se desea ejecutar</param>
/// <returns>Conjunto de datos obtenido</returns>
public DataTable obtenerConsulta(string consultaSQL)
{
consultaSQL.Trim();
m_datatable = new DataTable();
m_comando.CommandText = consultaSQL;
m_comando.CommandType = CommandType.Text;
m_adaptador = base.ObtenerFabrica().CreateDataAdapter();
m_adaptador.SelectCommand = m_comando;
base.Abrir();
try
{
m_adaptador.Fill(m_datatable);
}
catch (Exception ex)
{
throw new Exception("Error al ejecutar la consulta. " + ex.Message);
}
finally
{
base.Cerrar();
}
return m_datatable;
}
/// <summary>
/// 执行查询 返回DataSet
/// </summary>
/// <param name="strCmdText"></param>
/// <param name="args"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public DataSet ExecuteQueryDataSet(string strCmdText, DbParameter[] args, CommandType commandType)
{
DataSet ds = new DataSet();
try
{
Database db = GetDataBase();
_cmd = db.DbProviderFactory.CreateCommand();
_cmd.Connection = db.CreateConnection();
_cmd.CommandText = strCmdText;
_cmd.CommandTimeout = TimeOut;
_cmd.CommandType = commandType;
_cmd.Parameters.AddRange(args);
_da = db.DbProviderFactory.CreateDataAdapter();
_da.SelectCommand = _cmd;
_da.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Dispose();
}
return ds;
}
private void CreateFile(string tableName, IDbCommand cmd, DbDataAdapter da)
{
cmd.CommandText = string.Format("SELECT A.COLUMN_NAME,B.COMMENTS COLUMN_COMMENT, A.DATA_TYPE COLUMN_TYPE, A.NULLABLE IS_NULLABLE FROM USER_TAB_COLUMNS A INNER JOIN USER_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME WHERE A.TABLE_NAME = '{0}'", tableName.ToUpper());
DataTable dt = new DataTable();
da.Fill(dt);
cmd.CommandText = string.Format("SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME ='{0}'", tableName.ToUpper());
string tableComments = (cmd.ExecuteScalar() ?? string.Empty).ToString();
CreateModelFile(tableName, tableComments, dt);
}
public static DataTable FillDataTable(DbDataAdapter dbDataAdapter)
{
DataTable dataTable = new DataTable();
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
dbDataAdapter.Fill(dataTable);
dbConnection.Close();
return dataTable;
}
public void DataAdapter_TableMappings(DbDataAdapter dbDA)
{
Exception exp = null;
IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
IDbCommand ICmd = Ida.SelectCommand;
IDbConnection IConn = ICmd.Connection;
IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
IConn.Open();
ICmd.Transaction = IConn.BeginTransaction();
//--- Default value ---
try
{
BeginCase("TableMappings Default value");
Compare(dbDA.TableMappings.Count ,0);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
//init dataset
DataSet ds = new DataSet();
ds.Tables.Add("CustTable");
ds.Tables.Add("EmplTable");
//load the tables
dbDA.TableMappings.Add("Table","CustTable");
ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
dbDA.Fill(ds);
dbDA.TableMappings.Clear();
dbDA.TableMappings.Add("Table","EmplTable");
ICmd.CommandText = " SELECT EmployeeID, LastName, FirstName, Title FROM Employees where EmployeeID > 0";
dbDA.Fill(ds);
try
{
BeginCase("TableMappings.Count");
Compare(ds.Tables.Count ,2);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
try
{
BeginCase("Customers rows count");
Compare(ds.Tables["CustTable"].Rows.Count > 0,true);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
try
{
BeginCase("Employees rows count");
Compare(ds.Tables["EmplTable"].Rows.Count > 0,true);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
try
{
BeginCase("Employees Columns");
Compare(ds.Tables["EmplTable"].Columns.IndexOf("EmployeeID") >= 0,true);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
try
{
BeginCase("Customer Columns");
Compare(ds.Tables["CustTable"].Columns.IndexOf("CustomerID") >= 0,true);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
//Another checking
if (ConnectedDataProvider.GetDbType() != DataBaseServer.DB2) {
BeginCase("Check table mappings with stored procedure of another owner");
try
{
DataSet ds1 = new DataSet();
ds1.Tables.Add("EmplTable");
dbDA.TableMappings.Clear();
dbDA.TableMappings.Add("Table","EmplTable");
ICmd.CommandType = CommandType.StoredProcedure;
switch (ConnectedDataProvider.GetDbType(ConnectedDataProvider.ConnectionString))
{
case MonoTests.System.Data.Utils.DataBaseServer.Oracle:
// On ORACLE, the Scheam is the user is the owner.
ICmd.CommandText = "GHTDB.GH_DUMMY";
break;
case MonoTests.System.Data.Utils.DataBaseServer.PostgreSQL:
ICmd.CommandText = "public.gh_dummy";
break;
default:
ICmd.CommandText = "mainsoft.GH_DUMMY";
break;
}
// investigate the SP parameters
OleDbCommandBuilder.DeriveParameters((OleDbCommand)ICmd);
Compare(2,ICmd.Parameters.Count);
// add one numeric parameter, and Fill
ICmd.Parameters.Clear();
ICmd.Parameters.Add(new OleDbParameter("EmployeeIDPrm",1));
dbDA.Fill(ds1);
Compare(ds1.Tables.Count,1);
Compare(ds1.Tables[0].Rows.Count >0,true);
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
}
//
((IDbDataAdapter)dbDA).SelectCommand.Transaction.Commit();
//close connection
if ( ((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed )
((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
}
