November 13, 2007

C# SQL Update, Insert, Read, Delete

public void UpdateXXXs(XXX[] xxxs)
{
    using (SqlConnection databaseConnection = 
       new SqlConnection(DatabaseConnectionString))
    {
        try
        {
            databaseConnection.Open();
            SqlTransaction trans = 
              databaseConnection.BeginTransaction();

            // One strategy is to delete the whole table 
            // and then recreate with the new rows
            // it depends on how big the table is. This is 
            // not practical with a very large table
            DeleteXXXTable(databaseConnection, trans);
            foreach (XXX xxx in xxxs)
            {
                InsertXXX(databaseConnection, trans, xxx);
            }
            trans.Commit();
        }
        catch (SqlException se)
        {
            trans.Rollback();
            HandleException(se);
        }
    }
}


private void InsertXXX(SqlConnection databaseConnection,
    SqlTransaction trans, XXX xxx)
{
    try
    {
      using (SqlCommand sqlCommand = databaseConnection.CreateCommand())
      {
        command.Connection = databaseConnection;
        command.Transaction = trans;
        command.CommandText = "INSERT INTO [XXXTable] " + 
            "([field1], [field2]) VALUES " + 
            "(@field1, @field2)";
        command.Parameters.AddWithValue("@field1", xxx.field1);
        command.Parameters.AddWithValue("@field2", xxx.field2);
        Debug.Trace("SQL({0})", command.CommandText);
        int rows = command.ExecuteNonQuery();
        if (rows < 1)
        {
            trans.Rollback();
            HandleInsertError(xxx);
        }
      }
    }
    catch (SqlException se)
    {
        trans.Rollback();
        HandleException(se);
    }
}

public XXX[] ReadSomeDatabaseTable()
{
  List xxxList = new List();

  using (SqlConnection databaseConnection = 
    new SqlConnection(SomeConnectionString))
  {
    try
    {
      databaseConnection.Open();

      using (SqlCommand command = new SqlCommand())
      {
        command.Connection = databaseConnection;
        command.CommandText = "SELECT * FROM [XXXTable] ";

        using (SqlDataReader reader = command.ExecuteReader())
        {
          if (reader != null)
          {
            while (reader.HasRows && reader.Read())
            {
              string field1 = reader.GetString(0).Trim();
              string field2 = reader.GetString(1).Trim();
              XXX xxx = new XXX(field1, field2);
              xxxList.Add(xxx);
            }
          }
        }
      }
    }
    catch (SqlException se)
    {
        HandleException(se);
    }
  }
  return xxxList.ToArray();
}

private void DeleteXXXTable(SqlConnection databaseConnection,
    SqlTransaction trans)
{
    try
    {
      using (SqlCommand command = databaseConnection.CreateCommand())
      {
        command.Connection = databaseConnection;
        command.Transaction = trans;
        command.CommandText = "DELETE FROM [XXXTable];";
        command.ExecuteNonQuery();
      }  
    }
    catch (SqlException se)
    {
        trans.Rollback();
        HandleException(se);
    }
}

No comments: