public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlCommand v_cmd = null;
Npgsql.NpgsqlDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo PostgreSQL usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Npgsql.NpgsqlCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public void Close()
{
if (comm != null)
{
if (comm.Transaction != null)
{
try
{
if ((System.Runtime.InteropServices.Marshal.GetExceptionCode() == -858993460) ||
(System.Runtime.InteropServices.Marshal.GetExceptionCode() == 0))
{
comm.Transaction.Commit();
}
else
{
comm.Transaction.Rollback();
}
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
}
comm.Connection.Close();
comm.Dispose();
}
}
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();
}
}
}
}
private void cbox_table_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
BindingSource bs = new BindingSource();
ObjectThread data_data_table = new ObjectThread(dgv_data_table);
if (connect == null)
{
Utils.WriteLog("Veillez reselectionner la liaison");
}
if (connect.State == System.Data.ConnectionState.Closed)
{
connect.Open();
}
data_data_table.ClearDataGridView(true);
if (cbox_table.SelectedItem.Equals("users"))
{
//Chargement des utilisateurs de liaison
bs.DataSource = employes;
externe.DataSource = bs;
new Thread(delegate()
{
Npgsql.NpgsqlCommand cmd = null;
Npgsql.NpgsqlDataReader lect = null;
try
{
string query = "SELECT coderep, CONCAT(nom, ' ', prenom), externe FROM users ORDER BY coderep";
cmd = new Npgsql.NpgsqlCommand(query, connect);
lect = cmd.ExecuteReader();
if (lect.HasRows)
{
int i = 0;
while (lect.Read())
{
var _externe = lect[2];
int index = _externe != null ? employes.FindIndex(x => x.Split(',')[0].Equals(_externe.ToString())) : -1;
data_data_table.WriteDataGridView(new object[] { lect[0].ToString(), lect[1].ToString(), null });
if (index > -1 ? employes.Count > index ? dgv_data_table.Rows.Count > i : false : false)
{
ObjectThread cell = new ObjectThread(dgv_data_table.Rows[i].Cells[2] as DataGridViewComboBoxCell);
cell.ValueComboBoxCell(employes[index]);
}
i++;
}
}
}
catch (Exception ex)
{
Messages.Exception(ex);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (lect != null)
{
lect.Dispose();
}
}
}).Start();
}
else if (cbox_table.SelectedItem.Equals("tranchehoraire"))
{
bs.DataSource = tranches;
//Chargement des utilisateurs de liaison
externe.DataSource = bs;
new Thread(delegate()
{
Npgsql.NpgsqlCommand cmd = null;
Npgsql.NpgsqlDataReader lect = null;
try
{
string query = "SELECT id, CONCAT(typedejrnee, ' ', CONCAT(heure_debut, '-', heure_fin)), externe FROM tranchehoraire ORDER BY typedejrnee, heure_debut";
cmd = new Npgsql.NpgsqlCommand(query, connect);
lect = cmd.ExecuteReader();
if (lect.HasRows)
{
int i = 0;
while (lect.Read())
{
var _externe = lect[2];
int index = _externe != null ? tranches.FindIndex(x => x.Split(',')[0].Equals(_externe.ToString())) : -1;
data_data_table.WriteDataGridView(new object[] { lect[0].ToString(), lect[1].ToString(), null });
if (index > -1 ? tranches.Count > index ? dgv_data_table.Rows.Count > i : false : false)
{
var var = dgv_data_table.Rows[i].Cells[2];
if (var is DataGridViewComboBoxCell)
{
ObjectThread cell = new ObjectThread(var as DataGridViewComboBoxCell);
cell.ValueComboBoxCell(tranches[index]);
}
}
i++;
}
}
}
catch (Exception ex)
{
Messages.Exception(ex);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (lect != null)
{
lect.Dispose();
}
}
}).Start();
}
}
catch (Exception ex)
{
Messages.Exception(ex);
}
}
