Manipulate data with ADO

Content:
1. Accessing data with ADO.net
2. Execute simple scalar query
3. Insert data and return row id
4. Populate datatable with SqlDataAdapter
5. Populate datable with SqlDataReader
6. Using SqlDataReader to read data
7. Update data in the database with SQL
8. Propagate changes made in DataTable to SQL database - using SqlParameters

---

Data classes are available in System.Data.dll under System.Data.SqlClient.dll namespace

The intent of the examples below is to demonstrate principles. In real worlds, you may need to use try-catch block or using clause to close connection.

The simplest thing that you may need to do is to query database with a query that returns a scalar value, like a count of records in a table.

C#
// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");
connection.Open();

// create and execute sql command
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT count(*) FROM MY_SCHEMA.Persons";
command.Connection = connection;
int i = (int)command.ExecuteScalar();

connection.Close();

C#
command.CommandText = "INSERT INTO Persons (FirstName, LastName) VALUES ('John','First');SELECT SCOPE_IDENTITY();";
int i = Convert.ToInt32(command.ExecuteScalar());

If you need more than scalar, you can populate DataTable.

C#
// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

// create SQLCommand
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

// create DataTable with SqlAdapter and load data
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
try {
    connection.Open();
    int i = da.Fill(dt); 
}
finally { connection.Close(); }

This is an alternative how to populate datable.

C#
DataTable dt = new DataTable();
using (SqlConnection connnection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True"))
{
    SqlCommand command = new SqlCommand("SELECT * FROM MY_SCHEMA.Persons", connnection);
    connnection.Open();
    SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
    if (dr.HasRows)
    {
        dt.Load(dr);
    }
    return dt;
}

If you do not need all the data in memory, you can use SqlDataReader to process to read data as sequence. Sometimes it is called as "forward cursor".

C#
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

DataTable dt = new DataTable();

try
{
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();
    bool tableCreated = false;
    while (reader.Read())
    {
        if (!columnsCreated)
        {
            // create table columns
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string lsName = reader.GetName(i);
                Type t = reader[i].GetType();

                DataColumn column;
                if (t.Name != "DBNull")
                {
                    column = new DataColumn(lsName, t);
                }
                else
                {
                    column = new DataColumn(lsName, System.Type.GetType("System.Byte[]"));
                }
                
                dt.Columns.Add(column);
            }
            columnsCreated = true;
        }

        DataRow dr = dt.NewRow();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            dr[i] = reader.GetValue(i);
        }
        dt.Rows.Add(dr);
    }
}
finally { connection.Close(); }

If you need to update, insert or delete data in database, you can use ExecuteNonQuery() method that SqlCommands provides.

C#
// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");
connection.Open();

// create and execute sql command
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE MY_SCHEMA.Persons SET Height = 185 WHERE firstName='John'";
command.Connection = connection;

int i = (int)command.ExecuteNonQuery();

connection.Close();

This example is not very practical, but it demonstrates how you can use SqlParameters and it will be an introduction to explanation how Visual Studio designer works.

C#
// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

// create SQLCommand
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

// create DataTable with SqlAdapter and load data
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
try
{
    connection.Open();
    int i = da.Fill(dt);

    dt.Rows[1]["Height"] = 187;  // This line simulates user interaction. Imagine user changed height of the person in the first row. 

    SqlCommand updateCommand = new SqlCommand();
    updateCommand.CommandText = "UPDATE MY_SCHEMA.Persons SET Height = @height WHERE id = @id";
    updateCommand.Connection = connection;
    updateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 5, "id"));  // this parameter identifies the record being updated (5 is max length))
    updateCommand.Parameters.Add(new SqlParameter("@height", SqlDbType.Int, 5, "Height"));  // this parameter updates actual value
    
    da.UpdateCommand = updateCommand;
    da.Update(dt);
}
finally { connection.Close(); }