using System; using System.Data; using ByteFX.Data.MySqlClient; class MySqlConnect { static void Main() { string connString = @"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"; MySqlConnection conn = new MySqlConnection(connString); try { conn.Open(); Console.WriteLine("Connection opened."); Console.WriteLine("Connection Properties:"); Console.WriteLine(" Connection String: {0}",conn.ConnectionString); Console.WriteLine(" Database: {0}",conn.Database); Console.WriteLine(" ServerVersion: {0}", conn.ServerVersion); Console.WriteLine( " State: {0}", conn.State); } catch (MySqlException e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); Console.WriteLine("Connection closed."); } } }
Author: coder
Deal with the many to many relation
using System; using System.Data; // use ADO.NET namespace using System.Data.SqlClient; // use SQL Server provider class DataRelationExample { public static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection); SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); orderAdapter.Fill(thisDataSet, "Orders"); DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"] + " Name: " + custRow["CompanyName"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) { Console.WriteLine(" Order ID: " + orderRow["OrderID"]); } } custOrderRel.Nested = true; thisDataSet.WriteXml(@"nwinddata.xml"); Console.WriteLine(@"Successfully wrote XML output to file nwinddata.xml"); } }
Read image data from database and display that image
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.IO; public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; private System.Windows.Forms.TextBox textBox1; private System.Windows.Forms.PictureBox pictureBox1; private Images images; private System.ComponentModel.Container components = null; public Form1() { InitializeComponent(); images = new Images(); if (images.GetRow()) { this.textBox1.Text = images.GetFilename(); this.pictureBox1.Image = (Image)images.GetImage(); } else { this.textBox1.Text = "DONE"; this.pictureBox1.Image = null; } } private void InitializeComponent() { this.button1 = new System.Windows.Forms.Button(); this.textBox1 = new System.Windows.Forms.TextBox(); this.pictureBox1 = new System.Windows.Forms.PictureBox(); this.SuspendLayout(); this.button1.Location = new System.Drawing.Point(200, 8); this.button1.Name = "button1"; this.button1.TabIndex = 0; this.button1.Text = "Next"; this.button1.Click += new System.EventHandler(this.button1_Click); this.textBox1.Location = new System.Drawing.Point(24, 8); this.textBox1.Name = "textBox1"; this.textBox1.Size = new System.Drawing.Size(144, 20); this.textBox1.TabIndex = 1; this.textBox1.Text = ""; this.pictureBox1.Location = new System.Drawing.Point(8, 48); this.pictureBox1.Name = "pictureBox1"; this.pictureBox1.Size = new System.Drawing.Size(280, 208); this.pictureBox1.TabIndex = 2; this.pictureBox1.TabStop = false; this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(292, 272); this.Controls.Add(this.pictureBox1); this.Controls.Add(this.textBox1); this.Controls.Add(this.button1); this.Name = "Form1"; this.Text = "Display Images"; this.ResumeLayout(false); } static void Main() { Application.Run(new Form1()); } private void button1_Click(object sender, System.EventArgs e) { if (images.GetRow()) { this.textBox1.Text = images.GetFilename(); this.pictureBox1.Image = (Image)images.GetImage(); } else { this.textBox1.Text = "DONE"; this.pictureBox1.Image = null; } } } public class Images{ string imageFilename = null; byte[] imageBytes = null; SqlConnection imageConnection = null; SqlCommand imageCommand = null; SqlDataReader imageReader = null; public Images() { imageConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); imageCommand = new SqlCommand(@"select imagefile, imagedata from imagetable", imageConnection); imageConnection.Open(); imageReader = imageCommand.ExecuteReader(); } public Bitmap GetImage() { MemoryStream ms = new MemoryStream(imageBytes); Bitmap bmap = new Bitmap(ms); return bmap; } public string GetFilename() { return imageFilename; } public bool GetRow() { if (imageReader.Read()) { imageFilename = (string) imageReader.GetValue(0); imageBytes = (byte[]) imageReader.GetValue(1); return true; }else { return false; } } public void EndImages() { imageReader.Close(); imageConnection.Close(); } }
Insert Data Using SqlCommandBuilder
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class InsertingDataUsingCommandBuilder { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, Contact, Email FROM Test", MyConnection); SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); DataRow MyRow = MyDataSet.Tables[0].NewRow(); MyRow["ID"] = 200; MyRow["Contact"] = "Greg"; MyRow["Email"] = "MacBeth"; MyDataSet.Tables[0].Rows.Add(MyRow); MyDataAdapter.Update(MyDataSet); } } }
Inserte Data Using SQL Statements
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class InsertingDataUsingSQLStatements { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.Open(); String MyString = @"INSERT INTO Test(ID, Contact, Email) VALUES(2, 'Greg', 'MacBeth')"; SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.ExecuteScalar(); MyConnection.Close(); } } }
Run an INSERT statement with parameters
using System; using System.Data; using System.Data.SqlClient; class UsingParameters { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "INSERT INTO Customers (" + " CustomerID, CompanyName, ContactName" + ") VALUES (" + " @CustomerID, @CompanyName, @ContactName" + ")"; mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters["@CustomerID"].Value = "J4COM"; mySqlCommand.Parameters["@CompanyName"].Value = "J4 Company"; mySqlCommand.Parameters["@ContactName"].IsNullable = true; mySqlCommand.Parameters["@ContactName"].Value = DBNull.Value; mySqlCommand.ExecuteNonQuery(); Console.WriteLine("Successfully added row to Customers table"); mySqlConnection.Close(); } }
Bind parameters to insert command
using System; using System.Data; using System.Data.SqlClient; class PropagateAdds{ static void Main() { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string qry = @"select * from employee"; string upd = @"insert into employee(firstname,lastname)values(@firstname,@lastname)"; SqlConnection conn = new SqlConnection(connString); try{ SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(qry, conn); DataSet ds = new DataSet(); da.Fill(ds, "employee"); DataTable dt = ds.Tables["employee"]; DataRow newRow = dt.NewRow(); newRow["firstname"] = "R"; newRow["lastname"] = "B"; dt.Rows.Add(newRow); foreach (DataRow row in dt.Rows){ Console.WriteLine( "{0} {1}", row["firstname"].ToString().PadRight(15), row["lastname"].ToString().PadLeft(25)); } // Update employees SqlCommand cmd = new SqlCommand(upd, conn); cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname"); cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 20, "lastname"); da.InsertCommand = cmd; da.Update(ds, "employee"); } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }