public virtual bool GetSchema(string rstrSQL, string rstrTableName, DataSet rdatSet)
{
NpgsqlDataAdapter adapter = null;
try
{
if (rdatSet == null)
{
rdatSet = new DataSet();
}
using (NpgsqlCommand cmd = this.CreateCommand())
{
cmd.CommandText = rstrSQL;
cmd.CommandTimeout = this._CommandTimeout;
adapter = new NpgsqlDataAdapter(rstrSQL, this._ConnString);
adapter.SelectCommand = cmd;
adapter.FillSchema(rdatSet, SchemaType.Mapped, rstrTableName);
}
return(true);
}
catch (Exception ex)
{
this._InnerException = ex;
throw ex;
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
}
}
protected override DataTable BuildSchemaTable(Boolean withGeometryColumn)
{
DataTable dt = null;
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format("SELECT * FROM {0};", QualifiedTableName), conn))
{
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
dt = ds.Tables["Table"];
}
//dt.Columns[OidColumn].DataType = typeof(Int64);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == GeometryColumn)
{
dt.Columns[i].DataType = typeof(Byte[]);
}
//replaceObjectDataColumn(dt, dt.Columns[i]);
}
if (!withGeometryColumn)
{
dt.Columns.Remove(GeometryColumn);
}
}
return(dt);
}
/// <summary>Fills the data set with the data from the database table.</summary>
protected void FillDataSet()
{
if (dataAdapter == null)
{
SetupAdapter();
}
dataAdapter.FillSchema(dataSet, SchemaType.Source, tableName);
dataAdapter.Fill(dataSet, tableName);
}
//-----------------------------------------------------------------------------------------------------------------------------
// Select Delta DataSet
//-----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 테이블에 INSERT 하기 위한 스키마를 구하는 함수 입니다.
/// </summary>
/// <param name="connection_string"></param>
/// <param name="table_name"></param>
/// <returns></returns>
public DataSet SelectDeltaSchema(string connection_string, string table_name)
{
var _result = new DataSet();
using (var _sqlcon = new NpgsqlConnection(connection_string))
{
var _adapter = new NpgsqlDataAdapter("SELECT * FROM " + table_name, _sqlcon);
_adapter.FillSchema(_result, SchemaType.Source, table_name);
}
return(_result);
}
public virtual DataTable ExecuteResultSet(NpgsqlCommand cmd, DataSet rDataSet, string TableName, bool IsFillSchema)
{
NpgsqlDataAdapter adapter = null;
try
{
if (cmd.Connection == null)
{
cmd.Connection = this.Connection;
}
if (this._sqlTran != null)
{
cmd.Transaction = this._sqlTran;
}
if (rDataSet == null)
{
rDataSet = new DataSet();
}
adapter = new NpgsqlDataAdapter(cmd);
if (IsFillSchema)
{
adapter.FillSchema(rDataSet, SchemaType.Mapped, TableName);
}
adapter.Fill(rDataSet, TableName);
if (rDataSet.Tables.Contains(TableName))
{
return(rDataSet.Tables[TableName]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
this._InnerException = ex;
throw ex;
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
adapter = null;
}
}
private DataTable GetResultTable(string tableName, string keyPhrase = null, string place = null,
bool separate = false, string orderColumn = "")
{
DataTable table = new DataTable(tableName);
using (var connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
string query = GetSelectionQuery(tableName, keyPhrase, place, orderColumn, separate);
using (var command = new NpgsqlCommand(query, connection))
{
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = command;
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
}
connection.Close();
}
return(table);
}
public DataTable GetBooksByPublisher(int id)
{
DataTable table = new DataTable("books");
using (var connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
StringBuilder query = new StringBuilder();
query.Append("SELECT book.title, book.kind, book.max_count, book.available_count, book.publish_year, author.full_name AS author ")
.Append("FROM book, author, book_author ")
.Append("WHERE book.publisher_id = ").Append(id).Append(" AND book_author.book_id = book.id AND author.id = book_author.author_id;");
using (var command = new NpgsqlCommand(query.ToString(), connection))
{
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = command;
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
}
connection.Close();
}
return(table.Rows.Count == 0 ? null : table);
}
public virtual DataSet ExecuteResultSet(NpgsqlCommand cmd, bool IsFillSchema)
{
DataSet dts = null;
NpgsqlDataAdapter adapter = null;
try
{
if (cmd.Connection == null)
{
cmd.Connection = this.Connection;
}
if (this._sqlTran != null)
{
cmd.Transaction = this._sqlTran;
}
dts = new DataSet();
adapter = new NpgsqlDataAdapter(cmd);
if (IsFillSchema)
{
adapter.FillSchema(dts, SchemaType.Mapped);
}
adapter.Fill(dts);
return(dts);
}
catch (Exception ex)
{
this._InnerException = ex;
throw ex;
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
adapter = null;
}
}
public DataTable GetOrdersHistoryByReader(int id)
{
DataTable table = new DataTable("history");
using (var connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
StringBuilder query = new StringBuilder();
query.Append("SELECT book.title, book.kind, author.full_name AS author, publisher.name AS publisher, book_reader.taken, book_reader.returned ")
.Append("FROM book_reader, book, publisher, author, book_author ")
.Append("WHERE book_reader.reader_id = ").Append(id).Append(" AND book.id = book_reader.book_id AND publisher.id = book.publisher_id ")
.Append("AND book_author.book_id = book_reader.book_id AND author.id = book_author.author_id;");
using (var command = new NpgsqlCommand(query.ToString(), connection))
{
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = command;
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
}
connection.Close();
}
return(table.Rows.Count == 0 ? null : table);
}
/// <summary>
/// SELECT文を発行する
/// </summary>
/// <param name="rstrSQL">SQL文</param>
/// <param name="rstrTableName"> テーブル マップに使用するソース テーブルの名前。</param>
/// <param name="dataset">結果をセットするデータセット</param>
/// <param name="IsFillSchema">スキーマ情報をセットするかどうか</param>
/// <returns>Datasetを基づきの検索結果</returns>
public bool SelectSql(string rstrSQL, string rstrTableName, DataSet dataset, bool IsFillSchema)
{
NpgsqlDataAdapter adapter = null;
try
{
if (dataset == null)
{
throw new ArgumentException("dataset is null.");
}
using (NpgsqlCommand cmd = this.CreateCommand())
{
cmd.CommandText = rstrSQL;
cmd.CommandTimeout = this._CommandTimeout;
adapter = new NpgsqlDataAdapter(rstrSQL, this._ConnString);
adapter.SelectCommand = cmd;
if (IsFillSchema)
{
adapter.FillSchema(dataset, SchemaType.Mapped, rstrTableName);
}
adapter.Fill(dataset, rstrTableName);
}
return(true);
}
catch (Exception ex)
{
this._InnerException = ex;
throw ex;
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
}
}
private static void OnSaveGraphLeg(Tag tag, string nameBase)
{
bool postgresql = ServiceData.GetInstance().GetSqlName() == "PostgreSQL";
if (!postgresql)
{
using (SqlConnection connection = new SqlConnection(ServiceData.GetInstance().GetOpcTablesBase()))
{
try
{
connection.Open();
// Create an instance of a DataAdapter.
SqlDataAdapter adapter
= new SqlDataAdapter(
"SELECT Id, Legend, Koef, Color, ChangeVal, SaveByTime, RarelyChanging FROM " + nameBase + " WHERE NumTag = " + tag.NumTag,
connection);
// Create an instance of a DataSet, and retrieve data from the Authors table.
DataSet dbOpcTables = new DataSet("DbOpcTables");
adapter.FillSchema(dbOpcTables, SchemaType.Source, nameBase);
adapter.Fill(dbOpcTables, nameBase);
DataColumn[] myKey = new DataColumn[1];
myKey[0] = dbOpcTables.Tables[0].Columns[0];
dbOpcTables.Tables[0].PrimaryKey = myKey;
var tblAuthors = dbOpcTables.Tables[nameBase];
var dataRow = tblAuthors.Rows[0];
dataRow.BeginEdit();
dataRow["Legend"] = tag.NameTag;
dataRow["Color"] = tag.Color;
dataRow["Koef"] = Convert.ToDouble(tag.Koef);
dataRow["ChangeVal"] = Convert.ToDouble(tag.ChangeVal);
dataRow["SaveByTime"] = tag.SaveByTime;
dataRow["RarelyChanging"] = tag.RarelyChanging;
dataRow.EndEdit();
SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
adapter.Update(dbOpcTables, nameBase);
}
catch (Exception ex)
{
MessageBox.Show("Что-то пошло не так!" + "\n" + ex.Message);
}
finally
{
connection.Close();
}
}
}
else
{
using (NpgsqlConnection connection = new NpgsqlConnection(ServiceData.GetInstance().GetOpcTablesBase()))
{
try
{
connection.Open();
// Create an instance of a DataAdapter.
NpgsqlDataAdapter adapter
= new NpgsqlDataAdapter(
"SELECT \"Id\", \"Legend\", \"Koef\", \"Color\", \"ChangeVal\", \"SaveByTime\", \"RarelyChanging\" FROM dbo." + "\"" + nameBase + "\"" + " WHERE \"NumTag\" = " + tag.NumTag,
connection);
// Create an instance of a DataSet, and retrieve data from the Authors table.
DataSet dbOpcTables = new DataSet("DbOpcTables");
adapter.FillSchema(dbOpcTables, SchemaType.Source, nameBase);
adapter.Fill(dbOpcTables, nameBase);
DataColumn[] myKey = new DataColumn[1];
myKey[0] = dbOpcTables.Tables[0].Columns[0];
dbOpcTables.Tables[0].PrimaryKey = myKey;
var tblAuthors = dbOpcTables.Tables[nameBase];
var dataRow = tblAuthors.Rows[0];
dataRow.BeginEdit();
dataRow["Legend"] = tag.NameTag;
dataRow["Color"] = tag.Color;
dataRow["Koef"] = Convert.ToDouble(tag.Koef);
dataRow["ChangeVal"] = Convert.ToDouble(tag.ChangeVal);
dataRow["SaveByTime"] = tag.SaveByTime;
dataRow["RarelyChanging"] = tag.RarelyChanging;
dataRow.EndEdit();
NpgsqlCommandBuilder objCommandBuilder = new NpgsqlCommandBuilder(adapter);
adapter.Update(dbOpcTables, nameBase);
}
catch (Exception ex)
{
MessageBox.Show("Что-то пошло не так!" + "\n" + ex.Message);
}
finally
{
connection.Close();
}
}
}
foreach (var item in OpcServer.GetInstance().GetObjects())
{
if (item.Value.SqlTableName == nameBase)
{
var tagD = OpcServer.GetInstance().GetOpc(item.Key).DiscreteTags
.SingleOrDefault(x => x.NumTag == tag.NumTag);
if (tagD != null)
{
tagD.Legend = tag.NameTag;
tagD.Color = tag.Color;
tagD.Koef = tag.Koef;
tagD.ChangeVal = tag.ChangeVal;
tagD.SaveByTime = tag.SaveByTime;
tagD.RarelyChanging = tag.RarelyChanging;
break;
}
var tagA = OpcServer.GetInstance().GetOpc(item.Key).AnalogTags
.SingleOrDefault(x => x.NumTag == tag.NumTag);
if (tagA != null)
{
tagA.Legend = tag.NameTag;
tagA.Color = tag.Color;
tagA.Koef = tag.Koef;
tagA.ChangeVal = tag.ChangeVal;
tagA.SaveByTime = tag.SaveByTime;
tagA.RarelyChanging = tag.RarelyChanging;
break;
}
}
}
}
protected override DataTable BuildSchemaTable(Boolean withGeometryColumn)
{
DataTable dt = null;
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
CollectionExpression <PropertyNameExpression> attributes = null;
if (DefaultProviderProperties != null)
{
attributes = GetProviderPropertyValue
<AttributesCollectionExpression, CollectionExpression <PropertyNameExpression> >(
DefaultProviderProperties.ProviderProperties,
null);
}
string columns = attributes == null
?
"*"
:
string.Join(",", Enumerable.ToArray(Processor.Select(attributes,
delegate(
PropertyNameExpression
o)
{
return
(QualifyColumnName
(
o.
PropertyName));
})));
if (columns != "*")
{
if (!columns.Contains(QualifyColumnName(GeometryColumn)))
{
columns = string.Format("{0},{1}", QualifyColumnName(GeometryColumn), columns);
}
if (!columns.Contains(QualifyColumnName(OidColumn)))
{
columns = string.Format("{0},{1}", QualifyColumnName(OidColumn), columns);
}
}
using (
NpgsqlCommand cmd =
new NpgsqlCommand(string.Format("SELECT {0} FROM {1} LIMIT 1;", columns, QualifiedTableName),
conn))
{
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
dt = ds.Tables["Table"];
}
conn.Close();
}
if (!dt.Columns.Contains("oid") && HasOids)
{
dt.Columns.Add(new DataColumn("oid", typeof(Int64)));
DataColumn dc = dt.Columns["oid"];
dc.SetOrdinal(0);
if (dt.Constraints.Count == 0)
{
dt.Constraints.Add("PK", dt.Columns[0], true);
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == GeometryColumn)
{
dt.Columns[i].DataType = typeof(Byte[]);
}
}
if (!withGeometryColumn)
{
dt.Columns.Remove(GeometryColumn);
}
//remove Primary Key to avoid possibliy mismatched PrimaryKey of FeatureDataTable
dt.PrimaryKey = null;
return(dt);
}
