ADO.NET
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.
