/// <summary> /// 执行sql返回dataable /// </summary> /// <param name="sql"></param> /// <returns></returns> private DataTable ExecuteDataTable(string sql) { DataSet ds = new DataSet(); try { switch (sqltype) { case 1: using (SqlConnection conn = new SqlConnection(cononstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds); } } } break; case 2: using (MySqlConnection conn = new MySqlConnection(cononstr)) { conn.Open(); using (MySqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd)) { adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds); } } } break; default: break; } return ds.Tables[0]; } catch (Exception ex) { this.Dispatcher.Invoke(delegate { tbModel.Text = "连接字符串填写错误,请检查。\n错误信息:" + ex.Message + "\n" + ex.ToString(); tbDAL.Text = ex.Message + "\n" + ex.ToString(); }); ConnectSqlFailed(); return null; } finally { this.Dispatcher.Invoke(delegate { btnConnect.IsEnabled = true; tbConnStr.IsEnabled = true; }); } }
public Table GetTableMetadata(string tableName) { string selectCmdText = string.Format("SELECT * FROM {0}", tableName); ; MySqlCommand command = new MySqlCommand(selectCmdText, conn); MySqlDataAdapter ad = new MySqlDataAdapter(command); System.Data.DataSet ds = new DataSet(); ad.FillSchema(ds, SchemaType.Mapped, tableName); Table table = new Table(ds.Tables[0]); return table; }
public void GetSchema() { if (Version < new Version(5, 0)) return; execSQL("CREATE PROCEDURE spTest() BEGIN SELECT * FROM Test; END"); execSQL(@"CREATE TABLE Test(id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id)) "); MySqlCommand cmd = new MySqlCommand("spTest", conn); cmd.CommandType = CommandType.StoredProcedure; MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); reader.Read(); reader.Close(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataTable schema = new DataTable(); da.FillSchema(schema, SchemaType.Source); Assert.AreEqual(2, schema.Columns.Count); }
public void GetSubCategoryMasterDetail() { try { //DataSet oDS = new DataSet(); if (oDS_Main.Tables.Contains("SubCategory_Table")) { oDS_Main.Tables["SubCategory_Table"].Rows.Clear(); } MySqlConnection conn = new MySqlConnection(BG_Db_Class.getConnectionString()); conn.Open(); //***************For Sub Category Table ************************************************** MySqlCommand cmd3 = new MySqlCommand("SELECT * FROM SubCategory_Table Order By Site_Id", conn); MySqlDataAdapter oSiteDataAdapter3 = new MySqlDataAdapter(cmd3); MySqlCommandBuilder oSiteTableCmdBuilder3 = new MySqlCommandBuilder(oSiteDataAdapter3); oSiteDataAdapter3.FillSchema(oDS_Main, SchemaType.Source); oSiteDataAdapter3.Fill(oDS_Main, "SubCategory_Table"); DataTable SubCatTable = oDS_Main.Tables["SubCategory_Table"]; SubCatTable.TableName = "SubCategory_Table"; Log("SubCategory Loading Please wait"); } catch (Exception ex) { //return null; } finally { //conn.Close(); Log("SubCategory Master Loaded"); } }
private void GetData() { table_names = new String[] { "client_balance", "client_cashflow", "client_cashflow_type", "client_info", "commodity_category", "futures_account_balance", "futures_account_info", "futures_cashflow", "futures_cashflow_type", "futures_contracts", "futures_transactions", "futures_verbose_positions", "options_contracts", "options_direction_type", "options_transactions", "options_types", "options_verbose_positions" }; view_names = new String[] { "client_balance_join", "client_cashflow_view", "commodity_category_view", "futures_account_balance_view", "futures_cashflow_view", "futures_contracts_view", "futures_positions_summary", "futures_transactions_view", "futures_verbose_positions_view", "options_contracts_view", "options_direction_type_view", "options_positions_summary", "options_transactions_view", "options_types_view", "options_verbose_positions_view", "non_trade_dates", "business_state_view", "option_settle_info_view", "future_settle_info_view", "business_current_state", "option_position_settle_info", "future_position_settle_info", "business_overview" }; String selectString = ""; foreach (String t in table_names) { selectString = String.Format("select * from {0};", t); MySqlCommand command = new MySqlCommand(selectString, this.sql_connection); MySqlDataAdapter adapter = new MySqlDataAdapter(); command.CommandType = System.Data.CommandType.Text; adapter.SelectCommand = command; MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.FillSchema(this, System.Data.SchemaType.Source, t); adapter.FillSchema(display_ds, SchemaType.Source, t); adapter.Fill(this, t); adapter.Fill(display_ds, t); adapterDict.Add(t, adapter); } foreach (String t in view_names) { selectString = String.Format("select * from {0};", t); if (t == "futures_verbose_positions_view") selectString = "SELECT * FROM futures_verbose_positions;"; else if (t == "business_current_state") selectString = "SELECT * FROM accum_business_pnl ORDER BY settle_day DESC LIMIT 1"; MySqlCommand command = new MySqlCommand(selectString, this.sql_connection); MySqlDataAdapter adapter = new MySqlDataAdapter(); command.CommandType = System.Data.CommandType.Text; adapter.SelectCommand = command; if (t == "non_trade_dates") { MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); } adapter.FillSchema(this, System.Data.SchemaType.Source, t); adapter.FillSchema(display_ds, SchemaType.Source, t); adapter.Fill(this, t); adapter.Fill(display_ds, t); adapterDict.Add(t, adapter); } foreach (System.Data.DataTable table in this.Tables) { foreach (System.Data.DataColumn col in table.Columns) { col.AllowDBNull = true; } } foreach (System.Data.DataTable table in this.display_ds.Tables) { foreach (System.Data.DataColumn col in table.Columns) { col.AllowDBNull = true; } } { System.Data.DataTable client_table = this.Tables["client_balance_join"]; var tmp_client_table = this.display_ds.Tables["client_balance_join"]; client_table.PrimaryKey = new System.Data.DataColumn[] { client_table.Columns["client_id"] }; tmp_client_table.PrimaryKey = new System.Data.DataColumn[] { tmp_client_table.Columns["client_id"] }; } { System.Data.DataTable sum_table = this.Tables["options_positions_summary"]; var tmp_sum_table = this.display_ds.Tables["options_positions_summary"]; sum_table.PrimaryKey = new System.Data.DataColumn[] { sum_table.Columns["client_id"], sum_table.Columns["contract_code"], sum_table.Columns["long_short"] }; tmp_sum_table.PrimaryKey = new System.Data.DataColumn[] { tmp_sum_table.Columns["client_id"], tmp_sum_table.Columns["contract_code"], tmp_sum_table.Columns["long_short"] }; } { System.Data.DataTable sum_table = this.Tables["futures_positions_summary"]; var tmp_sum_table = this.display_ds.Tables["futures_positions_summary"]; sum_table.PrimaryKey = new System.Data.DataColumn[] { sum_table.Columns["account_no"], sum_table.Columns["contract_code"], sum_table.Columns["long_short"] }; tmp_sum_table.PrimaryKey = new System.Data.DataColumn[] { tmp_sum_table.Columns["account_no"], tmp_sum_table.Columns["contract_code"], tmp_sum_table.Columns["long_short"] }; } foreach (String t in table_names) { System.Data.DataTable table = Tables[t]; MySqlDataAdapter adapter = adapterDict[t]; System.Data.Common.DataTableMapping mapping = adapter.TableMappings.Add(t, t); foreach (System.Data.DataColumn col in table.Columns) { String mappedName = ""; if (colNameDict.TryGetValue(col.ColumnName, out mappedName)) mapping.ColumnMappings.Add(col.ColumnName, mappedName); } } foreach (String t in view_names) { System.Data.DataTable table = Tables[t]; MySqlDataAdapter adapter = adapterDict[t]; System.Data.Common.DataTableMapping mapping = adapter.TableMappings.Add(t, t); foreach (System.Data.DataColumn col in table.Columns) { String mappedName = ""; if (colNameDict.TryGetValue(col.ColumnName, out mappedName)) mapping.ColumnMappings.Add(col.ColumnName, mappedName); } } //this.Update(); foreach (System.Data.DataTable table in Tables) { foreach (System.Data.DataColumn col in table.Columns) { String mappedName = ""; if (colNameDict.TryGetValue(col.ColumnName, out mappedName)) col.ColumnName = mappedName; } } foreach (System.Data.DataTable table in display_ds.Tables) { foreach (System.Data.DataColumn col in table.Columns) { String mappedName = ""; col.AllowDBNull = true; if (colNameDict.TryGetValue(col.ColumnName, out mappedName)) col.ColumnName = mappedName; } } this.Tables.Add("risk_info"); using (DataTable table = this.Tables["risk_info"]) { table.Columns.Add("客户编号", Type.GetType("System.UInt32")); table.Columns.Add("合约代码", Type.GetType("System.String")); table.Columns.Add("标的代码", Type.GetType("System.String")); table.Columns.Add("买卖方向", Type.GetType("System.String")); table.Columns.Add("标的现价", Type.GetType("System.Decimal")); table.Columns.Add("数量", Type.GetType("System.Double")); table.Columns.Add("到期天数", Type.GetType("System.UInt32")); table.Columns.Add("波动率", Type.GetType("System.Double")); table.Columns.Add("Delta", Type.GetType("System.Double")); table.Columns.Add("Gamma", Type.GetType("System.Double")); table.Columns.Add("Theta", Type.GetType("System.Double")); table.Columns.Add("Vega", Type.GetType("System.Double")); table.Columns.Add("Rho", Type.GetType("System.Double")); table.PrimaryKey = new DataColumn[] { table.Columns["客户编号"], table.Columns["合约代码"], table.Columns["买卖方向"] }; } this.Tables.Add("risk_info_gross"); using (DataTable table = this.Tables["risk_info_gross"]) { table.Columns.Add("标的代码", Type.GetType("System.String")); table.Columns.Add("标的现价", Type.GetType("System.Decimal")); table.Columns.Add("Delta", Type.GetType("System.Double")); table.Columns.Add("Gamma", Type.GetType("System.Double")); table.Columns.Add("Theta", Type.GetType("System.Double")); table.Columns.Add("Vega", Type.GetType("System.Double")); table.Columns.Add("Rho", Type.GetType("System.Double")); table.PrimaryKey = new DataColumn[] { table.Columns["标的代码"] }; } using (var table = this.display_ds.Tables["futures_verbose_positions_view"]) { table.Columns.Add("盯市盈亏", Type.GetType("System.Decimal")); } //this.display_ds.Tables["business_state_view"].DefaultView.Sort = "结算日 DESC"; //将业务状态表按日期倒序排列 UpdateGreeks(); UpdateMarkToMarketPnl(); }
public void GetSchema() { if (version < new Version(5, 0)) return; try { execSQL("CREATE PROCEDURE spTest() BEGIN SELECT * FROM Test; END"); MySqlCommand cmd = new MySqlCommand("spTest", conn); cmd.CommandType = CommandType.StoredProcedure; MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); reader.Read(); reader.Close(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataTable schema = new DataTable(); da.FillSchema(schema, SchemaType.Source); Assert.AreEqual(2, schema.Columns.Count); //Bug #27668 FillSchema and Stored Proc with an out parameter execSQL("DROP TABLE IF EXISTS Test"); execSQL(@"CREATE TABLE Test(id INT AUTO_INCREMENT, PRIMARY KEY (id)) "); execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL(@"CREATE PROCEDURE spTest (OUT id INT) BEGIN INSERT INTO Test VALUES (NULL); SET id=520; END"); cmd = new MySqlCommand("spTest", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("?id", MySqlDbType.Int32); cmd.Parameters[0].Direction = ParameterDirection.Output; da = new MySqlDataAdapter(cmd); DataTable dt = new DataTable(); cmd.ExecuteNonQuery(); da.Fill(dt); da.FillSchema(dt, SchemaType.Mapped); } catch (Exception ex) { Assert.Fail(ex.Message); } }
/// <summary> ///Searches the table adressen for a given Searchstring ///and inserts a "tblSugarSearchHuman" into the DataSet.</summary> /// <param name="searchstring">the search string</param> public void search_ds_human_persons(string searchstring) { StringBuilder m_strCommand = new StringBuilder(); //SELECT `id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, LEFT(`description`, 256), `deleted`, `assigned_user_id`, `salutation`, `first_name`, `last_name`, `title`, `department`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `assistant`, `assistant_phone`, `lead_source`, `reports_to_id`, `birthdate`, `campaign_id` FROM `sugarcrm`.`contacts` LIMIT 0, 1000; m_strCommand.Append("select salutation as Anrede, first_name as Vorname, last_name as Nachname, primary_address_street as Strasse, primary_address_postalcode as PLZ, primary_address_city as Ort, phone_home as Telefon, phone_mobile as Mobile from contacts where last_name like "); m_strCommand.Append("'%"); m_strCommand.Append(searchstring); m_strCommand.Append("%' and deleted = 0;"); MySqlCommand m_cmdSearchCommand = new MySqlCommand(m_strCommand.ToString()); m_daSugar = new MySqlDataAdapter(m_cmdSearchCommand.CommandText, m_cnSugar); m_daSugar.FillSchema(m_dsSugar, SchemaType.Source, m_const_strSugarTableSearchHuman); m_daSugar.Fill(m_dsSugar, m_const_strSugarTableSearchHuman); //return m_dsSugar; }
/// <summary> /// Searches for Contacts by Street. /// Table: tblSugarSearchAllCompanies</summary> /// <param name="searchstring">the search string</param> public void SearchContactsByStreet(string searchstring) { StringBuilder m_strCommand = new StringBuilder(); m_strCommand.Append("select * from contacts where primary_address_street like "); m_strCommand.Append("'%"); m_strCommand.Append(searchstring); m_strCommand.Append("%' and deleted = 0;"); MySqlCommand m_cmdSearchCommand = new MySqlCommand(m_strCommand.ToString()); m_daSugar = new MySqlDataAdapter(m_cmdSearchCommand.CommandText, m_cnSugar); m_daSugar.FillSchema(m_dsSugar, SchemaType.Source, m_const_strSugarTableSearchAllPrivate); m_daSugar.Fill(m_dsSugar, m_const_strSugarTableSearchAllPrivate); }
/// <summary> /// Get Table Schema Definition from MySQL /// </summary> /// <param name="ConnString"></param> /// <param name="TableName"></param> /// <returns></returns> private static DataTable GetMySQLSchemaTypeTable(string ConnString, string TableName) { DataTable ReturnTable = null; MySqlConnection MySqlConn = null; try { using (MySqlConn = new MySqlConnection(ConnString)) { MySqlCommand MySqlCmd = new MySqlCommand(TableName, MySqlConn); MySqlCmd.CommandType = CommandType.TableDirect; MySqlDataAdapter MySqlDataAdapt = new MySqlDataAdapter(MySqlCmd); DataSet MyDataSet = new DataSet(); MySqlDataAdapt.FillSchema(MyDataSet, SchemaType.Source, TableName); if (MyDataSet.Tables != null && MyDataSet.Tables.Count > 0) { ReturnTable = MyDataSet.Tables[0]; } } } catch (Exception ex) { string ErrorMessage = string.Format("MemcachedLoaderService. Error Retrieving Table Specs from MySQL Connection. TableName is [{0}]. Error Message is [{1}].", TableName, ex.Message); Utils.GetEventLog().WriteEntry(ErrorMessage); } finally { if (MySqlConn != null) MySqlConn.Close(); } return ReturnTable; }
/// <summary> /// 执行返回DataSet /// </summary> /// <param name="con"></param> /// <param name="cmdText"></param> /// <param name="parameters"></param> /// <returns></returns> public static DataSet ExecuteDataSet(MySqlConnection con, string cmdText, params MySqlParameter[] parameters) { using (MySqlCommand cmd = con.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(ds); return ds; } } }
/// <summary> /// Execute a MySqlCommand (that returns a resultset) against the specified MySqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid MySqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(IDbConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); MySqlCommand setformat = new MySqlCommand("SET NAMES latin1;set character_set_database = big5;", connection as MySqlConnection); setformat.ExecuteNonQuery(); setformat.Dispose(); // Create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection as MySqlConnection, (MySqlTransaction)null, commandType, commandText, commandParameters as MySqlParameter[], out mustCloseConnection); // Create the DataAdapter & DataSet using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet("DataSet"); da.FillSchema(ds, SchemaType.Source); foreach (DataColumn col in ds.Tables[0].Columns) { if (col.DataType.Name.Equals("MySqlDateTime")) { col.DataType = Type.GetType("System.DateTime"); } } // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the MySqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); // Return the dataset return ds; } }
// 创建据有 Table-Schema 的 DataTable 实例 public DataTable NewTable() { MySqlDataAdapter adpt = new MySqlDataAdapter("SELECT * FROM " + this.TableName, this.sqlConn); DataTable dt = new DataTable(); adpt.FillSchema(dt, SchemaType.Mapped); return dt; }
public void GetSchema(DataTable dt) { MySqlConnection connection = new MySqlConnection(_sqlconn); MySqlDataAdapter adpter = new MySqlDataAdapter(_sqlcomm, connection); adpter.FillSchema(dt, SchemaType.Source); }
public DataTable ToDataTable(System.Data.Entity.DbContext ctx, string sql) { IDbCommand cmd = ctx.Database.Connection.CreateCommand(); MySqlDataAdapter adapter = new MySqlDataAdapter(sql,ctx.Database.Connection.ConnectionString); DataTable dt = new DataTable("sd"); try { cmd.Connection.Open(); adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } finally { cmd.Connection.Close(); } return dt; }
public override DataTable ObtenerEsquema(string tabla) { MySqlDataAdapter da = new MySqlDataAdapter("SELECT * from " + tabla, _connection); DataTable dt = new DataTable(); return da.FillSchema(dt, SchemaType.Source); }
/// <summary> /// Searches for Companies by Mainphone. /// Table: tblSugarSearchAllCompanies</summary> /// <param name="searchstring">the search string</param> public void SearchCompanyByPhone(string searchstring) { StringBuilder m_strCommand = new StringBuilder(); m_strCommand.Append("select * from accounts where phone_office like "); m_strCommand.Append("'%"); m_strCommand.Append(searchstring); m_strCommand.Append("%' and deleted = 0;"); MySqlCommand m_cmdSearchCommand = new MySqlCommand(m_strCommand.ToString()); m_daSugar = new MySqlDataAdapter(m_cmdSearchCommand.CommandText, m_cnSugar); m_daSugar.FillSchema(m_dsSugar, SchemaType.Source, m_const_strSugarTableSearchAllCompanies); m_daSugar.Fill(m_dsSugar, m_const_strSugarTableSearchAllCompanies); }
public void GetSchema2() { if (Version.Major < 5) return; execSQL(@"CREATE TABLE Test(id INT AUTO_INCREMENT, PRIMARY KEY (id)) "); execSQL(@"CREATE PROCEDURE spTest (OUT id INT) BEGIN INSERT INTO Test VALUES (NULL); SET id=520; END"); MySqlCommand cmd = new MySqlCommand("spTest", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("?id", MySqlDbType.Int32); cmd.Parameters[0].Direction = ParameterDirection.Output; MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataTable dt = new DataTable(); if (prepare) cmd.Prepare(); cmd.ExecuteNonQuery(); da.Fill(dt); da.FillSchema(dt, SchemaType.Mapped); }
/// <summary> /// Executes a SELECT statement against the database and returns a disconnected data table. NOTE: Use this overload to work with Typed DataSets. /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <param name="dataTable">Table that will contain the result</param> /// <returns>A data table object</returns> public override DataTable Select(Query selectQuery, DataTable dataTable) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("selectQuery"); } if (dataTable == null) { throw new ArgumentNullException("dataTable"); } #endregion Input Validation if (selectQuery.SqlStatement.Contains("TOP 2")) { selectQuery = CreateQuery(selectQuery.SqlStatement.Replace("TOP 2 ", string.Empty).Replace(";",string.Empty) + " LIMIT 2"); } IDbConnection connection = GetConnection(connectionString); MySqlDataAdapter adapter = new MySqlDataAdapter(); adapter.SelectCommand = (MySqlCommand)GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters); try { //Logger.Log(selectQuery); adapter.Fill(dataTable); adapter.FillSchema(dataTable, SchemaType.Source); return dataTable; } catch (Exception ex) { throw new System.ApplicationException("Error executing select query against the database.", ex); } }