1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
| string connectionString = " server= localhost;Port=3306;Database=cacti;Uid=root";
MySqlConnection conn2 = new MySqlConnection(connectionString);
MySqlCommand command2 = conn2.CreateCommand();
command2.CommandText = " SELECT * FROM host WHERE (disabled = '') and (status = 1)";
conn2.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet dt = new DataSet("ds1");
adapter.SelectCommand = command2;
adapter.Fill(dt);
DataTable ds1 = new DataTable();
ds1 = dt.Tables[0];
DataTableReader oReader = ds1.CreateDataReader();
int id;
string description;
string hostname;
string disabled = null;
int monitor;
int status;
DateTime status_fail_date;
int id_master;
string my_key;
while (oReader.Read())
{
id = oReader["description"];
description = (oReader.GetOrdinal("description"));
hostname = (oReader.GetOrdinal("hostname"));
disabled = (oReader.GetOrdinal("disabled"));
monitor = (oReader.GetOrdinal("monitor"));
status =(oReader.GetOrdinal("status"));
status_fail_date = (oReader.GetOrdinal("status_fail_date"));
id_master =(oReader.GetOrdinal("id_master"));
my_key = (oReader.GetOrdinal("my_key"));
dt.Tables.Add();
DataColumn dc_id = new DataColumn("id",
System.Type.GetType("System.Int32"));
DataColumn dc_description = new DataColumn("description",
System.Type.GetType("System.String"));
DataColumn dc_hostname = new DataColumn("hostname",
System.Type.GetType("System.String"));
DataColumn dc_disabled = new DataColumn("disabled",
System.Type.GetType("System.String"));
DataColumn dc_monitor = new DataColumn("monitor",
System.Type.GetType("System.String"));
DataColumn dc_status = new DataColumn("status",
System.Type.GetType("System.Int32"));
DataColumn dc_status_fail_date = new DataColumn("status_fail_date",
System.Type.GetType("System.Int32"));
DataColumn dc_id_master = new DataColumn("id_master",
System.Type.GetType("System.DateTime"));
DataColumn dc_my_key = new DataColumn("my_key",
System.Type.GetType("System.String"));
DataRow dr;
dr = ds1.NewRow();
dr["id"] = id;
dr["description"] = description;
dr["hostname"] = hostname;
dr["disabled"] = disabled;
dr["monitor"] = monitor;
dr["status"] = status;
dr["status_fail_date"] = status_fail_date;
dr["id_master"] = id_master;
dr["my_key"] = my_key;
ds1.Rows.Add(dr);
}
oReader.Close();
conn2.Close();
string connectString = " server= " + nom_serveur + " ;Port=3306;Database=supervision;Uid=root";
MySqlConnection conn3 = new MySqlConnection(connectString);
MySqlCommand command3 = conn3.CreateCommand();
//command3.CommandText = "INSERT INTO stat (id,description,hostname,disabled,monitor,status,status_fail_date,id_master,my_key) values (@id,@description,@hostname,@disabled,@monitor,@status,@status_fail_date,@id_master,@my_key) ";
conn3.Open();
// insertion des lignes
for (int i = 0; i < ds1.Rows.Count; i++)
{
string requête = " INSERT INTO stat (id,description,hostname,disabled,monitor,status,status_fail_date,id_master,my_key) values (@id,@description,@hostname,@disabled,@monitor,@status,@status_fail_date,@id_master,@my_key) ";
MySqlCommand sqlCommand = new MySqlCommand(requête, conn3);
sqlCommand.Parameters.AddWithValue("@id", ds1.Rows[i]["id"]);
sqlCommand.Parameters.AddWithValue("@description", ds1.Rows[i]["description"]);
sqlCommand.Parameters.AddWithValue("@disabled", ds1.Rows[i]["disabled"]);
sqlCommand.Parameters.AddWithValue("@monitor", ds1.Rows[i]["monitor"]);
sqlCommand.Parameters.AddWithValue("@status", ds1.Rows[i]["status"]);
sqlCommand.Parameters.AddWithValue("@status_fail_date", ds1.Rows[i]["status_fail_date"]);
sqlCommand.Parameters.AddWithValue("@id_master", ds1.Rows[i]["id_master"]);
sqlCommand.Parameters.AddWithValue("@my_key", ds1.Rows[i]["my_key"]);
// compilation de la commande
sqlCommand.Prepare();
sqlCommand.Parameters["@id"].Value = ds1.Rows[i]["id"];
sqlCommand.Parameters["@description"].Value = ds1.Rows[i]["description"];
sqlCommand.Parameters["@disabled"].Value = ds1.Rows[i]["disabled"];
sqlCommand.Parameters["@monitor"].Value = ds1.Rows[i]["monitor"];
sqlCommand.Parameters["@status"].Value = ds1.Rows[i]["statut"];
sqlCommand.Parameters["@status_fail_date"].Value = ds1.Rows[i]["statut_fail_date"];
sqlCommand.Parameters["@id_master"].Value = ds1.Rows[i]["id_master"];
sqlCommand.Parameters["@my_key"].Value = ds1.Rows[i]["my_key"];
// string nom_serveur = ds.TableName["host"].Rows[i]["user_server"].ToString();
// exécution requête
sqlCommand.ExecuteNonQuery();
}
conn3.Close();
}
}
}
} |
select et insert dans deux bases sql
Répondre avec citation
Partager