Csharp/CSharp Tutorial/ADO.Net/DataColumn

Add an expression column to the table

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT TOP 5 * FROM Sales.SalesOrderDetail";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable();
            da.Fill(dt);
            
            dt.Columns.Add(new DataColumn("ExtendedPrice", typeof(Decimal),"UnitPrice * (1 - UnitPriceDiscount) * OrderQty"));
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("ID = {0}, UnitPrice = {1}, " +
                    "Disc = {2}, Qty = {3}, Total = {4}",
                    row["SalesOrderDetailID"], row["UnitPrice"],
                    row["UnitPriceDiscount"], row["OrderQty"],
                    row["ExtendedPrice"]);
        }
    }

Creating an Autoincrementing Primary Key

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            DataColumn pkCol = dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt.PrimaryKey = new DataColumn[] {dt.Columns["Id"]};
            pkCol.AutoIncrement = true;
            pkCol.AutoIncrementSeed = 100;
            pkCol.AutoIncrementStep = 10;
            for (int i = 1; i <= 5; i++)
                dt.Rows.Add(new object[] {null, "Value " + i });
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}",row["Id"], row["Field1"]);
        }
    }

Find data by primary key, if not found add to database

using System;
using System.Data;            
using System.Data.SqlClient;  
using System.Collections.Generic;
using System.Text;

  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
      SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection);
      SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
      DataSet thisDataSet = new DataSet();
      thisAdapter.Fill(thisDataSet, "Customers");
      Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Customers"].Rows.Count);
      DataColumn[] keys = new DataColumn[1];
      keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
      thisDataSet.Tables["Customers"].PrimaryKey = keys;
      DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("1");
      if (findRow == null)
      {
        DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
        thisRow["CustomerID"] = "1";
        thisRow["CompanyName"] = "Abc Ltd.";
        thisDataSet.Tables["Customers"].Rows.Add(thisRow);
        if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("1")) != null)
        {
          Console.WriteLine("success");
        }
      }
      thisAdapter.Update(thisDataSet, "Customers");
      Console.WriteLine(thisDataSet.Tables["Customers"].Rows.Count);
      thisConnection.Close();
    }
  }

Output DataSet as Binary file

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
  class Program
  {
    static void Main(string[] args)
    {
      DataSet carsInventoryDS = new DataSet("Car");
      
      carsInventoryDS.ExtendedProperties["TimeStamp"] = DateTime.Now;
      carsInventoryDS.ExtendedProperties["DataSetID"] = Guid.NewGuid();
      carsInventoryDS.ExtendedProperties["Company"] = "Training";
      DataColumn carIDColumn = new DataColumn("CarID", typeof(int));
      carIDColumn.Caption = "Car ID";
      carIDColumn.ReadOnly = true;
      carIDColumn.AllowDBNull = false;
      carIDColumn.Unique = true;
      carIDColumn.AutoIncrement = true;
      carIDColumn.AutoIncrementSeed = 0;
      carIDColumn.AutoIncrementStep = 1;
      DataColumn carMakeColumn = new DataColumn("Make", typeof(string));
      DataColumn carColorColumn = new DataColumn("Color", typeof(string));
      DataColumn carPetNameColumn = new DataColumn("PetName", typeof(string));
      carPetNameColumn.Caption = "Pet Name";
      DataTable inventoryTable = new DataTable("Inventory");
      inventoryTable.Columns.AddRange(new DataColumn[] { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn });
      DataRow carRow = inventoryTable.NewRow();
      carRow["Make"] = "BMW";
      carRow["Color"] = "Black";
      carRow["PetName"] = "Hamlet";
      inventoryTable.Rows.Add(carRow);
      carRow = inventoryTable.NewRow();
      carRow[1] = "A";
      carRow[2] = "B";
      carRow[3] = "C";
      inventoryTable.Rows.Add(carRow);
      inventoryTable.PrimaryKey = new DataColumn[] { inventoryTable.Columns[0] };
      carsInventoryDS.Tables.Add(inventoryTable);
      
      carsInventoryDS.RemotingFormat = SerializationFormat.Binary;
      FileStream fs = new FileStream("BinaryCars.bin", FileMode.Create);
      BinaryFormatter bFormat = new BinaryFormatter();
      bFormat.Serialize(fs, carsInventoryDS);
      fs.Close();
      carsInventoryDS.Clear();
      fs = new FileStream("BinaryCars.bin", FileMode.Open);
      DataSet data = (DataSet)bFormat.Deserialize(fs);
      
    }
  }

Set auto increment, seed and step for primary key column

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            DataColumn pkCol = dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt.PrimaryKey = new DataColumn[] {dt.Columns["Id"]};
            pkCol.AutoIncrement = true;
            pkCol.AutoIncrementSeed = 100;
            pkCol.AutoIncrementStep = 10;
            for (int i = 1; i <= 5; i++)
                dt.Rows.Add(new object[] {null, "Value " + i });
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}",row["Id"], row["Field1"]);
        }
    }

Use a relationship with a calculated column

using System;
using System.Data;
using System.Data.SqlClient;
public class CalculatedColumn
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT * FROM Categories";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        con.Open();
        adapter.Fill(ds, "Categories");
    
        cmd.rumandText = "SELECT * FROM Products";
        adapter.Fill(ds, "Products");
        con.Close();
        DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"];
        DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];
        DataRelation relation = new DataRelation("Cat_Prod", parentCol,childCol);
        ds.Relations.Add(relation);
        ds.Tables["Categories"].Columns.Add("AveragePrice", typeof(Decimal), "AVG(Child(Cat_Prod).UnitPrice)");
        ds.Tables["Categories"].Columns.Add("TotalPrice", typeof(Decimal), "SUM(Child(Cat_Prod).UnitPrice)");
        foreach (DataRow row in ds.Tables["Categories"].Rows)
        {
            Console.WriteLine(row["CategoryName"]);
            Console.WriteLine(row["AveragePrice"]);
            Console.WriteLine(row["TotalPrice"]);
        }
    }
}