C# SQL in CSV speichern

rIpPeRrRrR · 25. Juli 2007

Hy Leute,

ich sitze hie rnun schon seit geraumer Zeit (2 Tagen) an einem Problem.
Und zwar habe ich mir ein Programm geschrieben mit dem ich eine SQL-DB
abfrage.
Also per sqlConnection verbinde ich zur DB, per sqlCommand rufe ich die Daten
ab und per dataGrid mittels DataSet lasse ich es anzeigen.
Nun meine Frage, wie schaffe ich es die Daten aus dem dataGrid per Klick auf
einen Button im Tab CSV in eine CSV Datei zu speichern?
Hier der bisherige Code des Programms:

Code:

using System;
using System.Drawing;
using System.Windows.Forms;

namespace SQL_Abfrage
{
	/// <summary>
	/// Description of MainForm.
	/// </summary>
	public class Abfrage : System.Windows.Forms.Form
	{
		private System.Data.SqlClient.SqlCommand sqlCommand2;
		private System.Data.DataSet dataSet2;
		private System.Data.DataSet dataSet1;
		private System.Windows.Forms.DataGrid dataGrid2;
		private System.Windows.Forms.TabPage tabPage1;
		private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter2;
		private System.Windows.Forms.DataGrid dataGrid1;
		private System.Windows.Forms.TabPage tabPage2;
		private System.Windows.Forms.TabControl tabControl1;
		private System.Windows.Forms.TabPage tabPage3;
		private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
		private System.Data.SqlClient.SqlConnection sqlConnection1;
		private System.Data.SqlClient.SqlCommand sqlCommand1;
		public Abfrage()
		{
			//
			// The InitializeComponent() call is required for Windows Forms designer support.
			//
			InitializeComponent();
			sqlConnection1.Open();
			sqlDataAdapter1.Fill(dataSet1);
			sqlDataAdapter2.Fill(dataSet2);			
			//
			// TODO: Add constructor code after the InitializeComponent() call.
			//
		}
		[STAThread]
		public static void Main(string[] args)
		{
			Application.Run(new Abfrage());
		}
		#region Windows Forms Designer generated code
		/// <summary>
		/// This method is required for Windows Forms designer support.
		/// Do not change the method contents inside the source code editor. The Forms designer might
		/// not be able to load this method if it was changed manually.
		/// </summary>
		private void InitializeComponent() {
			this.sqlCommand1 = new System.Data.SqlClient.SqlCommand();
			this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
			this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
			this.tabPage3 = new System.Windows.Forms.TabPage();
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.dataSet1 = new System.Data.DataSet();
			this.tabControl1 = new System.Windows.Forms.TabControl();
			this.tabPage1 = new System.Windows.Forms.TabPage();
			this.tabPage2 = new System.Windows.Forms.TabPage();
			this.dataGrid2 = new System.Windows.Forms.DataGrid();
			this.dataSet2 = new System.Data.DataSet();
			this.sqlDataAdapter2 = new System.Data.SqlClient.SqlDataAdapter();
			this.sqlCommand2 = new System.Data.SqlClient.SqlCommand();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
			this.tabControl1.SuspendLayout();
			this.tabPage1.SuspendLayout();
			this.tabPage2.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet2)).BeginInit();
			this.SuspendLayout();
			// 
			// sqlCommand1
			// 
			this.sqlCommand1.CommandText = "SELECT * FROM Benutzer";
			this.sqlCommand1.Connection = this.sqlConnection1;
			// 
			// sqlConnection1
			// 
			this.sqlConnection1.ConnectionString = "Data Source=192.168.233.2,1433;Network Library=DBMSSOCN;Initial Catalog=Benutzerv" +
			"erwaltung;User Id=xxx;Password=xxx";
			this.sqlConnection1.FireInfoMessageEventOnUserErrors = false;
			// 
			// sqlDataAdapter1
			// 
			this.sqlDataAdapter1.SelectCommand = this.sqlCommand1;
			// 
			// tabPage3
			// 
			this.tabPage3.Location = new System.Drawing.Point(4, 22);
			this.tabPage3.Name = "tabPage3";
			this.tabPage3.Size = new System.Drawing.Size(440, 326);
			this.tabPage3.TabIndex = 2;
			this.tabPage3.Text = "CSV";
			// 
			// dataGrid1
			// 
			this.dataGrid1.CaptionForeColor = System.Drawing.SystemColors.ActiveCaptionText;
			this.dataGrid1.DataMember = "";
			this.dataGrid1.DataSource = this.dataSet1;
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(0, 0);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.ReadOnly = true;
			this.dataGrid1.Size = new System.Drawing.Size(440, 328);
			this.dataGrid1.TabIndex = 0;
			// 
			// dataSet1
			// 
			this.dataSet1.DataSetName = "NewDataSet";
			this.dataSet1.Locale = new System.Globalization.CultureInfo("de-DE");
			this.dataSet1.RemotingFormat = System.Data.SerializationFormat.Binary;
			// 
			// tabControl1
			// 
			this.tabControl1.Controls.Add(this.tabPage1);
			this.tabControl1.Controls.Add(this.tabPage2);
			this.tabControl1.Controls.Add(this.tabPage3);
			this.tabControl1.Location = new System.Drawing.Point(0, 0);
			this.tabControl1.Name = "tabControl1";
			this.tabControl1.SelectedIndex = 0;
			this.tabControl1.Size = new System.Drawing.Size(448, 352);
			this.tabControl1.TabIndex = 1;
			// 
			// tabPage1
			// 
			this.tabPage1.AllowDrop = true;
			this.tabPage1.Controls.Add(this.dataGrid1);
			this.tabPage1.Location = new System.Drawing.Point(4, 22);
			this.tabPage1.Name = "tabPage1";
			this.tabPage1.Size = new System.Drawing.Size(440, 326);
			this.tabPage1.TabIndex = 0;
			this.tabPage1.Text = "tabPage1";
			// 
			// tabPage2
			// 
			this.tabPage2.Controls.Add(this.dataGrid2);
			this.tabPage2.Location = new System.Drawing.Point(4, 22);
			this.tabPage2.Name = "tabPage2";
			this.tabPage2.Size = new System.Drawing.Size(440, 326);
			this.tabPage2.TabIndex = 1;
			this.tabPage2.Text = "tabPage2";
			// 
			// dataGrid2
			// 
			this.dataGrid2.CaptionForeColor = System.Drawing.SystemColors.ActiveCaptionText;
			this.dataGrid2.DataMember = "";
			this.dataGrid2.DataSource = this.dataSet2;
			this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid2.Location = new System.Drawing.Point(0, 0);
			this.dataGrid2.Name = "dataGrid2";
			this.dataGrid2.Size = new System.Drawing.Size(440, 328);
			this.dataGrid2.TabIndex = 0;
			// 
			// dataSet2
			// 
			this.dataSet2.DataSetName = "NewDataSet";
			this.dataSet2.Locale = new System.Globalization.CultureInfo("de-DE");
			// 
			// sqlDataAdapter2
			// 
			this.sqlDataAdapter2.SelectCommand = this.sqlCommand2;
			// 
			// sqlCommand2
			// 
			this.sqlCommand2.CommandText = "SELECT * FROM Nutzer";
			this.sqlCommand2.Connection = this.sqlConnection1;
			// 
			// Abfrage
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(448, 349);
			this.Controls.Add(this.tabControl1);
			this.Name = "Abfrage";
			this.Text = "SQL Abfrage";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
			this.tabControl1.ResumeLayout(false);
			this.tabPage1.ResumeLayout(false);
			this.tabPage2.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet2)).EndInit();
			this.ResumeLayout(false);
		}
		#endregion	
	}
}

Da ich doch ein ziemlicher Anfänger in C# bin brauche ich Eure Hilfe.
Vielen Dank im voraus

ksk · 31. Juli 2007

Hy!

Am besten du schreibst zuerst mal die Spalltenköpfe.
Dafür nimmst du eine tabelle oder wenn du mehrere Tabellen Exportieren möchtest, legts ein Array vom Typ dataTable an und einen StreamWriter um zu schreiben.
z.B.:

Code:

DataTable[] allDT = new DataTable[X];

using (StreamWriter sw = new StreamWriter(pfad + ".csv",
                                                                false, System.Text.Encoding.Default))

Um die Spallten durch zu gehen muss du von deiner Tabelle die Spallten-Anzahl übergeben. Dies könnte so in etwas aussehen.

Code:

int numberOfColumns = allDT[y].Columns.Count;
for (int i = 0; i < numberOfColumns; i++)
{
    sw.Write(allDT[y].Columns[i]);
    if (i < numberOfColumns - 1)
    sw.Write(Hier_wird_der_Trennzeichen_geschrieben.Text);
}
sw.Write(sw.NewLine);

So, nun hast mal die Spalltenköpfe. um die Werte auch zu schreiben musst du alle Zeilen durch gehen und in dieser Schleife nochmals alle Spallten durch gehen. Somit schreibst du für jede Zeile in der richtigen Reihenfolge, die richtigen Werte.

Falls du´s nicht hinbekommst, sag bescheid ich poste dann ein Beispiel.

Gruß
ksk

rIpPeRrRrR · 13. August 2007

Hy, danke für Deine Hilfe ich hab es nun so gelöst:

Code:

			// 
			// saveFileDialog1
			// 
			this.saveFileDialog1.CreatePrompt = true;
			this.saveFileDialog1.DefaultExt = "csv";
			this.saveFileDialog1.Filter = "CSV-Dateien (*.csv)|*.csv|Alle Dateien (*.*)|*.*";
			this.saveFileDialog1.RestoreDirectory = true;
			this.saveFileDialog1.Title = "CSV-Export";
			// 
			// Abfrage
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(712, 349);
			this.Controls.Add(this.tabControl1);
			this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.MaximizeBox = false;
			this.Name = "Abfrage";
			this.Text = "BBSM53";
			this.tabPage1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dataSet2)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.tabPage4.ResumeLayout(false);
			this.tabPage2.ResumeLayout(false);
			this.tabPage3.ResumeLayout(false);
			this.tabControl1.ResumeLayout(false);
			this.ResumeLayout(false);
		}
		#endregion
		void Button1Click(object sender, System.EventArgs e)
		{
     		if (saveFileDialog1.ShowDialog(this) == DialogResult.OK)
     		{
     			StreamWriter sw = new StreamWriter(saveFileDialog1.FileName);
        		sw.Write("AccessCode" + ";");
        		sw.Write("Customer" + ";");
        		sw.Write("StartValid" + ";");
        		sw.Write("EndValid" + ";");
        		sw.Write("DurationMinutes" + ";");
        		sw.Write("RemainingMinutes" + ";");
        		sw.Write(";");
   				sw.Write("AccessCode" + ";");
        		sw.Write("Customer" + ";");
        		sw.Write("IPAddress" + ";");
        		sw.Write("MACAddress" + ";");
        		sw.WriteLine("DateTime" + ";");
        		for(int i = 0, j = 0; i < dataSet1.Tables[0].Rows.Count && j < dataSet2.Tables[0].Rows.Count; i++, j++)
        		{
					sw.Write(dataSet1.Tables[0].Rows[i] ["AccessCode"] + ";" );
					sw.Write(dataSet1.Tables[0].Rows[i]["Customer"] + ";" );
					sw.Write(dataSet1.Tables[0].Rows[i] ["StartValid"] + ";" );
					sw.Write(dataSet1.Tables[0].Rows[i] ["EndValid"] + ";" );
					sw.Write(dataSet1.Tables[0].Rows[i] ["DurationMinutes"] + ";" );
					sw.Write(dataSet1.Tables[0].Rows[i] ["RemainingMinutes"] + ";" );
					sw.Write(";");
					sw.Write(dataSet2.Tables[0].Rows[j] ["AccessCode"] + ";" );
					sw.Write(dataSet2.Tables[0].Rows[j] ["Customer"] + ";" );
					sw.Write(dataSet2.Tables[0].Rows[j] ["IPAddress"] + ";" );
					sw.Write(dataSet2.Tables[0].Rows[j] ["MACAddress"] + ";" );
					sw.WriteLine(dataSet2.Tables[0].Rows[j] ["DateTime"] + ";" );
        			sw.Flush();
        		}
        			sw.Close();
     		}
		}

Folgenden USING noch mit anfügen:

Code:

using System.IO;

Fals jemand anderes ein ähnliches Problem haben sollte.

C# SQL in CSV speichern

rIpPeRrRrR

Grünschnabel

ksk

Erfahrenes Mitglied

rIpPeRrRrR

Grünschnabel

Neue Beiträge