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); }