public override List <List <string> > Select(string sql, List <Database.Bind> bind_var = null) { if (!this.IsConnected) { throw new WmibException("The database is not connected"); } Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, this.connection); if (bind_var != null) { BindVars(sql, bind_var, command); } SystemHooks.OnSQL(LocalName, sql); Npgsql.NpgsqlDataReader dr = command.ExecuteReader(); List <List <string> > results = new List <List <string> >(); while (dr.Read()) { List <string> line = new List <string>(); results.Add(line); for (int i = 0; i < dr.FieldCount; i++) { line.Add(dr[i].ToString()); } } dr.Close(); return(results); }
public string NewID() { string i = ""; string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_booking_nextid') as id;"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; try { Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("id"))) { i = rdr.GetValue(0).ToString(); } else { i = ""; }; } rdr.Close(); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(""); } return(i); }
public List <List <object> > GetAllLinesValues() { List <List <object> > res = new List <List <object> >(); while (Read()) { List <object> line = new List <object>(); for (int i = 0; i < _reader.FieldCount; i++) { line.Add(_reader[i]); } res.Add(line); } _reader.Close(); return(res); }
public DbRecord GetData(string query) { Npgsql.NpgsqlConnection conn = null; try { conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); object reader = this.GetDataReader(query, conn); DbRecord record = new DbRecord(); if (reader != null) { Npgsql.NpgsqlDataReader npgsqlReader = (Npgsql.NpgsqlDataReader)reader; record.FieldCount = npgsqlReader.FieldCount; for (int i = 0; i < npgsqlReader.FieldCount; i++) { record.Columns.Add(npgsqlReader.GetName(i)); } while (npgsqlReader.Read()) { List <object> records = new List <object>(); for (int i = 0; i < npgsqlReader.FieldCount; i++) { records.Add(npgsqlReader.GetValue(i)); } record.RowCount++; record.Records.Add(records); } npgsqlReader.Close(); } return(record); } finally { if (conn != null) { conn.Close(); } } }
public bool GetByPrimaryKey(string pKey) { string sQuery = "select * from tbm_booking WHERE bookingid='" + pKey + "'"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); try { if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("bookingid"))) { bookingid = rdr.GetString(rdr.GetOrdinal("bookingid")); } else { bookingid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("customerid"))) { m_customerid = rdr.GetString(rdr.GetOrdinal("customerid")); } else { m_customerid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("date_order"))) { m_dateorder = rdr.GetDateTime(rdr.GetOrdinal("date_order")); } else { m_dateorder = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("sdatebooking"))) { m_sdatebooking = rdr.GetDateTime(rdr.GetOrdinal("sdatebooking")); } else { m_sdatebooking = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("edatebooking"))) { m_edatebooking = rdr.GetDateTime(rdr.GetOrdinal("edatebooking")); } else { m_edatebooking = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("down_payment"))) { m_downpayment = rdr.GetDecimal(rdr.GetOrdinal("down_payment")); } else { m_downpayment = 0; }; if (!rdr.IsDBNull(rdr.GetOrdinal("duration_booking"))) { m_duration = rdr.GetDecimal(rdr.GetOrdinal("duration_booking")); } else { m_duration = 0; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opadd"))) { m_opadd = rdr.GetString(rdr.GetOrdinal("opadd")); } else { m_opadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd"))) { m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd")); } else { m_pcadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luadd"))) { m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd")); } else { m_luadd = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opedit"))) { m_opedit = rdr.GetString(rdr.GetOrdinal("opedit")); } else { m_opedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit"))) { m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit")); } else { m_pcedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luedit"))) { m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit")); } else { m_luedit = System.DateTime.MinValue; }; m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt")); } return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } finally { if (rdr != null) { rdr.Close(); } } }
public bool GetByPrimaryKey(string pKey) { string sQuery = "select * from tbm_cartype WHERE typeid='" + pKey + "'"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); try { if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("typeid"))) { m_typeid = rdr.GetString(rdr.GetOrdinal("typeid")); } else { m_typeid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("carcategoryid"))) { m_categoryid = rdr.GetString(rdr.GetOrdinal("carcategoryid")); } else { m_categoryid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("carbrandid"))) { m_carbrandid = rdr.GetString(rdr.GetOrdinal("carbrandid")); } else { m_carbrandid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("typename"))) { m_typename = rdr.GetString(rdr.GetOrdinal("typename")); } else { m_categoryname = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opadd"))) { m_opadd = rdr.GetString(rdr.GetOrdinal("opadd")); } else { m_opadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd"))) { m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd")); } else { m_pcadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luadd"))) { m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd")); } else { m_luadd = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opedit"))) { m_opedit = rdr.GetString(rdr.GetOrdinal("opedit")); } else { m_opedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit"))) { m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit")); } else { m_pcedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luedit"))) { m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit")); } else { m_luedit = System.DateTime.MinValue; }; m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt")); } return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } finally { if (rdr != null) { rdr.Close(); } } }
public bool GetByPrimaryKey(string pKey) { string sQuery = "select * from tbm_car WHERE carid='" + pKey + "'"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); try { if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("typeid"))) { m_typeid = rdr.GetString(rdr.GetOrdinal("typeid")); } else { m_typeid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("platnumber"))) { m_carplatnumber = rdr.GetString(rdr.GetOrdinal("platnumber")); } else { m_carplatnumber = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("carid"))) { m_carid = rdr.GetString(rdr.GetOrdinal("carid")); } else { m_carid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("price"))) { //m_price = Convert.ToInt32((rdr.GetOrdinal("price"))); m_price = rdr.GetDecimal(rdr.GetOrdinal("price")); } else { m_price = 0; }; if (!rdr.IsDBNull(rdr.GetOrdinal("description"))) { m_description = rdr.GetString(rdr.GetOrdinal("description")); } else { m_description = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("passengercapacity"))) { m_capacity = rdr.GetDecimal(rdr.GetOrdinal("passengercapacity")); } else { m_capacity = 0; }; //if (System.IO.File.Exists(row["imagename"].ToString())) //{ if (!rdr.IsDBNull(rdr.GetOrdinal("imagename"))) { m_carimage = rdr.GetString(rdr.GetOrdinal("imagename")); } else { m_carimage = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("filepath_car"))) { m_filepath_car = rdr.GetString(rdr.GetOrdinal("filepath_car")); } else { m_filepath_car = ""; }; m_available = rdr.GetBoolean(rdr.GetOrdinal("statusavailable")); if (!rdr.IsDBNull(rdr.GetOrdinal("opadd"))) { m_opadd = rdr.GetString(rdr.GetOrdinal("opadd")); } else { m_opadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd"))) { m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd")); } else { m_pcadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luadd"))) { m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd")); } else { m_luadd = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opedit"))) { m_opedit = rdr.GetString(rdr.GetOrdinal("opedit")); } else { m_opedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit"))) { m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit")); } else { m_pcedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luedit"))) { m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit")); } else { m_luedit = System.DateTime.MinValue; }; m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt")); } return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } finally { if (rdr != null) { rdr.Close(); } } }
/// <summary> /// 执行存储过程或Sql语句返回DataTable /// </summary> /// <param name="sql">存储过程名称或Sql语句</param> /// <param name="commandType">命令类型</param> /// <param name="paras">参数</param> /// <returns></returns> private DataTable Run(string sql, CommandType commandType, params object[] paras) { DataTable table = new DataTable(); switch (this.DBType) { case DBTypeEnum.SqlServer: SqlConnection con = this.Database.GetDbConnection() as SqlConnection; SqlDataAdapter adapter = new SqlDataAdapter(); using (SqlCommand cmd = new SqlCommand(sql, con)) { adapter.SelectCommand = cmd; cmd.CommandTimeout = 2400; cmd.CommandType = commandType; if (paras != null) { foreach (var param in paras) { cmd.Parameters.Add(param); } } adapter.Fill(table); adapter.SelectCommand.Parameters.Clear(); } break; case DBTypeEnum.MySql: MySqlConnection mySqlCon = this.Database.GetDbConnection() as MySqlConnection; using (MySqlCommand cmd = new MySqlCommand(sql, mySqlCon)) { if (mySqlCon.State == ConnectionState.Closed) { mySqlCon.Open(); } cmd.CommandTimeout = 2400; cmd.CommandType = commandType; if (paras != null) { foreach (var param in paras) { cmd.Parameters.Add(param); } } MySqlDataReader dr = cmd.ExecuteReader(); table.Load(dr); dr.Close(); mySqlCon.Close(); } break; case DBTypeEnum.PgSql: Npgsql.NpgsqlConnection npgCon = this.Database.GetDbConnection() as Npgsql.NpgsqlConnection; using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, npgCon)) { if (npgCon.State == ConnectionState.Closed) { npgCon.Open(); } cmd.CommandTimeout = 2400; cmd.CommandType = commandType; if (paras != null) { foreach (var param in paras) { cmd.Parameters.Add(param); } } Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader(); table.Load(dr); dr.Close(); npgCon.Close(); } break; } return(table); }
public WindowArchive() { InitializeComponent(); AppWPF app = (AppWPF)Application.Current; MainWindow window = (MainWindow)Application.Current.MainWindow; if (app.ConfigProgramBin.UseDatabase) { //SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder(); //Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName; //Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName; //if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI) //{ // Sqlbuilder.IntegratedSecurity = true; //} //else //{ // Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName; // Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword; //} string connstring = String.Format("Server={0};Port={1};" + "User Id={2};Password={3};Database={4};", app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName, app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName); Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring); string sql = "Select table_name FROM information_schema.tables WHERE table_schema = 'public'"; Npgsql.NpgsqlCommand command = null; try { conn.Open(); command = new Npgsql.NpgsqlCommand(sql, conn); Npgsql.NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { string table = reader.GetString(0); CollectionTables.Add(table.Replace('_', ' ')); } reader.Close(); LBTables.ItemsSource = CollectionTables; LBTables.SelectionMode = SelectionMode.Multiple; Binding bindingLoadTable = new Binding(); bindingLoadTable.Source = LBTables; bindingLoadTable.Path = new PropertyPath("SelectedItem"); bindingLoadTable.Converter = new RemoveButtonConverter(); Binding bindingLoadTableChart = new Binding(); bindingLoadTableChart.Source = LBTables; bindingLoadTableChart.Path = new PropertyPath("SelectedItem"); bindingLoadTableChart.Converter = new RemoveButtonConverter(); Binding bindingGetOptionalData = new Binding(); bindingGetOptionalData.Source = LBTables; bindingGetOptionalData.Path = new PropertyPath("SelectedItem"); bindingGetOptionalData.Converter = new RemoveButtonConverter(); Binding bindingGetOptionalData2 = new Binding(); bindingGetOptionalData2.Source = CHBAverage; bindingGetOptionalData2.Path = new PropertyPath("IsChecked"); Binding bindingGetOptionalData3 = new Binding(); bindingGetOptionalData3.Source = CHBSum; bindingGetOptionalData3.Path = new PropertyPath("IsChecked"); Binding bindingGetOptionalData4 = new Binding(); bindingGetOptionalData4.Source = CHBMax; bindingGetOptionalData4.Path = new PropertyPath("IsChecked"); Binding bindingGetOptionalData5 = new Binding(); bindingGetOptionalData5.Source = CHBMin; bindingGetOptionalData5.Path = new PropertyPath("IsChecked"); Binding bindingGetOptionalData6 = new Binding(); bindingGetOptionalData6.Source = CHBIntegralSum; bindingGetOptionalData6.Path = new PropertyPath("IsChecked"); MultiBinding mBindingGetOptionalData = new MultiBinding(); mBindingGetOptionalData.Converter = new GetOptionalData(); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData2); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData3); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData4); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData5); mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData6); BLoadTable.SetBinding(Button.IsEnabledProperty, bindingLoadTable); BLoadTableChart.SetBinding(Button.IsEnabledProperty, bindingLoadTableChart); BGetOptionalData.SetBinding(Button.IsEnabledProperty, mBindingGetOptionalData); DataPickerForm.DisplayDateEnd = DateTime.Now; DataPickerForm.ToolTip = "Диапазон даты от"; DataPickerForm.SelectedDateChanged += DataPickerForm_SelectedDateChanged; DataPickerForm.SelectedDate = DateTime.Now; TimePickerForm.ValueChanged += TimePickerForm_ValueChanged; TimePickerForm.ToolTip = "Диапазон времени от"; TimePickerForm.Format = TimeFormat.Custom; TimePickerForm.FormatString = "HH:mm:ss"; if (DateTime.Now.Hour == 0) { TimePickerForm.Value = DateTime.Now; } else { TimePickerForm.Value = DateTime.Now.AddHours(-1); } DataPickerTo.DisplayDateEnd = DateTime.Now; DataPickerTo.ToolTip = "Диапазон даты до"; DataPickerTo.SelectedDateChanged += DataPickerTo_SelectedDateChanged; DataPickerTo.SelectedDate = DateTime.Now; TimePickerTo.ValueChanged += TimePickerTo_ValueChanged; TimePickerTo.ToolTip = "Диапазон времени до"; TimePickerTo.Format = TimeFormat.Custom; TimePickerTo.FormatString = "HH:mm:ss"; TimePickerTo.Value = DateTime.Now; } catch (SystemException ex) { if (window.CollectionMessage.Count > 300) { window.CollectionMessage.RemoveAt(0); window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now); } else { window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now); } //if (ex is SqlException) //{ // SqlException sqlex = ex as SqlException; // foreach (SqlError er in sqlex.Errors) // { // if (window.WindowErrorMessages.LBMessageError.Text.Length > 0) // { // window.CountLineTextMessage++; // window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now; // } // else // { // window.CountLineTextMessage++; // window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now; // } // } //} } finally { conn.Close(); conn.Dispose(); if (command != null) { command.Dispose(); } } } }
public bool GetByPrimaryKey(string pKey) { string sQuery = "select * from tbm_customer WHERE customerid='" + pKey + "'"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); try { if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("customerid"))) { m_customerid = rdr.GetString(rdr.GetOrdinal("customerid")); } else { m_customerid = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("name"))) { m_name = rdr.GetString(rdr.GetOrdinal("name")); } else { m_name = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("identity_type"))) { m_identity_type = rdr.GetString(rdr.GetOrdinal("identity_type")); } else { m_identity_type = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("identity_number"))) { m_identity_number = rdr.GetString(rdr.GetOrdinal("identity_number")); } else { m_identity_number = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("gender"))) { m_gender = rdr.GetString(rdr.GetOrdinal("gender")); } else { m_gender = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("phonenumber"))) { m_phoneNumber = rdr.GetDecimal(rdr.GetOrdinal("phonenumber")); } else { m_phoneNumber = 0; }; if (!rdr.IsDBNull(rdr.GetOrdinal("dateofbirth"))) { m_dob = rdr.GetDateTime(rdr.GetOrdinal("dateofbirth")); } else { m_dob = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("address"))) { m_address = rdr.GetString(rdr.GetOrdinal("address")); } else { m_address = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opadd"))) { m_opadd = rdr.GetString(rdr.GetOrdinal("opadd")); } else { m_opadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd"))) { m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd")); } else { m_pcadd = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luadd"))) { m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd")); } else { m_luadd = System.DateTime.MinValue; }; if (!rdr.IsDBNull(rdr.GetOrdinal("opedit"))) { m_opedit = rdr.GetString(rdr.GetOrdinal("opedit")); } else { m_opedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit"))) { m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit")); } else { m_pcedit = ""; }; if (!rdr.IsDBNull(rdr.GetOrdinal("luedit"))) { m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit")); } else { m_luedit = System.DateTime.MinValue; }; m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt")); } return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } finally { if (rdr != null) { rdr.Close(); } } }
public override void Close() { rdr.Close(); }