C# .NET - Sending a datasructure to a database? [C#]

Asked By Vladimir Gonzalez on 20-Mar-12 04:06 AM
Earn up to 10 extra points for answering this tough question.
Dear Reader,

I got a problem with my code i want to send information taken from a twincat plc system to my database.
Everything works fine expect for the last part the actual sending to the DB. If you look @ the code go to this part tbComplexStruct_Datavalues.Text String.Format (It's on line 330)
There you can find all the datavalues i pick up out of the twincat system these has to be sended to a mysql db this happens @   private void Main2() (It's on line 261)  I really hope someone could help me out with this since i am messing arround with it for weeks. Thanks in advance.

001.using System;
002.using System.Drawing;
003.using System.Collections;
004.using System.ComponentModel;
005.using System.Windows.Forms;
006.using System.Data;
007.using System.Runtime.InteropServices;
008.using TwinCAT.Ads;
009.using MySql.Data.MySqlClient;
010.using System.Timers;
011. 
012.namespace Sample07
013.{
014.  /// <summary>
015.  /// Summary description for Form1.
016.  /// </summary>
017.  public class Form1 : System.Windows.Forms.Form
018.  {
019.  internal System.Windows.Forms.Button btnDeleteNotifications;
020.  internal System.Windows.Forms.Button btnAddNotifications;
021. 
022.  internal System.Windows.Forms.Button btnRead;
023.  internal System.Windows.Forms.GroupBox GroupBox3;
024.  internal System.Windows.Forms.TextBox tbComplexStruct_Datavalues;
025.  internal System.Windows.Forms.Label Label14;
026. 
027.  internal System.Windows.Forms.TextBox tbComplexStruct_stringVal;
028.  internal System.Windows.Forms.Label Label13;
029. 
030. 
031.  internal System.Windows.Forms.TextBox tbComplexStruct_Countervalues;
032.  internal System.Windows.Forms.Label Label12;
033.  private IContainer components;
034. 
035.  //PLC variable handles
036. 
037.  private int hcomplexStruct;
038.  private ArrayList notificationHandles;
039.  private System.Windows.Forms.Timer timer1;
040. 
041.  private TcAdsClient adsClient;
042. 
043.  public Form1()
044.  {
045.    //
046.    // Required for Windows Form Designer support
047.    //
048.    InitializeComponent();
049. 
050.    //
051.    // TODO: Add any constructor code after InitializeComponent call
052.    //
053.  }
054. 
055.  /// <summary>
056.  /// Clean up any resources being used.
057.  /// </summary>
058.  protected override void Dispose(bool disposing)
059.  {
060.    if (disposing)
061.    {
062.    if (components != null)
063.    {
064.      components.Dispose();
065.    }
066.    }
067.    base.Dispose(disposing);
068.  }
069. 
070.  #region Windows Form Designer generated code
071.  /// <summary>
072.  /// Required method for Designer support - do not modify
073.  /// the contents of this method with the code editor.
074.  /// </summary>
075.  private void InitializeComponent()
076.  {
077.    this.components = new System.ComponentModel.Container();
078.    this.btnDeleteNotifications = new System.Windows.Forms.Button();
079.    this.btnAddNotifications = new System.Windows.Forms.Button();
080.    this.btnRead = new System.Windows.Forms.Button();
081.    this.GroupBox3 = new System.Windows.Forms.GroupBox();
082.    this.tbComplexStruct_Datavalues = new System.Windows.Forms.TextBox();
083.    this.Label14 = new System.Windows.Forms.Label();
084.    this.tbComplexStruct_stringVal = new System.Windows.Forms.TextBox();
085.    this.Label13 = new System.Windows.Forms.Label();
086.    this.tbComplexStruct_Countervalues = new System.Windows.Forms.TextBox();
087.    this.Label12 = new System.Windows.Forms.Label();
088.    this.timer1 = new System.Windows.Forms.Timer(this.components);
089.    this.GroupBox3.SuspendLayout();
090.    this.SuspendLayout();
091.    // 
092.    // btnDeleteNotifications
093.    // 
094.    this.btnDeleteNotifications.Location = new System.Drawing.Point(975, 96);
095.    this.btnDeleteNotifications.Name = "btnDeleteNotifications";
096.    this.btnDeleteNotifications.Size = new System.Drawing.Size(112, 23);
097.    this.btnDeleteNotifications.TabIndex = 13;
098.    this.btnDeleteNotifications.Text = "Delete Notifications";
099.    this.btnDeleteNotifications.Click += new System.EventHandler(this.btnDeleteNotifications_Click);
100.    // 
101.    // btnAddNotifications
102.    // 
103.    this.btnAddNotifications.Location = new System.Drawing.Point(975, 61);
104.    this.btnAddNotifications.Name = "btnAddNotifications";
105.    this.btnAddNotifications.Size = new System.Drawing.Size(112, 23);
106.    this.btnAddNotifications.TabIndex = 12;
107.    this.btnAddNotifications.Text = "Add Notifications";
108.    this.btnAddNotifications.Click += new System.EventHandler(this.btnAddNotifications_Click);
109.    // 
110.    // btnRead
111.    // 
112.    this.btnRead.Location = new System.Drawing.Point(975, 28);
113.    this.btnRead.Name = "btnRead";
114.    this.btnRead.Size = new System.Drawing.Size(112, 23);
115.    this.btnRead.TabIndex = 10;
116.    this.btnRead.Text = "Read";
117.    this.btnRead.Click += new System.EventHandler(this.btnRead_Click);
118.    // 
119.    // GroupBox3
120.    // 
121.    this.GroupBox3.Controls.Add(this.tbComplexStruct_Datavalues);
122.    this.GroupBox3.Controls.Add(this.Label14);
123.    this.GroupBox3.Controls.Add(this.tbComplexStruct_stringVal);
124.    this.GroupBox3.Controls.Add(this.Label13);
125.    this.GroupBox3.Controls.Add(this.tbComplexStruct_Countervalues);
126.    this.GroupBox3.Controls.Add(this.Label12);
127.    this.GroupBox3.Location = new System.Drawing.Point(12, 8);
128.    this.GroupBox3.Name = "GroupBox3";
129.    this.GroupBox3.Size = new System.Drawing.Size(957, 280);
130.    this.GroupBox3.TabIndex = 9;
131.    this.GroupBox3.TabStop = false;
132.    this.GroupBox3.Text = "Data Structure";
133.    this.GroupBox3.Enter += new System.EventHandler(this.GroupBox3_Enter);
134.    // 
135.    // tbComplexStruct_Datavalues
136.    // 
137.    this.tbComplexStruct_Datavalues.Location = new System.Drawing.Point(105, 53);
138.    this.tbComplexStruct_Datavalues.Name = "tbComplexStruct_Datavalues";
139.    this.tbComplexStruct_Datavalues.Size = new System.Drawing.Size(835, 20);
140.    this.tbComplexStruct_Datavalues.TabIndex = 20;
141.    this.tbComplexStruct_Datavalues.TextChanged += new System.EventHandler(this.tbComplexStruct_Datavalues_TextChanged);
142.    // 
143.    // Label14
144.    // 
145.    this.Label14.Location = new System.Drawing.Point(6, 60);
146.    this.Label14.Name = "Label14";
147.    this.Label14.Size = new System.Drawing.Size(99, 16);
148.    this.Label14.TabIndex = 19;
149.    this.Label14.Text = "Data array:";
150.    this.Label14.Click += new System.EventHandler(this.Label14_Click);
151.    // 
152.    // tbComplexStruct_stringVal
153.    // 
154.    this.tbComplexStruct_stringVal.Location = new System.Drawing.Point(105, 105);
155.    this.tbComplexStruct_stringVal.Name = "tbComplexStruct_stringVal";
156.    this.tbComplexStruct_stringVal.Size = new System.Drawing.Size(835, 20);
157.    this.tbComplexStruct_stringVal.TabIndex = 11;
158.    this.tbComplexStruct_stringVal.TextChanged += new System.EventHandler(this.tbComplexStruct_stringVal_TextChanged);
159.    // 
160.    // Label13
161.    // 
162.    this.Label13.Location = new System.Drawing.Point(6, 105);
163.    this.Label13.Name = "Label13";
164.    this.Label13.Size = new System.Drawing.Size(109, 16);
165.    this.Label13.TabIndex = 10;
166.    this.Label13.Text = "stringValues:";
167.    this.Label13.Click += new System.EventHandler(this.Label13_Click);
168.    // 
169.    // tbComplexStruct_Countervalues
170.    // 
171.    this.tbComplexStruct_Countervalues.Location = new System.Drawing.Point(105, 160);
172.    this.tbComplexStruct_Countervalues.Name = "tbComplexStruct_Countervalues";
173.    this.tbComplexStruct_Countervalues.Size = new System.Drawing.Size(835, 20);
174.    this.tbComplexStruct_Countervalues.TabIndex = 18;
175.    this.tbComplexStruct_Countervalues.TextChanged += new System.EventHandler(this.tbComplexStruct_Countervalues_TextChanged);
176.    // 
177.    // Label12
178.    // 
179.    this.Label12.Location = new System.Drawing.Point(6, 157);
180.    this.Label12.Name = "Label12";
181.    this.Label12.Size = new System.Drawing.Size(100, 23);
182.    this.Label12.TabIndex = 17;
183.    this.Label12.Text = "Counter array:";
184.    this.Label12.Click += new System.EventHandler(this.Label12_Click);
185.    // 
186.    // timer1
187.    // 
188.    this.timer1.Enabled = true;
189.    this.timer1.Interval = 10000;
190.    this.timer1.Tick += new System.EventHandler(this.btnRead_Click);
191.    // 
192.    // Form1
193.    // 
194.    this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
195.    this.ClientSize = new System.Drawing.Size(1181, 302);
196.    this.Controls.Add(this.btnDeleteNotifications);
197.    this.Controls.Add(this.btnAddNotifications);
198.    this.Controls.Add(this.btnRead);
199.    this.Controls.Add(this.GroupBox3);
200.    this.Name = "Form1";
201.    this.Text = "Readout PLC";
202.    this.Closing += new System.ComponentModel.CancelEventHandler(this.Form1_Closing);
203.    this.Load += new System.EventHandler(this.Form1_Load);
204.    this.GroupBox3.ResumeLayout(false);
205.    this.GroupBox3.PerformLayout();
206.    this.ResumeLayout(false);
207. 
208.  }
209.  #endregion
210. 
211.  /// <summary>
212.  /// The main entry point for the application.
213.  /// </summary>
214.  [STAThread]
215.  static void Main()
216.  {
217.    Application.Run(new Form1());
218.  }
219. 
220.  private void Form1_Load(object sender, System.EventArgs e)
221.  {
222.    adsClient = new TcAdsClient();
223.    notificationHandles = new ArrayList();
224.    try
225.    {
226.    adsClient.Connect(801);
227.    adsClient.AdsNotificationEx += new AdsNotificationExEventHandler(adsClient_AdsNotificationEx);
228.    btnDeleteNotifications.Enabled = false;
229.    //create handles for the PLC variables;
230. 
231.    hcomplexStruct = adsClient.CreateVariableHandle("MAIN.ComplexStruct1");
232.    }
233.    catch (Exception ex)
234.    {
235.    MessageBox.Show(ex.Message);
236.    }
237.  }
238. 
239.  private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
240.  {
241.    adsClient.Dispose();
242.  }
243. 
244. 
245.  private void btnRead_Click(object sender, System.EventArgs e)
246.  {
247.    Main2();
248.    try
249.    {
250.    //read by handle
251.    //the second parameter specifies the type of the variable
252. 
253.    FillStructControls((ComplexStruct)adsClient.ReadAny(hcomplexStruct, typeof(ComplexStruct)));
254.    }
255.    catch (Exception ex)
256.    {
257.    MessageBox.Show(ex.Message);
258.    }
259.  }
260. 
261.  private void Main2()
262.  {
263.    {
264.    string cs = @"server=localhost;userid=root;
265.    password=flex01;database=gen1";
266. 
267.    for (int i = 0; i < 5; i++)
268.    {
269.      using (var conn = new MySqlConnection(cs))
270.      using (var cmd = conn.CreateCommand())
271.      {
272.      conn.Open();
273.      cmd.CommandText = "INSERT INTO actualvalues (a,b,c) VALUES ('structure.datavalues[0],'structure.Datavalues[1]','structure.Datavalues[2]')";
274.      cmd.ExecuteNonQuery();
275. 
276.      }
277.    }
278.    }
279.  }
280.  private void btnAddNotifications_Click(object sender, System.EventArgs e)
281.  {
282.    notificationHandles.Clear();
283.    try
284.    {
285.    //register notification    
286. 
287.    notificationHandles.Add(adsClient.AddDeviceNotificationEx("MAIN.complexStruct1", AdsTransMode.OnChange, 100, 0, Datavalues, typeof(ComplexStruct)));
288.    }
289.    catch (Exception ex)
290.    {
291.    MessageBox.Show(ex.Message);
292.    }
293.    btnDeleteNotifications.Enabled = true;
294.    btnAddNotifications.Enabled = false;
295.  }
296. 
297.  private void btnDeleteNotifications_Click(object sender, System.EventArgs e)
298.  {
299.    //delete registered notifications.
300.    try
301.    {
302.    foreach (int handle in notificationHandles)
303.      adsClient.DeleteDeviceNotification(handle);
304.    }
305.    catch (Exception ex)
306.    {
307.    MessageBox.Show(ex.Message);
308.    }
309.    notificationHandles.Clear();
310.    btnAddNotifications.Enabled = true;
311.    btnDeleteNotifications.Enabled = false;
312.  }
313. 
314.  private void adsClient_AdsNotificationEx(object sender, AdsNotificationExEventArgs e)
315.  {
316.    TextBox textBox = (TextBox)e.UserData;
317.    Type type = e.Value.GetType();
318.    if (type == typeof(string) || type.IsPrimitive)
319.    textBox.Text = e.Value.ToString();
320.    else if (type == typeof(ComplexStruct))
321.    FillStructControls((ComplexStruct)e.Value);
322.  }
323. 
324.  private void FillStructControls(ComplexStruct structure)
325.  {
326. 
327.    tbComplexStruct_Datavalues.Text = String.Format(
328.    "{0:d}, {1:d}, {2:d}, {3:d}, {4:d}, {5:d}, {6:d}, {7:d}, {8:d}, {9:d}, {10:d}, {11:d}, {12:d}, {13:d}, {14:d}, {15:d}, {16:d}, {17:d}, {18:d}, {19:d}, {20:d}, {21:d}, {22:d}, {23:d}, {24:d},{25:d}, {26:d}, {27:d}, {28:d},{29:d},{30:d},{31:d}, {32:d}, {33:d}, {34:d},{35:d}, {36:d}, {37:d}, {38:d},{39:d}, {40:d}, {41:d}, {42:d}, {43:d}, {44:d},{45:d}, {46:d}, {47:d}, {48:d},{49:d}, {50:d}, {51:d}, {52:d}, {53:d}, {54:d},{55:d}, {56:d}, {57:d}, {58:d},{59:d}, {60:d}, {51:d}, {52:d}, {53:d}, {54:d},{55:d}, {56:d}, {57:d}, {58:d},{59:d}, {60:d}, {61:d}, {62:d}, {63:d}, {64:d},{65:d}, {66:d}, {67:d}, {68:d},{69:d}, {70:d}, {71:d}, {72:d}, {73:d}, {74:d},{75:d}, {76:d}, {77:d}, {78:d},{79:d}, {80:d}, {81:d}, {82:d}, {83:d}, {84:d},{85:d}, {86:d}, {87:d}, {88:d},{89:d}, {90:d}, {91:d}, {92:d}, {93:d}, {94:d},{95:d}, {96:d}, {97:d}, {98:d},{99:d}, {100:d}"
329. 
330.    , structure.Datavalues[0], structure.Datavalues[1], structure.Datavalues[2], structure.Datavalues[3],
331.    structure.Datavalues[4], structure.Datavalues[5], structure.Datavalues[6], structure.Datavalues[7],
332.    structure.Datavalues[8], structure.Datavalues[9], structure.Datavalues[10], structure.Datavalues[11],
333.    structure.Datavalues[12], structure.Datavalues[13], structure.Datavalues[14], structure.Datavalues[15],
334.    structure.Datavalues[16], structure.Datavalues[17], structure.Datavalues[18], structure.Datavalues[19],
335.    structure.Datavalues[20], structure.Datavalues[21], structure.Datavalues[22], structure.Datavalues[23],
336.    structure.Datavalues[24], structure.Datavalues[25], structure.Datavalues[26], structure.Datavalues[27],
337.    structure.Datavalues[28], structure.Datavalues[29], structure.Datavalues[30], structure.Datavalues[31],
338.    structure.Datavalues[32], structure.Datavalues[33], structure.Datavalues[34], structure.Datavalues[35],
339.    structure.Datavalues[36], structure.Datavalues[37], structure.Datavalues[38], structure.Datavalues[39],
340.    structure.Datavalues[40], structure.Datavalues[11], structure.Datavalues[42], structure.Datavalues[43],
341.    structure.Datavalues[44], structure.Datavalues[45], structure.Datavalues[46], structure.Datavalues[47],
342.    structure.Datavalues[48], structure.Datavalues[49], structure.Datavalues[50], structure.Datavalues[51],
343.    structure.Datavalues[52], structure.Datavalues[53], structure.Datavalues[54], structure.Datavalues[55],
344.    structure.Datavalues[56], structure.Datavalues[57], structure.Datavalues[58], structure.Datavalues[59],
345.    structure.Datavalues[60], structure.Datavalues[61], structure.Datavalues[62], structure.Datavalues[63],
346.    structure.Datavalues[64], structure.Datavalues[65], structure.Datavalues[66], structure.Datavalues[67],
347.    structure.Datavalues[68], structure.Datavalues[69], structure.Datavalues[70], structure.Datavalues[71],
348.    structure.Datavalues[72], structure.Datavalues[73], structure.Datavalues[74], structure.Datavalues[75],
349.    structure.Datavalues[76], structure.Datavalues[77], structure.Datavalues[78], structure.Datavalues[79],
350.    structure.Datavalues[80], structure.Datavalues[81], structure.Datavalues[82], structure.Datavalues[83],
351.     structure.Datavalues[84], structure.Datavalues[85], structure.Datavalues[86], structure.Datavalues[87],
352.    structure.Datavalues[88], structure.Datavalues[89], structure.Datavalues[90], structure.Datavalues[91],
353.    structure.Datavalues[92], structure.Datavalues[93], structure.Datavalues[94], structure.Datavalues[95],
354.    structure.Datavalues[96], structure.Datavalues[97], structure.Datavalues[98], structure.Datavalues[99],
355.    structure.Datavalues[100]
356.    );
357. 
358.    tbComplexStruct_stringVal.Text = structure.stringVal;
359. 
360. 
361.    tbComplexStruct_Countervalues.Text = String.Format(
362.    "{0:d}, {1:d}, {2:d}, {3:d}, {4:d}, {5:d}, {6:d}, {7:d}, {8:d}, {9:d}, {10:d}"
363. 
364.     , structure.Countervalues[0], structure.Countervalues[1], structure.Countervalues[2], structure.Countervalues[3],
365.     structure.Countervalues[4], structure.Countervalues[5], structure.Countervalues[6], structure.Countervalues[7],
366.     structure.Countervalues[8], structure.Countervalues[9], structure.Countervalues[10]);
367. 
368.  }
369. 
370.  private ComplexStruct GetStructFromControls()
371.  {
372.    ComplexStruct structure = new ComplexStruct();
373.    String[] stringArr = tbComplexStruct_Datavalues.Text.Split(new char[] { ',' });
374. 
375.    for (short i = 0; i < stringArr.Length; i++)
376.    structure.Datavalues[i] = short.Parse(stringArr[i]);
377. 
378. 
379.    structure.stringVal = tbComplexStruct_stringVal.Text;
380. 
381.    String[] stringArra = tbComplexStruct_Countervalues.Text.Split(new char[] { ',' });
382.    for (int i = 0; i < stringArra.Length; i++)
383.    structure.Countervalues[i] = int.Parse(stringArra[i]);
384. 
385.    return structure;
386.  }
387. 
388.  public object Datavalues { get; set; }
389. 
390.  private void tbComplexStruct_Datavalues_TextChanged(object sender, EventArgs e)
391.  {
392.  }
393. 
394.  private void Label14_Click(object sender, EventArgs e)
395.  {
396.  }
397. 
398.  private void tbComplexStruct_stringVal_TextChanged(object sender, EventArgs e)
399.  {
400.  }
401. 
402.  private void tbComplexStruct_Countervalues_TextChanged(object sender, EventArgs e)
403.  {
404.  }
405. 
406.  private void Label12_Click(object sender, EventArgs e)
407.  {
408.  }
409. 
410.  private void Label13_Click(object sender, EventArgs e)
411.  {
412. 
413.  }
414. 
415.  private void GroupBox3_Enter(object sender, EventArgs e)
416.  {
417. 
418.  }
419. 
420.  private void textBox1_TextChanged(object sender, EventArgs e)
421.  {
422. 
423.  }
424. 
425.  private void timer1_Tick(object sender, EventArgs e)
426.  {
427. 
428.  }
429.  }
430. 
431.  [StructLayout(LayoutKind.Sequential, Pack = 1)]
432.  public class ComplexStruct
433.  {
434.  //specifies how .NET should marshal the array
435.  //SizeConst specifies the number of elements the array has.
436.  [MarshalAs(UnmanagedType.ByValArray, SizeConst = 101)]
437.  public short[] Datavalues = new short[101];
438. 
439.  //specifies how .NET should marshal the string
440.  //SizeConst specifies the number of characters the string has.
441.  //'(inclusive the terminating null ). 
442.  [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 81)]
443.  public string stringVal = "";
444. 
445.  //SizeConst specifies the number of elements the array has.
446.  [MarshalAs(UnmanagedType.ByValArray, SizeConst = 11)]
447.  public int[] Countervalues = new int[11];
448.  }
449.}
[)ia6l0 iii replied to Vladimir Gonzalez on 20-Mar-12 12:42 PM
The problem is in the highlighted lines below:

using (var conn = new MySqlConnection(cs))
270.      using (var cmd = conn.CreateCommand())
271.      {
272.      conn.Open();
273.      cmd.CommandText = "INSERT INTO actualvalues (a,b,c) VALUES ('structure.datavalues[0],'structure.Datavalues[1]','structure.Datavalues[2]')";
274.      cmd.ExecuteNonQuery();
275. 
276.      }


It should be like this:
using (var conn = new MySqlConnection(cs))
270.      using (var cmd = conn.CreateCommand())
271.      {
272.      conn.Open();
273.      cmd.CommandText = string.Format("INSERT INTO actualvalues (a,b,c) VALUES ('{0},'{1}','{2}')", 
structure.datavalues[0], structure.datavalues[1], structure.datavalues[2]);

274.      cmd.ExecuteNonQuery();
275. 
276.      }


And few other things that I observed:

a) Not sure why you have the for loop that runs for fixed iterations (5).
Line#267 - for (int i = 0; i < 5; i++)

b) It is a bad practice to name methods like Main2. Provide a meaningful name.
Line# 261 private void Main2()

c) And these lines can be refactored to something more readable.
Line# 327 tbComplexStruct_Datavalues.Text = String.Format...to Line# 357 structure.Datavalues[100]

StringBuilder temporaryStringBuilder = new StringBuilder();
for (int counter = 0; counter < 100; counter++)
{
                temporaryStringBuilder.Append(string.Concat(string.Format("{0:d}", structure.Datavalues[counter]), ","));
}
string finalString = temporaryStringBuilder.ToString().Substring(0, temporaryStringBuilder.Length -1);


Hope this helps.