C#

Microsoft SQL Server, “Denali,” will be the last release to support OLE DB.

So the best strategy is using Ado.net + ODBC

Close the Connection

It is recommended that you always close the Connection when you are finished using it, in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Note   Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Programming for Garbage Collection.

https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdataadapter%28v=vs.90%29.aspx

Namespace:  System.Data.Odbc
Assembly:  System.Data (in System.Data.dll)

Best example

public DataSet GetDataSetFromAdapter(DataSet dataSet, string connectionString, string queryString)
{
    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, connection);

        // Open the connection and fill the DataSet. 
        try
        {
            connection.Open();
            adapter.Fill(dataSet);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the 
        // code exits the using block.
    }
    return dataSet;
}
 Other examples
Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
                                                     "Trusted_Connection=yes;Database=northwind")
nwindConn.Open()
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
                                              "Trusted_Connection=yes;Database=northwind");
nwindConn.Open();

https://msdn.microsoft.com/en-us/library/ms971481.aspx

http://www.easysoft.com/developer/languages/csharp/ado-net-odbc.html

Namespace:   System.Data.Odbc
Assembly:  System.Data (in System.Data.dll)

static private void CreateOdbcConnection()
{
	using (OdbcConnection connection = new OdbcConnection())
	{
		connection.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\Samples\\Northwind.mdb";
		connection.Open();
	}
}
static private void InsertRow(string connectionString)
{
	string queryString = 
              "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";
	OdbcCommand command = new OdbcCommand(queryString);

	using (OdbcConnection connection = new OdbcConnection(connectionString))
	{
		command.Connection = connection;
		connection.Open();
		command.ExecuteNonQuery();

              // The connection is automatically closed at 
              // the end of the Using block.
	}
}

======================================================================

ADO.NET no longer forces your code to depend on OLE DB or ODBC. Instead, the .NET Framework uses built-in or add-on .NET data providers to access data sources. These include:

  • SqlClient: This built-in managed provider accesses SQL Server TDS directly. It’s designed to connect to Microsoft® SQL Server™ versions 7.0 or later.   System.Data.SqlClient library
  • OracleClient: This built-in managed provider is designed to access Oracle databases.
  • Odbc: This built-in managed provider is designed to access any data source exposed with an ODBC driver.
  • OleDb: To connect to SQL Server 6.5, JET/Access databases or anything without a “native” or managed provider. It’s the slowest of all providers as it uses COM interop to access the OLE DB provider selected in the ConnectionString.   System.Data.OledbClient.Adodb 

======================Recordset gone with the wind ========================

The ADO Recordset bundled functionality together into one object and handles much behavior implicitly. ADO.NET, doesn’t use recordsets.

ADO.NET has been designed to factor behavior into separate components and to enable you to explicitly control behavior. The following table describes the individual ADO.NET objects that provide the functionality of the ADO Recordset.

ADO.NET object Description
DataReader Provides a forward-only, read-only stream of data from a data source.

The DataReader is similar to a Recordset with CursorType = adOpenForwardOnly and LockType = adLockReadOnly.

DataSet Provides in-memory access to relational data.

The DataSet is independent of any specific data source and therefore can be populated from multiple and differing data sources including relational databases and XML, or can be populated with data local to the application. Data is stored in a collection of one or more tables, and can be accessed non-sequentially and without limits to availability, unlike ADO in which data must be accessed a single row at a time. A DataSet can contain relationships between tables, similar to the ADO Recordset in which a single result set is created from a JOIN. A DataSet can also contain unique, primary key, and foreign key constraints on its tables.

The DataSet is similar to a Recordset with CursorLocation = adUseClient, CursorType = adOpenStatic, and LockType = adLockOptimistic. However, the DataSet has extended capabilities over the Recordset for managing application data.

DataAdapter Populates a DataSet with data from a relational database and resolves changes in the DataSet back to the data source.

The DataAdapter enables you to explicitly specify behavior that the Recordset performs implicitly.

 ==============================================================

DataSet resembles database. DataTable resembles database table, and DataRow resembles a record in a table. If you want to add filtering or sorting options, you then do so with a DataView object, and convert it back to a separate DataTable object.

If you’re using database to store your data, then you first load a database table to a DataSet object in memory. You can load multiple database tables to one DataSet, and select specific table to read from the DataSet through DataTable object. Subsequently, you read a specific row of data from your DataTable through DataRow. Following codes demonstrate the steps:

SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
DataTable dt = new DataTable();

da.SelectCommand = new SqlCommand(@"SELECT * FROM FooTable", connString);
da.Fill(ds, "FooTable");
dt = ds.Tables["FooTable"];
foreach (DataRow dr in dt.Rows)
{
    MessageBox.Show(dr["Column1"].ToString());
}

To read a specific cell in a row:

int rowNum // row number
string columnName = "DepartureTime";  // database table column name
dt.Rows[rowNum][columnName].ToString();

http://stackoverflow.com/questions/6409839/reading-dataset

=================================

ADO.NET relies on command objects

ADO.NET Create a Command Object

cmd = New SqlClient.SqlCommand
With cmd
     .CommandText = "SELECT Author, Year_Born  
       FROM Authors WHERE Author Like @AW"
     .CommandType = CommandType.Text
     .Parameters.Add("@AW", SqlDbType.VarChar, 20)
     .Connection = cn
End With

ADO.NET Execute an Action Query

cmd2 = New SqlClient.SqlCommand("UPDATE Authors 
    SET Year_Born = 1950 WHERE AU_ID =25", cn)
intRa = cmd.ExecuteNonQuery

ADO (ADODB) was a generic (COM) database library, that can be used by programming languages such as Visual Basic and C++ to access any type of database for which an OLEDB Provider has been developed.

ADO was an OLEDB Consumer. It communicates with the OLEDB Provider, which in turn communicates with the database directly or a database server.

Oledb (Object Linking and Embedding DB) is a standard format supported by a large number of dbs, (Sql Server, oracle, db2, even Access).  (I suggest this)

Adodb (ActiveX Data Objects DB) works with MS-based databases such as Sql Server.

ADO.Net is a .Net based db connection “architecture”.

-For Oledb:  System.Data.OledbClient.Adodb

 

connstring = “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=” + dbFileName;

//Microsoft ACE (Access Database Engine) driver. must be installed on the machine
OleDbConnection oledbcn = new OleDbConnection(connstring);  
oledbcn.Open();
System.Data.OleDb.OleDbDataAdapter amiroleda = new System.Data.OleDb.OleDbDataAdapter(query, oledbcn);
DataTable amirt = new DataTable();
DataSet amirds = new DataSet();

//Both data set and data table work 🙂
amiroleda.Fill(amirds,amirfieldoftableinfile);
amiroleda.Fill(amirt);

 

 

 

=================================================================

C# ADO.NET SqlDataAdapter

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection sqlCnn ;
            SqlCommand sqlCmd ;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;
            string sql = null;

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            sql = "Select * from product";

            sqlCnn = new SqlConnection(connetionString);
            try
            {
                sqlCnn.Open();
                sqlCmd = new SqlCommand(sql, sqlCnn);
                adapter.SelectCommand = sqlCmd;
                adapter.Fill(ds);
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]);
                }
                adapter.Dispose();
                sqlCmd.Dispose();
                sqlCnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }
    }
}

http://csharp.net-informations.com/data-providers/csharp-sqldataadapter.htm

——————-

OleDbDataAdapter  

using OleDbDataAdapter;

        private void OleDbDataAdapter_Click(object sender, System.Event Args e)
        {

        //Create a connection object
        string ConnectionString = @”provider=Microsoft.Jet.OLEDB.4.0;” +
        “Data Source= C:/northwind.mdb”;
        string SQL = “SELECT * FROM Orders”;
        OleDbConnection conn = new OleDbConnection(ConnectionString);

        // open the connection
        conn.Open( );

        // Create an OleDbDataAdapter object
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = new OleDbCommand(SQL, conn);

        // Create Data Set object
        DataSet ds = new DataSet(“orders”);
        // Call DataAdapter’s Fill method to fill data from the
        // DataAdapter to the DataSet
        adapter.Fill(ds);

        // Bind dataset to a DataGrid control
        dataGrid1.DataSource = ds.DefaultViewManager;
        }

The following code example populates a list of customers from the Northwind database on Microsoft SQL Server, and a list of orders from the Northwind database stored in Microsoft Access 2000. The filled tables are related with a DataRelation, and the list of customers is then displayed with the orders for that customer. For more information about DataRelation objects, see Adding DataRelations and Navigating DataRelations.

// Assumes that customerConnection is a valid SqlConnection object.
// Assumes that orderConnection is a valid OleDbConnection object.
SqlDataAdapter custAdapter = new SqlDataAdapter(
  "SELECT * FROM dbo.Customers", customerConnection);
OleDbDataAdapter ordAdapter = new OleDbDataAdapter(
  "SELECT * FROM Orders", orderConnection);

DataSet customerOrders = new DataSet();

custAdapter.Fill(customerOrders, "Customers");
ordAdapter.Fill(customerOrders, "Orders");

DataRelation relation = customerOrders.Relations.Add("CustOrders",
  customerOrders.Tables["Customers"].Columns["CustomerID"],
  customerOrders.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in customerOrders.Tables["Customers"].Rows)
{
  Console.WriteLine(pRow["CustomerID"]);
   foreach (DataRow cRow in pRow.GetChildRows(relation))
    Console.WriteLine("\t" + cRow["OrderID"]);
}

https://msdn.microsoft.com/en-us/library/bh8kx08z%28v=vs.110%29.aspx

——————–

Using SqlDataAdapter

SqlDataAdapter type

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
	public Form1()
	{
	    InitializeComponent();
	    FillData();
	}

	void FillData()
	{
	    // 1
	    // Open connection
	    using (SqlConnection c = new SqlConnection(
		Properties.Settings.Default.DataConnectionString))
	    {
		c.Open();
		// 2
		// Create new DataAdapter
		using (SqlDataAdapter a = new SqlDataAdapter(
		    "SELECT * FROM EmployeeIDs", c))
		{
		    // 3
		    // Use DataAdapter to fill DataTable
		    DataTable t = new DataTable();
		    a.Fill(t);

		    // 4
		    // Render data onto the screen
		    // dataGridView1.DataSource = t; // <-- From your designer
		}
	    }
	}
    }
}

http://www.dotnetperls.com/sqldataadapter

====================================================================

Using Connection object Directly (I don’t Suggest)

Sub ConnectionExample6()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Create a Recordset by executing an SQL statement.
   Set rs = cnn.Execute("Select * From authors")

   ' Show the first author.
   MsgBox rs("au_fname") & " " & rs("au_lname")

   ' Close the connection.
   rs.Close

End Sub
Sub ConnectionExample7()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Send a Delete statement to the database.
   cnn.Execute ("Delete From authors Where au_id = '011-01-0111'")

   ' Find out how many rows were affected by the Delete.
   Set rs = cnn.Execute("Select @@rowcount")
   ' Display the first field in the recordset.
   MsgBox rs(0) & " rows deleted"

   ' Close the connection.
   rs.Close

End Sub
Sub ConnectionExample8()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Create a recordset by running a stored procedure.
   Set rs = cnn.Execute("Exec byroyalty 50")

   ' Loop through the recordset and show the author's ID.
   Do While Not rs.EOF
      MsgBox rs("au_id")
      rs.MoveNext
   Loop

   ' Close the connection.
   rs.Close

End Sub

https://msdn.microsoft.com/en-us/library/ms807027.aspx

================================================

Setting the datatype of columns in a gridview

The DataType property supports the following base .NET Framework data types:

Boolean

Byte

Char

DateTime

Decimal

Double

Guid

Int16

Int32

Int64

SByte

Single

String

TimeSpan

UInt16

UInt32

UInt64

this.datagrid.Columns[0].ValueType = typeof(Int32);

this.datagrid.Columns[1].ValueType = typeof(String);

this.datagrid.Columns[2].ValueType = typeof(DateTime);

Setting the datatype of columns in a DataTAble

public DataTable MakeDataTable(){

    DataTable myTable;
    DataRow myNewRow; 
    // Create a new DataTable.
    myTable = new DataTable("My Table");

    // Create DataColumn objects of data types.
    DataColumn colString = new DataColumn("StringCol");
    colString.DataType = System.Type.GetType("System.String");
    myTable.Columns.Add(colString); 

    DataColumn colInt32 = new DataColumn("Int32Col");
    colInt32.DataType = System.Type.GetType("System.Int32");
    myTable.Columns.Add(colInt32);

    DataColumn colBoolean = new DataColumn("BooleanCol");
    colBoolean.DataType = System.Type.GetType("System.Boolean");
    myTable.Columns.Add(colBoolean);

    DataColumn colTimeSpan = new DataColumn("TimeSpanCol");
    colTimeSpan.DataType = System.Type.GetType("System.TimeSpan");
    myTable.Columns.Add(colTimeSpan);

    DataColumn colDateTime = new DataColumn("DateTimeCol");
    colDateTime.DataType = System.Type.GetType("System.DateTime");
    myTable.Columns.Add(colDateTime);

    DataColumn colDecimal = new DataColumn("DecimalCol");
    colDecimal.DataType = System.Type.GetType("System.Decimal");
    myTable.Columns.Add(colDecimal);

    DataColumn colByteArray = new DataColumn("ByteArrayCol");
    colByteArray.DataType = System.Type.GetType("System.Byte[]");
    myTable.Columns.Add(colByteArray);


    // Populate one row with values.
    myNewRow = myTable.NewRow();

    myNewRow["StringCol"] = "Item Name";
    myNewRow["Int32Col"] = 2147483647;
    myNewRow["BooleanCol"] = true;
    myNewRow["TimeSpanCol"] = new TimeSpan(10,22,10,15,100);
    myNewRow["DateTimeCol"] = System.DateTime.Today;
    myNewRow["DecimalCol"] = 64.0021;
    myNewRow["ByteArrayCol"] = new Byte[] { 1, 5, 120 };
    myTable.Rows.Add(myNewRow);
    return myTable;  
 }

https://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype%28v=vs.110%29.aspx

==============================================================

R.NET https://rdotnet.codeplex.com/documentation

R.net Simple example  http://coders-corner.net/2015/11/01/use-r-in-c/

==========================================

Use a C# class in F#