ADO.NET

From no name for this wiki
Jump to: navigation, search

Disconnected Classes

DataSet und Table

            // Create a new DataTable.
            System.Data.DataTable table = new DataTable("ParentTable");
            // Declare variables for DataColumn and DataRow objects.
            DataColumn column;
            DataRow row;
 
            // Create new DataColumn, set DataType, 
            // ColumnName and add to DataTable.    
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "id";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            table.Columns.Add(column);
 
            // Create second column.
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ParentItem";
            column.AutoIncrement = false;
            column.Caption = "ParentItem";
            column.ReadOnly = false;
            column.Unique = false;
            // Add the column to the table.
            table.Columns.Add(column);
 
            // Make the ID column the primary key column.
            DataColumn[] PrimaryKeyColumns = new DataColumn[1];
            PrimaryKeyColumns[0] = table.Columns["id"];
            table.PrimaryKey = PrimaryKeyColumns;
 
            // Instantiate the DataSet variable.
            DataSet dataSet = new DataSet();
            // Add the new DataTable to the DataSet.
            dataSet.Tables.Add(table);
 
            // Create three new DataRow objects and add 
            // them to the DataTable
            for (int i = 0; i <= 10; i++)
            {
                row = table.NewRow();
                row["id"] = i;
                row["ParentItem"] = "ParentItem " + i;
                table.Rows.Add(row);
            }

Connected Classes

Simple Select

using (SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDb2.mdf;Integrated Security=True;User Instance=True"))
{
  connection.Open();
  SqlCommand command = connection.CreateCommand();
  command.CommandType = CommandType.Text;
  command.CommandText = "select id, name from MyTable";
  SqlDataReader rdr = command.ExecuteReader();
  DataTable table = new DataTable();
  table.Load(rdr);
  foreach (DataRow row in table.Rows)
  {
    int id = (int) row[0];
    string name = row[1] as string;
  }                                
}

Parameter

using (SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDb2.mdf;Integrated Security=True;User Instance=True"))
{
  connection.Open();
  SqlCommand command = connection.CreateCommand();
  command.CommandType = CommandType.Text;
  command.CommandText = "select id, name from MyTable where id > @minid";
 
  SqlParameter paramMinId = new SqlParameter();
  paramMinId.ParameterName = "@minid";
  paramMinId.Value = 1;
 
  command.Parameters.Add(paramMinId);
 
  SqlDataReader rdr = command.ExecuteReader();
  while (rdr.Read())
  {
    int id = (int) rdr["id"];
    string name = (string) rdr["name"];
  } 
  rdr.Close();                      
}

Putting binary

byte[] bytes = File.ReadAllBytes(file);
 
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "UPDATE dbo.WechselzoneEvent SET IMAGE = @Bin WHERE Id=@ID";
 
 
command.Parameters.Add("Bin", SqlDbType.Binary).Value = bytes;
command.Parameters.Add("ID", SqlDbType.Int).Value = id;
 
command.ExecuteNonQuery();

oder

using (FileStream sourceStream = new FileStream(filePath, FileMode.Open))
{
    using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE BigFiles SET Data=@Data WHERE BigDataID = @BigDataID"), _sqlConn))
    {
        cmd.Parameters.AddWithValue("@Data", sourceStream);
        cmd.Parameters.AddWithValue("@BigDataID", entryId);
 
        cmd.ExecuteNonQuery();
    }
}

Reading binary

using System;
using System.Data;
using System.Data.SqlClient;
 
 
namespace ForHeinz
{
    class Program
    {
        static void Main(string[] args)
        {
            ReadBinary(@"Server=Bla;Database=Bli;Trusted_Connection=True;", 75);
        }
 
        private static byte[] ReadBinary(string connectionString, int binaryid)
        {
            using (SqlConnection c = new SqlConnection(connectionString))
            {
                c.Open();
                string sql = @"
                        SELECT 
                            binBinary
                        FROM dbo.tabBinary 
                        WHERE (lngBinaryID = @BinaryId)";
 
                using (SqlCommand cmd = new SqlCommand(sql, c))
                {
                    cmd.Parameters.Add("@BinaryId", SqlDbType.Int).Value = binaryid;
                    using (SqlDataReader d = cmd.ExecuteReader())
                    {
                        if (d.Read())
                        {
                            byte[] binaryData = (byte[])d["binBinary"];
                            return binaryData;
                        }                      
                    }
                }
                throw new Exception("Konnte Eintrag in tabBinary nicht lesen");
            }
        }
    }
}

Connection String in Config-File

Das Configfile:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Ado2Demo.Properties.Settings.MyDb2ConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDb2.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Im Code Referenz auf Assembly System.Configuration hinzufuegen. Code:

 ConnectionStringSettings css;
 css = ConfigurationManager.ConnectionStrings["Ado2Demo.Properties.Settings.MyDb2ConnectionString"];
 using (SqlConnection connection = new SqlConnection(css.ConnectionString))
 {
   connection.Open();
   //Do stuff
 }

DbProviderFactory

Fuer DB unabhaengigkeit kann man DbProviderFactory nutzen.

DbProviderFactory factory = SqlClientFactory.Instance;
DbConnection connection = factory.CreateConnection();
ConnectionStringSettings css;
css = ConfigurationManager.ConnectionStrings["Ado2Demo.Properties.Settings.MyDb2ConnectionString"];
connection.ConnectionString = css.ConnectionString;
DbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select id, name from MyTable where id > @minid";
 
DbParameter paramMinId = factory.CreateParameter();
paramMinId.ParameterName = "@minid";
paramMinId.Value = 1;
 
command.Parameters.Add(paramMinId);
 
try
{
   connection.Open();
   DbDataReader rdr = command.ExecuteReader();
   while (rdr.Read())
   {
     int id = (int)rdr["id"];
     string name = (string)rdr["name"];
   }
   rdr.Close();
   }
finally
{
     connection.Close();
}

ODBC Sample

using (OdbcConnection connection = new OdbcConnection(@"Dsn=MyDerby;Uid=app;Pwd=app"))
{
  connection.Open();
  DbCommand command = connection.CreateCommand();
  command.CommandType = CommandType.Text;
  command.CommandText = "select * from APP.QUESTION";
  DbDataReader rdr = command.ExecuteReader();
  while (rdr.Read())
  {
     object id = rdr[0];
     object name = rdr[1];
  }
  rdr.Close();                
}

Transaction

using (DbTransaction transaction = connection.BeginTransaction())
{
  try
  {
     //dowork here
     transaction.Commit();
  }
  catch (Exception ex)
  {
     transaction.Rollback();
  }
 
}

Transaction Scope

   ConnectionStringSettings css;
   css = ConfigurationManager.ConnectionStrings["Ado2Demo.Properties.Settings.MyDb2ConnectionString"];
   using (TransactionScope tscope = new TransactionScope())
   {
        using (SqlConnection connection = new SqlConnection(css.ConnectionString))
        {
            //Do work here
 
            //Commit
            tscope.Complete();
 
        }
   }

Joining a transaction

            ConnectionStringSettings css;
            css = ConfigurationManager.ConnectionStrings["Ado2Demo.Properties.Settings.MyDb2ConnectionString"];
            //Required: A transaction is required by the scope. 
            //It uses an ambient transaction if one already exists. 
            //Otherwise, it creates a new transaction before entering the scope. This is the default value. 
            using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Required))
            {
                using (SqlConnection connection = new SqlConnection(css.ConnectionString))
                {
                    //Do work here
                    tscope.Complete();
 
                }
            }

Enlisting a transactional object

   public class TransactionalObject : IEnlistmentNotification
    {
        public void Prepare(PreparingEnlistment preparingEnlistment)
        {
            Console.WriteLine("Prepare notification received");          
            preparingEnlistment.Prepared();
            // otherwise, do a ForceRollback
            //preparingEnlistment.ForceRollback();
        }
 
        public void Commit(Enlistment enlistment)
        {
            Console.WriteLine("Commit notification received");            
            //Declare done on the enlistment
            enlistment.Done();
        }
 
        public void Rollback(Enlistment enlistment)
        {
            Console.WriteLine("Rollback notification received");           
 
            //Declare done on the enlistment
            enlistment.Done();
        }
 
        public void InDoubt(Enlistment enlistment)
        {
            Console.WriteLine("In doubt notification received");         
 
            //Declare done on the enlistment
            enlistment.Done();
        }
 
    }

Enlisting:

            using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Required))
            {
                TransactionalObject obj = new TransactionalObject();
                Transaction.Current.EnlistVolatile(obj, EnlistmentOptions.None);
 
                tscope.Complete();
            }


DataAdapter

using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data.Common;
 
namespace Sample
{
    public  class Sample
    {
        public Sample()
        {
 
        }
 
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
  		Console.WriteLine("the app");
 
        	string connStr = "server=localhost;user=claude;database=pets;port=3306;";
        	using(MySqlConnection conn = new MySqlConnection(connStr)){
    	        	try
        		{
            			Console.WriteLine("Connecting to MySQL...");
            			conn.Open();
				Console.WriteLine("Conected. Executing commands now");
  				MySqlCommand command = conn.CreateCommand();
  				command.CommandType = CommandType.Text;
  				command.CommandText = "select name, owner, sex from pet";
  				DataAdapter adapter = new MySqlDataAdapter(command);
				DataSet myDataSet = new DataSet("MyDataSet");
				adapter.Fill(myDataSet);
 
				PrintRows(myDataSet);
 
            			conn.Close();
        		}
        		catch (Exception ex)
        		{
            			Console.WriteLine(ex.ToString());
        		}
        		Console.WriteLine("Done.");
		}
 
 
        } //End Main      
 
	private static void PrintRows(DataSet dataSet)
	{
    		// For each table in the DataSet, print the row values.
    		foreach(DataTable table in dataSet.Tables)
    		{
        		foreach(DataRow row in table.Rows)
        		{
            			foreach (DataColumn column in table.Columns)
            			{
               				Console.WriteLine(row[column]);
            			}
        		}
    		}
	}
 
    } 
}

Handcrafted DataAdapter, ohne CommandBuilder

Sample mit folgenden Eigenschaften:

  • Kein CommandBuilder verwendet. Ist schneller, da die Metadaten der DB nicht abgefragt werden.
  • AUTO_INCREMENT als PrimaryKey
  • UPDATE, INSERT, SELECT und DELETE Command.

Tabellenstruktur des Beispiels:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | bigint(20)  | NO   | PRI | NULL    | auto_increment | 
| NAME  | varchar(50) | YES  |     | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+

using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data.Common;
 
namespace Sample
{
    public  class Sample
    {
        public Sample()
        {
 
        }
 
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
  		Console.WriteLine("the app");
 
        	string connStr = "server=localhost;user=claude;database=pets;port=3306;";
        	using(MySqlConnection conn = new MySqlConnection(connStr)){
    	        	try
        		{
            			Console.WriteLine("Connecting to MySQL...");
				conn.Open();
 
				MySqlDataAdapter adapter = new MySqlDataAdapter();
 
				//Select command			            							
  				MySqlCommand selectCommand = new MySqlCommand("SELECT ID, NAME FROM PETS2", conn);				
				adapter.SelectCommand = selectCommand;
 
				//Insert command
				MySqlCommand insertCommand = 
					new MySqlCommand("INSERT INTO PETS2 (NAME) VALUES (@NAME);" +
                                                         "SELECT LAST_INSERT_ID() AS ID", conn);
				insertCommand.Parameters.Add("@NAME",MySqlDbType.VarChar, 50,"NAME");
				insertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
				adapter.InsertCommand = insertCommand;	
 
				//Update command
				MySqlCommand updateCommand = new MySqlCommand("UPDATE PETS2 SET NAME=@NAME WHERE ID=@ID", conn);
				updateCommand.Parameters.Add("@NAME",MySqlDbType.VarChar, 50,"NAME");
				MySqlParameter param = updateCommand.Parameters.Add("@ID",MySqlDbType.Int64,0,"ID");
				param.SourceVersion = DataRowVersion.Original;
				adapter.UpdateCommand = updateCommand;	
 
				//Delete command			
				MySqlCommand deleteCommand = new MySqlCommand("DELETE FROM PETS2 WHERE ID=@ID", conn);
				param = deleteCommand.Parameters.Add("@ID",MySqlDbType.Int64,0,"ID");
				adapter.DeleteCommand = deleteCommand;
 
				DataSet myDataSet = new DataSet("MyDataSet");
 
 
				adapter.Fill(myDataSet, "PETS2");
 
				myDataSet.Tables[0].Rows[0]["NAME"] = "Claude";
				myDataSet.Tables[0].Rows[1]["NAME"] = "Zulu";
				myDataSet.Tables[0].Rows[2].Delete();
 
 
				DataRow row = myDataSet.Tables[0].NewRow();
				row["NAME"] = "Sabrina";
				myDataSet.Tables[0].Rows.Add(row);	
 
				adapter.Update(myDataSet, "PETS2");				
 
				PrintRows(myDataSet);
            			conn.Close();
        		}
        		catch (Exception ex)
        		{
            			Console.WriteLine(ex.ToString());
        		}
        		Console.WriteLine("Done.");
		}
 
 
        } //End Main    
 
	private static void PrintRows(DataSet dataSet)
	{
    		// For each table in the DataSet, print the row values.
    		foreach(DataTable table in dataSet.Tables)
    		{
        		foreach(DataRow row in table.Rows)
        		{
				Console.Write("\n");
            			foreach (DataColumn column in table.Columns)
            			{
               				Console.Write(row[column]);
					Console.Write("; ");
            			}
        		}
			Console.Write("\n");
    		}
 
	}
 
    } 
}

LINQ

Siehe LINQ.